Twitter Facebook Google + RSS Feed

Arreglos en SQL Server

16
.NETSQL Server

Viernes, 03 de octubre de 2008 a las 17:18hs por Dario Krapp

El uso de arreglos (o arrays) es tan necesario en ciertas ocasiones que ha hecho que su aparición sea tan inmediata como los primeros lenguajes declarativos, y hasta la actualidad, los lenguajes más modernos no los han descartado.

mov [BX],AX; Assember
array = (int *)malloc (N*sizeof(int)); C
type Int_Buffer is array (1..10) of Integer; Adda
int *iarray;iarray = new int [10]; C++
DIM vars$(52) Basic
Dim c(3 to 82) as Integer Microsoft Visual Basic
int[] numbers = new int[10]; C#

Entonces ¿podrían ser los arreglos tan necesarios en el T-SQL de Sql Server? La respuesta es que depende para quien, como suelen ser las respuestas a casi todas las preguntas formulables. Quizás esta necesidad no se manifieste tanto dentro del propio motor de base de datos, ya que siempre es posible acceder a los datos necesarios de alguna u otra forma, pero dentro del mundo de los desarrolladores la necesidad es innegable, como lo veremos con un ejemplo posteriormente. Claro que todo este preámbulo no tendría sentido si el T-SQL de Sql Server tuviese arreglos, pero de hecho no los posee (dejemos de lado por ahora la llegada de SQL Server 2008). ¿Entonces como se puede resolver este problema?, plantearemos entonces el siguiente caso que es, como acostumbramos, un caso del mundo real.

Hace un tiempo desarrollamos una aplicación que empleaba SQL Server 2005 como gestor para el soporte de datos. No tardó en aparecer la siguiente situación, entre nuestras tablas se daba, por las reglas de negocios el caso de que cuando se daba de alta un registro en una tabla del tipo “cabecera” debían asociársele unos cuantos registros del tipo “detalle” conjuntamente. Supongamos el caso de las tablas que se muestran a continuación que representan la estructura del problema abstrayendo complicaciones adicionales.


En nuestro caso estábamos empleando C# sobre el Framework 3.5 y ADO.NET para que nuestra capa de datos accediera al motor de base de datos SQL Server 2005, ante el problema analizamos las siguientes opciones:

  1. Emplear procedimientos almacenados (haciendo tantas llamadas como fueran necesarias).
  2. Emplear consultas dinámicas.
  3. Emplear un procedimiento almacenado que resolviera todo de alguna forma.

Recordemos que nuestro problema a resolver era que cuando se creaba un registro cabecera, en conjunto se creaban siempre varios registros detalle.

La primera aproximación fue la más inocente, poseer un procedimiento almacenado que inserta una cabecera y otro que inserta un detalle, llamémoslos ins_cabecera e ins_detalle para darle nombres intuitivos, entonces si había que crear una cabecera con, por ejemplo, 10 detalles. La metodología era llamar a los procedimientos almacenados desde nuestra capa de datos en .NET de la siguiente forma

ins_cabecera “datos cabecera”
ins_detalle “datos detalle 1”
ins_detalle “datos detalle 2”
ins_detalle “datos detalle 3”
ins_detalle “datos detalle 4”
ins_detalle “datos detalle 5”
ins_detalle “datos detalle 6”
ins_detalle “datos detalle 7”
ins_detalle “datos detalle 8”
ins_detalle “datos detalle 9”
ins_detalle “datos detalle 10”

Claro está que funciona perfectamente, pero hay algo que no se ve muy bien y es cada vez que se llama a una inserción en la capa de datos de .NET se produce una larga cantidad de llamadas contra el motor de base de datos, a pesar de ser procedimientos almacenados, claro está que hay una gran pérdida de tiempo. Inmediatamente se pensó que había que evitar hacer tantas llamadas.

Una segunda opción, (que creo que la descartamos incluso desde antes que a alguien se le ocurriera mencionarla), es la del uso de consultas dinámicas. Básicamente es la idea de escribir una larga sentencia T-SQL y pasársela al servidor de una sola vez para que el mismo la ejecute, este tipo de solución haría una sola llamada, claro está, pero hay muchas desventajas al usar consultas dinámicas en contrapartida a procedimientos almacenados.

En SQL Server (y estoy convencido que en cualquier otro motor de base de datos también), el uso de procedimientos almacenados ofrece ventajas en contraste a las consultas dinámicas, un factor clave es por ejemplo la compilación del plan de trabajo (la estrategia que empleará el motor de base de datos para acceder a los datos) que es reutilizada en procedimientos almacenados y no tiene que recalcularse cada vez que se lanza la consulta, un factor que suma puntos a la hora de evaluar la performance.

