Best way to store SQL in variables

Question

Currently I put my query in a variable like this.

query = @"  select top 1
                u.UserID
            from
                dbo.Users u
            where
                u.SystemUser = 1
                and u.Status = @Status";

The problem with this is that indentation is lost when going to a new line and I have to add it myself.

Does anyone know of a better way? I know stored procedures are a possibility (eliminating this indentation problem), but I'm not sure if they are necessarily better for pure data retrieval.

is it giving u an error in data retrieval then y are u looking for such thing i mean if u require the better and optimized query then fine but how your query looks is of no use it should be quick and less complicated. — Karan Shah, Aug 04 '11 at 12:24
@Karan It's not giving me an error. The problem is one of Visual Studio. I try to keep the query readable, but indentation is lost when pressing the Enter key. I am looking for a better way to use ad-hoc queries, or for an explanation why stored procedures for data retrieval are a better option. — user247702, Aug 04 '11 at 12:25
`(from u in dbo.Users select u.userId where u.SystemUser == 1 && u.Status = @Status).Take(1)` — Eric, Aug 04 '11 at 12:29
@Eric that's a whole lot easier than I expected it to be. I haven't learned about LINQ to SQL yet. — user247702, Aug 04 '11 at 12:31
I don't actually know how you set it up with the database. That's likely to be messier. But I managed to contruct that query with no prior knownledge of LINQ syntax, from a couple of google searches. It may or may not work. — Eric, Aug 04 '11 at 12:33

score 6 · Accepted Answer · answered Aug 04 '11 at 12:40

Ignore the TSQL haters; there's nothing inherently wrong with knowing some TSQL! Anyway, I would approach this by (if I keep your formatting, which isn't my norm - but... meh);

                // your existing code at, say, this level
                var query = @"
select top 1
      u.UserID
from
      dbo.Users u
where
      u.SystemUser = 1
      and u.Status = @Status";

                // some more code at, say, this level

By keeping your TSQL over to the left, any indentation etc is easier to do in the IDE, but it also makes your TSQL shorter, and is easier to debug when looking at a trace, as it isn't bizarrely 30-something characters in. Starting with a newline before the select also helps keep things tidy.

Personally, I also find the disjoint between the code indent and the TSQL indent helps find TSQL - and TSQL is pretty important to me, so this is a good thing. And emphasising that we've just switched "world" (for want of a better term) is not harmful, either.

+1. @" syntax is a lot easier to handle when dealing with SQL in code. — Adam Robinson, Aug 04 '11 at 12:51

score 3 · Answer 2 · answered Aug 04 '11 at 12:27

3

You should at least consider using LINQ. It does have a learning curve, but it will give you the advantage of the query syntax being checked by the compiler.

You do not say whether this is a web-application or not, but if you are getting any input to your query from user input (say from a web url or posted data from a browser), embedding user input in a string before sending to the query engine also risks SQL injection attack more than other methods of executing a query.

Using Entity Framework is another excellent approach. I have used the Code First method recently and it is very elegant. Finally a stored procedure is a good method too.

answered Aug 04 '11 at 12:27

iandotkelly

9,024
8
48
67

It is a Windows Service without user input. The query syntax checking is a good advantage, I'll try to study LINQ a bit deeper for the next project. (Already know a bit of LINQ but not nearly enough). – user247702 Aug 04 '11 at 12:31
2

He is using bound parameter (@Status), so he is not at risk of SQL injection. – Branko Dimitrijevic Aug 04 '11 at 12:32
@Branko, true, but the principal for other readers of the question. Its still the case that query strings are the easiest way of quickly writing SQL (and I stil do it at times myself for a quick and dirty solution) - but its to be discouraged in general. – iandotkelly Aug 04 '11 at 12:34
2

@iandotkelly, it is true that not paying attention when crafting your SQL may open you to SQL injection attacks. However, handcrafted SQL has its uses and should not be dismissed out of hand - and it is certainly neither "quick" (which is bad) nor "dirty" (which is good) when used properly. – Branko Dimitrijevic Aug 04 '11 at 12:44
1

I'm with @Branko here; your anti-SQL stance is frankly bizarre - there are, for someone who knows what they are doing, also many ways to optimize SQL that go far beyond what is possible in LINQ etc. – Marc Gravell Aug 04 '11 at 12:49
I think 'anti-SQL' and 'TSQL hater' is rather taking it too far @Marc Gravel. I have stated an opinion that embedded strings is not the approach that I would recommend. Of course anything 'used properly' is good, but you do get example's like 'string sql = "SELECT * from User Where Name = " + Username;' where the Username is grabbed direct from a textfield or a URL parameter. Sorry if I offended you by pointing out that sql string 'can' be an avenue for SQL injection if used improperly. – iandotkelly Aug 04 '11 at 13:55
When you suggest a stored procedure as a good alternative, it seems that you've reached the point of accepting pretty much anything that does not involve writing SQL strings in your code. In reality, writing SQL strings in your code actually works pretty well and a lot of real-world apps do it. I wouldn't say it's the best method ever, but not something that I'd advise a beginner to never do. – Nate C-K Aug 04 '11 at 13:56
Way to go guys ... quite possibly the most disagreeable discussion I've had here. I've expressed a preference for approaches that are better at assisting the programmer with syntax checking and/or defending some security issues. For the record I am not dissing (T)SQL, or expressing any dislike of SQL or saying that you shouldn't use or understand SQL. I am not saying that you should never do this, nor that many real world apps don't do this. You guys need to lighten up. – iandotkelly Aug 04 '11 at 14:16
Not quite sure why a fairly bland recommendation to consider embedding SQL strings *after* considering one of the current easy-to-use ORMs should be 'bizarrely anti-sql'. There's nothing about the original query suggests that hand-coded SQL is going to be particularly worthwhile. – Will Dean Aug 04 '11 at 15:12
If I offended, then I apologise. Merely, the question is about working with (formatting) TSQL. Jumping from there to an ORM is *steep* is all I was trying to say. Parameters / injection is *done wrong* very risky, yes - as are most things when used incorrectly. – Marc Gravell Aug 04 '11 at 15:57
@Marc Gravell - no problem. The leap from formatting to ORM is a fair point. – iandotkelly Aug 04 '11 at 16:50

