AddWithValue is evil!

I'm of course talking about SqlParameterCollection.AddWithValue. As with any blanket statement about some kind of technology being evil of course it isn't, but it sure did throw me for a loop today.

First let me give you a little background, for the last couple of years I've had the privilege of using a technology called SPEW, which reads the definition of a Stored Procedure from the Database and Generates a wrapper class which provides a strongly typed interface to a stored procedure. It means that I never had to instanciate my own SqlCommand objects and add Parameters and have to worry about if I spelled it correctly or look up types.

However that was proprietary technology to the company I no longer work for :( so until I can come up with an equivalent, it's manual creation for me.

So when my options for creating a SqlParamater are:

cmd.Parameters.Add("@SomeID", SqlDbType.Int, 4).Value = _someID;
or
cmd.Paramaters.AddWithValue("@SomeID", _someID);

of course I chose the latter, as I'm adding 20 or so parameters at a time on many stored procedures. It seemed to work just fine at the time.

Now AddWithValue uses type inference since they aren't explicitly set. string maps to NVARCHAR, decimal to DECIMAL, bool to BIT, etc.

So I developed an ASP.NET application locally using the built in development web server. When I deployed it to the target IIS server and tested it it gave me a SQL error to the effect of trying to use an NVARCHAR value as type MONEY, try using CONVERT. Eh? I then profiled it, the SQL generated by ADO.NET was perfectly valid and ran without errors. After messing around with the server configuration and re-deploying the site twice without fixing the problem I went back to the code and on a whim converted it to explicitly state the Sql Type. What do you know it worked on the IIS server.

It seems really weird that this would happen, as one would think the Web Hosting environment would have nothing to do with the way ADO.NET operates a SqlCommand aside from connection pooling.

So lesson learned, don't be lazy and avoid AddWithValue.