I'm just tired of making a big SQL statement, test it, and then paste the SQL into the code and adding all the sqlstmt.append(" at the beginning and the ") at the end.

It's 2011, isn't there a better way the handle a big chunk of strings inside code?

Please: don't suggest stored procedures or ORMs.

edit Found the answer using XML literals and CData. Thanks to all the people that actually tried to answer the question without questioning me for not using ORM, SPs and using VB

edit 2 the question leave me thinking that languages could try to make a better effort for using inline SQL with color syntax, etc. It will be cheaper that developing Linq2SQL.

Just something like:

dim sql = <sql>
          SELECT * ...
  • 1
    Thats weird, was doing this very same thing today, and had the exact same thought. I was thinking VS could do with some kind of detection (maybe a possible add-in?) to work out if SQL statement has been pasted in and automagicly create the relevant stringbuilder code for you. – Sean Jun 22 '11 at 21:16
  • 8
    "Please: don't suggest stored procedures or ORMs" - uh, why not? You have to make at least some effort to explain why the common-sense answers don't apply to you. – Aaronaught Jun 22 '11 at 21:24
  • Not mentioning SPs or ORM is kinda putting us in a tight spot here.. Actually I find using inline SQL a pretty unprofessional thing to do. But, may be, you should try using micro-ORMs like Simple.Data, Massive or Dapper-dot-net. Or, may be, read your SQL from a file on disk (which really sound weird to me after writing it :S) – Shady M. Najib Jun 22 '11 at 21:28
  • 7
    @Eduardo, if you are so opinionated and picky, then why are you coding in VB.Net? – Job Jun 22 '11 at 21:38
  • 1
    So.. You don't like it? – Eduardo Molteni Jun 28 '11 at 23:04

Stabbing in the VB.NET dark here using google - which is a wicked sharp knife, properly wielded...

dim sqlstmt = <s>Select *
    from dbo.SomeTable
    where SomeField = 1
    and AnotherField = 2</s>.Value

from http://coolthingoftheday.blogspot.com/2008/10/have-long-literals-string-in-vb-forget.html

Caveat: I have not personally tried this syntax. Or even read the entire article.

|improve this answer|||||
  • I've never used XML Literals because you can't use <, > / and other reserved XML characters, but just now found that you can use CDATA to avoid the problem! – Eduardo Molteni Jun 22 '11 at 22:00

How about this:

sqlstmt.append(@"SELECT *
                 FROM Contacts
                 WHERE LastName = 'Johnson'
                   AND ContactId > 42
                 ORDER BY FirstName");

The key is the @ sign. This is C# syntax.

According to VB.NET and C# Comparison no such string literal operator exists in vb.net.

|improve this answer|||||
  • Nice, but I'm on VB.net – Eduardo Molteni Jun 22 '11 at 21:21
  • @Eduardo - I added a note about vb.net – mpenrow Jun 22 '11 at 21:25
  • No, and I think that they can't add it because clashes with XML literals, which is impossible to use. Razor syntax in also broken in VB.net for this reason – Eduardo Molteni Jun 22 '11 at 21:28

If you're using .NET/Visual Studio then the tools for embedding large strings in an app have been around for a very long time.

See the following: Adding and Editing Resources.

One type of resource you can add is a text file. You can put any text you want in there. It's great for SQL statements if for some reason you can't use a stored procedure (although valid reasons for such limitations are few and far between).

You reference the content of a text file resource the same way you reference any other string:

sql = Resources.NameOfSqlQuery;

or in VB.NET:

sql = My.Resources.NameOfSqlQuery;

That's it.

Oh, and just for the record, if you do intend to do this, please resist the temptation to use String.Format on them. Parameterize them as you would any other inline SQL statement.

|improve this answer|||||

If funny how asking a question forces me to find an answer.

I've never used XML Literals because you can't use <, > / and other reserved XML characters, but just now found that you can use CDATA to avoid the problem!

 Dim sqlstmt = <s><![CDATA[                      
             SELECT *
             FROM Contacts
             WHERE LastName = {0}
               AND ContactId > {1}
             ORDER BY FirstName

Thanks all for your time!

|improve this answer|||||
  • 2
    That's (a) grotesque and (b) a SQL injection vulnerability. Unless it's being passed through Linq to SQL, which uses that syntax for parameterized queries - but since you said you refuse to use ORMs, I assume that's headed for a String.Format. Please don't do this. – Aaronaught Jun 23 '11 at 0:28
  • 2
    Works equaly well with paramerized queries. E.g. LastName = @LastName – Sean Jun 23 '11 at 7:36
  • @Aaron: why do assume things? Do you know what I'm building? It's a web app or a desktop app? Do you know if I'm using Dapper.net? If you don't like the question or don't know the answer: GO SOMEWHERE ELSE. – Eduardo Molteni Jun 23 '11 at 13:22
  • @Aaron: ... and .. that is a sample, not the actual code. – Eduardo Molteni Jun 23 '11 at 13:33
  • 1
    Defensive much? Several of us did in fact post valid answers that were not ugly hacks or SQL injection vulnerabilities. If this is, in fact, using dapper.net then (a) it supports stored procs and (b) it uses the same query parameterization syntax as ordinary SQL queries. I don't know why you posted your own answer at all, since this is essentially identical to Steven's answer, with the only change being a SQL injection vulnerability. The question was OK; this answer is not adding value. – Aaronaught Jun 23 '11 at 14:40

I recall a tool I used to use called Paste-As.... Looks like it's been rebranded as SmartPaster, but it allows you to paste whatever is in your clipboard as a StringBuilder (among other things)

|improve this answer|||||

I agree with the approach @aaronnaught uses but with a slight change. Create a file with sql as the extension in the project and put your query text in there. Then add it as a resource (project properties, Resources tab, Add Resource, then go looking for the sql file you created) and reference it in code as My.Resources.Filename:

Dim cmd As New SqlClient.SqlCommand(My.Resources.Query)

Because it's a sql file you can run the query, see results in VS and the text is nicely formatted.

|improve this answer|||||

Which programming language?

In Java, you should not build SQL statements by concatenating parts with a StringBuilder. Use a PreparedStatement instead, where you put ? question marks in the SQL for the parameters, and set the values of the parameters using the various set...() methods of PreparedStatement.

Doing this protects you from SQL injection attacks. When you concatenate together an SQL statement including parameter values manually, you would also have to escape characters manually to prevent SQL injection attacks.

|improve this answer|||||
  • Oh, I see you've edited your question to mention that you're using vb.net. I'll leave my answer here in case anyone finds it useful. – Jesper Jun 22 '11 at 21:25
  • Prepared statements are a pain to debug, in my opinion. I prefer stored procedures. – Job Jun 22 '11 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.