Übergeben eines Wertearrays in SQL Server gespeicherten Prozedur mitHILFE von XML und Visual Basic .NET

Artikel
05/08/2022
4 Minuten Lesedauer

In diesem Artikel wird gezeigt, wie Sie ein Array von Werten mithilfe von XML und Visual Basic .NET an SQL Server gespeicherte Prozedur übergeben.

Originalversion des Produkts: Visual Basic
Ursprüngliche KB-Nummer: 555266

Problembeschreibung

Sie müssen ein Array von Werten an SQL Server gespeicherte Prozedur übergeben, um es als Liste für eine IN Klausel zu verwenden.

URSACHE

Die aktuelle Version von Microsoft SQL Server unterstützt keinen Array-Datentyp, der das Übergeben eines Wertearrays als Parameter der gespeicherten Prozedur oder SQL Anweisung ermöglichen würde. Häufig müssen Entwickler ein Array der Werte übergeben, um Datensätze basierend auf einer bereitgestellten Liste in einer IN Klausel auszuwählen. In einigen Fällen kann die Liste der Parameter, die an die gespeicherte Prozedur als durch Trennzeichen getrennte Zeichenfolge übergeben werden, und diese durch Trennzeichen getrennte Zeichenfolge nicht direkt als Parameter der IN-Klausel verwendet werden und muss in das Formular umgewandelt werden, das für die Verwendung innerhalb der IN Klausel akzeptabel ist.

Lösung

Eine der Lösungen für dieses Problem besteht darin, das Array der Werte als XML-Zeichenfolgenparameter an die gespeicherte Prozedur zu übergeben und den Anbieter innerhalb der gespeicherten Prozedur zum Auswählen von Werten aus dem bereitgestellten XML zu verwenden OPENXML rowset . Die Verwendung des OPENXML Anbieters in Verbindung mit den Transact-SQL-Anweisungen bietet eine flexible und einfache Möglichkeit zum Bearbeiten von Daten in einer Datenbank basierend auf einem übergebenen Array von Werten.

Erstellen des Projekts

Hinweis

Dieses Beispiel enthält keinen Code für die Ausnahmebehandlung, der in einer Anwendung auf Produktionsebene erforderlich wäre.

Wir verwenden die Tabelle "Bestellungen" aus der Northwind SQL Server Datenbank. Verwenden Sie die folgende Starke, um eine gespeicherte Prozedur in einer Northwind Datenbank zu erstellen.

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

Starten Sie Visual Studio .NET, und erstellen Sie die Konsolenanwendung. Standardmäßig wird die Datei "Module1.vb " erstellt.

Ersetzen Sie die starke Innenseite der Datei "Module1.vb " durch die folgende Datei. Beispiel strong wählt zur Vereinfachung und zum Beispiel die Liste aller Kunden aus der Tabelle "Kunden" aus, bereitet ein Array von Werten als XML-Zeichenfolge nur für die Hälfte der Kunden vor (um eine eingeschränkte Auswahl zu demonstrieren) und führt dann die gespeicherte Prozedur aus, um eine Liste der Bestellungen aus der Tabelle "Bestellungen" für die ausgewählten Kunden auszuwählen.

Hinweis

Sie müssten die Verbindungszeichenfolge in einem Beispiel stark ändern, um sie in Ihrer Umgebung zu verwenden.

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

Drücken Sie F5 , um die Anwendung zu kompilieren und auszuführen. Die Konsole zeigt die Ergebnisse an.