SqlCommand Parameters Add vs. AddWithValue [duplicate]

Question

When should I use Parameters. Add/AddWithValue? In the following MSDN example they use Parameters.Add for int and Parameters.AddWithValue for string

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

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

What is the best to use for datetime

Pammy

cmd.Parameters.Add is deprecated now. Instead use cmd.Parameters.AddWithValue – Haines

@RahulNikate As stated by Bacon Bits below, "cmd.Parameters.Add(String, SqlDbType) is not deprecated. Only cmd.Parameters.Add(String, Object) is deprecated (now obsolete)." – Magnificent

In fact, the recent trend among SQL experts (search relevant keywords for articles) seems to favor using Add(String, SqlDBType) and abandoning AddWithValue due to inefficiency caused by datatype ambiguity. (Yeah, the question is ancient...but I'm here now!) – Mediate

Answer 1

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.

Ulm

cmd.Parameters.Add is deprecated now. Instead use cmd.Parameters.AddWithValue – Haines

@RahulNikate: AddWithValue is also not the best way since it infers the type of the parameter from the paramater value. This often leads to bad execution plans or incorrect conversions. It also doesn't validate the parameter in the first place(f.e. type if Datetime but you pass a String). Only Add(String parameterName, Object value) is deprecated all other overloads of Add not. I prefer the one that takes the parameter-name and the SqlDbType(consider to specify the size if string-type). – Ulm

AddWithValue just avoids the problem mentioned in my answer and has the same functionality as Add(string, object). – Ulm

I disagree with Add being better than AddWithValue -- they can both make mistakes -- so use which ever one is simpler. Add can be used to set up your parameters ahead of time so that you only need to specify the object. Plus, this Parameters.Add("@BEGDATE", SqlDbType.DateTime).Value = myDate. Almost as short as AddWithValue. – Dieball

Note that cmd.Parameters.Add(String, SqlDbType) is not deprecated. Only cmd.Parameters.Add(String, Object) is deprecated (now obsolete). – Golliner