Difference between Parameters.Add(string, object) and Parameters.AddWithValue
Solution 1
There is no difference in terms of functionality. In fact, both do this:
return this.Add(new SqlParameter(parameterName, value));
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.
Solution 2
The difference is the implicit conversion when using AddWithValue. If you know that your executing SQL query (stored procedure) is accepting a value of type int, nvarchar, etc, there's no reason in re-declaring it in your code.
For complex type scenarios (example would be DateTime, float), I'll probably use Add since it's more explicit but AddWithValue for more straight-forward type scenarios (Int to Int).
Solution 3
Without explicitly providing the type as in command.Parameters.Add("@ID", SqlDbType.Int);
, it will try to implicitly convert the input to what it is expecting.
The downside of this, is that the implicit conversion may not be the most optimal of conversions and may cause a performance hit.
There is a discussion about this very topic here: http://forums.asp.net/t/1200255.aspx/1
Solution 4
When we use CommandObj.Parameter.Add()
it takes 2 parameters, the first is procedure parameter and the second is its data type, while .AddWithValue()
takes 2 parameters, the first is procedure parameter and the second is the data variable
CommandObj.Parameter.Add("@ID",SqlDbType.Int).Value=textBox1.Text;
for .AddWithValue
CommandObj.Parameter.AddWitheValue("@ID",textBox1.Text);
where ID
is the parameter of stored procedure which data type is Int
phadaphunk
Updated on July 09, 2022Comments
-
phadaphunk almost 2 years
I read the MSDN documentation and examples here and I know that the correct syntax for a
Paramters.Add
call is :command.Parameters.Add("@ID", SqlDbType.Int); command.Parameters["@ID"].Value = customerID;
Where you have to specify the Parameter Name, the
SqlDbType
AND the Value with.Value
.Now the correct syntax for a
Parameters.AddWithValue
call is :command.Parameters.AddWithValue("@demographics", demoXml);
Single line and skip the
Type
part.My Question is : How is it that when I do it like this,
command.Parameters.Add("@demographics", demoXml); // .Add method with .AddWithValue syntax
I don't get any compiling error and even weirder, everything seems to work properly when the code is executed ?
-
phadaphunk about 12 yearsThe only thing is you'll have to live with a new warning for each parameter you add this way.
-
David Ruttka about 12 yearsYes, the warning is because this overload is obsolete as marked in the documentation. Also from the documentation, "Use caution when you are using this overload of the SqlParameterCollection.Add method to specify integer parameter values."
-
an phu over 7 yearsIn case someone who is curious like me, is wondering why zero can implicitly convert to an enum but not any other integer. Here is a link to a SO explanation, stackoverflow.com/questions/14950750/…
-
Steve over 4 yearsPerhaps we should integrate this well known answer with some more info about the pitfalls of AddWithValue. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… and docs.microsoft.com/en-us/archive/msdn-magazine/2009/brownfield/…
-
StingyJack almost 3 yearsThe type conversion errors are a difference in functionality. Also when you dont specify the type, it will be inferred. For strings this means a widening conversion to NVARCHAR. If your table is VARCHAR then SQL will convert the value of that column for every row in the table for the query. It wont be an error, and the end result will probably be the same, but functionally its different.