I strongly recommend always using the method that allows you to explicitly set the data type and precision. Yes, this is more work, both up front and if the data type in the database ever changes (which is common when, say, an IDENTITY
column exceeds the range of int
and it is upsized to bigint
).
cmd.parameters.Add("@VehicleId", SqlDbType.Int).Value = Vehicles.VehicleId;
This is simply better practice, since other forms of AddWithValue()
can cause issues. For example, the wrong types will be inferred here:
cmd.parameters.AddWithValue("@intParam", 5); -- tinyint
cmd.parameters.AddWithValue("@intParam", 257); -- smallint
cmd.parameters.AddWithValue("@varchar20", "bob"); -- nvarchar(3)
cmd.parameters.AddWithValue("@varchar20", "frank"); -- nvarchar(5)
In the int
range you're unlikely to come across much in the way of problems, but if you extend this to other types, and are using ad hoc queries as opposed to stored procedure calls with strongly typed parameters, things can get very bad very quickly. As David pointed out, you can get bad plans, for example implicit conversions that can make seeks switch to scans. Allowing the default nvarchar
to be passed through to a varchar
column can cause massive performance issues, as Jonathan Kehayias demonstrates here. And allowing .NET to determine the length of the parameter based on the length of the string can - depending on version of SQL Server and parameterization settings - lead to a different plan for every unique length of string passed in. This won't make those specific queries slower, but they can waste a lot of memory, since there will be a redundant plan stored and used for each length.
This question has been asked in other places:
And Joel Coehoorn has blogged about the type conversion issues too, recommending you stay away from AddWithValue()
(even in cases where you "know" the problem doesn't exist):