.net 将参数传递给 SQLCommand 的最佳方法是什么？

What's the best method to pass parameters to SQLCommand?

提问by TAG

What's the best method to pass parameters to SQLCommand? You can do:

将参数传递给 SQLCommand 的最佳方法是什么？你可以做：

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.

似乎第一个可能在某种程度上“更好”，无论是在性能方面还是在错误检查方面。但我想更明确地知道。

采纳答案by Mitch Wheat

You can also use AddWithValue(), but be aware of the possibility of the wrong implicit type conversion.

您也可以使用AddWithValue()，但要注意错误隐式类型转换的可能性。

cmd.Parameters.AddWithValue("@Name", "Bob");

回答by Peter Wone

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 the SqlParameterclass. They essentially construct the parameter object using whatever constructor has the same signature as the convenience method you called, and then call SqlParameterCollection.Add(SqlParameter)like this:

您引用了 的几个重载的参数列表Add。这些是直接对应于SqlParameter类的构造函数重载的便捷方法。它们本质上使用与您调用的便捷方法具有相同签名的任何构造函数来构造参数对象，然后SqlParameterCollection.Add(SqlParameter)像这样调用：

SqlParameter foo = new SqlParameter(parameterName, dbType, size);
this.Add(foo);

AddWithValueis similar but takes convenience even further, also setting the value. However, it was actually introduced to resolve a framework flaw. To quote MSDN,

AddWithValue类似，但更方便，还设置了值。但是，它实际上是为了解决框架缺陷而引入的。引用 MSDN，

The overload of Addthat takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Addoverload that takes a Stringand a SqlDbTypeenumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbTypevalue. Use AddWithValuewhenever you want to add a parameter by specifying its name and value.

其重载Add采用字符串和对象已被弃用，因为SqlParameterCollection.Add采用 aString和SqlDbType枚举值的重载可能存在歧义， 其中传递带有字符串的整数可能被解释为参数值或相应的 SqlDbType值。AddWithValue每当您想通过指定其名称和值来添加参数时使用。

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.

类的构造函数重载SqlParameter仅仅是设置实例属性的便利。它们缩短了代码，对性能的影响很小：构造函数可以绕过 setter 方法并直接对私有成员进行操作。如果有差别，那不会太大。

What should I do?

我该怎么办？

Note the following (from MSDN)

请注意以下（来自 MSDN）

For bidirectional and output parameters, and return values, you must set the value of Size. This is not required for input parameters, and if not explicitly set, the value is inferred from the actual size of the specified parameter when a parameterized statement is executed.

对于双向和输出参数以及返回值，您必须设置 的值Size。这对于输入参数不是必需的，如果没有明确设置，则在执行参数化语句时从指定参数的实际大小推断该值。

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 dropeven if you get a bit excited with the oversizing.

如果您在循环中重复传递相同的逻辑参数，我建议您在循环外创建一个 SqlParameter 对象并适当调整其大小。过大的 varchar 是无害的，所以如果它是一个 PITA 以获得确切的最大值，只需将它设置为比您预期的列更大。因为您正在回收对象而不是为每次迭代创建一个新对象，所以即使您对过大的尺寸感到有些兴奋，循环期间的内存消耗也可能会下降。

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, use AddWithValueto keep your code simple and easy to maintain.

说实话，除非您处理数千个电话，否则这些都不会产生太大影响。AddWithValue创建一个新对象，避开了大小问题。它短小精悍，易于理解。如果您遍历数千个，请使用我的方法。如果不这样做，请使用AddWithValue使您的代码简单且易于维护。

---

---

2008 was a long time ago

2008年是很久以前

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.

对于不熟悉术语的人来说，扩大和缩小是数据类型转换的特性。如果将 int 分配给 double，则不会损失精度，因为 double 是“更宽的”。这样做总是安全的，因此转换是自动的。这就是为什么您可以将 int 分配给 double 但以另一种方式您必须进行显式转换 - double 到 int 是一种可能会损失精度的缩小转换。

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!

这适用于字符串：NVARCHAR 比 VARCHAR 更宽，因此您可以将 VARCHAR 分配给 NVARCHAR，但反过来需要强制转换。比较有效是因为 VARCHAR 隐式扩展为 NVARCHAR，但这会干扰索引的使用！

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.

C# 字符串是 Unicode，因此 AddWithValue 将生成 NVARCHAR 参数。在另一端，VARCHAR 列值会扩大到 NVARCHAR 以进行比较。这不会停止查询执行，但会阻止使用索引。这不好。

What can you do about it? You have two possible solutions.

你能为这个做什么？您有两种可能的解决方案。

• Explicitly type the parameter. This means no more AddWithValue
• Change all string column types to NVARCHAR.

• 显式键入参数。这意味着不再有 AddWithValue
• 将所有字符串列类型更改为 NVARCHAR。

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.

放弃 VARCHAR 可能是最好的主意。这是一个具有可预测后果的简单更改，它改进了您的本地化故事。但是，您可能没有这个选项。

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.

这些天我不做很多直接的 ADO.NET。Linq2Sql 现在是我选择的武器，编写此更新的行为让我想知道它是如何处理这个问题的。我突然强烈地希望通过 VARCHAR 列检查我的数据访问代码以进行查找。

---

---

2019 and the world has moved on again

2019年，世界再次前行

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.

Linq2Sql 在 dotnet Core 中不可用，所以我发现自己在使用 Dapper。[N]VARCHAR 问题仍然是一个问题，但到目前为止它不再被掩埋。我相信人们也可以使用 ADO，所以在这方面事情已经完全循环了。

回答by Booji Boy

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:

我肯定会说#1。但是，无论微软在企业库中的数据访问应用程序块中做的是最好的，尤其是对于SQL服务器：

http://msdn.microsoft.com/en-us/library/dd203144.aspx

http://msdn.microsoft.com/en-us/library/dd203144.aspx

回答by aSkywalker

I used to use your option 1:

我曾经使用您的选项 1：

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";

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.

效果很好，但后来我开始使用 .AddWithValue 并且它变得非常简单。经过成千上万次使用后，它并没有给我带来问题。请注意，我几乎总是传递我的类私有变量，所以我不必担心隐式类型转换。

回答by Charles Graham

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.

这取决于您的应用程序。我实际上喜欢 2，因为如果我更改存储的 proc 参数的长度，我不想更改我的 DAO。不过那只是我。我不知道是否有任何性能惩罚或任何东西。