StrataFrame Forum

Writing SQL command text in BO

http://forum.strataframe.net/Topic20122.aspx

By Edhy Rijo - 10/15/2008

What is the cleanest, suggested way to write SQL commands in code in a BO or a class?

I have the following code which works, but it may be hard to either debug or understand at a later time:

Dim cmd As New SqlCommand()
cmd.CommandText = "SELECT DISTINCT dbo.SC_Appliances.* " & _
"FROM dbo.SC_Appliances " & _
"RIGHT OUTER JOIN dbo.ServiceCalls ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls) " & _
"WHERE " & _
"(dbo.ServiceCalls.Status NOT IN (" & ServiceCallStatus.Done & "," & ServiceCallStatus.Void & ")) AND " & _
"(dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);"
cmd.Parameters.AddWithValue("@pServiceCallsScheduleDate", serviceCallsScheduleDate).SqlDbType = SqlDbType.DateTime
Return cmd

Basically that will translate to this:

SELECT DISTINCT dbo.SC_Appliances.* 
FROM dbo.SC_Appliances 
RIGHT OUTER JOIN dbo.ServiceCalls ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls) 
WHERE 
(dbo.ServiceCalls.Status NOT IN (2,3)) AND 
(dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);
By Trent L. Taylor - 10/15/2008

Well, this comes down to personal preference really.  When it starts getting very long (even as long as this) I will create a stored procedure.  It is much cleaner and easier to maintain.  If I will have a small and quick query than I will put it in code, but otherwise I have gotten into the habit of creating a sproc as it is actually easier to deal with and requires less changes in the code base.  For example, the other day there was an issue with a query which took a little bit of detective work to figure out, but ultimately all I had to do was change the query and redeploy the sproc...and I didn't have to change anything inside of the EXE or code base.
By Edhy Rijo - 10/15/2008

Got you, thank!
By Greg McGuffey - 10/15/2008

To add to this a bit, I've a suggestion about how to deal with long strings (which you might already know). If I were to put the above sql into code I'd use this technique:



Dim sqlBuilder As New System.Text.StringBuilder(255)

With sqlBuilder

  .AppendLine("SELECT DISTINCT dbo.SC_Appliances.*")

  .AppendFormat("FROM {0}", Me.TableName).Append(ControlChars.NewLine)

  .AppendLine("  RIGHT OUTER JOIN dbo.ServiceCalls")

  .AppendLine("    ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls)")

'-- Convert enums to integers when using AppendFormat, or you'll get the enum value's name

  .AppendFormat("WHERE (dbo.ServiceCalls.Status NOT IN ({0},{1}))", CInt(ServiceCallStatus.Done), CInt(ServiceCallStatus.Void)).Append(ControlChars.NewLine)

  .AppendLine("  AND (dbo.SC_Appliances.ServicedDate = @pServiceCallsScheduleDate);")

End With



'-- If you debug here, the SQL string will be nicely formatted.

cmd.CommandText = sqlBuilder.ToString()





This isn't really suggesting that you put long SQL statements in code (Trent's suggestion is a good one). Rather just a suggestion about using StringBuilder instead of the concatenation operator. Each of the strings concatenated becomes an immutable variable, so StringBuilder is easier on memory (only one string created when you call ToString). Also, note that you can use format strings via AppendFormat. Note that using AppendFormat does a ToString on any of the replacement values, thus when the value is an enum, you need to convert to an integer first, other wise you'll get the name of the enum (resulting in SQL like 'NOT IN (Done, Void)' instead of 'NOT IN (2, 3)'). Finally, if you put a break in code after its been built, and look at the result, it will be nicely formatted if you need to debug it.



Again, I'm not suggesting you put long SQL into your code. I've just really come to appreciate this technique of handling long strings. Cool
By Trent L. Taylor - 10/15/2008

Good comments, Greg.  I agree.
By Edhy Rijo - 10/15/2008

Wow Greg, thanks a lot for taking the time to provide this sample with my code, very much appreciated.

I understand the limitations and the advantages to use an SP, but I find this method a lot cleaner than what I was using for this kind of quick SQL with joins, etc.  In my learning process the idea is to make it work with the SQL in code and then move all those SQL code to more generic store procedures.

By Greg McGuffey - 10/15/2008

My pleasure Edhy.



I've also modified a tool like this to make life a bit easier:



http://www.knowdotnet.com/articles/TextToStringBuilder.html



Now I can write the SQL if I need to in SSMS, then paste into a tool like this and have it output my string builder. Saves some typing.