什么时候应该“SqlDbType"?和“大小"添加SqlCommand参数时使用? [英] When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?
问题描述
有一个相关的问题:
但我想知道不同之处是什么，以及不同方式是否存在任何问题.
But I am wanting to know what the differences are and if there are any problems with the different ways.
我通常使用这样的结构:
I usually use a structure something like this:
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 参数，我想知道哪种方法最好:
Now there are several ways to add the cmd parameters and I am wondering which is best:
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");
我认为在传递 varchars 时具有字段长度是不可取的，因为它是一个神奇的值，以后可能会在数据库中更改.这个对吗?它是否会导致以这种方式传递 varchar 的任何问题(性能或其他)，我假设它默认为 varchar(max) 或数据库等效项.我很高兴这会奏效.
Having the length of the field in the passing of the varchars I assume is not preferable as it's a magic value which may be changed later at the database. Is this correct? Does it cause any issue passing a varchar in this way (performance or other), I assume it defaults to varchar(max) or the database equivalent. I am reasonably happy this will work.
如果我使用上面列出的第三个或第四个选项，我更关心的部分是 SqlDbType 枚举的丢失，我根本没有提供类型.在某些情况下这不起作用我可以想象 varchar 被错误地转换为 char 或反之亦然的问题，或者可能是小数转换为金钱的问题......
The part that concerns me more is the loss of the SqlDbType enum if I am using the third or fourth options I listed above I am not supplying a type at all. Are there cases where this won't work I can imagine problems with varchar being incorrectly cast to char or vice versa or perhaps issues with decimal to money....
就数据库而言，我想说的字段类型更改的可能性远小于长度，因此值得保留吗?
In terms of the database the field type I would say is much less likely to change than the length so is it worth retaining?
推荐答案
根据我的经验，我会确保我做这些事情:
In my experience, I would make sure I do these things:
确保是您自己定义了参数的数据类型.ADO.NET 在猜测方面做得不错，但在某些情况下，它可能会非常糟糕——所以我会避免这种方法:
make sure it's you that defines the data type for the parameter. ADO.NET does a decent job at guessing, but in some cases, it can be terribly off - so I would avoid this method:
cmd.Parameters.Add("@Name").Value = "Bob";
cmd.Parameters.AddWithValue("@Name", "Bob");
让 ADO.NET 通过传递的值来猜测参数的类型是很棘手的，如果它因任何原因而关闭，那么跟踪和查找这些 bug 真的很棘手！想象一下当您传入 DBNull.Value 时会发生什么 - ADO.NET 应该为此选择什么数据类型?
Letting ADO.NET guess the type of the parameter by the value passed is tricky, and if it's off for any reason, those are really tricky bugs to track and find! Imagine what happens when you pass in a DBNull.Value - what datatype should ADO.NET pick for that?
只要明确 - 说出你想要的类型！
Just be explicit - say what type it is you want!
如果您使用字符串参数，请确保明确定义长度 - 所以我也会避免这种方法:
if you're using string parameters, make sure to explicitly define the length - so I would avoid this method, too:
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";
如果您不提供长度，ADO.NET 可能会默认为某个任意值，或者作为值传入的字符串的长度，或者其他什么 - 您永远无法确定.如果您的长度与存储过程的实际期望不匹配，您可能会看到转换和其他令人不快的惊喜.所以如果你定义一个字符串，也要定义它的长度！
If you don't provide a length, ADO.NET might default to some arbitrary value, or the length of the string passed in as a value, or something else - you're never quite sure. And if your length doesn't match what the stored proc really expects, you might see conversion and other unpleasant surprises. So if you define a string, define its length, too!
所以在你的情况下，唯一真正适合我的方法是这里的这个:
So in your case, the only approach that really works for me is this one here:
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";
因为它 a) 明确定义要使用的数据类型，b) 明确定义字符串的长度.
because it a) defines the data type to use explicitly, and b) defines the length of the string explicitly.
这篇关于什么时候应该“SqlDbType"?和“大小"添加SqlCommand参数时使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
