SQL Query in VB.NET code

Question

I have a project where we use the SQL queries directly in the code.

Dim strSQL As String = "SELECT P.NAME, C.ID, C.NAME" +
         "FROM PERSONS P " + 
"INNER JOIN CITIES " +
"ON P.IDCITY = C.ID" +
"WHERE P.ID = {0}"

Is a little bit ennoying to format such a query using "" and "+" Is there a way to write the script "As Is" (from a SQL file), without reformating it?

I'd use

strSQL = My.Resources.SELECT_PERSON_WITH_CITY

but in debug I can't see/modify the original query.

I know, I know, this is not a best practice using SQL directly, but however, I use what I have.

PS.

As Conor Gallagher mentioned, in C# there is a way to achieve this:

string x = @"
  my 
  name 
  is {0}";
string y = string.Format(x, "jimmy");

Does anyone know a VB.NET equivalent?

Solution 1

I believe the answer in vb.net is actually No, you can't create strings in vb.net over multiple lines without using " and + etc. In C# you can span strings over multiple lines using the "@" escape character. Bit like this:

string x = @"
  my 
  name 
  is {0}";
string y = string.Format(x, "jimmy");

But VB.Net doesn't have an equivalent.

Update

As per D..'s comment below, Xml Literals could solve this problem. See http://www.codeproject.com/Articles/292152/Mutliline-String-Literals-in-VB-NET

Basically, as per the link, you can do clever stuff like this using Xml Literals:

Dim sourceText As String =
    <string>
        Imports Microsoft.VisualBasic
        Imports System
        Imports System.Collections
        Imports Microsoft.Win32
        Imports System.Linq
        Imports System.Text
        Imports Roslyn.Compilers
        Imports System.ComponentModel
        Imports System.Runtime.CompilerServices
        Imports Roslyn.Compilers.VisualBasic

        Namespace HelloWorld
          Module Program
            Sub Main(args As String())
              Console.WriteLine("Hello, World!")
            End Sub
          End Module
        End Namespace
    </string>

Update 2

Been playing around with this a bit. XmlLiterals are actually really nice! If you wanted to add variables into the loop you can do stuff like this:

Dim x As String
Dim y As String
y = "Jimmy"

x = <string>
        Select *
        From Person
        Where Person.FirstName = <%= y %>
    </string>

Solution 2

You must use SQLParameters, not format your query manually!!

Dim con AS New SqlConnection("...")
Dim cmd As New SqlCommand(
    "SELECT P.NAME, C.ID, C.NAME FROM PERSONS P " +
    "INNER JOIN CITIES ON P.IDCITY = C.ID " +   
    "WHERE P.ID = @id", con)
cmd.Parameters.AddWithValue("@id" , your_id)

Solution 3

I use brijpad to copy Long SQL query, paste in left side, Advance tab > click on text to VB.NET,It will generate equivalent VB.NET string and use it in our code.

If there is any parameter, I use 'custom format' to generate command parameters syntax.

mycmd.Parameters.AddWithValue("@Par" , parVal)

Solution 4

http://www.dpriver.com/pp/sqlformat.htm

The best side code formatter for me. You just paste the long SQL Statement and it will generate ready-to-copy codes for vbnet. It does not only support VBNet but also C#, PHP, Delphi, and etc.

Solution 5

You should really use a parameterised query not raw SQL in your code but to give a possible answer to your question:

You could use a StringBilder class to add improved formatting:

    Dim sb As New StringBuilder()
    With sb
        .Append("SELECT P.NAME, C.ID, C.NAME")
        .Append("  FROM PERSONS P ")
        .Append("  INNER JOIN CITIES ")
        .Append("    ON P.IDCITY = C.ID")
        .Append("  WHERE P.ID = {0}")
        Debug.WriteLine(sb.ToString)
    End With

I have created my own DataAccess class which expands on this and allows me to concatenate queries and adds a space at the end of each line in case I forget to add one (as in your example):

Class DataAccess
    Private _queryString As New StringBuilder(String.Empty)

    Public Sub QryAdd(ByVal query As String)
        _queryString.Append(query.TrimEnd + " ")
    End Sub

    Public ReadOnly Property QryStr As String
        Get
            Return _queryString.ToString.TrimEnd()
        End Get
    End Property
End Class

View more solutions