SqlCommand Parameters Add vs. AddWithValue

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

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.