Several answers give you warnings about AddWithValue. These two links are commonly given. The impression that I get is that it's dangerous because AddWithValue has to guess the SQL type of your C# data, leading to the usage of bad query plans. However, in my use case, nearly all of my parameters are ints in C# that are passed to parametrised stored procedures in SQL. I can't imagine many wrong SQL guesses for what type a C# int could be.
Does the above mean that I'm free of the performance risks of using AddWithValue? If so, was I already free because AddWithValue is only dangerous with C# stings? Or was I already free because using stored procedures rather than parametrised queries already saves the day?
ADO.Net always has to guess your parameter type, but yes some types always have a fixed mapping.
The following parameter types can be problematic
(n)(var)char(var)binarydecimalfloatreal(small)money(small)(date)(time)(2/offset)These ones just mentioned are problematic not just because of the parameter type, but also because of the length/precision/scale.
The other types, such as
int, may not match the type you pass in, and you are advised to always use the correct type just in case. Yes, it's true thatintis always mapped toint, but many of the others do not. It's probably best to get into the habit of always specifying it.Yes, in that specific case, but I still wouldn't do it.
No, there are other problematic data types also, as mentioned.
Stored procedures convert what you send back to the correct data type automatically, with a slight overhead. In the case of certain data types there may be a loss of data (precision or certain characters). Eg if you pass an
nvarcharas avarcharthen extended character sets may be lost completely, and procedures do not help here