Además el uso de procedimientos almacenados asegura la inmunidad contra ataques por injection, como ya sabemos es posible crear consultas dinámicas parametrizadas, pero queda en manos de l del programador utilizarlos, quien puede omitir esta práctica y armar las sentencias por simple concatenación, quedando el sitio vulnerable a ataques por injection, (aunque parezca una locura, claramente este problema es más común de lo que uno se imagina), en cambio, en las llamadas a procedimientos almacenados no existe la posibilidad de dejar esa puerta de entrada. Finalmente otra ventaja es que los procedimientos almacenados pueden invocarse unos a otros, lo cual puede permitir atomizar y reutilizar código, otro punto a favor (Exceptuando lamentablemente nuestro caso en SQL Server 2005 donde la opción no está disponible) los procedimientos almacenados pueden depurarse con mucha facilidad, estos son a mi parecer motivos suficientes para desterrar a las consultas dinámicas, aunque hay situaciones en que no es posible utilizar procedimientos almacenados, por ejemplo existen aplicaciones que crean, modifican y destruyen estructuras de datos dentro del motor de base de datos empleando instrucciones DDL, en éstos casos el acceso a datos posiblemente deberá llevarse a cabo mediante consultas dinámicas. Esperemos que los programados sean cautos en esos casos delicados.

Entonces nos quedó nuestra última carta emplear una sola llamada a un procedimiento almacenado que resolviera todo el problema. Siendo algo así como la solución mágica, la misma consideraría pasarle a nuestro procedimiento almacenado, llamémoslo ins_cabdet, los datos de la cabecera y una estructura con todos los detalles (ya que no sabemos cuántos puede haber) para que los inserte juntos ins_cabdet de una sola vez.

Nuestra función desde la capa de datos en .NET tomaría la siguiente forma:

ins_cabdet "datos cabecera", Estructura("datos detalle 1", "datos detalle 2" ... "datos detalle 10");

Pero aquí mismo es donde aparece el problema, un arreglo nos vendría más que bien en este caso, pero ya dijimos que SQL Server no soporta arreglos (para quien recién halla cambiado de canal y se haya perdido el principio, recordemos que estamos omitiendo SQL Server 2008). Claramente ahora deberíamos pensar que los arreglos si eran tan necesarios el T-SQL de Sql Server. De todas formas no todo estuvo perdido, el problema pudimos resolverlo desde otro lugar algo lejano. Una de las características que posee SQL Server 2005 es el uso de XML, SQL Server 2005 posee un tipo de datos XML y es capaz de parsear XML’s empleando incluso XPath, entonces la idea fue clara. si no podemos pasarle arreglos al SQL Server intentemos pasarle un parámetro XML con los datos necesarios para poder realizar las inserciones, el XML es flexible y fácil de usar por lo que este tipo de solución es adaptable a muchos casos similares. El precio es claramente la creación del XML desde .NET y el parseo desde SQL Server, pero hoy día ya casi nada es gratis.

Debimos diseñar primero un formato de XML, creamos algo similar a lo siguiente:

<r>
    <h nm="nombre_cab" />
    <dc>
        <d nm="nombre_de1"/>
        <d nm="nombre_de2"/>
    </dc>
</r>

