(3/25/2007 update: Fixed the incorrect parameter name the last two examples)

Parameters without Stored Procedures?

Let's assume that for some reason you are not using Stored Procedures. While I can respect your choice in that regard, that doesn't mean that you cannot still use parameters when constructing your SQL statements at the client. There are few absolutes in this world, but along with "never use RIGHT OUTER JOINS" and "Never trust someone with two first names" there is another: Always use parameters when incorporating user input or any variables into your SQL.

I expect that you know about SQL Injection, so that takes care of the security argument for Parameters -- I won't even get into that here (though that reason alone should be enough to convince any reasonably responsible programmer). My focus will be on the other advantages to using Parameters over concatenating things together into one big string. Much like other best practices which people seem to work very hard to avoid, using parameters makes things much easier on yourself and keeps your code clearer, cleaner and often shorter.

Underutilizing a SqlCommand by creating SQL Strings

Let's consider an example of doing things the wrong way, using VB.NET as our client language. This is the article for you if the following code looks familiar:

First, keep in mind that the CommmandText property that we are setting is a String. Every time we use & (or +) to concatenate expressions while constructing our CommandText property, each of those expressions -- which might be Integers or Dates -- must be first be converted to a String. When doing this, we are not creating a SQL command, but simply a string. We are forcing all of our syntax and all of our input data into one long string expression that SQL Server will simply execute for us.

The example above has two implicit conversions happening: ID is an Integer, and TranDate is a Date, so both must be converted to a String before they can be concatenated with the rest of the CommandText.

Now, what happens when the Integer value of 12 is converted to a String? It becomes "12" -- a one followed by a two. This format is acceptable and pretty universal, so this will never cause a problem. However, to avoid implicit conversions which can lead to errors and sometimes unintended results, we will use the ToString() method of the Integer class to convert the integer to a string explicitly. (Many languages force us to do this or the code will not compile.) Easy enough for the integer, but what happens when TranDate is converted to a string? How is it converted?

Well, if you don't explicitly specify it, then it depends on the computer that the code is executing on. It may come out as mm/dd/yyyy, or dd/mm/yyyy, or some other variant. Even if you explicitly use the DateTime's ToShortDateString() or ToLongDateString() methods, it still depends on settings. Or, you may even wish to explicitly provide the format -- say, force it to be mm/dd/yyyy. Either way, the important thing to realize is: A conversion to a String is happening here, and formatting, by definition, must be involved in this conversion.

So, based on that information, when the CommandText property is evaluated, we get something like this:

And that is what is sent to SQL Server to be executed. As you can see, that is not a valid SQL statement. Let's see what we need to fix.

First, a common error which occurred here is that we did not delimit some our non-numeric expressions with ', which results in syntax errors. Remember, by not using parameters, SQL Server doesn't know or care that "O'Neil" came from a variable; it just knows that those characters have been added to a SQL statement that must be executed. So, we need to handle this ourselves when we construct our CommandText property. One way to do it is to include the ' characters before and after we concatenate a non-numeric values to our CommandText:

A bit harder to read now, and troubleshooting extra or missing ' characters (especially when they are next to " characters) can be quite difficult, but it's necessary when building SQL strings in this manner. Evaluating that expression results in:

Next, you might have noticed that the name of "O'Neil" has an apostrophe, which means that the above SQL will still result in a syntax error. To fix this, we will need to replace all single apostrophes in all of our variables with a double apostrophe ('') while creating our SQL String, so let's add that to our CommandText expression:

Remember, if we had 4 different string variables that we were concatenating, we'd need to do this Replace() on each of them. We could also write a helper function to do the replace and add the delimiters if we wanted.

Handling the ' with our Replace() method now results in:

OK, we are looking good now ... However, what happens if the Date format on the server is dd/mm/yyyy ? The above will cause an error -- there is no month 21. Suddenly, though it did not seem like anything we are doing should depend on date formats at the client or the server, it becomes something that we need to be aware of which we must consider and plan for. So, we need to be sure that we explicitly format our date in a format that is universally acceptable, say yyyy-mm-dd:

Again, we would need to do this to any DateTime variable that we concatenate with our CommandText. Now, we seem to be in good shape. Our SQL String looks pretty good at this point:

Well, congratulations! You now have a working SQL statement, and you've done 5 times as much work as you needed to, dealing with non-numeric delimiters, scrubbing input data, long concatenated string expressions, both client and server date formatting, and implicit and explicit string conversions. Plus, our CommandText expression is pretty darn hard to look at and to work with -- imagine if this DELETE involved more "parameters" or if it was a complicated SELECT with several join expressions? Would this be easy to maintain?

Also, let's recap the journey of our TranDate variable. It started as a DateTime value, got converted to a string in a particular format at the client and then converted back to a DateTime from that string by SQL Server. Surely, there must be a better (and easier) way to pass that DateTime value directly to SQL Server without all this converting and formatting going on.

Using a SqlCommand to create SQL Commands

Remember, earlier I mentioned that if you are writing code in the manner presented above, you are not creating SQL Commands, but rather string expressions. Let's create an actual SQL Command with parameters. We don't need to concatenate anything, our CommandText property is clear and simple and easy to read, and there are no issues with delimiters or date formats or conversions! All we need to do is map our variables to the named parameter placeholders that we specify our CommandText property, just as you would do in a Stored Procedure.

Consider the following code:

Here, we have just added @ID, @Name and @TranDate to our SQL statement as parameters, using the @ syntax that SQL Server uses. Then, using the Parameters() property of the SqlCommand object, we are able to assign them datatypes and values.

The Add() method of the parameters class has many overloads, but this is the one I like to use. You can specify the parameter name and the datatype, and since the Add() method returns an instance of the parameter, you can immediately access the Value property and set it. Note that we are setting the parameter values directly from our variables -- no conversions, no formatting, no delimiters. We don't need to worry about any of that. Because we have created a command and not just a string, SQL Server can now deal directly with parameters values instead of parsing a big, long string.

What makes this even better is when you need to reference a parameter more than once in your SQL; you just repeat the @ParamName as many times as you like in the CommandText, but you only need to set the parameter once. With concatenation, you need to concatenate that string expression over and over.

Even if you need to create big, long complicated SQL statements dynamically with optional filters and joins based on user input or whatnot, it is still very easy to conditionally use parameters:

The above handles situations where you might want to build a WHERE clause dynamically by excluding optional parameters. As you can see, you are still able to assign and set parameters even in that scenario.

Conclusion

I hope this has given you some good things to think about when it comes to using the SqlCommand class to construct dynamic SQL statements. Even though you might not be using stored procedures, you can still make use of strongly-typed parameters in your SQL, which will make your life much easier and your code less prone to data conversion issues and SQL Injection.

ADO.NET and even older ADO and DAO all support creating parameters when constructing commands, even if you do not use stored procedures. I strongly urge you to make use of this feature.

I welcome your feedback and thoughts, especially if you feel that you can provide a situation where concatenating SQL statements with values all converted to delimited strings has an advantage over using parameters.

• Thinking Set-Based .... or not?
• What I learned about SQL from using Access
• How to be an Effective Report Writer
• Sometimes the problem isn't the code. It's the specs.
• Alias Naming Conventions
• Always Use Parameters. Even if you don't use Stored Procedures.
• Programming is a funny thing ....
• SQL Problem Solving: Identify ... and then Simplify!