2

When I have to put SQL strings in code I like to it become like that (example):

VB.NET
"  Select                      " + vbCrLf + _
"    Id,                       " + vbCrLf + _ 
"    Name                      " + vbCrLF + _
"  from                        " + vbCrLF + _
"    Customers                 " + vbCrLF

C#
var vbCrLf = "\r\n"; 
var SQL = 
"  Select                      " + vbCrLf + 
"    Id,                       " + vbCrLf +  
"    Name                      " + vbCrLF + 
"  from                        " + vbCrLF + 
"    Customers                 " + vbCrLF ;

That way have some advantages:

  • Since each like gets an NewLine sequence, when I have to read SQL on a server trace I don't have to reformat to get an easy reading
  • Because each line is surrounded in spaces, there's no risk of string concatenations generate an invalid SQL (something in the line of select Id from Customerswhere Id =1)
  • If I have to review it on an separated SQL application (SSMS, IBExpert, SQLDBX, etc) I can block select easily.

It's somewhat tedious to make it by hand. There's some plugin/extension that already do that? I looked on VS setting, but didn't find something like that.

EDIT: All examples are made-up. No actual code here and there's no need to preach me on parameterized queries either, as I already use them.

  • What language is that? Visual Basic? – Nicolas Raoul Oct 6 '16 at 10:27
  • In that example yes, VB.NET. But it could be C#, it's just to give an clear example of what kind of formatting I want. – Fabricio Araujo Oct 6 '16 at 15:33
  • 1
    I like the vertical alignment, but I would not inluded all thsoe trailing spaces in the quesy string, but, rather, in the code E.g not " Select <lots of spaces here> " + vbCrLf + _ bur " Select " <lots of spaces here> + vbCrLf + _. It is theoretcially possible that a compelx query might result in an input line which is too long, using your way. Plus, of course, the longer strin will take infintesimally longer to process ;-) – Mawg Apr 20 '17 at 7:09
  • 1
    @Mawg if the query is anywhere near the query limits of the db engine, I would convert it on a stored procedure. – Fabricio Araujo Apr 20 '17 at 21:17
  • A very good point (+1); it's just that those spaecs in the quesry string any me (YMMV) :-) But, I do like the vertical alignment :-) – Mawg Apr 21 '17 at 6:53
2

You might consider an online SQL builder as well. http://www.buildmystring.com will build your sql strings and has quite a few spacing and formatting options.

Cheers.

  • But, then you have to give that website access to your databse, in order for it to know the schema. It calls for a bit of trust to give user & password of your databse to a website. – Mawg Apr 20 '17 at 8:14
  • @Mawg Have you followed the link? It doesn't need anything like that. The problem here is FORMATTING. Forget connections, passwords, etc. The site just formats the string, And the options are very near to what I asked. – Fabricio Araujo Apr 20 '17 at 21:15
  • Sorry, my mistake. I somehow thought that you wnated a query builder :-( – Mawg Apr 21 '17 at 6:59
  • No, I didn't. I can build my queries and have some good query builders on the tools I use daily. – Fabricio Araujo Apr 24 '17 at 0:57
  • Although I would prefer an local program, the "Build my String" site gets very near what I need. So gets the check. – Fabricio Araujo Apr 24 '17 at 19:16
0

I will suggest you to use ReSharper: https://www.jetbrains.com/resharper/

With ReSharper, when you are writing strings, if you press enter it will move you to a new line and it will add the necessary quoutes.

Let me give you an example. So, let's say you are writing:

var dbRequest = "SELECT * FROM table WHERE table.id = 1";

If you were to press ENTER key before 'WHERE', ReSharper will automatically format it like this:

var dbRequest = "SELECT * FROM table " +
                "WHERE table.id = 1";

Also, I highly recommend you to use parameters instead of + vbCrLf + because it will be a lot easier to change the code later, if your parameter changes.

  • And binbd those query parameters "WHERE table.id = :table_id" – Mawg Apr 20 '17 at 8:12
  • W.r.t: "Also, I highly recommend you to use parameters instead of + vbCrLf + because it will be a lot easier to change the code later, if your parameter changes." This is not about PARAMETERIZATION, it's about FORMATTING. Have you read the question thoroughly? It's about formatting to make reading server traces less laborious to read. – Fabricio Araujo Apr 20 '17 at 21:22

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.