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

Add comment


(Will show your Gravatar icon)  

  Country flag

biuquote
  • Comment
  • Preview
Loading




 

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

<<  December 2020  >>
MoTuWeThFrSaSu
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar