Search code examples
c#.netparametersado.netsqlparameter

Is AddWithValue only dangerous for strings?

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?

Solution

  • 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)binary
    • decimal
    • float
    • real
    • (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 that int is always mapped to int, but many of the others do not. It's probably best to get into the habit of always specifying it.

    Does the above mean that I'm free of the performance risks of using AddWithValue?

    Yes, in that specific case, but I still wouldn't do it.

    If so, was I already free because AddWithValue is only dangerous with C# strings?

    No, there are other problematic data types also, as mentioned.

    Or was I already free because using stored procedures rather than parametrised queries already saves the day?

    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 nvarchar as a varchar then extended character sets may be lost completely, and procedures do not help here