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 ?
This question is related to
c#
sql
stored-procedures
parameters
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
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
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
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).
Source: Stackoverflow.com