2007.11.19 01:17 PM

Multi-line SQL String Literals in C#

Back in September there were a couple of unrelated blog posts that used literal multi-line SQL strings as examples to illustrate two different points. The first by David Cumps investigated the performance aspects of string concatenation vs. memory allocation. The second by Chinh Do presented a number of Visual Studio 2005 IDE tips and tricks, one of which was the use of regular expressions in the find and replace dialog. In both cases, the syntax they chose to initialize their C# string variables with literal multi-line SQL statements were, in my opinion, more cumbersome than necessary. This may be due to their primary points being about something other than string initialization syntax; however, it seemed like something worth pointing out.

David Cumps uses the following syntax samples in his (very thorough) investigation of string concatenation performance:

string myString = "SELECT column1,"
                + "       column2,"
                + "       column3,"
                + "       column4,"
                + "       column5,"
                + "       column6,"
                + "  FROM table1 t1"
                + "  JOIN table2 t2"
                + "    ON t1.column1 = t2.column1";
return myString;

//---------

string myString = "SELECT column1,";
       myString += "       column2,";
       myString += "       column3,";
       myString += "       column4,";
       myString += "       column5,";
       myString += "       column6,";
       myString += "  FROM table1 t1";
       myString += "  JOIN table2 t2";
       myString += "    ON t1.column1 = t2.column1";
return myString;

//---------

string[] pieces = new string[] {
    "SELECT column1,",
    "       column2,",
    "       column3,",
    "       column4,",
    "       column5,",
    "       column6,",
    "  FROM table1 t1",
    "  JOIN table2 t2",
    "    ON t1.column1 = t2.column1"
};
return String.Concat(pieces);

//---------

StringBuilder builder = new StringBuilder();
builder.Append("SELECT column1,");
builder.Append("       column2,");
builder.Append("       column3,");
builder.Append("       column4,");
builder.Append("       column5,");
builder.Append("       column6,");
builder.Append("  FROM table1 t1");
builder.Append("  JOIN table2 t2");
builder.Append("    ON t1.column1 = t2.column1");
return builder.ToString();

//---------

StringBuilder builder = new StringBuilder();
builder.AppendFormat("SELECT {0},", "column1");
builder.AppendFormat("       {0},", "column2");
builder.AppendFormat("       {0},", "column3");
builder.AppendFormat("       {0},", "column4");
builder.AppendFormat("       {0},", "column5");
builder.AppendFormat("       {0},", "column6");
builder.AppendFormat("  FROM {0} t1", "table1");
builder.AppendFormat("  JOIN {0} t2", "table2");
builder.Append("    ON t1.column1 = t2.column1");
return builder.ToString();

And Chinh Do's regular expression-based formatting of a pasted SQL statement results in the following syntax, which is essentially the same syntax as in Mr. Cumps' first example:

string sql = "select "
+ "  employee_id "
+ "  , first_name "
+ "  , last_name "
+ "  , zip "
+ "from employees "
+ "where "
+ "  employee_id = :id ";

Using their examples, here is the syntax I use to initialize string variables with literal multi-line SQL statements in C#:

string myString = @"
  SELECT column1,
         column2,
         column3,
         column4,
         column5,
         column6,
    FROM table1 t1
    JOIN table2 t2
      ON t1.column1 = t2.column1";

string sql = @"
  select
    employee_id,
    first_name,
    last_name,
    zip
  from employees
  where
    employee_id = :id";

Very simple. No concatenation, no arrays, no StringBuilders, just a single string. The @-quoted string literal syntax does result in the string containing new-line characters at the line breaks, but I've never encountered an ADO.NET provider that had a problem with it. I just assumed that everyone did it this way.


Comments

you are luck,never encountered and ADO.Net Provider that had a problem with it. I encountered it Today.
First Time It's OK. But the Second Time (not same SQLStatement) it encountered. OOP! I don't know why.
but concat .Replace("/r/n","").It's ok. I use vs2005 sqlcommand.executereader().

x16man | 2007.12.05 07:56 AM

x16man,

Good to know. Can you please share with us the .NET version you were using, as well as the version of the SQL Server client you had installed? Also, was there anything special about the nature of the query or perhaps where you had choosen to break the lines?

Thanks.

ewbi.develops | 2007.12.05 08:47 AM

Microsoft VisualStudio 2005 Version 8.0.50727.762 (sp.050727-7600)
Microsoft .Net Framework Version 2.0.50727 SP1

VS2005's international setting is English.

SQL Server 2000 +SP4
The Microsoft SQL Server 2000 Service Manager's Version is
8.00.194 (Chinese Version) and Database is set to Chinese_RPC_CI_AS
the version of the SQL Server client? I can't understand the SQL Server client. :(

the Code :
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = sqlCon;
myCommand.CommandText = @"Select I_Tag_ID,I_Cycle_ID,I_Value_Man
From T_Tag_Hour
Where I_cycle_ID >=43824 And
I_Cycle_ID<52584 And
I_Tag_ID In ('1401001','1401002',...,'1501022','1501023')".Replace("\\r\\n","");
myCommand.CommandText = myCommand.CommandText.Replace("\r\n", "");
SqlDataReader myReader = myCommand.ExecuteReader();

If no the Replace Statement. When it run will occur the Exception.'/r/n' error.

Wish these will help. And wish to tell me What's meaning of the SQL Server Client. :)

x16man | 2007.12.06 02:15 AM

The VS2005 is TeamSuit Version. Sorry.I forget to mention it.

x16man | 2007.12.06 02:17 AM

x16man,

I modified your example some to reduce its width. I attempted to recreate your example as closely as I could using the information you provided in both SQL Server 2000 and SQL Server 2005 but was unable to reproduce the error without the Replace(). I can only imagine that this is related to the use of a localized/Chinese SQL Server client or perhaps with your local Windows language and/or regional settings.

I'm glad you shared this with us. Perhaps it will help someone who runs into the same issue when formatting their literal SQL strings in this way.

ewbi.develops | 2007.12.06 09:59 AM

Heh, indeed, thanks for pointing out. I vaguely remembered some other use of the @"" way besides escaping characters automatically, but it's so rare for me to multi-line concatenate in production code, I forgot all about it.

David Cumps | 2007.12.09 03:31 PM

David, thanks for stopping by. I didn't mention above how much I appreciated your performance analysis post. I know from experience that that kind of post takes a real investment of time and energy. I hope folks make it over to give it a read.

ewbi.develops | 2007.12.09 04:50 PM

Thank you!! Knowing how to do this has made my day.

Have always hated run on to the right single line sql statements so had been doing the tedious multi line concatenation method as at least then I could see my code in all on the page.

Had no idea this could be done.

Jim Holloway | 2010.09.22 02:29 PM

Thank you!! Knowing how to do this has made my day.

Have always hated run on to the right single line sql statements so had been doing the tedious multi line concatenation method as at least then I could see my code in all on the page.

Had no idea this could be done.

Jim Holloway | 2010.09.22 02:29 PM

How do you substitute variables inside the sql string? Like in your example ... how is the employee id substituted?

Alan Lilly | 2012.08.06 01:12 PM

Hi Alan,

That particular SQL came from one of Chinh Do's samples, so I'm not sure what he intended to do with it, but that type of colon-prefixed SQL parameter is consistent with the formatting supported by Oracle's ADO.NET provider. Here's more on the various query parameter formats supported by some different ADO.NET providers:

http://dotnetfacts.blogspot.com/2009/01/adonet-command-parameters.html

ewbi.develops | 2012.08.06 08:04 PM


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00e54f9cb5a78834

Listed below are links to weblogs that reference Multi-line SQL String Literals in C#: