具有WHERE子句和Null的SQL Server参数化查询 [英] SQL Server parameterized query with a WHERE clause with Nulls

问题描述

VB.NET 2012，ADO.NET，SQL Server 2014

VB.NET 2012, ADO.NET, SQL Server 2014

我设置了一个运行良好的参数化查询.我基本上是从DataTable读取记录的，而DataTable来自与SQL Server不同的来源.它很小，我选择逐条记录地读取记录并构建查询，然后命中SQL Server以进行匹配.

I setup a parameterized query that works well. I essentially read records from a DataTable that comes from a different source than my SQL Server. It's small enough that I elected to read record by record and build a query and hit the SQL Server for a match.

但是，当我的一个字段应该被匹配为null时，我很难找到匹配项.我知道一条记录存在，因为我可以直接在SQL Server中查看它并查看它.使用我的参数化查询，null会被不正确地翻译.我尝试用DBNull.Value手动替换参数@EngSerialNo，但仍然无法正常工作.似乎我需要两个不同的查询，具体取决于我的DataTable值是否为空.

However I am having trouble getting a match when one of my fields is supposed to be matched for a null. I know a record exist because I can look at it in SQL Server directly and see it. With my parameterized query somehow the null is being translated improperly. I tried manually replacing the parameter @EngSerialNo with DBNull.Value and still doesn't work. Almost seems like I need two different queries depending if my DataTable value is null.

sqQry.AppendLine("SELECT CityCode,CarNum,RegNum,Event,EngSerialNum)")
sqQry.AppendLine("FROM [MyDB].[dbo].[Events]")
sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum=@EngSerialNo)") 'this looks for a value in EngSerialNo
'sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum IS NULL)")     'this looks for a Null in EngSerialNo     

 Dim cmd As New SqlCommand

 With cmd
        .Connection = connMyDb
        .CommandType = CommandType.Text
        .CommandText = sqQry.ToString

         'cycle through each DataRow in the DataTable and check for returns
         Dim total As Integer = 0
         For Each row As DataRow In dtMain.Rows
             .Parameters.Clear()
             .Parameters.AddWithValue("@City", row.Item("City"))
             .Parameters.AddWithValue("@CarNo", row.Item("CarNo"))
             .Parameters.AddWithValue("@RegNo", row.Item("RegNo"))
             .Parameters.AddWithValue("@Event", row.Item("Event"))
             .Parameters.AddWithValue("@EngSerialNo", row.Item("EngSerialNo")) 'how do I get this to look for a null value when the DataTable contains a null value?

             Dim rowsAffected As Integer = .ExecuteNonQuery()
             total += rowsAffected
         Next row
End With

更新:我最终为每个DataRow创建了一个动态SQL.基本上，对于每个DataRow，我都会检查键字段是否为NULL或实际值，并创建适当的SQL命令文本.我有4个字段可以包含NULL，但为简单起见，我仅在此处演示了一个.我认为开发人员可以按照示例创建自己的查询.

Update: I ended up creating a dynamic SQL for every DataRow. Basically for each DataRow I check key fields for NULL or an actual value and create the appropriate SQL command text. I have 4 fields that could contain a NULL but for sake of simplicity I only demonstrated one here. I think the developer can follow the example to create their own query.

 Dim cmd As New SqlCommand
 With cmd
        .Connection = connMyDb
        .CommandType = CommandType.Text

         'cycle through each DataRow in the DataTable and check for returns
         Dim total As Integer = 0
         For Each row As DataRow In dtMain.Rows
             .CommandText = BuildSql(row)

             .Parameters.Clear()
             .Parameters.AddWithValue("@City", row.Item("City"))
             .Parameters.AddWithValue("@CarNo", row.Item("CarNo"))
             .Parameters.AddWithValue("@RegNo", row.Item("RegNo"))
             .Parameters.AddWithValue("@Event", row.Item("Event"))
             .Parameters.AddWithValue("@EngSerialNo", row.Item("EngSerialNo")) 

             Dim rowsAffected As Integer = .ExecuteNonQuery()
             total += rowsAffected
         Next row
End With

Private Function BuildSql(ByVal dr As DataRow) As String
     Dim sqQry As New StringBuilder
     sqQry.AppendLine("SELECT CityCode,CarNum,RegNum,Event,EngSerialNum)")
     sqQry.AppendLine("FROM [MyDB].[dbo].[Events]")

     If dr.Item("EngSerialNo") Is DBNull.Value Then
        sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum IS NULL)")     'this looks for a Null in EngSerialNo            
     Else
        sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum=@EngSerialNo)") 'this looks for a value in EngSerialNo
     End If

     Return sqQry.ToString
End Function

推荐答案

在SQL中，您无法比较空值，即，即使字段中的值为空，EngSerialNum = null始终求值为false.

In SQL you can't compare null values, i.e. EngSerialNum = null always evaluates to false, even if the value in the field is null.

您可以动态创建查询，以便使用is null来匹配空值，或者可以使用如下表达式:

Either you can create the query dynamically so that you use is null to match the null values, or you can use an expression like this:

((EngSerialNum is null and @EngSerialNo is null) or EngSerialNum = @EngSerialNo)

这篇关于具有WHERE子句和Null的SQL Server参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！