.net - 使用 IN 子句参数的 SQL Server 查询

Question

我正在尝试使用 IN 子句进行更新，但我似乎无法让它工作。我的 iArr 创建了一个用逗号分隔的数字列表，理论上一旦用括号括起来它应该更新，但它似乎不起作用。我不断收到错误

Conversion failed when converting nvarchar value '111111, 222222, 333333' to datatype int.

知道如何解决这个问题吗？完整代码如下:

    Dim iArr As String
    iArr = ""
    For i As Integer = 0 To ufReview.InvoiceGrid.Rows.Count - 1
        iArr = iArr & ufReview.InvoiceGrid.Rows(i).Cells(0).Value & ", "
    Next i
    iArr = Left(iArr, Len(iArr) - 2)

    Dim SQL_Arr As String = _
            <SQL>
               UPDATE SCInv SET Inv_Transfer_Date = @UpdateCommand WHERE Inv_Num IN (@Array)
            </SQL>

    Dim cnt As New SqlConnection()
    Dim cmd As New SqlCommand()
    cnt.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=CAT;User ID=USERID;Password=PASSWORD"
    Try
        cnt.Open()
        cmd.Connection = cnt
        cmd.CommandText = SQL_Arr
        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("@UpdateCommand", UpdateCommand)
        cmd.Parameters.AddWithValue("@Array", iArr)
        Dim RACount As Integer = cmd.ExecuteNonQuery()
        MsgBox("The following Summary Numbers are now marked as " & UpdateFeedback & ": " & vbCrLf & iArr _
               & vbCrLf & vbCrLf & "(" & RACount & " row(s) affected)", vbOKOnly + vbInformation, "Tesseract")
    Catch ex As Exception
        MsgBox("Failed to update, please try again" & vbCrLf & vbCrLf & ex.Message, vbOKOnly + vbCritical, "SQL Error")
    End Try
    cnt.Close()
    cnt.Dispose()

Answer 1

SQL Server 现在可能支持传递数组作为参数。许多数据库没有，并且在 Microsoft Connect 上仍然有一个开放的请求。 .值得一探究竟。

您还应该打开 Option Strict。您的 SQL_Arr 被声明为字符串，但您为其分配了一个 XML 文字，然后将该文字作为命令文本传递。

这将创建一个 SQL 语句，其中 Id 列表中的每个项目都被赋予了自己的参数，然后是它为它设置的值。它使用 Id 的一些随机数据来模拟不同的列表，并且它使用 MySQL ......概念将是相同的。

' test data: random set of IDs to update
Dim idList = Enumerable.Range(1, 2500).OrderBy(Function(r) RNG.Next()).Take(8).ToList()

Dim sql As String = <sql>
            UPDATE SampleX SET `Value`= @v WHERE Id IN (@theList)
          </sql>.Value

' create the parameter placeholders
' results in a list of "@pX" items where X is a number
Dim params = Enumerable.Range(1, idList.Count).
            Select(Function(q) String.Format("@q{0}", q)).
            ToList()

' join the list of parms to make '@q1, @q2...'
' then replace @thelist with that string
sql = sql.Replace("@theList", String.Join(",", params))

' run the query
Using dbcon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(sql, dbcon)

        ' specify the value
        cmd.Parameters.Add("@v", MySqlDbType.Int32).Value = -6

        ' loop to supply an Id value for each IN parameter
        ' placeholder created
        For n As Int32 = 0 To params.Count - 1
            cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)
        Next

        dbcon.Open()
        Dim rows = cmd.ExecuteNonQuery()
    End Using

    ' debug/demo: show the results
    sql = "SELECT Name, Descr, Value, Country FROM SampleX WHERE Value=-6"
    Using cmd As New MySqlCommand(sql, dbcon)
        dtSample = New DataTable
        dtSample.Load(cmd.ExecuteReader())
        dgv1.DataSource = dtSample
    End Using
End Using

我们希望查询保持参数化，所以第一部分创建了一个 @q1 占位符列表——它们的数量与要更新的 ID 一样多。在循环中创建占位符:

Dim params As New List(Of String)
For n As Int32 = 0 To idList.Count - 1
    params.Add(String.Format("@q{0}", (n + 1).ToString()))
Next

接下来，代码将 SQL 中的 @theList 替换为列表的连接版本。结果:

UPDATE SampleX SET `Value`= @v WHERE Id IN (@q1,@q2,@q3,@q4,@q5,@q6,@q7,@q8)

如果 IN 子句处理的是字符串而不是整数，则在创建参数时添加标记:String.Format("'@q{0}'",...

剩下的很简单:为每个参数指定 Id:

' params(n) e.g. "q1"  and idList(n) is the value e.g. 11111
cmd.Parameters.Add(params(n), MySqlDbType.Int32).Value = idList(n)

我的示例只是将 a 值设置为 -6 以便于查找。它有效:

最后，请注意生成的 SQL 允许的最大长度。该最大值取决于 DB 提供程序并且非常大，但是如果您的 IN 子句有很多 ID，您可能希望将其分成几批。