LINQ Didn't Replace SQL Queries

by Jon Davis 3. January 2009 04:17

Just observing the obvious here, but about three and a half or so years ago when I heard about C-Omega, which later became LINQ, which then later became a part of C# 3.0, I got the impression that LINQ would perform SQL querying on the fly with integrated SQL-like syntax directly inline with C#, with strong typing and everything. In other words, I thought the language would inherently know ADO.NET/SQL semantics.

It does, but well, no, it doesn't. LINQ isn't an inline SQL translator at all. It is only syntactical sugar for a set of interfaces to a neutral provider to different sources of data. Once the provider is set, LINQ is still not translating SQL inline.

LINQ-to-SQL, LINQ-to-Entities, LINQ-to-NHibernate, LINQ-to-YoMamasDB, all of these use ORM templating and database boilerplating with class-specific generated code to match up the generated SQL.

I'm not against ORM, but it's still too much for smaller (read: tiny) quick-and-dirty hacks. In these cases LINQ (-to-anything) would be overkill galore in the database context. I do say this as a complaint: I still have to use plain old ADO.NET for quick-and-dirty SQL invocation additions, there's no way around it without making it not-so-quick-and-dirty.

Meanwhile, LINQ-to-Objects and LINQ-to-XML are legit. No boilerplating / generated code there. Very sweet.

Tags: , , ,

C#

Multi-line SQL strings in C# and Stored Procs

by Jon Davis 25. July 2007 10:20

Here's a tip. When dealing with SQL strings, try to minimize the multiline delimiters like quotation marks and concatonators, so that you can easily read the SQL string.

Bad: 

            string sBrokerSQL = "SELECT MemberId, UserId, PackageConfigId"
                + " FROM Broker"
                + " WHERE"
                + "     PushToSystem = 1"
                + "     AND UserId IS NOT NULL";

Worse:

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT MemberId, UserId, PackageConfigId");
            sbSql.Append(" FROM Broker");
            sbSql.Append(" WHERE");
            sbSql.Append("      PushToSystem = 1");
            sbSql.Append("      AND UserId IS NOT NULL");

Better:

            string sBrokersSQL = @"
                SELECT MemberId, UserId, PackageConfigId
                FROM Broker
                WHERE
                    PushToSystem = 1
                    AND UserId IS NOT NULL";

With the last sample, you can copy and paste the SQL string right into SQL Server Management Studio (or for SQL 2000 users, into SQL Server Query Analyzer). This formatting is perfectly valid and SQL doesn't care about extra spaces, as long as the SQL itself doesn't have extra spaces in a SQL-quoted value (which should use SqlParameter anyway).  If you need to add concatonated bits, you can either use String.Format, or you can break out and restart a "@" sequence again.

            string sBrokersSQL = @"
                SELECT MemberId, UserId, PackageConfigId
                FROM Broker
                WHERE
                    PushToSystem = " + iPush + @"
                    AND UserId IS NOT NULL";

            string sBrokersSQL = String.Format(@"
                SELECT MemberId, UserId, PackageConfigId
                FROM Broker
                WHERE
                    PushToSystem = {0}
                    AND UserId IS NOT NULL", iPush);
 

Likewise, you can follow the same princple when working with dynamically generated SQL in a stored procedure. When concatonating strings to build a SQL query at runtime, the single-quote identifier supports line breaks and doesn't raise any exception as, say, Visual Basic would. It behaves similarly to C# literals having an "@" prefix.

Currently rated 5.0 by 1 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: , , ,

Software Development


 

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

About the author

Jon Davis (aka "stimpy77") has been a programmer, developer, and consultant for web and Windows software solutions professionally since 1997, with experience ranging from OS and hardware support to DHTML programming to IIS/ASP web apps to Java network programming to Visual Basic applications to C# desktop apps.
 
Software in all forms is also his sole hobby, whether playing PC games or tinkering with programming them. "I was playing Defender on the Commodore 64," he reminisces, "when I decided at the age of 12 or so that I want to be a computer programmer when I grow up."

Jon was previously employed as a senior .NET developer at a very well-known Internet services company whom you're more likely than not to have directly done business with. However, this blog and all of jondavis.net have no affiliation with, and are not representative of, his former employer in any way.

Contact Me 


Tag cloud

Calendar

<<  September 2018  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

View posts in large calendar