Passare una matrice di valori in SQL Server stored procedure usando XML e Visual Basic .NET
Questo articolo illustra come passare la matrice di valori in SQL Server stored procedure usando XML e Visual Basic .NET.
Versione originale del prodotto: Visual Basic
Numero KB originale: 555266
Sintomi
È necessario passare una matrice di valori in SQL Server stored procedure per usarla come elenco per una IN
clausola.
CAUSA
La versione corrente di Microsoft SQL Server non dispone di alcun supporto del tipo di dati della matrice che consenta il passaggio della matrice di valori come parametro della stored procedure o dell'istruzione SQL. Spesso gli sviluppatori devono passare una matrice dei valori per selezionare i record in base a un elenco specificato in una IN
clausola. In alcuni casi l'elenco dei parametri passati alla stored procedure come stringa delimitata da virgole e questa stringa delimitata non può essere utilizzata direttamente come parametro della clausola IN e deve essere trasformata nel formato accettabile per l'uso all'interno della IN
clausola .
Risoluzione
Una delle soluzioni a questo problema consiste nel passare la matrice dei valori alla stored procedure come parametro di stringa XML e usare OPENXML rowset
il provider all'interno della stored procedure per selezionare i valori dal codice XML fornito. L'uso del OPENXML
provider in combinazione con le istruzioni Transact-SQL offre un modo semplice e flessibile per modificare i dati in un database in base a una matrice di valori passata.
Creare il progetto
Nota
Questo esempio non contiene codice per la gestione delle eccezioni necessaria in un'applicazione a livello di produzione.
Verrà usata la tabella Orders del Northwind
database SQL Server. Usare il codice sicuro seguente per creare una stored procedure in un Northwind
database.
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
Avviare Visual Studio .NET e creare un'applicazione console. Per impostazione predefinita verrà creato Module1.vb file.
Sostituire l'elemento sicuro all'interno del file Module1.vb con quello seguente. Per semplificare ed esempio, l'esempio strong seleziona l'elenco di tutti i clienti dalla tabella Customers, prepara la matrice di valori come stringa XML solo per metà dei clienti (per dimostrare la selezione limitata) e quindi esegue la stored procedure per selezionare l'elenco degli ordini dalla tabella Orders per i clienti selezionati.
Nota
È necessario modificare stringa di connessione in un campione sicuro per usarlo nell'ambiente in uso
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
Premere F5 per compilare ed eseguire l'applicazione. La console visualizzerà i risultati.
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per