ASP.NET Choosing Parameters.Add or Parameters.AddWithValue

If you are adding parameters to a SqlCommand object then you can use either Parameters.Add or Parameters.AddWithValue. The Parameters.Add syntax requires you to explicitly specify the DataType of the parameter you are passing, while the Parameters.AddWithValue syntax implicitly attempts to convert the parameters you pass in.

When choosing which method you want to use, you should be aware that although there is no difference in the functionality of either method, but Microsoft added Parameters.AddWithValue because overloads for Parameters.Add resulted in possible confusion of which specific overload was being used. I do find myself using Parameters.AddWithValue for its convenience and clarity, but the fact that you are leaving the datatype for the compiler to implicitly convert is not a good thing.

Some Reasons Why it is a Bad Idea to Implicitly Type Parameters:

Implicitly typing your parameters doesn’t affect your ASP.NET code as much as it affects your actual database.

Implicit Data Conversions Cause Data Errors

You can accidentally introduce data errors if you use the Parameters.AddWithValue syntax to send a date datatype to your database, then you are at the mercy of the database to identify how it will store your date value. This can lead to problems since there are numerous ways of entering dates: mm/dd/yyyy in North America, dd/mm/yyyy in the UK, and yyyy-mm-dd as an upcoming standard. The database will look at the date value of 1/2/2012 that you are sending and can handle the ‘1’ as either the day or as the month.

Implicit Data Conversions Cause Invisible Performance Bottlenecks

As another example, if you pass a String datatype value to the database, then SQL Server defaults the String value to the NVarchar datatype rather than VarChar.  This can result in undesirable performance decreases if your database is expecting a Char or VarChar value and then needs to cast the NVarchar value from your application to a Char or VarChar value.

On a side note: if your database design uses a mix of NVarChar and VarChar columns, then you will see a significant performance hit on queries that join NVarchar table columns with Varchar table columns.

Also note that a disadvantage to NVarchar table columns is that you cannot use indexes on them if the NVarchar column is larger than 450 since the maximum index size is 900 bytes. I just ran a test of this on an instance of SQL Server 2008 and received the error: Changing a column data type results in an index that is too large.

Myth: Explicitly Declared DataTypes Help Against SQL Injection Attacks

Both Parameters.Add and Parameters.AddWithValue methods provide solid protection against SQL Injection attacks. The implicit type conversion of variables via Parameters.AddWithValue does not expose a system to SQL injection any more than the Parameters.AddWithValue method does. However, in my opinion every bit helps when it comes to hardening your code against attacks, so the less ambiguity there is in your code, the safer your code becomes.

Parameter Examples:

1) Using Parameters.Add

Below I’m giving a simple example of how to write a SQL parameter in a single line of code using Parameters.Add.

This is both best practice and convenient. The convenience comes from the statement taking just a single line of code. It is best practice because it 1) explicitly declares the type of data being sent to the database and 2) abstracts the SQL within a Stored Procedure

Dim aDateVariable As String = "1/2/2012"
commandObject.Connection = conn
commandObject.CommandType = CommandType.StoredProcedure
commandObject.Parameters.Add("@aDateParameter", SqlDbType.DateTime).Value = "Value for the Database"

2) Using Parameters.AddWithValue

Here is an example of using Parameters.AddWithValue. As you can see the datatype is not declared and the database needs to identify and convert the parameter when it is received. In terms of security is is good that the statement uses a stored procedure, but not so good that the type of data is not identified.

Dim aDateVariable As String = "1/2/2012"
Dim commandObject As New SqlCommand()
commandObject.Connection = conn
commandObject.CommandType = CommandType.StoredProcedure
commandObject.Parameters.AddWithValue("@aDateParameter","1/2/2012" )

3) Concatenated In-Line Query

Here is the least safe method of  adding your variables directly to a SQL statement. In terms of safety this is completely open to a variety of attacks on your application:

Dim aDateVariable As String = "1/2/2012"
commandObject.Connection = conn
commandObject.CommandType = CommandType.Text
commandObject.CommandText = "SELECT * FROM MyTable WHERE aDateValue=" & aDateVariable

4) In-Line SQL with Parameters

Here is a more secure in-line SQL statement that uses parameters. Although in-line SQL by nature is less secure than using stored procedures, this syntax is significantly more secure than simply concatenating the values into the statement.

Dim aDateVariable As String = "1/2/2012"
commandObject.Connection = conn
commandObject.CommandType = CommandType.Text
commandObject.CommandText = "SELECT * FROM aTable WHERE aDateValue=@aDateParameter" 
commandObject.Parameters.Add("@aDateParameter", SqlDbType.DateTime).Value = "1/2/2012"