Pasar una matriz de valores a un procedimiento almacenado de SQL Server mediante XML y Visual Basic .NET

  • Tiempo de lectura: 4 minutos

En este artículo se muestra cómo pasar una matriz de valores a un procedimiento almacenado de SQL Server mediante XML y Visual Basic .NET.

Versión del producto original:   Visual Basic
Número de KB original:   555266

Síntomas

Es necesario pasar una matriz de valores al procedimiento almacenado de SQL Server para usarlo como una lista de una IN cláusula.

CAUSA

La versión actual de Microsoft SQL Server no tiene ninguna compatibilidad con DataType de Array que permitiera pasar una matriz de valores como un parámetro del procedimiento almacenado o de la instrucción SQL. Con frecuencia, es necesario que los desarrolladores pasen una matriz de los valores para seleccionar registros basándose en una lista proporcionada en una IN cláusula. En algunos casos, 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 debe transformarse en el formulario que es aceptable para usar dentro de la IN cláusula.

Solución

Una de las soluciones a este problema es pasar la matriz de los valores al procedimiento almacenado como un parámetro de cadena XML y usar el OPENXML rowset proveedor dentro del procedimiento almacenado para seleccionar valores del XML proporcionado. El uso del OPENXML proveedor junto con las instrucciones Transact-SQL proporciona una forma sencilla y flexible de manipular datos en una base de datos basada en una matriz de valores que se pasa.

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.

Se usará la tabla Orders de la Northwind base de datos de SQL Server. Use el siguiente nivel de seguridad 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. Se creará el archivo Module1. VB predeterminado.

Reemplace el Strong dentro del archivo Module1. VB por el siguiente. Para simplificar y, por ejemplo, el ejemplo selecciona de forma segura la lista de todos los clientes de la tabla clientes, prepara una matriz de valores como cadena XML sólo para la mitad de los clientes (para mostrar una selección limitada) y, a continuación, ejecuta el procedimiento almacenado para seleccionar la lista de los pedidos de la tabla pedidos para los clientes seleccionados.

Nota

Debe modificar la cadena de conexión en una muestra segura para usarla 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.