Cómo pasar una matriz de valores a un procedimiento almacenado de SQL Server mediante XML y Visual Basic .NET


Autorización


MVP de Val Mazur

RENUNCIA DE CONTENIDO DE SOLUCIONES DE LA COMUNIDAD


MICROSOFT CORPORATION Y/O SUS RESPECTIVOS PROVEEDORES NO REALIZAN NINGUNA DECLARACIÓN SOBRE LA IDONEIDAD, FIABILIDAD O PRECISIÓN DE LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS QUE SE INCLUYEN EN EL PRESENTE DOCUMENTO. TODA LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS SE PROPORCIONAN "TAL CUAL", SIN GARANTÍAS DE NINGÚN TIPO. MICROSOFT Y/O SUS RESPECTIVOS PROVEEDORES RENUNCIAN POR LA PRESENTE A TODA GARANTÍA Y CONDICIÓN EN RELACIÓN CON ESTA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS, INCLUIDAS TODAS LAS GARANTÍAS Y CONDICIONES IMPLÍCITAS DE COMERCIABILIDAD, IDONEIDAD PARA UN PROPÓSITO PARTICULAR, ESFUERZO PROFESIONAL, TITULARIDAD Y AUSENCIA DE INFRACCIÓN. USTED ACEPTA EXPRESAMENTE QUE EN NINGÚN CASO MICROSOFT NI SUS PROVEEDORES SERÁN RESPONSABLES POR NINGÚN DAÑO DIRECTO, INDIRECTO, PUNITIVO, INCIDENTAL, ESPECIAL, DERIVADO NI NINGÚN DAÑO INCLUIDO, SIN LÍMITES, DAÑOS POR PÉRDIDA DE USO, DATOS O BENEFICIOS, DERIVADOS DE O DE NINGUNA MANERA RELACIONADA CON EL USO O LA INCAPACIDAD PARA USAR LA INFORMACIÓN Y LOS GRÁFICOS RELACIONADOS QUE SE INCLUYEN EN EL PRESENTE, YA SEA EN FUNCIÓN DE UN CONTRATO, AGRAVIO, NEGLIGENCIA, RESPONSABILIDAD ESTRICTA O DE OTRO MODO, INCLUSO SI SE HA INFORMADO A MICROSOFT O A SUS PROVEEDORES DE LA POSIBILIDAD DE DAÑOS.

TOMA


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

CAUSA


La versión actual de Microsoft SQL Server no tiene ninguna compatibilidad de tipo de tabla de matriz que permitiera pasar matrices de valores como parámetro del procedimiento almacenado o de la instrucción SQL. A menudo, es necesario que los desarrolladores pasen una matriz de los valores para seleccionar los registros basándose en una lista proporcionada en una cláusula IN. 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 se debe transformar en el formulario que sea aceptable para usar dentro de la cláusula in.

Solution