Con nuestro fragmento XML armamos un procedimiento almacenado, al que llamamos Ins_CabDet el cual haría lo siguiente:

  • Extracción de cabecera del xml
  • Inserción de cabecera
  • Para cada detalle del xml
    • Extracción del detalle del xml
    • Inserción del detalle

    En el mundo real la función tomó la siguiente forma:

    CREATE PROCEDURE Ins_CabDet
     @doc xml
    AS
    BEGIN
     BEGIN TRY
      DECLARE @docHandle int
      EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      BEGIN TRAN
      INSERT INTO Cabecera (Nombre)
      SELECT nm 'Nombre'
      FROM
      OPENXML (@docHandle,'r/h',1)
       WITH (
        nm nvarchar(20)
       )
      DECLARE @IDCabecera int
      SET @IDCabecera = @@IDENTITY
      INSERT INTO Detalle (IDCabecera, Nombre, OtroDato1, OtroDato2)
      SELECT @IDCabecera 'IDCabecera', nm 'Nombre', od1 'OtroDato1',  od2 'OtroDato2'
      FROM
      OPENXML (@docHandle,'r/dc/d',1)
       WITH (
        nm nvarchar(20),
        od1 int,
        od2 datetime
       )
      COMMIT
      RETURN 0
     END TRY
     BEGIN CATCH
      IF @@TRANCOUNT > 0
       ROLLBACK
      RETURN -1
     END CATCH
    END

    Básicamente hace lo esperado, parsea el XML y lo inserta en las tablas adecuadamente.

    Finalmente quedaba un último punto, efectuar la llamada desde la capa de datos en .NET, escribimos el código necesario (en nuestro caso estábamos empleando VS2008) para armar el XML y llamar al procedimiento almacenado, creamos una función similar a la siguiente:

    public void Ins_CabDet(string Cab_Nombre, List Detalles) {
     using (SqlCommand objSCmd = new SqlCommand("Ins_CabDet", new SqlConnection("Data  Source=XXX...."))) {
      objSCmd.CommandType = CommandType.StoredProcedure;
      objSCmd.Connection.Open();
      MemoryStream objMS = new MemoryStream();
      using (XmlWriter objXMLW = XmlWriter.Create(objMS))
      {
       objXMLW.WriteStartElement("r");
       objXMLW.WriteStartElement("h");
       objXMLW.WriteAttributeString("nm", Cab_Nombre);
       objXMLW.WriteEndElement();
       objXMLW.WriteStartElement("dc");
       foreach (Detalle objDet in Detalles) {
        objXMLW.WriteStartElement("d");
        objXMLW.WriteAttributeString("nm", objDet.Nombre);
        objXMLW.WriteAttributeString("od1", objDet.OtroDato1.ToString());
        objXMLW.WriteAttributeString("od2", objDet.OtroDato2.ToString("yyyyMMdd", CultureInfo.InvariantCulture));
        objXMLW.WriteEndElement();
       }
       objXMLW.WriteEndElement();
       objXMLW.WriteEndElement();
       objMS.Position = 0;
       objSCmd.Parameters.Add("@doc", SqlDbType.Xml).Value = new SqlXml(objMS);
      }
      objSCmd.ExecuteNonQuery();
     }
    }

    Detalle es la estructura:

    public struct Detalle {
     public string Nombre { set; get; }
     public int OtroDato1 { set; get; }
     public DateTime OtroDato2 { set; get; }
    }

    Con esta simple solución pudimos resolver el problema. Un punto a considerar es que en realidad no estamos pasando arreglos, de alguna forma estamos adaptando algo que fue pensado para otra cosa, como una especie de artificio para conseguir nuestro fin, por lo que este tipo de solución no es una solución universal y debería evaluarse en qué casos simplifica y en que otros complica el problema a resolver. Otra opción que no mencionamos es crear un tipo de datos y un procedimiento CLR, aprovechando las ventajas de poder ejecutar código manejado dentro del motor SQL Server 2005, esta es una opción válida que dependiendo del caso también podría implementarse.

    Arreglos en SQL 2008

    La versión 2008 de Sql Server ha resuelto el problema, finalmente hay arreglos disponibles, aunque los arreglos en T-SQL poseen una visión más cercana al manejo de tablas que al manejo de arreglos de los lenguajes de programación a los que los desarrolladores estamos acostumbrados, esto no provoca ningún problema. La idea aplicada al caso anterior es la siguiente;

    suponiendo que seguimos teniendo las mismas tablas Cabecera y Detalle, lo primero que vamos a hacer es crear un tipo de datos de usuario, en SQL Server 2008 no hay que escribir código CLR, la forma de hacerlo es simplemente escribir un script, para nuestro caso el siguiente:

    create type TipoDetalle as table
    (
     Nombre        nvarchar(20),
     OtroDato1     int,
     OtroDato2     DateTime
    )

    Algo bastante similar a nuestra estructura en C#, una vez creado el tipo de datos ya podemos pasar a crear el procedimiento almacenado

    CREATE PROCEDURE Ins_CabDet
    (
     @Nombre_Cab nvarchar(20),
     @Detalles TipoDetalle  readonly
    )
    AS
    BEGIN
     BEGIN TRY
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      BEGIN TRAN
      INSERT INTO Cabecera (Nombre) VALUES (@Nombre_Cab)
      DECLARE @IDCabecera int
      SET @IDCabecera = @@IDENTITY
      INSERT INTO
      Detalle (IDCabecera,Nombre, OtroDato1, OtroDato2)
      SELECT @IDCabecera ,Nombre, OtroDato1, OtroDato2
      FROM
       @Detalles;
      COMMIT
      RETURN 0
     END TRY
     BEGIN CATCH
      IF @@TRANCOUNT > 0
       ROLLBACK
      RETURN -1
     END CATCH
    END

    Claramente puede verse en T-SQL un arreglo es visto como una especie tabla, en cada fila de la tabla hay un elemento (del tipo Detalle en nuestro caso, para eso primero debimos crearlo) y
    cada columna representa una propiedad del elemento (Nombre, OtroDato1, etc.), como mencionamos previamente, es un poco diferente a lo usual, pero no puede negarse que es una visión bastante razonable para un motor de base de datos.

    Finalmente queda ver como efectuar la llamada desde .NET, en C# la llamada tomaría la siguiente forma:

    public void Ins_CabDet(string Cab_Nombre, List Detalles) {
     DataTable dt = new DataTable();
     dt.Columns.Add("Nombre", typeof(string));
     dt.Columns.Add("OtroDato1", typeof(int));
     dt.Columns.Add("OtroDato2", typeof(DateTime));
     foreach (Detalle objDet in Detalles) {
      DataRow dr = dt.NewRow();
      dr[0] = objDet.Nombre;
      dr[1] = objDet.OtroDato1;
      dr[2] = objDet.OtroDato2;
      dt.Rows.Add(dr);
     }
     using (SqlCommand objSCmd = new SqlCommand("Ins_CabDet", new SqlConnection("Data  Source=XXX...."))) {
      objSCmd.CommandType = CommandType.StoredProcedure;
      objSCmd.Connection.Open();
      objSCmd.Parameters.Add("@Nombre_Cab", SqlDbType.NVarChar, 20).Value = Cab_Nombre;
      objSCmd.Parameters.AddWithValue("@Detalles", dt);
      objSCmd.ExecuteNonQuery();
     }
    }

    Un último detalle es que desde en el código C# puede verse como el arreglo es preparado como una tabla, mapeando la estructura que el motor espera recibir.

    SQL Server 2008 trae muchas otras novedades como los campos Date (sin Time) y Time (sin Date), tipos de datos espaciales, la sentencia MERGE y otras cosas que seguramente veremos en algún otro artículo si es que las hemos utilizado para resolver algún problema.


