Uma das maiores novidades (e aparentemente pouco utilizada/comentada) que o SQL Server 2005 trouxe em conjunto com o .NET Framework 2.0, foi a capacidade de escrevermos Stored Procedures, Triggers e Functions em código gerenciado, ou seja, VB.NET ou C#, como dito aqui.

A idéia por trás da criação destes objetos em .NET é justamente fornecer ao SQL Server, acesso a recursos que são extremamente difícieis em uma linguagem de banco, garantindo também que outras aplicações (não gerenciadas) possam fazer o acesso as mesmas Stored Procedures sem pagar pelo preço do COM Interop. Sendo assim, dentro de uma Stored Procedure, antes de retornar os dados para o chamador, voce pode customizar o result-set, definindo os campos que achar conveniente e, além disso, pode aplicar a manipulação que desejar.

Imagine o seguinte cenário: voce tem uma coluna na sua tabela e, dentro dela, é necessário guardar um relatório. Dependendo do tamanho do relatório e da quantidade de registros, voce poderá “inchar” a sua base de dados rapidamente; sendo assim, voce poderia recorrer a compactação fornecida pelo .NET 2.0 e, antes de guardar ou antes de exibir, aplicar essa (des)compactação. A idéia do código abaixo é justamente, antes de retornar o result-set para o cliente, customizar a sua saída, ou seja, não é necessário retornar exatamente os dados provenientes da query que voce faz internamente.

[SqlProcedure]
public static void ResgatarConsultas()
{
    using (SqlConnection conn = new SqlConnection(“context connection=true”))
    {
        string query = “SELECT Data, Relatorio FROM Tabela”;

        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            conn.Open();
            using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                SqlDataRecord record =
                    new SqlDataRecord(
                        new SqlMetaData(“Data”, SqlDbType.DateTime),
                        new SqlMetaData(“Relatorio”, SqlDbType.Text));

                SqlContext.Pipe.SendResultsStart(record);

                while (dr.Read())
                {
                    if (!dr.IsDBNull(0) && !dr.IsDBNull(1))
                    {
                        record.SetDateTime(0, dr.GetDateTime(0));
                        record.SetString(1,
                            Encoding.Default.GetString(IO.Decompress((byte[])dr.GetValue(1))));

                        SqlContext.Pipe.SendResultsRow(record);
                    }
                }

                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }
}

Se repararmos no código acima, não há nada de novidade, pois utilizamos as mesmas classes do ADO.NET em uma aplicação tradicional. A única diferença é que não vamos expor o result-set da forma em que ele é retornado pela query interna; antes disso, será necessário passar pelo algoritmo de descompactação.

A classe SqlDataRecord representa uma linha de metadados; é através dela que vamos construir a estrutura do result-set. O construtor recebe um array de objetos do tipo SqlMetadata onde, em cada uma delas, precisamos especificar o nome e o tipo de dado do campo. Feito isso, recorremos ao método SendResultsStart passando a instancia da classe SqlDataRecord para caracterizar o ínicio do retorno para o cliente.

Uma vez que isso está pronto, utilizamos o tradicional SqlDataReader para percorrer os registros internos (retornados pelo query) e definirmos os valores fornecidos por ele para o SqlDataRecord. Note que vinculamos cada coluna do SqlDataReader a uma determinada coluna do SqlDataRecord, fazendo isso através de métodos do tipo SetXXX, onde XXX deve ser substituído pelo tipo de dado a ser carregado. O primeiro parametro de métodos SetXXX é um número inteiro que representa a coluna (do result-set) em que uma determinada coluna do SqlDataReader será carregada. Fazemos esse processo por cada iteração do SqlDataReader e, dentro deste loop, passamos o SqlDataRecord para o método SendResultsRow que, por sua vez, envia a linha para o cliente.

Finalmente, para encerrar o processamento, invocamos o método SendResultsEnd para informar ao chamador que o result-set está finalizado.