Una de las soluciones a este problema es pasar la matriz de valores a continuación como un parámetro de cadena XML y usar el proveedor de conjuntos de filas OPENXML en el procedimiento almacenado para seleccionar los valores del XML proporcionado. El uso del proveedor OPENXML junto con las instrucciones Transact-SQL proporciona una forma sencilla y flexible de manipular datos en una base de datos basándose en una matriz de valores que se pasa. Crear el proyecto tenga en cuenta que este ejemplo no contiene seguridad para el control de excepciones que sería necesaria en una aplicación de nivel de producción. Usaremos la tabla pedidos de la base de datos Northwind de SQL Server 2000. Use el siguiente nivel de seguridad para crear un procedimiento almacenado en una base de datos Northwind.  Si existe (seleccione nombre de sysobjects donde name = N ' sp_SelectOrders ' AND type = ' P ') Quite el procedimiento sp_SelectOrdersGO CREATE PROC sp_SelectOrders @in_values nText como declare @hDoc int--Prepare valores de entrada como un XML documnetexec sp_xml_preparedocument @hDoc OUTPUT. @in_values: seleccione los datos de la tabla en función de los valores de XMLSELECT * FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM OPENXML (@hdoc, '/NewDataSet/Customers ', 1) WITH (CustomerID NCHAR (5))) EXEC sp_xml_removedocument @hDoc iniciar Microsoft Visual Studio .NET y crear una aplicación de consola. Se creará el archivo. VB predeterminado Module1. VB. Reemplace el Strong dentro del archivo Module1. VB con el siguiente. Para simplificar y, por ejemplo, la opción strong de ejemplo selecciona la lista de todos los clientes de la tabla clientes, prepara una matriz de valores como una cadena XML para la mitad de los clientes (para mostrar la selección limitada) y, a continuación, ejecuta el procedimiento almacenado para seleccionar la lista de los pedidos de la tabla pedidos de los clientes seleccionados. Nota: Tendría que modificar la cadena de conexión de una de las muestras seguras para usarla en el entorno Imports. System. Data. SqlClientImports System. XmlImports System. Text (módulo) Module1 PublicSub Main () Dim loCustomers as DataSet DIM loOrders as DataTable Try ' obtener una lista de los clientes de la base de datos loCustomers = GetCustomers () Console. WriteLine ("total clientes:" & loCustomers. Rows. Count. ToString) IfNot loCustomers IsNothingThen loOrders = GetOrders (loCustomers) Console. WriteLine ("pedidos totales:" & loOrders. Rows. Count. ToString) EndIf Catch ex as Exception Console. WriteLine (p. ej. Mensaje) Finally 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 as DataColumn Try ' preparar la salida XML del DataSet de customers como una cadena de loCol Columnas loCol. ColumnMapping = System. Data. MappingType. Attribute Next ' pasar XML al procedimiento almacenado como un parámetro loParameter = New SqlParameter ("@in_values", System. Data. SqlDbType. NText) loParameter. Value = loCustomers. GetXml ' obtener una lista de los pedidos de la base de datos loOrders = GetDataFromDb ("sp_SelectOrders", CommandType. StoredProcedure, "clientes", loParameter) ' lista de devoluciones de pedidos como DataTable si (no loOrders IsNothing) AndAlso loOrders. tables. Count = 1, devuelve loOrders. Tables (0) EndIf Catch ex as Exception Throw ex EndTry EndFunction PrivateFunction GetCustomers () as DataSet DIM loCustomers as DataSet DIM i as Int32 Try ' obtener una lista de los clientes de la base de datos loCustomers = GetDataFromDb ("seleccione CustomerID de customers", CommandType. Text, "clientes") ' Quite la mitad de los clientes para los fines de demostración para mostrar que seleccionamos info solo para algunos de ellos IfNot loCustomers IsNothingThen si loCustomers. tables. Contains ("Customers"), entonces con loCustomers. Tables ("Customers") i =. Rows. Count \ 2. Rows. Count > i. Rows. RemoveAt (0) loop ' aceptar los cambios para quitar las filas por completo de la DataTable. AcceptChanges () EndWith else ThrowNew ApplicationException ("la tabla de clientes no existe") EndIf EndIf ' lista de devoluciones de los clientes como devolución de DataSet loCustomers Catch ex as Exception Throw ex EndTry EndFunction PrivateFunction GetDataFromDb (ByVal lcSQL, 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 use ' crear y abrir conexión con la base de datos Northwind loConnection = New SqlConnection ("Persist Security info = false; Integrated Security = SSPI; Database = Northwind; Server = (local); Connect timeout = 30 ") loConnection. Open () ' comando de preparación y para seleccionar 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) Next EndIf loAdapter = New SqlDataAdapter (loCommand) loResult = New DataSet loAdapter. Fill (loResult, lcTableName) ' lista devuelta de los clientes como un valor DataSet Return loResult Catch ex as Exception Throw ex, ' recursos limpios 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. La consola mostrará los resultados.