Neste segundo passo no desenvolvimento de procedimentos armazenados com CLR, vamos conhecer algumas facilidades existentes na integração CLR para permitir consultas de forma prática, que podem ajudar bastante o desenvolvimento de novas soluções.
Como ponto de partida, vamos estudar como utilizar SqlConnection e SqlDataReader se comportam em assemblies CLR no SQL Server.
Ao invés de depender de strings de conexão “complexas”, com informações de fonte de dados, usuário e senha para se conectar ao banco de dados onde esta o assembly CLR, somente será necessário definir que os objetos de conexão utilizarão a conexão do contexto, por meio da string de conexão “Context Connection”, 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
Para quem já conhece ADO.NET, não será grande o desafio de trabalhar DataReader, DataTable, SqlTransaction e SqlConnection em procedimentos armazenados ou qualquer outra funcionalidade desenvolvida em CLR para o SQL Server.
Mas como executar 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 criar a 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 à um SqlDataRecord, sendo a 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 podemos entender um pouco mais sobre trabalhar com conexões ao banco de dados dentro de procedimentos armazenados desenvolvidos em CLR e retornar de formas diversas resultados 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.
1 Resposta para “Trabalhando com SQL CLR: Stored Procedure – Step 2”