当要与QUOT;的SqlDbType＆QUOT;和＆QUOT;大小＆QUOT;添加SqlCommand的参数时，可以使用？ [英] When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?
问题描述
有一个相关的问题是：
<一个href="http://stackoverflow.com/questions/293311/whats-the-best-method-to-pass-parameters-to-sqlcommand">What's最好的方法来传递参数的SqlCommand？
不过，我想知道的区别是什么，如果有与不同的方式出现任何问题。
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");
具有字段的长度，我承担VARCHAR处理的传球不是preferable，因为它是可后来在数据库中更改的魔法值。它是否正确？不会引起任何问题，经过一个varchar以这种方式（性能或其他方式），我认为它默认为varchar（最大值）或等价的数据库。我有理由高兴，这将正常工作。
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";
，因为它一）定义了数据类型明确地使用，和b）定义的字符串的长度显式
because it a) defines the data type to use explicitly, and b) defines the length of the string explicitly.
这篇关于当要与QUOT;的SqlDbType＆QUOT;和＆QUOT;大小＆QUOT;添加SqlCommand的参数时，可以使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
