The 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 calling Paramters.Add :

  command.Parameters.Add("@ID", SqlDbType.Int); command.Parameters["@ID"].Value = customerID; 

If you need to specify a parameter name, SqlDbType AND value with .Value .

Now the correct syntax for calling Parameters.AddWithValue :

  command.Parameters.AddWithValue("@demographics", demoXml); 

Single line and skip the Type part.

My question is: how does this happen when I do it this way

  command.Parameters.Add("@demographics", demoXml); // .Add method with .AddWithValue syntax 

I am not getting a compilation error and even weirder, does everything seem to work correctly when the code is executed?

Answer 1

There is no difference in functionality. In fact, both do this:

 return this.Add(new SqlParameter(parameterName, value)); 

The reason they are deprecated in favor of AddWithValue is to add extra clarity, and also because the second parameter is object , which makes it not immediately obvious to some people whose overload was Add and they led to completely different behavior.

Take a look at this example:

  SqlCommand command = new SqlCommand(); command.Parameters.Add("@name", 0); 

At first glance, it looks like it causes an overload of Add(string name, object value) , but it is not. It causes an overload of Add(string name, SqlDbType type) ! This is because 0 is implicitly converted to enumeration types. So these two lines:

  command.Parameters.Add("@name", 0); 

and

  command.Parameters.Add("@name", 1); 

Actually call two different methods. 1 implicitly converted to an enum, so it selects an object overload. Using 0 he selects an overload of the enumeration.

Answer 2

The difference is implicit conversion when using AddWithValue. If you know that your executable SQL query (stored procedure) takes a value of type int, nvarchar, etc., there is no reason to re-declare it in your code.

For scripts of a complex type (an example would be DateTime, float), I will probably use Add because it is more explicit, but AddWithValue for simpler script types (Int to Int).

Answer 3

Without explicit type indication, as in command.Parameters.Add("@ID", SqlDbType.Int); , he will try to implicitly transform the input into what he expects.

The disadvantage of this is that implicit conversion may not be the most optimal conversion and may lead to performance degradation.

This topic is discussed here: http://forums.asp.net/t/1200255.aspx/1

Answer 4

when we use CommandObj.Parameter.Add (), it takes 2 parameters, first a procedure parameter, and the second its data type, but .AddWithValue () takes 2 parameters, first a procedure parameter, and the second a 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 the stored procedure whose data type is Int

Answer 5

There is no difference in terms of functionality

The addwithvalue method takes an object as a value. No data type validation. This could potentially lead to an error if the data type does not match the SQL table. The add method requires you to specify the type of database first. This helps to reduce such errors.

For more information, please click here.