将参数传递给 SQLCommand 的最佳方法是什么？
将参数传递给 SQLCommand 的最佳方法是什么？ 你可以这样做：
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";
或
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";
或
cmd.Parameters.Add("@Name").Value = "Bob";
似乎第一个可能在某种程度上“更好”，无论是性能方面还是错误检查方面。 但我想更明确地知道。
What's the best method to pass parameters to SQLCommand? You can do:
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";
or
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";
or
cmd.Parameters.Add("@Name").Value = "Bob";
It seems like the first one might be somehow "better" either performance-wise or error checking-wise. But I would like to know more definitively.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
里面发生了什么事？
您引用
Add的多个重载的参数列表。 这些是直接对应于 SqlParameter 类的构造函数重载的便捷方法。 它们本质上使用与您调用的便捷方法具有相同签名的任何构造函数来构造参数对象，然后像这样调用SqlParameterCollection.Add(SqlParameter)：AddWithValue类似，但是进一步方便了，还设置了值。 然而，它实际上是为了解决框架缺陷而引入的。 引用MSDN的话，SqlParameter类的构造函数重载只是为了方便设置实例属性。 它们缩短了代码，对性能影响很小：构造函数可以绕过 setter 方法并直接对私有成员进行操作。 如果有差别的话，也不会太大。我应该怎么办？
请注意以下事项（来自 MSDN）
默认类型是输入。 但是，如果您允许像这样推断大小，并且在循环中回收参数对象（您确实说过您关心性能），那么大小将由第一个值设置，任何后续值更长将被设置被剪掉了。 显然，这仅对于可变长度值（例如字符串）才有意义。
如果您在循环中重复传递相同的逻辑参数，我建议您在循环外部创建一个 SqlParameter 对象并适当调整其大小。 增大 varchar 的大小是无害的，因此如果它是 PITA 以获得确切的最大值，只需将其设置为比您预期的列大即可。 因为您是回收对象而不是为每次迭代创建一个新对象，所以即使您对尺寸过大感到有点兴奋，循环期间的内存消耗也可能会下降。
说实话，除非您处理数千个呼叫，否则这些都不会产生太大影响。
AddWithValue创建一个新对象，回避了大小调整问题。 它简短、甜蜜且易于理解。 如果您循环数千次，请使用我的方法。 如果不这样做，请使用AddWithValue来保持代码简单且易于维护。2008 年已经是很久以前的
事了，自从我写这篇文章以来，世界已经发生了变化。 有新的日期类型，而且还有一个问题一直没有出现在我的脑海中，直到最近的一个日期问题让我开始思考扩大日期的含义。
对于那些不熟悉这些术语的人来说，加宽和缩小是数据类型转换的性质。 如果将 int 分配给 double，则不会损失精度，因为 double 更“宽”。 这样做总是安全的，因此转换是自动的。 这就是为什么您可以将 int 分配给 double，但反过来，您必须执行显式强制转换 - double 到 int 是一种缩小转换，可能会导致精度损失。
这适用于字符串：NVARCHAR 比 VARCHAR 更宽，因此您可以将 VARCHAR 分配给 NVARCHAR，但反之则需要强制转换。 比较之所以有效，是因为 VARCHAR 隐式扩展为 NVARCHAR，但这会干扰索引的使用！
C# 字符串是 Unicode，因此 AddWithValue 将生成 NVARCHAR 参数。 在另一端，VARCHAR 列值扩展为 NVARCHAR 以进行比较。 这不会停止查询执行，但会阻止使用索引。 这不好。
你能为这个做什么？ 您有两种可能的解决方案。
放弃 VARCHAR 可能是最好的主意。 这是一个简单的更改，其后果可预测，并且可以改善您的本地化故事。 但是，您可能没有这个选择。
这些天我没有做很多直接的 ADO.NET 工作。 Linq2Sql 现在是我选择的武器，编写此更新的行为让我想知道它如何处理这个问题。 我突然强烈地希望检查我的数据访问代码以通过 VARCHAR 列进行查找。
2019 年，世界再次发生了变化。Linq2Sql
在 dotnet Core 中不可用，所以我发现自己在使用 Dapper。 [N]VARCHAR 问题仍然是一个问题，但它不再被埋藏起来。 我相信人们也可以使用 ADO，所以在这方面事情又回到了原点。
What's going on in there?
You quote the parameter lists for several overloads of
Add. These are convenience methods that correspond directly to constructor overloads for theSqlParameterclass. They essentially construct the parameter object using whatever constructor has the same signature as the convenience method you called, and then callSqlParameterCollection.Add(SqlParameter)like this:AddWithValueis similar but takes convenience even further, also setting the value. However, it was actually introduced to resolve a framework flaw. To quote MSDN,The constructor overloads for the
SqlParameterclass are mere conveniences for setting instance properties. They shorten the code, with marginal impact on performance: the constructor may bypass setter methods and operate directly on private members. If there's a difference it won't be much.What should I do?
Note the following (from MSDN)
The default type is input. However, if you allow the size to be inferred like this and you recycle the parameter object in a loop (you did say you were concerned with performance) then the size will be set by the first value and any subsequent values that are longer will be clipped. Obviously this is significant only for variable length values such as strings.
If you are passing the same logical parameter repeatedly in a loop I recommend you create a SqlParameter object outside the loop and size it appropriately. Over-sizing a varchar is harmless, so if it's a PITA to get the exact maximum, just set it bigger than you ever expect the column to be. Because you're recycling the object rather than creating a new one for each iteration, memory consumption over the duration of the loop will likely drop even if you get a bit excited with the oversizing.
Truth be told, unless you process thousands of calls, none of this will make much difference.
AddWithValuecreates a new object, sidestepping the sizing problem. It's short and sweet and easy to understand. If you loop through thousands, use my approach. If you don't, useAddWithValueto keep your code simple and easy to maintain.2008 was a long time ago
In the years since I wrote this, the world has changed. There are new kinds of date, and there is also a problem that didn't cross my mind until a recent problem with dates made me think about the implications of widening.
Widening and narrowing, for those unfamiliar with the terms, are qualities of data type conversions. If you assign an int to a double, there's no loss of precision because double is "wider". It's always safe to do this, so conversion is automatic. This is why you can assign an int to a double but going the other way you have to do an explicit cast - double to int is a narrowing conversion with potential loss of precision.
This can apply to strings: NVARCHAR is wider than VARCHAR, so you can assign a VARCHAR to an NVARCHAR but going the other way requires a cast. Comparison works because the VARCHAR implicitly widens to NVARCHAR, but this will interfere with the use of indexes!
C# strings are Unicode, so AddWithValue will produce an NVARCHAR parameter. At the other end, VARCHAR column values widen to NVARCHAR for comparison. This doesn't stop query execution but it prevents indexes from being used. This is bad.
What can you do about it? You have two possible solutions.
Ditching VARCHAR is probably the best idea. It's a simple change with predictable consequences and it improves your localisation story. However, you may not have this as an option.
These days I don't do a lot of direct ADO.NET. Linq2Sql is now my weapon of choice, and the act of writing this update has left me wondering how it handles this problem. I have a sudden, burning desire to check my data access code for lookup via VARCHAR columns.
2019 and the world has moved on again
Linq2Sql is not available in dotnet Core, so I find myself using Dapper. The [N]VARCHAR problem is still a thing but it's no longer so far buried. I believe one can also use ADO so things have come full circle in that regard.
您还可以使用
AddWithValue()，但请注意可能出现错误的隐式类型转换。You can also use
AddWithValue(), but be aware of the possibility of the wrong implicit type conversion.我曾经使用你的选项1：
效果很好，但后来我开始使用 .AddWithValue ，它就这么简单。 经过数千次使用后，它并没有给我带来任何问题。 请注意，我几乎总是传递类私有变量，因此我不必担心隐式类型转换。
I used to use your option 1:
which worked fine, but then I started using .AddWithValue and it is as simple as it gets. It hasn't caused me a problem after many many thousands of uses. Mind you, I almost always pass my classes private variables, so I don't have to worry about the implicit type conversion as much.
我肯定会说#1。 但是，无论如何，微软在企业库中的数据访问应用程序块中做到这一点是最好的，特别是对于 SQL Server：
http://msdn.microsoft.com/en-us/library/dd203144.aspx
I'd say #1 for sure. But, however Microsoft does it in the data access application block in the enterprise library is the best, esp for SQL server:
http://msdn.microsoft.com/en-us/library/dd203144.aspx
这取决于您的应用程序。 我实际上喜欢 2，因为如果我更改存储过程参数的长度，我不喜欢必须更改我的 DAO。 但这只是我。 我不知道是否有任何性能惩罚或其他什么。
It depends on your application. I actually like 2, because I don't lke to have to change my DAO if I change the length of a stored proc parameter. That's just me though. I don't know if there are any performance penalties or anything.