Difference with Parameters.Add and Parameters.Addwithvalue

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.

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.

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 time

When 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)

Parameters.Add(Overloads) vs Parameters.AddWithValue(Potentially losing characters)

The posted article already contains suggestions about how to avoid your problem.

Basically, you need to use the right overload of .Add:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.add%28v=vs.110%29.aspx

As you can see from MSDN, there's only one overload marked [Obsolete]. I would use the most appropriate .Add overload that fits my needs.

Difference Between Using `NpgsqlParameterT` and `AddWithValue` with `NpgsqlDbType`

After careful consideration of the input we've received from the community, we have decided to go with NpgsqlParameter<T> with an explicit NpgsqlDbType.

While we agree that performance hits are indeed very difficult to notice with both options, the explicitness of declaring what you're passing and what you want it to be converted to has convinced us to go with the slightly more verbose option.

Many thanks to everyone who helped!

Difference between adding MySql Parameters with Add() and AddWithValue()

When the documentation says nothing, consult the source.
These methods are identical (in their implementation):

/// <summary>
/// Adds a <see cref="MySqlParameter"/> to the <see cref="MySqlParameterCollection"/> given the specified parameter name and value.
/// </summary>
/// <param name="parameterName">The name of the parameter.</param>
/// <param name="value">The <see cref="MySqlParameter.Value"/> of the <see cref="MySqlParameter"/> to add to the collection.</param>
/// <returns>The newly added <see cref="MySqlParameter"/> object.</returns>
[Obsolete("Add(String parameterName, Object value) has been deprecated.  Use AddWithValue(String parameterName, Object value)")]
public MySqlParameter Add(string parameterName, object value)
{
    return Add(new MySqlParameter(parameterName, value));
}

public MySqlParameter AddWithValue(string parameterName, object value)
{
    return Add(new MySqlParameter(parameterName, value));
}

http://mysql-connector-net-5.0.sourcearchive.com/documentation/5.0.8.1/parameter__collection_8cs-source.html

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