On the Lambda

Programming, Technology, and Systems Administration

On the Lambda

Can we stop using AddWithValue() already?

May 12th, 2014 · 13 Comments · .net, c#, sql

I see code examples posted online all the time that look like this:

cmd.Parameters.AddWithValue("@Parameter", txtTextBox1.Text);

This needs to stop. There is a problem with the AddWithValue() function: it has to infer the database type for your query parameter. Here’s the thing: sometimes it gets it wrong. This especially happens with database layers that deal in Object arrays or similar for the parameter data, where some of the important information ADO.Net uses to infer the type is missing. However, this can happen even when the .Net type is known. VarChar vs NVarChar or Char from strings is one way. Date vs DateTime is another.

The good news is that most of the time, these type mismatches don’t matter. Unfortunately, that’s not the whole story. Sometimes they do matter, and when it matters, it can matter in a big way.

For example, say you have a varchar database column, but send a string parameter using the AddWithValue() function. ADO.Net will send this to the database as an nvarchar value. The database is not permitted to implicitly convert your nvarchar parameter to a varchar value to match the column type for the query. That would be a narrowing conversion that has the potential to lose information from the original value (because you might have non-Latin characters in the parameter), and if that happened the database might produce the wrong query results. Instead, the database will likely need to convert the varchar column to nvarchar for this query (which is a widening conversion that is guaranteed not to lose information). The problem is that it will need to do this for every row in your table.

This conversion can also happen with some other mismatches: For example, date columns may need to be widened to datetime values. And don’t even get me started on what happens if you have a mismatch between a date or number type and a string type. Even with nvarchar or nchar, you may find that the lengths don’t match up, such that a table has to have every value in an nvarchar field of a specific length modified to match a value of a different length.

If that kind of operation sounds expensive to you (potential run-time conversions for data in a table containing possibly millions of rows), you’re right. It is. But that’s only the beginning. These newly converted values now no longer are technically the same value as what is stored in any indexes that may use this column, making those indexes useless for completing your query. Now we’re really hitting below the belt. Index use cuts to the core of database performance. Failing to hit an index can be the difference between a query taking hours or taking seconds, between a query taking minutes or returning instantly. And it all began with AddWithValue().

So what should you do instead? The solution is to be aware of the underlying database type you need to end up with, and then create a query parameter that uses this exact type. Here’s an example using a DateTime database type:

cmd.Parameters.Add("@Parameter", SqlDbType.DateTime).Value = MyDateTimeVariable;

Here’s another example using a decimal(11,4):

cmd.Parameters.Add("@Parameter", SqlDbType.Decimal, 11, 4).Value = MyDecimalVariable;

Note that while this is slightly longer, it’s still a single line of code. That’s it. This simple change to how you define parameters can potentially save significant performance penalties.

Tags:

13 Comments so far ↓

  • Bill Cleveland

    This is much less of a concern if you’re calling a stored procedure, because the procedure parameter will have its own type, and that’s what will be checked against the underlying tables.

  • Grant Winney

    I find myself using the AddWithValue code frequently, and recommending it to others on SO (though typically, it’s to replace SQL-injection-friendly code, so it’s better than nothing).

    Thanks for the heads-up. I had no idea the data type could be incorrectly inferred and I especially had no idea the repercussions of that could be so devastating. Definitely sticking to Parameters.Add().Value from now on.

  • Dirk

    Thanks for posting this article. I had no idea on the potential run-time costs of the conversions.

    However your second example with decimal(11,4) looks wrong. SqlParameterCollection.Add doesn’t have any overload taking these types.

  • apostille stamp

    Thanks for posting this article. I had no idea on the potential run-time costs of the conversions.

  • Justin

    Great article, I was studying a proper way to handle/fight SQL Injection and came from a SO page referring to this page. (http://stackoverflow.com/questions/13276602/c-sharp-using-parameters-addwithvalue-in-sqldataadapter)

    Glad I did look further than the quickest/first fix.

  • Olivier Jacot-Descombes

    In enterprises databases often have several front-ends maintained by different developers and nobody has the overview of the consequences of schema changes. AddWithValue has the advantage that changes to column types do not break the program code. But a safer approach that addresses both aspects of this problem would be to use an o/r-mapper. O/r-mappers usually read the table schema and act accordingly.

  • Jay Asbury

    Gee. .net uses unicode strings. You SHOULD be using nChar or nVarChar anyways.

  • Dondon

    Thank you.. I’m a beginner and it is always good to correct your way/method while in the early stages.

  • Charlotte

    This is helpful, and for my experience, we should use add instead addWithValue. Apart from the reason above, addWithValue send the default data type ‘nvarchar ‘ may bring some other problem, which is very underground. For example, I have set one query, use
    addWithValue but fail, because SqlDbType.NVarChar conversion fails if the string is greater than 4,000 characters, Obviously, i have a params value of more then 4,000 characters, so i need to declare the length like cmd.Parameters.Add(“@myparam”, System.Data.SqlDbType.VarChar, 9000).Value = value;

  • Richie Lee

    AddWithValue works well enough in most cases. The data type sql_variant is one that it doesn’t work well with.

    BTW your solution is the first one that I have found that makes sense. Nicely done.

    • jcoehoorn

      I think you should read the post again. I gave some very specific scenarios that cause problems with more than just sql_variant.

  • Mary O'Brien

    I read the best practices and complied, changed all my code to Add instead of what I thought was the easy way, AddWithValue. I am happy to see the reasons behind the best practice. Trying to catch up after a long absence. Thank you.

Leave a Comment