Pasar matriz de valores a SQL Server procedimiento almacenado mediante XML y Visual Basic .NET
En este artículo se muestra cómo pasar la matriz de valores a SQL Server procedimiento almacenado mediante XML y Visual Basic .NET.
Versión original del producto: Visual Basic
Número de KB original: 555266
Síntomas
Debe pasar la matriz de valores a SQL Server procedimiento almacenado para usarlo como una lista de una IN
cláusula.
CAUSA
La versión actual de Microsoft SQL Server no admite ningún tipo de datos de matriz que permita pasar la matriz de valores como parámetro del procedimiento almacenado o la instrucción SQL. A menudo, los desarrolladores deben pasar una matriz de los valores para seleccionar registros en función de una lista proporcionada en una IN
cláusula. En algunos casos, la lista de los parámetros pasados al procedimiento almacenado como una cadena delimitada por comas y esta cadena delimitada no se puede usar directamente como parámetro de la cláusula IN y tiene que transformarse en el formulario que es aceptable para usar dentro de la cláusula .IN
Solución
Una de las soluciones a este problema es pasar la matriz de los valores al procedimiento almacenado como parámetro de cadena XML y usar OPENXML rowset
el proveedor dentro del procedimiento almacenado para seleccionar valores del XML proporcionado. El uso del OPENXML
proveedor junto con las instrucciones Transact-SQL proporciona una manera flexible y sencilla de manipular los datos de una base de datos en función de una matriz de valores pasada.
Cree el proyecto
Nota:
Este ejemplo no contiene código para el control de excepciones que sería necesario en una aplicación de nivel de producción.
Usaremos la tabla Orders de la Northwind
base de datos SQL Server. Use la siguiente opción segura para crear un procedimiento almacenado en una Northwind
base de datos.
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
Inicie Visual Studio .NET y cree una aplicación de consola. De forma predeterminada , se creará Module1.vb archivo.
Reemplace el elemento seguro dentro del archivo Module1.vb por el siguiente. Para simplificar y dar ejemplo, el ejemplo fuerte selecciona la lista de todos los clientes de la tabla Customers, prepara la matriz de valores como una cadena XML solo para la mitad de los clientes (para demostrar una selección limitada) y, a continuación, ejecuta el procedimiento almacenado para seleccionar la lista de pedidos de la tabla Orders para los clientes seleccionados.
Nota:
Tendría que modificar cadena de conexión en un ejemplo seguro para usarlo en su entorno
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
Presione F5 para compilar y ejecutar la aplicación. La consola mostrará los resultados.
Comentarios
https://aka.ms/ContentUserFeedback.
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea:Enviar y ver comentarios de