Cómo pasar la matriz de valores en SQL Server con Visual Basic .NET y XML de procedimiento almacenado

Autor:

Val Mazur MVP

COMUNIDAD SOLUCIONES CONTENIDO RENUNCIA

MICROSOFT CORPORATION Y/O SUS RESPECTIVOS PROVEEDORES GARANTIZAN LA IDONEIDAD, FIABILIDAD O EXACTITUD DE LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS CONTENIDOS EN ESTE DOCUMENTO. DICHA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS SE PROPORCIONAN "TAL CUAL" SIN GARANTÍA DE NINGÚN TIPO. MICROSOFT Y/O SUS RESPECTIVOS PROVEEDORES RENUNCIAN A TODA GARANTÍA Y CONDICIÓN RESPECTO A ESTA INFORMACIÓN Y GRÁFICOS RELACIONADOS, INCLUIDAS TODAS LAS GARANTÍAS IMPLÍCITAS Y LAS CONDICIONES DE COMERCIABILIDAD, IDONEIDAD PARA UN FIN DETERMINADO, ESFUERZO RAZONABLE, TÍTULO Y AUSENCIA DE INFRACCIÓN. USTED ACEPTA ESPECÍFICAMENTE QUE EN NINGÚN CASO MICROSOFT O SUS PROVEEDORES SERÁN RESPONSABLES DE DAÑOS DIRECTOS, INDIRECTOS, PUNITIVOS, INCIDENTALES, ESPECIALES, CONSECUENTES NI NINGÚN DAÑO, INCLUIDOS, SIN LIMITACIÓN, DAÑOS POR PÉRDIDA DE USO, DATOS O BENEFICIOS, QUE SURJA DE O EN CUALQUIER FORMA RELACIONADA CON EL USO DE O INCAPACIDAD DE USO DE LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS CONTENIDOS EN ESTE DOCUMENTO , YA SEA BASADO EN CONTRATO, AGRAVIO, NEGLIGENCIA, RESPONSABILIDAD ESTRICTA O DE LO CONTRARIO, INCLUSO SI TIENE MICROSOFT O CUALQUIERA DE SUS PROVEEDORES AVISADO DE LA POSIBILIDAD DE DAÑOS.

SÍNTOMAS

Es necesario pasar la matriz de valores en el procedimiento de SQL Server almacena para utilizarla como una lista para una cláusula IN.

CAUSA

Versión actual de Microsoft SQL Server no tiene ninguna compatibilidad de tipo de datos de matriz que permitiría pasando la matriz de valores como un parámetro del procedimiento almacenado o instrucción SQL. A menudo los programadores deben pasar una matriz de valores para seleccionar registros basados en una lista proporcionada en una cláusula IN. En alguna lista de casos de los parámetros pasados al procedimiento almacenado como una coma cadena delimitada y esta cadena delimitada no puede utilizarse directamente como un parámetro de la cláusula IN y debe transformarse en la forma que sea aceptable para el uso dentro de en la cláusula.

SOLUCIÓN

Una de las soluciones a este problema es pasar matriz de valores al procedimiento almacenado como XML parámetro de cadena y utilice el proveedor de conjunto de filas OPENXML dentro del procedimiento almacenado para seleccionar valores del XML proporcionado. Uso del proveedor OPENXML junto con las instrucciones de Transact-SQL proporciona una forma flexible y sencilla para manipular los datos en una base de datos basado en una matriz de valores que se pasa.
 
Crear el proyecto
 
Nota: En este ejemplo no contiene seguro para el control de excepciones que se necesitaría en una aplicación de nivel de producción.
 
Utilizaremos la tabla Orders de la base de datos Northwind de SQL Server 2000. Utilice el siguiente seguro para crear un procedimiento almacenado en una base de datos Northwind.
 
 
IF EXISTS (SELECT name
DE sysobjects
DONDE nombre = N 'sp_SelectOrders'
Y escriba = 'P')
DROP PROCEDURE sp_SelectOrders
IR
 
CREATE PROC sp_SelectOrders @in_values nText como
 
DECLARE @hDoc int
 
--Preparar los valores de entrada como un documento XML
exec sp_xml_preparedocument @hDoc salida, @in_values
 
