Lluís Franco on Geeks.ms
  • Home

Usando SQL Server Table-Valued Parameters en C# (mediante un Custom Iterator)

  • By lfranco
  • Sep-12-2011
  • Sin categoría
  • 3 Comments.

Una de las características más esperadas de SQL Server 2008 son los parámetros de tipo tabla en procedimientos almacenados y funciones de SQL SERVER.

Estos Table-Valued Parameter (TVP en adelante ) permiten definir un tipo de datos estructurado (al fin!) que puede ser pasado como parámetro a un PROCEDURE o a una FUNCTION, mejorando sustancialmente el manejo de datos de tipo tabla, ya que anteriormente dependíamos de tablas temporales o variables de tipo tabla, pero ambas tenían varios inconvenientes. Las primeras por ejemplo sólo eran accesibles en la conexión actual, mientras que las segundas ni siquiera podían ser accedidas desde otros elementos procedimientos o funciones.

SQL Server 2008 y TVP al rescate!

Mediante los TVP podemos definir un tipo fuertemente tipado en el servidor (valga la rebuznancia :-P), y usarlo para definir los parámetros de nuestros procedimientos o funciones. De este modo estamos portando una de las cosas que más me gustan de la OOP a TSQL, que dicho sea de paso, falta le hacía. Además, nos permite solventar algo que desde tiempo ha llevado de cabeza a muchos desarrolladores: El poder enviar en un sólo viaje al servidor varias filas.

Vamos a ver. Que levante la mano quién no se ha dado cabezazos para tratar de mandar varios valores a un procedimiento o función desde una aplicación cliente?

A lo largo de los años he visto soluciones más o menos ingeniosas, desde pasar un ‘chorizo’ de 4000 caracteres separados por comas y usarlo con SQL dinámico o volcarlo en una tabla temporal para hacer un JOIN, hasta usar XML para el paso de valores, pasando por tablas temporales globales y otras ‘perlas’. En todos los casos las soluciones distaban mucho de ser perfectas y comportaban serios problemas de rendimiento al tener que implementar lógica en ambos extremos para empaquetar y desempaquetar los valores.

Aquí los TVP nos vienen a echar un cable, ya que permiten definir un tipo de registro que luego pasaremos a SQL Server, aunque la gracias de todo está en -como veremos un poco más adelante- crearse un custom iterator en c#.

Manos a la obra: Coding

Un escenario típico (y que vamos a usar como ejemplo) es el de tener una aplicación cliente que permite seleccionar un subconjunto de datos, por ejemplo bancos. Y posteriormente, envía ésta selección de elementos a un procedimiento o función que realice *algo* con éstos datos, como por ejemplo mostrar un listado por pantalla, report, etc.

La parte del servidor es la más sencilla, supongamos que partimos de un par de tablas de bancos y oficinas:

banksoffices

Basta con crear un tipo de datos de tipo tabla (con un sólo campo para simplificar):

CREATE TYPE [dbo].[MultiSelectionList] AS TABLE(

    [Id] [int] NULL

)

GO

A continuación usamos este tipo en las funciones o procedimientos almacenados para recibir los valores como una tabla de elementos de este tipo :

CREATE FUNCTION [dbo].[GetBanksOffices](@SELECTION AS MultiSelectionList READONLY)

RETURNS TABLE 

AS

RETURN 

  SELECT * FROM [system].[BankOffices] BO

  INNER JOIN @SELECTION S

  ON BO.BankId = S.Id

Observar el uso de READONLY, ya que es imperativo que la lista de valores no se pueda modificar desde el cuerpo de la rutina TSQL. Por otro lado, la lista de valores se trata a todos los efectos como una tabla temporal, o variable de tipo tabla. Así pues podemos realizar un JOIN sobre ella sin más, y aquí radica la belleza de los TVP, nos permiten un código mucho más limpio. Y como todos sabemos, contra más limpio es un código, menos propenso a errores resulta.

La parte del cliente es un poco más compleja, ya que supongo que la mayoría os estaréis preguntando “vale ¿pero cómo demonios se le pasa un parámetro de este tipo desde .NET?”, y con razón. Este tipo es un tipo de datos personalizado que *sólo* conoce nuestro servidor. Sin embargo en .NET podemos crearnos el equivalente a este tipo mediante una clase, y luego mediante una lista genérica pasárselo a la función mediante un SqlParameter… o lo sería si existiese un tipo de parámetro adaptado a las colecciones genéricas, cosa que no es así.

Entonces que hacemos? Pues lo primero empezar por lo básico: Hay que crear una clase equivalente al tipo de datos de SQL SERVER:

public class MultiSelectionItem

{

    public int Id { get; set; }

 

    public MultiSelectionItem(int id)

    {

        Id = id;

    }

}

Ya tenemos lo básico para empezar. Ahora a ver cómo podemos conectar nuestros objetos de negocio con los TVP de SQL SERVER:

