Deprecation in ADO.NET

On the topic of deprecation, I came across this post today from Plip:

Do you use SqlCommand.Parameters.Add("@Name","Value"); ?

Well, you're in for a shock, it's been depricated in ADO.NET 2.0.
When you move to 2.0, you should instead be using: -


While the depricated code will still compile it will throw a compiler warning: -

System.Data.SqlClient.SqlParameterCollection.Add(string, object) is obsolete: Add(String parameterName, Object value) has been deprecated.

Use AddWithValue(String parameterName, Object value).

The depricated method will not work in .NET v-Next (2.0 +).

Now, for the life of me, I could not fathom the reason for this change - even though I am a big fan (some call me neurotic) of naming conventions.  Well, here is the reason, as explained by Pablo Castro of the SQL Server team:

The problem is that both the C# and the VB.NET compilers will expose very weird behavior for this code:

command.Parameters.Add(“@p”, 0);

you may expect this to use the overload that takes an object and assign the value 0 to it, but instead it will pick the overload that takes a SqlDbType as the second parameter! In order to avoid this (and potentially others) ambiguity between the Add(string, sqldbtype) and Add(string, object), we deprecated Add(string, object) and introduced AddWithValue(string, object). In general, having multiple overloads where the distinguishing parameter type is “object” in one of them is a dangerous thing to do.



  • Jackie,

    That's great I had wondered myself but had not gotten round to asking :-)


  • Hi Eric,

    Note that AddWithValue is a strict replacement for Add(string name, object value), it's not an attempt to extend the API.

    I somewhat agree that adding more overloads would save a line of code, but it would also increase the surface area of the ADO.NET API quite a bit (we'd need an Add method for every CLR primitive type, and probably one for every SQL type as well).

    We have certainly thought avoid this (more along the lines of avoiding boxing than for avoiding the extra line), but we decided not to go ahead with the extension in the Whidbey release.

    Also note that if you don't specify the type we'll not introduce implicit conversions on the client; we'll infer the type directly from your value. We'll only do conversions if you give us both a value and a type and they don't match. (conversions also might happen on the server-side if the target type is different, of course)

    I hope this helps clarify the issue.

    Pablo Castro

    Program Manager - ADO.NET Team

    Microsoft Corp.

  • I think

    "In general, having multiple overloads where the distinguishing parameter type is “object” in one of them is a dangerous thing to do."

    should become an FxCop rule now.

Comments have been disabled for this content.