--Seleccionar datos de la tabla basándose en valores de XML
Seleccione * FROM Pedidos WHERE (IdCliente IN
Seleccione CustomerID de OPENXML (@hdoc, '/ NewDataSet/clientes', 1)
CON NCHAR(5))) (CustomerID
 
EXEC sp_xml_removedocument @hDoc
 
IR
 
Inicie Microsoft Visual Studio .NET y cree la aplicación de consola. Se creará el archivo Module1.vb de forma predeterminada.
 
Reemplazar el seguro dentro del archivo Module1.vb con el siguiente. Para simplificar el ejemplo, selecciona fuerte muestra la lista de todos los clientes de la tabla Customers, prepara la matriz de valores como una cadena XML sólo para la mitad de los clientes (para demostrar la selección limitada) y, a continuación, se ejecuta un procedimiento almacenado para seleccionar la lista de los pedidos de la tabla pedidos para los clientes seleccionados y.
 
Nota: Debe modificar la cadena de conexión en un ejemplo fuerte 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
 
' Obtener la lista de los clientes de la base de datos
loCustomers = GetCustomers()
 
Console.WriteLine ("Total de clientes:" & loCustomers.Tables(0). Rows.Count.ToString)
 
IfNot loCustomers IsNothingThen
loOrders = GetOrders(loCustomers)
Console.WriteLine ("Total de pedidos:" & loOrders.Rows.Count.ToString)
EndIf
 
Catch ex As excepción
            Console.WriteLine(ex.Message)
Por último
 
IfNot loCustomers IsNothingThen
loCustomers.Dispose()
loCustomers = Nothing
EndIf
 
IfNot loOrders IsNothingThen
loOrders.Dispose()
loOrders = Nothing
EndIf
 
EndTry
 
EndSub
 
PrivateFunction GetOrders(ByVal loCustomers As DataSet) As DataTable
 
Dim loOrders As DataSet
Dim loParameter As SqlParameter
Dim loCol DataColumn como
 
Try
 
' Preparar la salida XML del conjunto de datos de los clientes como una cadena
LoCol de ForEach en loCustomers.Tables("Customers"). Columnas
                loCol.ColumnMapping = System.Data.MappingType.Attribute
Siguiente
 
' Pasar el XML al procedimiento almacenado como un parámetro
loParameter = New SqlParameter("@in_values", System.Data.SqlDbType.NText)
            loParameter.Value = loCustomers.GetXml
 
' Obtener la lista de los pedidos de la base de datos
loOrders = GetDataFromDb ("sp_SelectOrders", CommandType.StoredProcedure, "Customers", loParameter)
 
' Lista de devolución de los pedidos como DataTable
Si (no loOrders IsNothing) AndAlso loOrders.Tables.Count = 1 Then
Devolver loOrders.Tables(0)
EndIf
 
Catch ex As excepción
Throw ex
EndTry
 
EndFunction
 
PrivateFunction GetCustomers() como DataSet
 
Dim loCustomers As DataSet
Dim i As Int32
 
Try
 
' Obtener la lista de los clientes de la base de datos
loCustomers = GetDataFromDb ("SELECT IdCliente FROM clientes", CommandType.Text, "Customers")
 
' Quitar la mitad de los clientes para los fines de la demostración para mostrar que seleccionamos información sólo para algunos de ellos
IfNot loCustomers IsNothingThen
 
Si, a continuación, loCustomers.Tables.Contains("Customers")
Con loCustomers.Tables("Customers")
 
i = . Rows.Count \ 2
DoWhile. Rows.Count > i
                            .Rows.RemoveAt(0)
Bucle
 
' Aceptar cambios para quitar por completo las filas de la tabla de datos
. AcceptChanges()
EndWith
Else
ThrowNew ApplicationException("Customers table does not exist")
EndIf
 
EndIf
 
' Lista de devolución de los clientes como un conjunto de datos
Devuelva loCustomers
 
Catch ex As excepción
Throw ex
EndTry
 
EndFunction
 
PrivateFunction GetDataFromDb(ByVal lcSQL AsString, ByVal loCommandType As CommandType, _
LcTableName ByVal AsString, ByValParamArray loParameters() As SqlParameter) As DataSet
 
Dim loResult As DataSet
Dim loConnection As SqlConnection
Dim loCommand como SqlCommand
Dim loAdapter As SqlDataAdapter
Dim i As Int32
Dim loParameter As SqlParameter
 
Try
 
' Crear y abrir la conexión a la base de datos Northwind
loConnection = New SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=(local); Conectar Timeout = 30")
loConnection.Open()
 
' Preparar el comando y seleccionar los datos de la base de datos
loCommand = New SqlCommand(lcSQL, loConnection)
loCommand.CommandType = loCommandType
 
IfNot loParameters IsNothingThen
ForEach loParameter en loParameters
loCommand.Parameters.Add(loParameter)
Siguiente
EndIf
 
loAdapter = New SqlDataAdapter(loCommand)
 
loResult = nuevo conjunto de datos
loAdapter.Fill (loResult, lcTableName)
 
' Lista de devolución de los clientes como un conjunto de datos
Devuelva loResult
 
Catch ex As excepción
Throw ex
Por último
 
' Limpiar los recursos
IfNot loAdapter IsNothingThen
loAdapter.Dispose()
loAdapter = Nothing
EndIf
 
IfNot loCommand IsNothingThen
loCommand.Dispose()
loCommand = Nothing
EndIf
 
IfNot loConnection IsNothingThen
 
Si loConnection.State = ConnectionState.Open Then
loConnection.Close()
EndIf
 
loConnection.Dispose()
loConnection = Nothing
 
EndIf
EndTry
 
EndFunction
 
EndModule
 
4. presione F5 para compilar y ejecutar la aplicación. Consola mostrará los resultados.
Propiedades

Id. de artículo: 555266 - Última revisión: 15 feb. 2017 - Revisión: 1

Microsoft ADO.NET 1.1, Microsoft ADO.NET 1.0, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft Visual Basic .NET 2003 Standard Edition, Microsoft Visual Basic .NET 2002 Standard Edition

Comentarios