How to make a long SQL string look nice

Question

I use the command below to run a SQL query.

Dim SQLString as String
da.SelectCommand = New SqlCommand(SQLString, Conn)

Often the SQLstring is really long. I use following method:

SQLString = " Select *
SQLString += vbCrLf & " From"
SQLString += vbCrLf & " Student"

But when you debug and get SQLString in immideate window, and paste it to SQL Server Management Studio (SSMS) Tools, it becomes one line and not formatted/indented nicely.

What's the trick for this so I can keep the string formatted nice?

Use C#. (couldn't resist). You can use the verbatim string literal in C# .NET to go over multiple lines (even though that's not its main purpose). — PeteGO, Mar 27 '14 at 22:59
possible duplicate of [Multiline strings in VB.NET](http://stackoverflow.com/questions/706382/multiline-strings-in-vb-net) — miroxlav, Mar 27 '14 at 23:46
How about using [StringBuilder](http://msdn.microsoft.com/en-us/library/ms172824.aspx) — huMpty duMpty, Mar 28 '14 at 14:52

Andrew Morton · Accepted Answer · 2016-01-22T11:29:15.967

4

You can use an XML literal in VB.NET to create your SQL statement:

        Dim sql = <sql>
SELECT *
    FROM Student AS S
    JOIN Class AS C
        ON S.Id = C.Id
</sql>.Value

Note that if you want to use the < or & symbols then you should type < and &, respectively, to comply with XML.

The spaces that you see in the XML are literally in the resulting string, so it wouldn't really matter if you had it formatted as

Dim sql = <sql>
            SELECT *
                FROM Student AS S
                JOIN Class AS C
                    ON S.Id = C.StudentId
          </sql>.Value

(as long as there isn't a literal string in the SQL which goes over more than one line).

I don't know who should be attributed as the first person to realise this can be done.

UPDATE FOR VB version 14 (the one that comes with VS2015): Multi-line string literals are now implemented, so you can use

Dim sql = "SELECT *
From Student As S
Join Class As C
    On S.Id = C.Id"

edited Jan 22 '16 at 11:29

answered Mar 27 '14 at 22:40

Andrew Morton

24,203
9
60
84

Isn't instanciating an `XElement` just to work around the VB syntax a bit excessive? Also, I haven't tried but are the line breaks properly preserved? Since this is not a `CDATA` block I would guess not? The need to escape `<` is also _highly_ annoying when writing SQL. – jods Mar 27 '14 at 23:10
@jods It may be excessive, but it's the computer doing the work for you. Yes, escaping `<` is annoying. TBH, I just do everything with stored procedures where possible. – Andrew Morton Mar 27 '14 at 23:18
Yeah... just not feeling good about referencing `System.Xml.Linq` and having all the instanciation and processing of `XElement` happening at runtime for what should just be a string constant (i.e. free). – jods Mar 27 '14 at 23:37
1

@jods as a programmer my job is to write code so the user has a good experience. This sometimes means me working harder so the code runs faster... – Adam Heeg Aug 05 '15 at 20:35
in VS2015 having this is implemented can be one of a good reason to upgrade :) – BobNoobGuy May 04 '16 at 21:19

jods · Answer 2 · 2014-03-27T23:11:05.153

VB.NET does not really have a clean way to do that (yet). C# has verbatim strings but AFAIK it's not in VB.

I usually do this:

Dim sql = "SELECT * " &
          "FROM Table " &
          "WHERE x > 4 " &
          "ORDER BY 1 DESC"

It's not too bad to read in the code, but as you wrote it's ugly in the debugger -- all on one line. You can still copy-paste from the debugger to a SQL editor and use a code formatting feature, not great :(

EDIT Just in case you can code in C# rather than VB, you can do that and it's nice:

string sql = @"
SELECT *
FROM Table
WHERE x > 4
ORDER BY 1 DESC";

The key is the @ before the string. Now you have nice code and good formatting in the debugger (line breaks are preserved).

How to make a long SQL string look nice

2 Answers2