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.

Lysenko

Answer 1

You can also use AddWithValue(), but be aware of the possibility of the wrong implicit type conversion.

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

Flem

There can be some issues using AddWithValue() – Skilken

This is another way, but the question is the best way, not this one for sure. – Correll

... "but be aware of the possibility of the wrong implicit type conversion". So how is this the best method and the accepted answer?! – Ulster

@JoelCoehoorn +1 dbdelta.com/addwithvalue-is-evil – Film

Answer 2

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.

Cenis

" I have a sudden, burning desire to purge my databases of VARCHAR" - and double the storage space required, and increase the memory required to run queries. Even more of an issue now with hosted DB's. I use NVARCHAR where the Domain requires it. – Flem

@MitchWheat "..and double the storage..." yes, so? The price of storage is in free fall. Phones measure storage in gigabytes. If hosted storage is too expensive then don't use it. Cloud is only a good idea for geographically distributed systems. For everything else it's just buzzword compliance., – Cenis

It's not just the storage size. Anyone who has designed a large and/or high performance datawarehouse would tell you what any good database designer would tell you: use the datatype that best fits the domain type and don't simply "Change all string column types to NVARCHAR" – Flem

Everything in context. Who does indexed lookups of long strings? Database design is a completely separate argument. I did say my databases, which are neither stupendously large nor poorly designed. But I'll amend the statement since there are people out there likely to apply it blindly. – Cenis

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.

Calamine

Answer 4

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

Osteotomy

I use the #1 too because cmd.Prepare() requires the length of variable size data types like nvarchar etc. – Gurule

Answer 5

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.

Iotacism