El mecanismo que nos proporciona ADO.NET para acceder a procedimientos o funciones son los archiconocidos SqlCommand y SqlParameter, los cuales he usado infinidad de veces. Sin embargo, hasta hoy no conocía la existencia de uno de los valores de la enumeración SqlDbType: El valor Structured. Cuando usamos este valor para definir el tipo del parámetro de SQL Server, en realidad estamos especificando que vamos a usar un TVP. Esto parece solventar parte del problema, ahora sólo nos falta ver como convertir una colección de objetos MultiSelectionItem y ‘convertirla’ a un objeto que podamos pasarle al parámetro.

Ahora demos un vistazo a la clase SqlDataRecord, que representa una fila de datos junto con sus metadatos, y está especialmente indicada para su uso en conjunción con SqlCommand y SqlDataReader, para enviar y recibir información de procedimientos y funciones utilicen TVP.

Es muy posible que estéis pensando en usar esta clase como ‘puente’ entre ambos mundos, y la verdad es que SI SE PUEDE. Y además, creo que de forma muy elegante.

Custom iterators en C#

Un iterador es un método que realiza una iteración personalizada sobre una colección a través de la palabra reservada yield. La instrucción yield return provoca que un elemento en la secuencia de origen sea devuelto de inmediato al método llamador que llama antes de que el siguiente elemento de la secuencia de origen sea procesado. En otras palabras, nos permite hacer algo tan bonito como esto:

public class MultiSelectionCollection : List<MultiSelectionItem>, IEnumerable<SqlDataRecord>

{

    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()

    {

        var sqlDataRecord = new SqlDataRecord(new SqlMetaData("Id", SqlDbType.Int));

        foreach (MultiSelectionItem multiSelectionItem in this)

        {

            sqlDataRecord.SetInt32(0, multiSelectionItem.Id);

            yield return sqlDataRecord;

        }

    }

}

De verdad que a veces no me imagino la vida antes de la aparición de Generics 😛

Poniéndolo todo junto

Recapitulemos. Por un lado tenemos el TVP que hemos creado en SQL SERVER, y que posteriormente hemos usado en una función de usuario de tipo tabla. A continuación hemos creado una clase equivalente en la aplicación cliente, y finalmente hemos creado una clase que hereda de una lista genérica de nuestro tipo equivalente, y que implementa la interfaz IEnumerable, para así poder crear un iterador personalizado que ‘convierta’ la lista de elementos MultiSelectionItem en algo inteligible para SQL Server, en este caso una secuencia de elementos SqlDataRecord.

Y ahora que? Ahora, sólo queda hacer la llamada a la función. Es decir, crear la colección de valores a pasar a SQL SERVER, hacer la llamada y recoger los datos de vuelta:

public static List<BankOffice> GetBanksOffices(MultiSelectionCollection selectedbanks)

{

    var officebanks = new List<BankOffice>();

    using (SqlConnection sqlcon = new SqlConnection(

        ConnectionStringManager.GetFirstConnectionString()))

    {

        try

        {

            sqlcon.Open();

            const string sql = "SELECT * FROM dbo.GetBanksOffices(@SELECTION)";

            using (SqlCommand sqlcmd = new SqlCommand(sql, sqlcon))

            {

                SqlParameter param = new SqlParameter("@SELECTION", SqlDbType.Structured);

                param.TypeName = "MultiSelectionList";

                param.Value = selectedbanks;

                sqlcmd.Parameters.Add(param);

                var result = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (result.Read())

                {

                    BankOffice newoffice = new BankOffice

                    {

                        BankOfficeId = int.Parse(result[0].ToString()),

                        ShowOrder = int.Parse(result[1].ToString()),

                        BankId = int.Parse(result[2].ToString()),

                        OfficeId = int.Parse(result[3].ToString()),

                        Name = result[4].ToString()

                    };

                    officebanks.Add(newoffice);

                }

            }

        }

        catch (Exception)

        {

            throw;

        }

    }

    return officebanks;

}

Un par de cosas importantes: Aunque parece que estamos haciendo lo de siempre, fijaros en que el tipo de datos del parámetro de SQL SERVER es SqlDbType.Structured, y además hay que indicar el nombre del tipo de datos de SQL SERVER mediante la propiedad TypeName. Esto es más importante de lo que parece, ya que el nombre del tipo de datos de SQL SERVER no tiene por que coincidir con el nombre que hemos dado a nuestra clase en la aplicación cliente.

Y para probar la llamada, basta con crear una colección con los valores que deseamos pasar a la función:

var selectedbanks = new MultiSelectionCollection();

selectedbanks.Add(new MultiSelectionItem(1));

selectedbanks.Add(new MultiSelectionItem(3));

 

var result = TVPExtensions.GetBanksOffices(selectedbanks);

result.ForEach(p => Console.WriteLine(string.Format("{0}, {1}", p.BankId, p.Name)));

Y tachán!!! Ya hemos visto cómo pasar listas de valores a procedimientos y funciones de SQL mediante TVP.

Nos leemos 😉

Comments

