VB.NET中SqlDataSource多值IN参数化查询问题咨询

你遇到的问题核心是：SQL会把传入的逗号分隔字符串当成单个完整的参数值，而非多个独立值，因此IN子句只会匹配等于该完整字符串的记录，自然无法得到预期结果。以下是三种可行的修改方案：

这是SQL Server官方推荐的多值参数传递方式，适合复杂或高频使用的场景。

步骤1：在SQL Server中创建用户定义表类型 #

CREATE TYPE dbo.SkuList AS TABLE (SkuCode VARCHAR(50)) -- 字段类型、长度需与Table1的SkuCode一致

步骤2：修改VB.NET代码 #

Dim connectionString As String = "你的连接字符串"
Using sqlconn As New SqlConnection(connectionString)
    sqlconn.Open()

    ' 构造要传入的多值集合
    Dim skuList As New DataTable()
    skuList.Columns.Add("SkuCode", GetType(String))
    skuList.Rows.Add("1")
    skuList.Rows.Add("2")

    Using cmd As New SqlCommand("SELECT * FROM Table1 WHERE SkuCode IN (SELECT SkuCode FROM @SKU)", sqlconn)
        ' 创建表值参数
        Dim parm As New SqlParameter("@SKU", SqlDbType.Structured)
        parm.TypeName = "dbo.SkuList" ' 对应SQL中创建的表类型名称
        parm.Value = skuList

        cmd.Parameters.Add(parm)

        Using ds As New DataSet()
            Using sqlDa As New SqlDataAdapter(cmd)
                sqlDa.Fill(ds)
            End Using

            Dim dt As DataTable = ds.Tables(0)
            MsgBox(If(dt.Rows.Count > 0, "Done", "Not done."))
        End Using
    End Using
End Using

无需创建表类型，根据SKU数量动态生成参数占位符，每个值对应独立参数，彻底避免注入问题。

Dim connectionString As String = "你的连接字符串"
Dim skus As New List(Of String) From {"1", "2"} ' 待传入的多值集合

Using sqlconn As New SqlConnection(connectionString)
    sqlconn.Open()

    ' 生成参数占位符，如@SKU0、@SKU1
    Dim paramPlaceholders As String = String.Join(",", skus.Select(Function(s, i) $"@SKU{i}"))
    Dim cmdText As String = $"SELECT * FROM Table1 WHERE SkuCode IN ({paramPlaceholders})"

    Using cmd As New SqlCommand(cmdText, sqlconn)
        ' 循环添加参数
        For i As Integer = 0 To skus.Count - 1
            cmd.Parameters.AddWithValue($"@SKU{i}", skus(i))
        Next

        Using ds As New DataSet()
            Using sqlDa As New SqlDataAdapter(cmd)
                sqlDa.Fill(ds)
            End Using

            Dim dt As DataTable = ds.Tables(0)
            MsgBox(If(dt.Rows.Count > 0, "Done", "Not done."))
        End Using
    End Using
End Using

若你的SQL Server版本支持STRING_SPLIT函数，可传入逗号分隔字符串，拆分后进行匹配。

Dim connectionString As String = "你的连接字符串"
Dim skuStr As String = "1,2" ' 逗号分隔的SKU字符串

Using sqlconn As New SqlConnection(connectionString)
    sqlconn.Open()

    Using cmd As New SqlCommand("SELECT * FROM Table1 WHERE SkuCode IN (SELECT value FROM STRING_SPLIT(@SKU, ','))", sqlconn)
        cmd.Parameters.AddWithValue("@SKU", skuStr)

        Using ds As New DataSet()
            Using sqlDa As New SqlDataAdapter(cmd)
                sqlDa.Fill(ds)
            End Using

            Dim dt As DataTable = ds.Tables(0)
            MsgBox(If(dt.Rows.Count > 0, "Done", "Not done."))
        End Using
    End Using
End Using

内容的提问来源于stack exchange，提问作者Ram