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?

Answer 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>

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