Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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 * ...
          </sql>
share|improve this question
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
7  
"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
6  
@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

6 Answers 6

up vote 5 down vote accepted

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.

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

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

share|improve this answer

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)

share|improve this answer
    
Nice find! (5 more to go) –  Eduardo Molteni Jun 22 '11 at 23:24

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.

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

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
          ]]></s>.Value

Thanks all for your time!

share|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
    
@Aaron: You are not adding value. –  Eduardo Molteni Jun 23 '11 at 14:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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