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

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:

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 😉