本文介绍如何使用 XML 和 Visual Basic .NET 将值数组传递到SQL Server存储过程中。

原始产品版本： Visual Basic
原始 KB 编号： 555266

需要将值数组传递到SQL Server存储过程中，才能将其用作子句的 IN 列表。

当前版本的 Microsoft SQL Server 不支持允许将值数组作为存储过程或 SQL 语句的参数传递的数组数据类型。 通常，开发人员需要传递值的数组，以根据子句中 IN 提供的列表选择记录。 在某些情况下，作为逗号分隔的字符串传递给存储过程的参数列表，此带分隔符的字符串不能直接用作 IN 子句的参数，必须转换为可接受的窗体，以便在子句内部 IN 使用。

解决此问题的解决方案之一是将值数组作为 XML 字符串参数传递给存储过程，并使用 OPENXML rowset 存储过程中的提供程序从提供的 XML 中选择值。 OPENXML 将提供程序与 Transact-SQL 语句结合使用提供了灵活且简单的方法，用于基于传递的值数组操作数据库中的数据。

此示例不包含生产级别应用程序中所需的异常处理代码。

我们将使用SQL Server数据库中的 Northwind Orders 表。 使用以下强项在数据库中 Northwind 创建存储过程。

IF EXISTS (SELECT name
    FROM sysobjects
    WHERE name = N'sp_SelectOrders'
    AND type = 'P')
    DROP PROCEDURE sp_SelectOrders
CREATE PROC sp_SelectOrders @in_values nText AS
DECLARE @hDoc int
--Prepare input values as an XML documnet
exec sp_xml_preparedocument @hDoc OUTPUT, @in_values
--Select data from the table based on values in XML
SELECT * FROM Orders WHERE CustomerID IN (
 SELECT CustomerID FROM OPENXML (@hdoc, '/NewDataSet/Customers', 1)
 WITH (CustomerID NCHAR(5)))
EXEC sp_xml_removedocument @hDoc
启动 Visual Studio .NET 并创建控制台应用程序。 默认情况下将创建 Module1.vb 文件。
将 Module1.vb 文件中的强文件替换为以下文件。 为了简化和示例，示例强从“客户”表中选择所有客户的列表，为一半 (的客户准备值数组作为 XML 字符串，以演示有限的选择) ，然后运行存储过程，从“订单”表中为所选客户选择订单列表。
需要修改强示例中的连接字符串才能在环境中使用它
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Text
Module Module1
PublicSub Main()
    Dim loCustomers As DataSet
    Dim loOrders As DataTable
        'Get list of the customers from the database
        loCustomers = GetCustomers()
        Console.WriteLine("Total customers: " & loCustomers.Tables(0).Rows.Count.ToString)
        If Not loCustomers Is Nothing Then
            loOrders = GetOrders(loCustomers)
            Console.WriteLine("Total orders: " & loOrders.Rows.Count.ToString)
        EndIf
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
        If Not loCustomers Is Nothing Then
            loCustomers.Dispose()
            loCustomers = Nothing
        EndIf
        If Not loOrders Is Nothing Then
            loOrders.Dispose()
            loOrders = Nothing
        EndIf
    EndTry
EndSub
Private Function GetOrders(ByVal loCustomers As DataSet) As DataTable
    Dim loOrders As DataSet
    Dim loParameter As SqlParameter
    Dim loCol As DataColumn
        'Prepare XML output from the Customers DataSet as a string
        ForEach loCol In loCustomers.Tables("Customers").Columns
            loCol.ColumnMapping = System.Data.MappingType.Attribute
        'Pass XML into the stored procedure as a parameter
        loParameter = New SqlParameter("@in_values", System.Data.SqlDbType.NText)
        loParameter.Value = loCustomers.GetXml
        'Get list of the orders from the database
        loOrders = GetDataFromDb("sp_SelectOrders", CommandType.StoredProcedure, "Customers", loParameter)'Return list of the orders as a DataTable
        If (Not loOrders Is Nothing) AndAlso loOrders.Tables.Count = 1 Then
            Return loOrders.Tables(0)
        EndIf
    Catch ex As Exception
        Throw ex
    EndTry
EndFunction
Private Function GetCustomers() As DataSet
    Dim loCustomers As DataSet
    Dim i As Int32
    'Get list of the customers from the database
    loCustomers = GetDataFromDb("SELECT CustomerID FROM Customers", CommandType.Text, "Customers")'Remove half of the customers for the demo purposes to show that we select info just for some of them
    If Not loCustomers Is Nothing Then
    If loCustomers.Tables.Contains("Customers") Then
            With loCustomers.Tables("Customers")
        i = .Rows.Count \ 2
        DoWhile .Rows.Count > i
            .Rows.RemoveAt(0)
        'Accept changes to remove the rows completely from the DataTable
        .AcceptChanges()
        EndWith
            ThrowNew ApplicationException("Customers table does not exist")
        EndIf
    EndIf
    'Return list of the customers as a DataSet
    Return loCustomers
    Catch ex As Exception
        Throw ex
    EndTry
EndFunction
Private Function GetDataFromDb(ByVal lcSQL AsString, ByVal loCommandType As CommandType, _
ByVal lcTableName AsString, ByValParamArray loParameters() As SqlParameter) As DataSet
    Dim loResult As DataSet
    Dim loConnection As SqlConnection
    Dim loCommand As SqlCommand
    Dim loAdapter As SqlDataAdapter
    Dim i As Int32
    Dim loParameter As SqlParameter
        'Create and open connection to the Northwind database
        loConnection = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=(local);Connect Timeout=30")
        loConnection.Open()'Prepare command and to select data from the database
        loCommand = New SqlCommand(lcSQL, loConnection)
        loCommand.CommandType = loCommandType
        IfNot loParameters IsNothingThen
        ForEach loParameter In loParameters
        loCommand.Parameters.Add(loParameter)
        EndIf
        loAdapter = New SqlDataAdapter(loCommand)
        loResult = New DataSet
        loAdapter.Fill(loResult, lcTableName)'Return list of the customers as a DataSet
        Return loResult
    Catch ex As Exception
        Throw ex
    Finally
        'Clean resources
        If Not loAdapter Is Nothing Then
            loAdapter.Dispose()
            loAdapter = Nothing
        EndIf
        If Not loCommand Is Nothing Then
            loCommand.Dispose()
            loCommand = Nothing
        EndIf
        If Not loConnection Is Nothing Then
            If loConnection.State = ConnectionState.Open Then
                loConnection.Close()
            EndIf
            loConnection.Dispose()
            loConnection = Nothing
        EndIf
    EndTry
  EndFunction
EndModule
按 F5 编译和运行应用程序。 控制台将显示结果。