Übergeben eines Arrays von Werten an SQL Server gespeicherte Prozedur mithilfe von XML und Visual Basic .NET
In diesem Artikel wird gezeigt, wie Sie ein Array von Werten mithilfe von XML und Visual Basic .NET an SQL Server gespeicherte Prozedur übergeben.
Ursprüngliche Produktversion: Visual Basic
Ursprüngliche KB-Nummer: 555266
Symptome
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 verfügt über keine Unterstützung des Arraydatentyps, der die Übergabe eines Arrays von Werten als Parameter der gespeicherten Prozedur oder SQL-Anweisung ermöglicht. 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 als durch Trennzeichen getrennte Zeichenfolge an die gespeicherte Prozedur übergeben werden, und diese durch Trennzeichen getrennte Zeichenfolge kann nicht direkt als Parameter der IN-Klausel verwendet werden und muss in die Form transformiert werden, die für die Verwendung innerhalb der IN
-Klausel akzeptabel ist.
Lösung
Eine der Lösungen für dieses Problem besteht darin, ein Array der Werte als XML-Zeichenfolgenparameter an die gespeicherte Prozedur zu übergeben und den Anbieter innerhalb der gespeicherten Prozedur zu verwenden OPENXML rowset
, um Werte aus dem bereitgestellten XML-Code auszuwählen. Die Verwendung des OPENXML
Anbieters in Verbindung mit den Transact-SQL-Anweisungen bietet eine flexible und einfache Möglichkeit, Daten in einer Datenbank basierend auf einem übergebenen Array von Werten zu bearbeiten.
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 Orders aus der Northwind
SQL Server-Datenbank. Verwenden Sie den folgenden Starken, 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 eine Konsolenanwendung. Standardmäßig wird Module1.vb Datei erstellt.
Ersetzen Sie das strong-Element in der Module1.vb-Datei durch die folgende Datei. Zur Vereinfachung und zum Beispiel wählt sample strong die Liste aller Kunden aus der Tabelle Customers aus, bereitet ein Array von Werten als XML-Zeichenfolge nur für die Hälfte der Kunden vor (um die eingeschränkte Auswahl zu veranschaulichen) und führt dann eine gespeicherte Prozedur aus, um die Liste der Aufträge aus der Tabelle Orders für die ausgewählten Kunden auszuwählen.
Hinweis
Sie müssen Verbindungszeichenfolge in einem Beispiel stark ändern, um es 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. In der Konsole werden die Ergebnisse angezeigt.
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unterFeedback senden und anzeigen für