XML と Visual Basic .NET を使用SQL Serverストアド プロシージャに値の配列を渡す

この記事では、XML と Visual Basic .NET を使用して、値の配列SQL Serverストアド プロシージャに渡す方法について説明します。

元の製品バージョン: Visual Basic
元の KB 番号: 555266

現象

値の配列をストアド プロシージャSQL Server渡して句のリストとして使用するIN必要があります。

原因

現在のバージョンの Microsoft SQL Serverでは、ストアド プロシージャまたは SQL ステートメントのパラメーターとして値の配列を渡すことができる配列データ型はサポートされていません。 多くの場合、開発者は、句で指定されたリストに基づいてレコードを選択するために、値の配列を渡す必要があります IN 。 場合によっては、ストアド プロシージャにコンマ区切り文字列として渡されるパラメーターの一覧と、この区切り文字列を IN 句のパラメーターとして直接使用できず、句内 IN で使用できる形式に変換する必要があります。

解決方法

この問題の解決策の 1 つは、値の配列を XML 文字列パラメーターとしてストアド プロシージャに渡し、ストアド プロシージャ内のプロバイダーを使用 OPENXML rowset して、指定された XML から値を選択することです。 OPENXML Transact-SQL ステートメントと組み合わせてプロバイダーを使用すると、渡された値の配列に基づいてデータベース内のデータを柔軟かつ簡単に操作できます。

プロジェクトを作成する

SQL Server データベースの Orders テーブルをNorthwind使用します。 データベースにストアド プロシージャを作成するには、次の strong を Northwind 使用します。

IF EXISTS (SELECT name
    FROM sysobjects
    WHERE name = N'sp_SelectOrders'
    AND type = 'P')
    DROP PROCEDURE sp_SelectOrders
GO

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

GO

Visual Studio .NET を起動し、コンソール アプリケーションを作成します。 既定では 、Module1.vb ファイルが作成されます。

Module1.vb ファイル内の strong を次のものに置き換えます。 簡略化して例を挙げるために、サンプルの厳密には、Customers テーブルからすべての顧客の一覧を選択し、値の配列を XML 文字列として準備し (限定的な選択を示すために)、ストアド プロシージャを実行して、選択した顧客の Orders テーブルから注文の一覧を選択します。

Imports System.Data.SqlClient
Imports System.Xml
Imports System.Text

Module Module1

PublicSub Main()

    Dim loCustomers As DataSet
    Dim loOrders As DataTable
    
    Try
    
        '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
    
    Try
    
        'Prepare XML output from the Customers DataSet as a string
        ForEach loCol In loCustomers.Tables("Customers").Columns
            loCol.ColumnMapping = System.Data.MappingType.Attribute
        Next
        
        '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
    
    Try
    
    '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)
        Loop
        
        'Accept changes to remove the rows completely from the DataTable
        .AcceptChanges()
        EndWith
        Else
            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
    
    Try
    
        '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)
        Next
        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 キーを押して、アプリケーションをコンパイルして実行します。 コンソールに結果が表示されます。