使用ADO.NET和AddWithValue()处理空值

Question

我有一个控件，在回发时将表单结果保存回数据库。它通过迭代查询字符串来填充要保存的值。因此，对于以下SQL语句（为了讨论而大大简化）...

UPDATE MyTable
SET MyVal1 = @val1,
    MyVal2 = @val2
WHERE @id = @id

如果循环查询字符串键，则会按以下方式进行：

For Each Key As String In Request.QueryString.Keys
    Command.Parameters.AddWithValue("@" & Key, Request.QueryString(Key))
Next

然而，我现在遇到了一个情况，在某些情况下，这些变量可能不在查询字符串中出现。如果我没有在查询字符串中传递val2，我会收到一个错误：System.Data.SqlClient.SqlException: 必须声明标量变量 "@val2"。

尝试在SQL语句中检测缺失的值...

IF @val2 IS NOT NULL
UPDATE MyTable
SET MyVal1 = @val1,
    MyVal2 = @val2
WHERE @id = @id

...已经失败。

如何最好地解决这个问题？我必须使用正则表达式解析SQL块，扫描查询字符串中不存在的变量名吗？或者，有没有更优雅的方法来解决这个问题？

更新：在VB代码中检测空值会破坏将代码与其上下文解耦的目的。我不想为每个可能被传递或未被传递的变量添加条件，以此来污染我的函数。

- dansays

Answer 1

首先，我建议不要将查询字符串中的所有条目都添加为参数名称，我不确定这是否安全，但我不想冒险。

问题在于您正在调用

Command.Parameters.AddWithValue("@val2", null)

你应该调用以下方法：

If MyValue Is Nothing Then
    Command.Parameters.AddWithValue("@val2", DBNull.Value)
Else
    Command.Parameters.AddWithValue("@val2", MyValue)
End If

- Sander Rijken

---

我的理解是，通过使用Paramaters.AddWithValue()，框架可以使我免受SQL注入攻击的影响。遍历查询字符串键的整个目的是将VB与SQL分离。VB不能意识到正在传递或未传递的特定值。 - dansays

“问题”在于您正在使用空值（Nothing）调用AddWithValue方法，您可以在迭代查询字符串时检查是否为Nothing，然后使用该值或DBNull.Value调用它。 - Sander Rijken

我见过的最简洁的方法是：Command.Parameters.AddWithValue("@val2", (object)MyValue ?? (object)DBNull.Value)。 - Nick Strupat

确实是个不错的想法，但这只适用于C＃（http://msdn.microsoft.com/en-us/library/ms173224.aspx）。 - Sander Rijken

VB.NET 也有一个空值合并运算符：Command.Parameters.AddWithValue("@val2", If(MyValue, DBNull.Value))。（可能需要将 MyValue 强制转换为 Object，但尚未测试。） - Heinzi

Answer 2

更新：我提供的解决方案是基于存储过程的假设。

将 SQL 存储过程参数的默认值设置为 Null 是否有效？

如果是动态 SQL，请始终传递正确数量的参数，无论是 null 还是实际值或指定默认值。

- Gulzar

Answer 3

我喜欢使用AddWithValue方法。

对于“可选”参数，我总是指定默认的SQL参数。这样，如果它为空，ADO.NET将不包括该参数，存储过程将使用其默认值。

这样我就不必处理检查/传递DBNull.Value了。

- user7658

Answer 4

在苦苦寻找更简单的解决方案无果后，我放弃了并编写了一个例程来解析我的SQL查询中的变量名：

Dim FieldRegEx As New Regex("@([A-Z_]+)", RegexOptions.IgnoreCase)
Dim Fields As Match = FieldRegEx.Match(Query)
Dim Processed As New ArrayList

While Fields.Success
    Dim Key As String = Fields.Groups(1).Value
    Dim Val As Object = Request.QueryString(Key)
    If Val = "" Then Val = DBNull.Value
    If Not Processed.Contains(Key) Then
        Command.Parameters.AddWithValue("@" & Key, Val)
        Processed.Add(Key)
    End If
    Fields = Fields.NextMatch()
End While

这有点像是一个技巧，但它让我能够让我的代码完全不知道我的SQL查询的上下文。

- dansays