在添加SqlCommand参数时，何时应使用"SqlDbType"和"size"？

Question

这个问题有一个相关的提问：

什么是向SQLCommand传递参数的最佳方法？

但我想知道它们之间的区别，以及不同的方法是否存在任何问题。

我通常使用类似于以下结构的方式：

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(SQL, conn))
{
     cmd.CommandType = CommandType.Text;
     cmd.CommandTimeout = Settings.Default.reportTimeout;
     cmd.Parameters.Add("type", SqlDbType.VarChar, 4).Value = type;

     cmd.Connection.Open();

     using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
     {
         adapter.Fill(ds);
     }
      //use data                    
}

现在有几种方法可以添加cmd参数，我想知道哪种方法最好：

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

假设在传递变量字符中使用字段长度不是很理想，因为它是一个可能在以后更改的神奇值。这样做会有什么问题吗（性能或其他方面），我认为它默认为varchar(max)或数据库等效项。我相当满意这样可以工作。

我更担心的是，如果我使用上述第三或第四个选项，则会丢失SqlDbType枚举。是否存在无法正常工作的情况？我可以想象varchar被错误地转换为char或反过来，或者decimal到money的问题....

就数据库而言，我认为字段类型比长度更不可能更改，那么保留它是否值得呢？

- PeteT

---

虽然没有回答你的问题，但你知道在调用SqlDataAdapter.Fill()之前不需要打开连接，而且这样做有好处吗？ - Rowland Shaw

@Rowland 说得好。我知道这一点，但在我的代码中却疏忽了。我想我从某个地方复制了原始代码块，那时我正在使用SqlDataReader而不是适配器（你需要打开连接才能使用吧？）。这就是所谓的抄袭编程的后果 :-）。现在我的代码已经修复了。 - PeteT

只是想提一下，因为这是那些“人们可能不知道的晦涩提示”之一。 - Rowland Shaw

Answer 1

根据我的经验，我建议您确保完成以下操作：

• 确保是您定义了参数的数据类型。ADO.NET会猜测数据类型，但在某些情况下，它可能会完全错误 - 所以我建议避免使用这种方法：

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

让 ADO.NET 根据传入的值猜测参数类型很棘手，如果有任何原因导致其偏差，那么这些错误就非常难以追踪和解决！想象一下当 DBNull.Value 被传入时会发生什么情况 - ADO.NET 应该选择哪种数据类型呢？

只需要明确地说明你想要的类型即可！

如果要使用字符串参数，请确保定义长度 - 所以我建议避免使用以下方法：

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

如果你不指定长度，ADO.NET可能会默认为某个随意的值，或者作为值传递的字符串的长度，或者其他一些可能性 - 你永远不确定。如果您指定的长度不符合存储过程的实际期望，那么可能会出现转换和其他不愉快的问题。因此，如果您定义了一个字符串，请同时定义它的长度！在你的情况下，唯一真正有效的方法是这个：

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

因为它 a) 明确地定义要使用的数据类型，以及 b) 明确地定义了字符串的长度。

- marc_s

---

如果您正在使用存储过程，难道不能从SqlCommandBuilder.DeriveParameters(cmd)提供的元数据中获取参数类型吗？ - devinbost

传递的长度应该是基础参数定义的长度吗？如果是，那不是违反了DRY原则吗？ - tbone

Answer 2

通过添加类型，您的请求更有可能通过使用缓存的查询计划来提高性能。

以下是MSDN上的一句引用：

参数化命令也可以提高查询执行性能，因为它们帮助数据库服务器将传入命令与适当的缓存查询计划精确匹配。

在执行计划缓存和重用中可以阅读更多内容。

- StefanE

---

彼得的所有选项都是参数化的，因此如果以不同的方式添加参数影响性能，我会感到惊讶--在您提供的文章中肯定没有明确提到。 - Rowland Shaw

我持有不同意见。这篇文章所描述的是，通过尽可能精确地定义参数，更有可能使用缓存的查询计划，并且能够获得更高的性能表现。子章节“简单参数化”指出，在SQL Server中，在Transact-SQL语句中使用参数或者参数标记可以增加关系引擎匹配新SQL语句和已编译执行计划的能力。 - StefanE