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 ?
Here, Add forces the conversion from string to date as it goes into the parameter. AddWithValue would have simply passed the string on to the SQL Server. Using Parameters. AddWithValue - you explicitly set the sqldb.
AddWithValue replaces the SqlParameterCollection. Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.
SqlDbType: It is used to set the SQL Server Datatypes for a given parameter. ParameterName: It is used to specify a parameter name. Direction: It is used for setting the direction of a SqlParameter. It is Input or Output or both (InputOutput).
C# SqlParameter is a handy feature allows you to safely pass a parameter to a SqlCommand object in . NET. A security best practice when writing . NET data access code, is to always use parameters in SqlCommand objects (whenever parameters are required of course).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With