16 comentarios »

  1. hola, muy bueno el blog, si deseas, ingresa a mi pagina, a publicar un comentario.saludos.
    base de datos de argentina-chile y españa
    http://tvinternet08.blogspot.com/

  2. Alberto dice:

    Usando php es posible usar este metodo?
    saludos

  3. franklin dice:

    hola te felicito muy buena la aportacion gracias me va a servir mucho

  4. franklin dice:

    AHHH Y ME PUEDES DAR UNA MANO CON EL USO DE IMAGENES EN SQL SERVER COMO GUARDARLAS SIN Q OCUPEN MUCHO ESPACIO EN LA BD

  5. david dice:

    Hola! espero este procedimiento funcione junto a php =)

  6. Excelente publicación andaba buscando una solución para un problema similar al que plantean en estos momentos seguiré su consejo a ver si me funciona, y de nuevo .

  7. javier dice:

    hola una duda cuando se manda los parametros para guardarlos en el create type table que pasaria si en el mismo instante desde otra pc tambien manda parametros al mismo create type por ejem: para un detalle de factura desde la pc mando 3 registros las cuales se visualizan en ese momento en el create tyoe y desde otra pc2 mando al mismo tiempo tambien 4 registros a ese mismo create type hasta ahi que pasara si hago un select a ese create type acaso vere los 7 registros totales ? o me equivoco si en el caso sea asi como se manejaria .. agradeceria su explicacion estimado amigos saludos

    • Dario Krapp dice:

      Hola Javier, En realidad con estos párametros sucede los mismo que con los parámetros más comunmente utilizados como por ejemplo: int o varchar, cada llamada al Stored Proc junto con los parámetros enviados es independiente y separada de las otras llamadas, por lo que el SQL Server en el ejemplo que comentás recibirá las dos llamadas, una con 3 registros y otra con 4 en el parámetro enviado en el Datatable.
      Suerte y slds

  8. Roberto dice:

    estimado como estas una pregunta como seria para pasarlo desde vb6.0 me gustaria saber ..gracias

    • Dario Krapp dice:

      Hola Roberto, ya hace bastantes años que no programo con VB6 y desconozco si que se puedan pasar Datatables como se esta haciendo en el ejemplo con .NET, pero no creo que se pueda. Quizás una alternativa que puedas utilizar es pasar el array en XML y parsearlo desde SQL Server.
      Suerte y slds

  9. Roberto dice:

    Dario Krapp que tal gracias por responder.. estimado agradeceria me des una mano de como pasar lo parametros mediante xml por favor desde vb6 sql

    tengo:

    create proc add_guia (@guia char(15), @detalle_guia type deta_guiaremision) .. algo asi

    queria desde vb6.0 mandarle con este codigo .

    ‘Public Function EjecutaComandoPS(ByVal SQL As String, ParamArray PARs() As Variant)
    ‘Call ConectBD
    ‘Set CMDEjecutaCommandSalidaPS = New ADODB.Command
    ‘Dim I As Long
    ‘With CMDEjecutaCommandSalidaPS
    ‘.ActiveConnection = Cn
    ‘.CommandType = adCmdStoredProc
    ‘.CommandText = SQL
    ‘.Parameters.Refresh
    ‘For I = 0 To UBound(PARs)
    ‘ .Parameters(I + 1).Value = PARs(I)
    ‘Next
    ‘.Execute
    ‘End With
    ‘EjecutaComandoPS = CMDEjecutaCommandSalidaPS.Parameters(I + 1).Value
    ‘End Function

    pero bota error cuando quiere mandar un arreglo que esta metido dentro del pars()
    con el cual lo meti mediante este codigo que encontre en una pagina de un programador conocido..

    Private Sub tOpcional1(ParamArray optParams())
    ‘ Para solucionar el problema si se pasan arrays
    ‘ entre los parámetros opcionales
    Dim i As Long
    Dim Params As Variant

    ‘ Si el número de elementos de los parámetros opcionales es mayor que uno
    If UBound(optParams) – LBound(optParams) > 1 Then
    Params = optParams
    Else
    ‘ === Esto es lo mismo que antes ===
    ‘ Comprobar si el primer parámetro es un array
    If IsArray(optParams()) Then
    Params = optParams()
    Else
    Params = optParams
    End If
    End If

    ‘ He cambiado el Print Params(i) por TypeName(Params(i)) para que muestre
    ‘ el tipo de dato que se ha pasado en cada parámetro.
    ‘ En el caso de un array de tipo cadena, mostrará String()
    For i = LBound(Params) To UBound(Params)
    Print TypeName(Params(i))
    Next

    MsgBox Params(0)(1)
    End Sub
    Private Sub tOpcional2(ParamArray optParams())
    ‘ Llamar a otro procedimiento que acepta parámetros opcionales
    tOpcional1 optParams
    End Sub

    Private Sub Form_Load()
    Dim tArray(1 To 3) As String

    tArray(1) = “Hola, ”
    tArray(2) = “¿cómo ”
    tArray(3) = “estás?”

    ‘tOpcional2 tArray(), 12345.67, CDec(12345.67), 1234, 1234567, “Varios parámetros pasados”, “a un procedimiento”

    ‘ Por supuesto, se pueden incluir varios Arrays:
    Dim tArray2(1 To 2) As Long
    tArray2(1) = 987654
    tArray2(2) = 1234567

    tOpcional1 tArray(), 12345.67, tArray2(), “Cadena”, 123, 123.3, 12345.5564

    End Sub

    trate de adaptarlo pude meter los valores y los arrays al paramarray pero de ahi cuando quiero mandarlo al store procedure que mencione mas arriba pues sale error … ojala me pudieran dar una mano muchas gracias estimados

    • Dario Krapp dice:

      Hola Roberto, buenos días,

      Hace bastante que no programo en VB6 y cuando lo hacía no existían los arrays en SQL Server, pero no creo que puedas pasar un array en VB6 de esta forma, me parece que la única opción es que pases un XML y lo parsees del otro lado, hay un par de páginas que hablan un poco de estos temas:

      http://www.devx.com/tips/Tip/39859

      http://www.mofeel.net/931-microsoft-public-sqlserver-programming/105447ab9be.aspx

      El asunto es que VB6 a pesar de ser un buen lenguaje ya tiene 14 años, y (aunque pueda aducirse que los buenos lenguajes nunca caducan), la falta de actualización y el hecho de que los desarrolladores vayan pasandose a otras tecnologias hace que algunas cosas sean más dificiles de realizar o que haya que buscar alternativas, pero este último comentario no es más que una opinión personal que no estoy seguro si vendrá al caso.
      En resumen espero que estas páginas puedan ayuarte.
      Suerte!

  10. Javier dice:

    porque borran mi consulta que malos que son.. sino saben solo digan que no saben y listo.

    • Dario Krapp dice:

      Hola Javier, creo que no hemos borrado ningún comentario, vamos aprobando y contestando las consultas cuando tenemos tiempo y ahí es cuando aparecen. De todas formas si tenes alguna consulta, avisanos que cuando tenemos tiempo te contestamos.
      Suerte!!

  11. jhon marca dice:

    Excelente artículo! de seguro lo voy a emplear

  12. Carlos dice:

    Muy buen articulo me ayudo bastante, te agardezco mi amigo..

Deja un comentario

Buscar