3 Responsesso far

  1. jirigoyen dice:
    13 septiembre, 2011 a las 12:14 am

    Un par de apuntes para complementar tu artículo:

    Compaq framework no soporta todavía TVP, así que cuidado si queréis llamar directamente a un SP.

    Otra forma de simular parámetros de tipo tabla es usar un objecto DataTable y dimensionar las columnas con los tipos requeridos.

    DataTable dt = new DataTable(«MultiSelectionList») { Locale = CultureInfo.CurrentCulture };
    dt.Columns.Add(«Id», typeof(int));

    DataRow dr1 = dt.NewRow();
    dr1[«Id»] = 2;
    dt.Rows.Add(dr1);
    DataRow dr2 = dt.NewRow();
    dr2[«Id»] = 4;
    dt.Rows.Add(dr2);

    y finalmente añadir el objeto
    sp.Parameters.Add(«@SELECTION”, SqlDbType.Structured).Value = dt;

    Para poder testear los sp, por ejemplo desde Team for Database Developers, podemos definir el parámetro tipo tabla como en el ejemplo siguiente:

    DECLARE @dataTest [dbo].[MultiSelectionList]

    INSERT INTO @dataTest (Id)
    select 2
    union
    select 4
    union
    select 6

    Un saludo.

    Responder
  2. lfranco dice:
    13 septiembre, 2011 a las 8:50 am

    🙂
    Hola Juan,

    Si, conozco lo del Compaq Framework aunque no lo uso.
    Respecto a los DataTable, he visto algonos ejemplos similares al tuyo, pero soy muy reacio a usarlos. Guardo muy malos recuerdos de los datatables, la verdad 🙂
    Prefiero 1000 veces trabajar con objetos de negocio.
    Saludos,

    Responder
  3. jirigoyen dice:
    13 septiembre, 2011 a las 9:23 am

    Entiendo tu reticencia a usarlos y yo tambien prefiero utilizar objetos de negocio, pero en este contexto te da practicamente igual, es un objeto en memoria normalmente con una estructura sencilla de uno o dos campos en el que además puedes hacer un dispose despues de su uso, en ningún caso te afecta al rendimiento y no te provocara problemas, no te crea dependencias de las clase Microsoft.SqlServer, que normalmente no se referencian en la capa de negocios.

    Un saludo.

    Responder

Deja un comentario Cancelar respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

← Previous Post Next Post →

Tags

async Back best practices

Entradas recientes

  • Video de mi charla en la #dotNetSpain2016
  • I’m back. Miss me?
  • Office365 actualizado a 2013 para nuevas suscripciones
  • Serializar listas genéricas en aplicaciones WinRT
  • [TPL] Problemas de concurrencia

Comentarios recientes

  • Darling Chavez en Tip: Mostrar objetos relacionados en DevExpress GridControl
  • Alexander en [TPL] Problemas de concurrencia
  • cristinakity en Funciones escalares en TSQL, JOINS, CROSS APPLY, y la madre que parió al topo.
  • cristinakity en Funciones escalares en TSQL, JOINS, CROSS APPLY, y la madre que parió al topo.
  • anonymous en HowTo: Crear una pantalla de inicio (splash screen)

Archivos

  • marzo 2016
  • marzo 2013
  • octubre 2012
  • septiembre 2012
  • agosto 2012
  • febrero 2012
  • diciembre 2011
  • noviembre 2011
  • octubre 2011
  • septiembre 2011
  • agosto 2011
  • junio 2011
  • mayo 2011
  • abril 2011
  • febrero 2011
  • enero 2011
  • diciembre 2010
  • noviembre 2010
  • octubre 2010
  • agosto 2010
  • julio 2010
  • marzo 2010
  • febrero 2010
  • enero 2010
  • diciembre 2009
  • noviembre 2009
  • octubre 2009
  • septiembre 2009
  • agosto 2009
  • julio 2009
  • junio 2009
  • mayo 2009
  • abril 2009
  • marzo 2009
  • febrero 2009
  • enero 2009
  • diciembre 2008
  • noviembre 2008
  • octubre 2008
  • septiembre 2008
  • agosto 2008
  • julio 2008
  • junio 2008
  • mayo 2008
  • abril 2008
  • marzo 2008
  • febrero 2008
  • enero 2008
  • diciembre 2007
  • noviembre 2007
  • octubre 2007
  • septiembre 2007
  • agosto 2007
  • abril 2007
  • febrero 2007
  • enero 2007

Categorías

  • .NET
  • C#
  • Channel9
  • Evento
  • Personal
  • Videos

Meta

  • Acceder
  • RSS de las entradas
  • RSS de los comentarios
  • WordPress.org
About This Site

A cras tincidunt, ut tellus et. Gravida scel ipsum sed iaculis, nunc non nam. Placerat sed phase llus, purus purus elit.

Archives Widget
  • January 2010
  • December 2009
  • November 2009
  • October 2009
Categories
  • Entertainment
  • Technology
  • Sports & Recreation
  • Jobs & Lifestyle
Search
  • facebook
  • twitter
  • rss

Powered by WordPress  |  Business Directory by InkThemes.

This site uses cookies: Find out more.