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

Use AddWithValue(), but be aware of the possibility of the wrong implicit type conversion.

cmd.Parameters.AddWithValue("@Name", "Bob");

Answer 2

I'd say #1 for sure. But, however Microsoft does it in the data access application block in the enterprise library is the best, esp for SQL server:

http://msdn.microsoft.com/en-us/library/dd203144.aspx

Answer 3

I used to use your option 1:

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

which worked fine, but then I started using .AddWithValue and it is as simple as it gets. It hasn't caused me a problem after many many thousands of uses. Mind you, I almost always pass my classes private variables, so I don't have to worry about the implicit type conversion as much.

Answer 4

It depends on your application. I actually like 2, because I don't lke to have to change my DAO if I change the length of a stored proc parameter. That's just me though. I don't know if there are any performance penalties or anything.

Answer 5

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.