CODEWITHSUNDEEP
sqlvb.net
BobNoobGuy
BobNoobGuy

Reputation: 1645

How to make a long SQL string look nice

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?

Upvotes: 2

Views: 6104

Answers (2)

Andrew Morton
Andrew Morton

Reputation: 25057

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 &lt; and &amp;, 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"

Upvotes: 4

jods
jods

Reputation: 4591

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).

Upvotes: 1

Related Questions