Difference between Parameters Add string object and Parameters AddWithValue

Question

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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

There is no difference in terms of functionality    The addwithvalue method takes an object as the value  There is no type data type checking  Potentially  that could lead to error if data type does not match with SQL table  The add method requires that you specify the Database type first  This helps to reduce such errors   For more detail  Please click here

User · Answer

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

[c#] Difference between Parameters.Add(string, object) and Parameters.AddWithValue

Examples related to c#

Examples related to sql

Examples related to stored-procedures

Examples related to parameters