SqlCommand Parameters Add vs. AddWithValue
Use Add
if you want to make all explicit with a little bit more work. Use AddWithValue
if you are lazy. AddWithValue
will derive the type of the parameter of its value, so ensure that it's the correct type. You should, for example, parse a string
to int
if that is the correct type.
There is one reason to avoid Add
: if your parameter type is int
you must be careful with the overload that takes the parameter-name and an object since then another overload is chosen with the SqlDbType
-enum.
From remarks (method overload is even obsolete
now):
Use caution when you are using this overload of the
SqlParameterCollection.Add
method to specify integer parameter values.
Because this overload takes a value of type Object, you must convert
the integral value to an Object type when the value is zero
... If you do not perform this conversion, the
compiler assumes that you are trying to call the
SqlParameterCollection.Add(string, SqlDbType)
overload.
Difference between SqlParameter.Add and AddWithValue
According to the MSDN:
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. Add overload that takes a String and a
SqlDbType enumeration value where passing an integer with the string
could be interpreted as being either the parameter value or the
corresponding SqlDbType value. Use AddWithValue whenever you want to
add a parameter by specifying its name and value.
So the AddWithValue
replaces deprecated overload that create ambiguity.
Difference with Parameters.Add and Parameters.AddWithValue
With Add()
method you may restrict user input by specifying type and length of data - especially for varchar
columns.
.Parameters.Add("@name",SqlDbType.VarChar,30).Value=varName;
In case of AddWithValue() (implicit conversion of value) method, it sends nvarchar value to the database.
Difference between Parameters.Add(string, object) and Parameters.AddWithValue
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.
Parameters.AddWithValue vs Parameters.Add?
The first error is caused because you pass your connstr
(string
) instead of your conn
(OracleConnection
). Change this:
using (OracleCommand cmd =new OracleCommand(cmdstr,connstr))
To this
using (OracleCommand cmd =new OracleCommand(cmdstr,conn))
As for the second one, the OracleParameter
does not seem to need :
. Example use:
OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
WHERE domain_user_name = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", domainUser));
It seems like there is a difference between Oracle.DataAccess.Client
and System.Data.OracleClient
. OracleParameterCollection.AddWithValue
seems to exist in System.Data.OracleClient
. But you use Oracle.DataAccess.Client
.
Difference between adding parameters to stored procedure in SQL Server?
Here are some explanations:
difference between command Add and AddWithValue
Dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE MyDate>@TheDate",conn)
cmd.Parameters.Add("@TheDate",SqlDbType.DateTime).Value="2/1/2007"
vs
cmd.Parameters.AddWithValue("@TheDate","2/1/2007")
"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.
When using
Parameters.Add
- the SqlDbType is known at compile timeWhen using
Parameters.AddWithValue
the method has to box and unbox the value to find out its type.Additional benefits of the former is that
Add
is a bit more code safe
and will assist against SQL injection attacks , code safe in terms
that if you try to pass a value that doesn't match the SqlDb type
defined - the error will be caught in .Net code and you will not have
to wait for the round trip back.
- http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
- http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx
Edit:
example to get an Output-Parameter:
C#
cmd.Parameters.Add(new SqlParameter("@TheNewId", SqlDbType.Int, int.MaxValue));
cmd.Parameters("@TheNewId").Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int theNewID = (int)cmd.Parameters("@TheNewId").Value;
VB.Net
cmd.Parameters.Add(New SqlParameter("@TheNewId", SqlDbType.Int, Int32.MaxValue))
cmd.Parameters("@TheNewId").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim theNewID As Int32 = DirectCast(cmd.Parameters("@TheNewId").Value, Int32)
SqlCommand with parameters accepting different data formats
If the value isn't semantically a string, you shouldn't send it as a string
. The type of the parameter value is important, and influences how it is transmitted and can lead to culture issues (comma vs dot, etc).
If the value is semantically a decimal, then use something like:
string value = "1.23";
var typedValue = decimal.Parse(value); // possible specifying a culture-info
//...
cmd.Parameters.AddWithValue("@p1", typedValue);
Then it should work reliably.
Unrelated, but you can make ADO.NET a lot easier with tools like "Dapper":
connection.Execute("UPDATE MYTABLE SET COL1=@typedValue", new { typedValue });
Related Topics
.Net Core MAChine Key Alternative for Webfarm
Prevent Caching in ASP.NET MVC for Specific Actions Using an Attribute
How to Properly Exit a C# Application
How to Merge Multiple Assemblies into One
How to Apply Orderby on an Iqueryable Using a String Column Name Within a Generic Extension Method
Design - Where Should Objects Be Registered When Using Windsor
Reading 64Bit Registry from a 32Bit Application
Differencebetween a Shared Project and a Class Library in Visual Studio 2015
Error: "Cannot Modify the Return Value" C#
C# Reflection: How to Get Class Reference from String
How to Delete a Line from a Text File in C#
Entering Keys Manually with Entity Framework
Why Does My .Net 4 Application Know .Net 4 Is Not Installed
Web App Blocked While Processing Another Web App on Sharing Same Session
Parameters Passed by Reference Come Back Garbage Using P/Invoke