Eric · Answer 3 · 2011-08-04T12:35:29.077

1

You could always do this:

query = " select top 1"
      + "     u.UserID"
      + " from"
      + "     dbo.Users u"
      + " where"
      + "     u.SystemUser = 1"
      + "     and u.Status = @Status";

At least that way, your IDE will indent the string, and just not the SQL. If you go this way, you have to be careful to add a leading space to each line.

A better bet would be to go with LINQ:

result = (from
             u in dbo.Users
         select
             u.userId
         where
             u.SystemUser == 1 &&
             u.Status = @Status
).Take(1)

edited Aug 04 '11 at 12:35

answered Aug 04 '11 at 12:25

Eric

95,302
53
242
374

If you're going to that trouble, you should also add newlines. – Nate C-K Aug 04 '11 at 12:29
@Nate: Why? The SQL doesn't care about new lines. All it needs is whitespace between statements. Obviously, `select top 1 u.UserIDfrom ...` wouldn't work, whereas `select top 1 u.UserID from ...` would. – Eric Aug 04 '11 at 12:30
Whilst fixing the indentation issue, it does not address the problem that this is generally a bad practice for creating a query, and at the same time involves the creation of 13 strings in total before the final string is constructed (unless the compiler can play some tricks with it that I am not aware of) – iandotkelly Aug 04 '11 at 12:32
@iandotkelly: I would hope said optimizations would be made. – Eric Aug 04 '11 at 12:33
1

@iandotkelly no, the compiler sees exactly 1 string there; and re creating a query - TSQL has been around a lot longer than LINQ. It isn't "bad practice" to understand TSQL – Marc Gravell Aug 04 '11 at 12:36
@Marc: I'd consider it unwise to use an ORM *without* at least a basic understanding of the underlying model and language. – Adam Robinson Aug 04 '11 at 12:49
@Adam indeed - it is a complex machine; pretending that it isn't is folly. Additionally, the many abstractions also introduce some painful overheads (and, often, sub-optimal queries). I think the fact that we went to the trouble of writing "dapper" illustrates that here at SE/SO: we like our TSQL. A lot. – Marc Gravell Aug 04 '11 at 12:51
-1 This is terrible from the source code versioning point of view. Also, it's bad for readability. @ is the way to go. – Adriano Carneiro Aug 04 '11 at 13:45
@Eric: Because this does not produce a string with indentation, it produces a string with gaps. If you actually want indentation, you need the newlines. My comment here is not judging whether this is a worthwhile exercise, as that's a different question. – Nate C-K Aug 04 '11 at 13:51
@Adrian: In what sense is it bad from a versioning point of view? @Nate: As I read it, @Stijn was concerned with the appearance within the source code, not the indentation of the generated string itself. `` – Eric Aug 04 '11 at 14:15
I thought he wanted a string that actually contains the indentation, so it will display nicely. However, upon rereading the question, it seems that in fact he's just unhappy that the editor doesn't auto-indent when he hits Enter. If that's the case... well, that really an editor problem, not a language problem. – Nate C-K Aug 15 '11 at 15:47

score 1 · Answer 4 · answered Aug 04 '11 at 12:27

1

First off, the formatting of your SQL is important only if human beings are going to see it.

If you really want to preserve the indentation, you could put the string in a resource (use SHIFT+ENTER in resource editor to insert new lines). Thanks to Visual Studio magic, accessing resources is easy (Properties.Resources.*).

If you are using WPF, you could also play with XAML resources.

answered Aug 04 '11 at 12:27

Branko Dimitrijevic

50,809
10
93
167

To clarify, it's not so much the indenation but more so the reasability for myself and/or colleagues. – user247702 Aug 04 '11 at 12:37
If you or your colleagues are ever only going to see the source code, than what you do already is OK. If humans are to see the SQL in the debugger or in some kind of execution log, than this is a small problem, but in my own experience not a large one! I wouldn't worry about it too much. – Branko Dimitrijevic Aug 04 '11 at 12:49
@Branko: I'd much prefer to have the SQL that shows up in, say, SQL Server Profiler be readable. – Adam Robinson Aug 04 '11 at 12:59

score 0 · Answer 5 · answered Aug 04 '11 at 12:25

0

or you can use different ways to get your pure data
you may use

stored procedues LINQ 2 SQL Entity Framework ADO.NET

hardcoded sql syntax is not that best practice.

answered Aug 04 '11 at 12:25

Rami Alshareef

7,015
12
47
75

1

Do you have any reasons to support your last statement please? – user247702 Aug 04 '11 at 12:27
3

Whether "hardcoded sql syntax is not the best practice" is open for debate and not really pertinent to the question. – Branko Dimitrijevic Aug 04 '11 at 12:29
2

I disagree. Simply saying "X is better than Y" without giving a reason does not teach anyone anything. – user247702 Aug 04 '11 at 12:33

Best way to store SQL in variables

5 Answers5