Storing SQL strings in XML XElement instead of String to preserve line breaks

When it comes to storing SQL in code, most people use strings:

Dim strSql As String = "Select Foo, Bar From FooBarTable Where Something = @Something"

The problem is, when you have very long SQL strings, reading them in code becomes difficult. You can usually split the strings up like this:

Dim strSql As String = "Select Foo, Bar From FooBarTable " & _
                       "Where Something = @Something"

This causes a number of problems:

• You can't copy it to or from SSMS
• It uses an inefficient way (&) of concatenating the string (not that it really matters since your DB is orders of magnitude slower than the string class)

So why not store long SQL strings as a System.XML.Linq.XElement type?

Dim SQL = <SQL>
               Select Foo, Bar From FooBarTable
               Where Something = @Something
          </SQL>

You can call it in code by using SQL.Value.

With this method you can preserve line breaks and for开发者_开发知识库matting to make it both easier to read and easier to copy around.

My question is, basically, did I stumble across a clever way to store multi-line SQL strings in code, or am I totally missing something here? It seems to work in my code, but I'm not sure how efficient it is.

Any thoughts?

I think it's a nice way of placing multi-line string constants in your code. I like it and I doubt it's much less efficient. If you run a performance profiler and discover you've got performance problems, you could always replace them with the string constants, but while you're developing the code, this method is definitely easier when copying and pasting SQL commands.

You should be able to simply use SQL.Value without the .ToString call because the Value property is a string.

this is how is use it... it works very well

    Dim sFieldName As String = "MyField"
    Dim sTableName As String = "MyTable"
    Dim sValue As String = "MyValue"

    Dim xeSQL = <SQL>
        SELECT  <%= sFieldName %> AS Field1
        FROM
            <%= sTableName %> 
        WHERE
            <%= sFieldName %>  = '<%= sValue %>'
            </SQL>