What's the best method to pass parameters to SQLCommand?

Question

What's the best method to pass parameters to SQLCommand? You can do:

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";

or

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";

or

cmd.Parameters.Add("@Name").Value = "Bob";

It seems like the first one might be somehow "better" either performance-wise or error checking-wise. But I would like to know more definitively.

Answer 1

What's going on in there?

You quote the parameter lists for several overloads of Add. These are convenience methods that correspond directly to constructor overloads for the SqlParameter class. They essentially construct the parameter object using whatever constructor has the same signature as the convenience method you called, and then call SqlParameterCollection.Add(SqlParameter) like this:

SqlParameter foo = new SqlParameter(parameterName, dbType, size);
this.Add(foo);

AddWithValue is similar but takes convenience even further, also setting the value. However, it was actually introduced to resolve a framework flaw. To quote MSDN,

The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.

The constructor overloads for the SqlParameter class are mere conveniences for setting instance properties. They shorten the code, with marginal impact on performance: the constructor may bypass setter methods and operate directly on private members. If there's a difference it won't be much.

What should I do?

Note the following (from MSDN)

For bidirectional and output parameters, and return values, you must set the value of Size. This is not required for input parameters, and if not explicitly set, the value is inferred from the actual size of the specified parameter when a parameterized statement is executed.

The default type is input. However, if you allow the size to be inferred like this and you recycle the parameter object in a loop (you did say you were concerned with performance) then the size will be set by the first value and any subsequent values that are longer will be clipped. Obviously this is significant only for variable length values such as strings.

If you are passing the same logical parameter repeatedly in a loop I recommend you create a SqlParameter object outside the loop and size it appropriately. Over-sizing a varchar is harmless, so if it's a PITA to get the exact maximum, just set it bigger than you ever expect the column to be. Because you're recycling the object rather than creating a new one for each iteration, memory consumption over the duration of the loop will likely drop even if you get a bit excited with the oversizing.

Truth be told, unless you process thousands of calls, none of this will make much difference. AddWithValue creates a new object, sidestepping the sizing problem. It's short and sweet and easy to understand. If you loop through thousands, use my approach. If you don't, use AddWithValue to keep your code simple and easy to maintain.

---

2008 was a long time ago

In the years since I wrote this, the world has changed. There are new kinds of date, and there is also a problem that didn't cross my mind until a recent problem with dates made me think about the implications of widening.

Widening and narrowing, for those unfamiliar with the terms, are qualities of data type conversions. If you assign an int to a double, there's no loss of precision because double is "wider". It's always safe to do this, so conversion is automatic. This is why you can assign an int to a double but going the other way you have to do an explicit cast - double to int is a narrowing conversion with potential loss of precision.

This can apply to strings: NVARCHAR is wider than VARCHAR, so you can assign a VARCHAR to an NVARCHAR but going the other way requires a cast. Comparison works because the VARCHAR implicitly widens to NVARCHAR, but this will interfere with the use of indexes!

C# strings are Unicode, so AddWithValue will produce an NVARCHAR parameter. At the other end, VARCHAR column values widen to NVARCHAR for comparison. This doesn't stop query execution but it prevents indexes from being used. This is bad.

What can you do about it? You have two possible solutions.

• Explicitly type the parameter. This means no more AddWithValue
• Change all string column types to NVARCHAR.

Ditching VARCHAR is probably the best idea. It's a simple change with predictable consequences and it improves your localisation story. However, you may not have this as an option.

These days I don't do a lot of direct ADO.NET. Linq2Sql is now my weapon of choice, and the act of writing this update has left me wondering how it handles this problem. I have a sudden, burning desire to check my data access code for lookup via VARCHAR columns.

---

2019 and the world has moved on again

Linq2Sql is not available in dotnet Core, so I find myself using Dapper. The [N]VARCHAR problem is still a thing but it's no longer so far buried. I believe one can also use ADO so things have come full circle in that regard.