Trabalhando com CLR: Stored Procedure

Continuando com o desenvolvimento de procedimentos armazenados com CLR, vamos conhecer algumas facilidades existentes na integração CLR do SQL Server, que permitem conexões ao banco de dados e retornar resultados de forma prática.

Como ponto de partida, vamos aprender como utilizar SqlConnection e SqlDataReader em assemblies CLR no SQL Server.

Para aqueles já conhecem ADO.NET, não será grande o desafio de trabalhar com SqlConnection e SqlDataReader em procedimentos armazenados ou qualquer outra funcionalidade desenvolvida em CLR para o SQL Server.

Se você não conhece esta tecnologia, sugiro conferir os seguintes artigos:
http://sqlfromhell.wordpress.com/2009/10/31/ado-net-introducao
http://sqlfromhell.wordpress.com/2009/11/07/ado-net-datareader-e-transacoes

Ao utilizar SqlConnection nos assemblies CLR, ao invés de depender de complexas strings de conexão com informações de fonte de dados, usuário e senha para se conectar ao banco de dados onde esta o assembly CLR, é possível utilizar a conexão do contexto, que já traz consigo estas informações. Para isso, basta informar na string de conexão “Context Connection = True”, conforme demonstrado abaixo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

SqlContext.Pipe.Send("1.Conectando");

using (var conn = new SqlConnection("Context Connection = True"))

{

conn.Open();

SqlContext.Pipe.Send("2.Conectado");

}

SqlContext.Pipe.Send("3.Desconectado");

}

};

EXEC Primeiro

Mas como vamos retornar consultas por meio dos procedimentos armazenados em CLR? Para esta pergunta existem várias respostas, sendo a primeira com a utilização de DataReader, como demonstrada abaixo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

var query = "SELECT * FROM sys.all_objects";

using (var conn = new SqlConnection("Context Connection = True"))

{

conn.Open();

var cmd = new SqlCommand(query, conn);

SqlContext.Pipe.Send(cmd.ExecuteReader());

}

};

EXEC Primeiro

A segunda forma de retornar consultas por procedimentos armazenados e a mais simples, é o envio dos comandos SQL por meio do método ExecuteAndSend:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

var query = "SELECT * FROM sys.all_objects";

var cmd = new SqlCommand(query);

SqlContext.Pipe.ExecuteAndSend(cmd);

}

};

EXEC Primeiro

A terceira forma e a que permite uma grande facilidade para customização, é a criação de uma estrutura de “resultado”, primeiramente definindo a estrutura das colunas e em seguida definindo seus valores.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

var dr = new SqlDataRecord(new[] {

new SqlMetaData("Código", SqlDbType.Int),

new SqlMetaData("Descrição", SqlDbType.VarChar, 90)

});

dr.SetValues(1, "Primeiro");

SqlContext.Pipe.Send(dr);

}

};

EXEC Primeiro

Explicando o código acima, o objeto do tipo SqlDataRecord é responsável por estruturar os dados a serem enviados ao SQL Server, tendo uma estrutura bem semelhante a um DataTable (objeto já conhecido por desenvolvedores ADO.NET), onde cada SqlMetaData corresponde à uma coluna do resultado (semelhante às colunas do DataTable e gerado de forma semelhante aos SqlParamenters). Por fim, é possível utilizar vários métodos para adicionar valores a um SqlDataRecord, sendo o mais simples o método SetValues, onde passamos com parâmetro os valores correspondentes a cada coluna de nossa linha de resultado (ou registro, como preferir).

Visto que cada chamada ao método Send é um envio de uma estrutura de dados completa para o SQL Server, você terá consultas separadas para cada Send, mesmo utilizando uma mesma estrutura de resultado, conforme demonstrado abaixo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

var dr = new SqlDataRecord(new[] {

new SqlMetaData("Código", SqlDbType.Int),

new SqlMetaData("Descrição", SqlDbType.VarChar, 90)

});

dr.SetValues(1, "Primeiro");

SqlContext.Pipe.Send(dr);

dr.SetValues(2, "Segundo");

SqlContext.Pipe.Send(dr);

}

};

EXEC Primeiro

Para enviar uma consulta (ou resultado) com várias linhas, deveremos informar o início do envio da nossa estrutura de dados pelo método SendResultsStart, em seguida enviar as linhas pelo método SendResultRow e ao final informar o término dos envios pelo método SendResultsEnd, retornando uma assim uma estrutura de resultados unificada com várias linhas, conforme o exemplo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

var dr = new SqlDataRecord(new[] {

new SqlMetaData("Código", SqlDbType.Int),

new SqlMetaData("Descrição", SqlDbType.VarChar, 90)

});

SqlContext.Pipe.SendResultsStart(dr);

dr.SetValues(1, "Primeiro");

SqlContext.Pipe.SendResultsRow(dr);

dr.SetValues(2, "Segundo");

SqlContext.Pipe.SendResultsRow(dr);

dr.SetValues(3, "Terceiro");

SqlContext.Pipe.SendResultsRow(dr);

SqlContext.Pipe.SendResultsEnd();

}

};

EXEC Primeiro

Com estas demonstrações, entendemos um pouco mais sobre trabalhar com conexões a banco de dados dentro de procedimentos armazenados desenvolvidos em CLR e como retornar consultas por meio destes procedimentos.

No próximo artigo estaremos conhecendo as funções escalares, um ponto de partida para funcionalidades mais complexas, como funções “tabulares” e de agregação.

Banco de Dados - SQL Server

Leia também