Monday, November 3, 2014

Coding: SqlCommand Add vs AddWithValue

I ran into the issue but had simply converted it to a string value which worked for me. Later I was cleaning up my code and realized that Add was deprecated then out of curiosity looked up the difference. I thought this was interesting because on how 0 and 1 were implicitly converted differently.


vcsjones (@stackoverflow):

The reason they deprecated the old one in favor of AddWithValue is to add additional clarity, as well as because the second parameter is object, which makes it not immediately obvious to some people which overload of Add was being called, and they resulted in wildly different behavior.
Take a look at this example:
 SqlCommand command = new SqlCommand();
 command.Parameters.Add("@name", 0);
At first glance, it looks like it is calling the Add(string name, object value) overload, but it isn't. It's calling the Add(string name, SqlDbType type) overload! This is because 0 is implicitly convertible to enum types. So these two lines:
 command.Parameters.Add("@name", 0);
and
 command.Parameters.Add("@name", 1);
Actually result in two different methods being called. 1 is not convertible to an enum implicitly, so it chooses the object overload. With 0, it chooses the enum overload.

Reference

http://stackoverflow.com/questions/9999751/difference-between-parameters-add-and-parameters-addwithvalue