Passer un tableau de valeurs dans SQL Server procédure stockée à l’aide de XML et de Visual Basic .NET
Cet article explique comment passer un tableau de valeurs à SQL Server procédure stockée à l’aide de XML et de Visual Basic .NET.
Version d’origine du produit : Visual Basic
Numéro de la base de connaissances d’origine : 555266
Symptômes
Vous devez passer un tableau de valeurs dans SQL Server procédure stockée pour l’utiliser comme liste pour une IN
clause.
CAUSE
La version actuelle de Microsoft SQL Server ne prend pas en charge le type de données de tableau qui permettrait de transmettre un tableau de valeurs en tant que paramètre de la procédure stockée ou de l’instruction SQL. Souvent, les développeurs doivent passer un tableau des valeurs pour sélectionner des enregistrements en fonction d’une liste fournie dans une IN
clause. Dans certains cas, liste des paramètres passés à la procédure stockée en tant que chaîne délimitée par des virgules et cette chaîne délimitée ne peut pas être utilisée directement comme paramètre de la clause IN et doit être transformée dans la forme acceptable pour l’utilisation à l’intérieur de la IN
clause.
Résolution
L’une des solutions à ce problème consiste à passer un tableau des valeurs à la procédure stockée en tant que paramètre de chaîne XML et à utiliser OPENXML rowset
le fournisseur à l’intérieur de la procédure stockée pour sélectionner des valeurs à partir du code XML fourni. L’utilisation du OPENXML
fournisseur conjointement avec les instructions Transact-SQL offre un moyen simple et flexible de manipuler des données dans une base de données en fonction d’un tableau de valeurs transmis.
Créez le projet
Remarque
Cet exemple ne contient pas de code pour la gestion des exceptions qui serait nécessaire dans une application de niveau production.
Nous allons utiliser la table Orders de la Northwind
base de données SQL Server. Utilisez la valeur forte suivante pour créer une procédure stockée dans une Northwind
base de données.
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
Démarrez Visual Studio .NET et créez une application console. Par défaut , Module1.vb fichier est créé.
Remplacez le fort à l’intérieur du fichier Module1.vb par le suivant. Pour simplifier et par exemple, l’exemple strong sélectionne la liste de tous les clients de la table Customers, prépare le tableau de valeurs sous forme de chaîne XML uniquement pour la moitié des clients (pour illustrer la sélection limitée), puis exécute la procédure stockée pour sélectionner la liste des commandes de la table Orders pour les clients sélectionnés.
Remarque
Vous devez modifier chaîne de connexion dans un exemple fort pour l’utiliser dans votre environnement
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
Appuyez sur F5 pour compiler et exécuter l’application. La console affiche les résultats.
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultezEnvoyer et afficher des commentaires pour