It’s generally seen as a good rule of thumb to avoid writing inline SQL and it’s a rule I try to observe - the obvious pitfall being it isn’t parsed until runtime. Stored procedures can mitigate against this but I tend to avoid - with cached queries the advantage of compilation isn’t there anymore and I find they add a layer of abstraction I don’t want. So sometimes I find myself getting down and dirty with a bit of inline SQL.

Anyhow, I’ve noticed my style of writing inline SQL has changed over the years. The first real world programming I did was in VBA and like a lot of people at the time I employed the Hungarian convention. And impossibly long SQL statements would all be one long continuous string seperated on different lines with underscores.

1
2
3
4
5
Dim strSQL As String

strSQL = "SELECT ID, FirstName, LastName, CreatedDate, MofidifedDate " _
  "FROM ThisIsAnImpossiblyLongUsersTableName " _
  "WHERE ID = " & intID

With the advent of .NET the Hungarian convention went out of fashion. Code was more elegant and I found this was enhanced by concatenating the SQL string (I also learned to use a command object properly!).

1
2
3
4
5
Dim sql = String.Empty

sql &= "SELECT Id, FirstName, LastName, CreatedDate, MofidifedDate "
sql &= "FROM ThisIsAnImpossiblyLongUsersTableName "
sql &= "WHERE Id = @Id "

And this approach served me for quite a while until recently. I don’t do much VB.NET these days. In fact I code less on the .NET stack than I used to but when I do it’s always C#. But I have legacy applications to support and sometimes there is a requirement for VB.NET. One of the things I like about C# is the ability to write multiline strings using the @ character.

1
2
3
4
5
var sql = @"

  select Id, FirstName, LastName, CreatedDate, MofidifedDate
  from ThisIsAnImpossiblyLongUsersTableName
  where Id = @Id ";

I found out you can do something similar to that above in VB.NET using Linq and XML. Import both the System.XML and System.XML.Linq namespaces and you can do the following.

1
2
3
4
5
6
7
Dim sql = <sql>

  select Id, FirstName, LastName, CreatedDate, MofidifedDate
  from ThisIsAnImpossiblyLongUsersTableName
  where Id = @Id
  
  </sql>.Value

I didn’t realise you could write inline XML and I think it’s pretty cool - I’ve tried to do the same in C# and not been able to (if someone knows different please let me know). I find this really handy as I can now write very readable SQL statements which can be copied and pasted into SSMS easily if need be.