英文:
Is AddWithValue only dangerous for strings?
问题
这些答案中有关AddWithValue的警告通常与这两个链接 1 2 相关。我的理解是，它被认为是危险的，因为AddWithValue必须猜测你的C#数据的SQL类型，从而导致使用不良的查询计划。然而，在我的用例中，我几乎所有的参数都是C#中的整数，这些整数被传递到SQL中的参数化存储过程中。我无法想象一个C#整数可能被错误猜测为哪种类型的错误SQL。
上述是否意味着我可以放心使用AddWithValue而不用担心性能风险？如果是的话，是因为AddWithValue只有在处理C#字符串时才危险吗？还是因为使用存储过程而不是参数化查询已经足够安全了？
英文:
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?
答案1
得分: 1
ADO.Net始终必须猜测您的参数类型，但是是的，某些类型始终有固定的映射。
以下参数类型可能会有问题
(n)(var)char(var)binarydecimalfloatreal(small)money(small)(date)(time)(2/offset)
刚刚提到的这些类型可能会有问题，不仅因为参数类型，还因为长度/精度/比例。
其他类型，如int，可能与您传递的类型不匹配，建议您始终使用正确的类型以防万一。是的，int始终映射为int，但其中许多类型不会。最好养成始终明确指定类型的习惯。
> 上面的意思是我不再受使用AddWithValue时的性能风险的限制吗？
是的，在这种特定情况下是这样，但我仍然不建议这样做。
> 如果是这样，是不是因为AddWithValue只在使用C#字符串时才危险，所以我已经不受限制了？
不是的，还有其他一些有问题的数据类型，如前文所述。
> 还是说，我已经不受限制，因为使用存储过程而不是带参数的查询已经解决了问题？
存储过程会自动将您发送的内容转换回正确的数据类型，但会稍微增加开销。对于某些数据类型，可能会丢失数据（精度或某些字符）。例如，如果您将nvarchar作为varchar传递，那么可能会完全丢失扩展字符集，并且存储过程在这种情况下无法帮助。
英文:
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 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论