未使用的SQL参数 - 任何伤害他们? [英] Unused sql parameters - are they of any harm?
问题描述
考虑以下code:
Dim sql = "SELECT * FROM MyTable WHERE value1 = @Param1"
If someCondition Then
sql = sql + " AND value2 = @Param2"
End If
Dim cmd As New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@Param1", param1Value)
cmd.Parameters.AddWithValue("@Param2", param2Value)
假设我建一个复杂的SQL语句,动态地,可能会或可能不会包括 @参数2
参数 - 是否有任何伤害的将其添加到命令作为参数?
Assuming that I built a complex sql statement dynamically that may or may not have included the @Param2
parameter - is there any harm in adding it to the command as a parameter?
我的实际使用情况显然要复杂得多超过这一点,但总的来说,这是一个模式,我应该避免;如果是这样,为什么?
My real use-case is obviously far more complicated than this, but in general, is this a pattern I should avoid; and if so, why?
推荐答案
的唯一一点我会注意的是,如果你调用 .AddWithValue
,你把它的事实达SQL Server以弄清参数的数据类型将是。
The only point I would take note is the fact that if you call .AddWithValue
, you leave it up to SQL Server to figure out what the data type of the parameter will be.
SQL Server会猜测的非常好 - 但有时,它得到它次优,这将有助于为您提供细节
SQL Server does a remarkably good job of guessing - but sometimes, it gets it "sub-optimally" and it would be helpful for you to provide the details.
所以我个人倾向于一直使用这个片段的code:
So I personally tend to always use this snippet of code:
SqlParameter aParam = new SqlParameter("@Param1", SqlDbType.VarChar, 50);
aParam.Value = param1Value;
这主要有两个好处:
- 您去定义明确的类型,这是很重要的,例如使用VARCHAR与NVARCHAR时(否则你可能会招致许多不必要的类型转换)
- 你定义的例如最大长度字符串参数
您可以很容易地在例如包装这个静态辅助类,甚至把它作为一个扩展方法。
You could easily wrap this in a e.g. static helper class, or even use it as an extension method.
这是一点点更多的工作,但你得到更多的控制权,并且可能避免不必要的,费时的数据类型转换和其他意外的副作用,如果你离开它到SQL Server去猜测你的类型。
It's a tad more work, but you get more control, and might avoid unnecessary, time-consuming datatype conversions and other unexpected side effects if you leave it up to SQL Server to guess your types.
这篇关于未使用的SQL参数 - 任何伤害他们?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!