具有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屋!