使用参数为IN子句的SQL Server查询

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

接下来，代码将@theListSQL替换为列表的联接版本。结果：

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 Provider，并且很大，但是如果您的IN子句有很多Id ，则可能需要将其分成几批。