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 · 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

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

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

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

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