Integração SQL Server 2012 x .NET: criando procedures utilizando código C#

Além de comandos para a consulta, inclusão, alteração e exclusão de informações, os principais bancos de dados relacionais contam ainda com mecanismos que estendem esses recursos básicos da linguagem SQL. Talvez uma das estruturas mais conhecidas desse tipo sejam as stored procedures (também chamadas de “procedimentos armazenados” ou, simplesmente, “procedures”).

Uma stored procedure é um objeto que combina instruções convencionais de acesso a dados a elementos como variáveis, estruturas de decisão e laços de repetição, conseguindo assim ampliar as capacidades oferecidas por comandos SQL padrão. Não existe, contudo, uma padronização quanto à forma como procedures são implementadas; cada fornecedor de banco de dados costuma disponibilizar um conjunto de recursos específicos e que, em muitos casos, não encontra paralelo em produtos similares da concorrência.

Podem ser citadas como vantagens proporcionadas pela utilização de stored procedures:

• Uma maior performance em operações envolvendo dados, em virtude do fato de procedures permanecerem pré-compiladas dentro de um repositório de dados. Com isto se consegue uma maior velocidade de execução, visto que as instruções SQL utilizadas foram validadas no momento de criação da procedure, assim como tiveram seus respectivos planos de execução previamente montados. Esse comportamento não acontece com comandos SQL convencionais, em que a validação e elaboração de um plano de execução são processos repetidos a cada nova instrução SQL enviada ao banco de dados;
• A redução do tráfego de dados em uma rede, através de operações que ficam centralizadas dentro do servidor de um banco. Isto pode ser um instrumento de grande valia, sobretudo ao se considerar processos de duração mais extensa e que englobem um grande volume de informações;
• A possibilidade de reuso de procedures em várias aplicações que dependam de funcionalidades disponibilizadas por rotinas deste tipo;
• Uma maior segurança na manipulação de informações, levando em conta a capacidade de administradores de bancos de dados concederem o direito de execução somente a stored procedures com uma finalidade específica (revogando dessa forma o acesso direto a prováveis tabelas contendo informações sensíveis);
• Ainda sobre a questão da segurança, stored procedures são uma boa alternativa quando a meta é tornar uma aplicação menos sujeita a ataques de injeção de SQL (ou em inglês "SQL Injection"). Por receberem dados a serem processados sob a forma de parâmetros de entrada, construções deste tipo ajudam a evitar a ação de códigos que buscam causar danos às estruturas de uma base relacional.

Dentre as principais tecnologias de banco de dados da atualidade, o SQL Server é sem sombra de dúvidas uma das que conta com maior aceitação por parte do mercado. Por ser um produto robusto, escalável e extremamente flexível, não é de se estranhar que grandes corporações e órgãos governamentais façam uso de bancos de dados SQL Server em inúmeras aplicações de caráter crítico.

Sendo um banco de dados relacional, o SQL Server conta tanto com construções típicas da linguagem SQL (instruções baseadas nas cláusulas SELECT, INSERT, UPDATE e DELETE), quanto oferece mecanismos para a codificação de estruturas mais complexas. Neste último caso, objetos como stored procedures, triggers e functions são implementados utilizando uma extensão conhecida como T-SQL (sigla do inglês “Transact-SQL”).

Inúmeros foram os recursos introduzidos a partir da versão 2005 do SQL Server. Considerando o desenvolvimento voltado a banco de dados, merece destaque a integração que passou existir neste novo release com o Common Language Runtime (CLR) do .NET Framework: tornou-se possível desde então a criação de stored procedures, triggers e functions empregando linguagens compatíveis com a plataforma .NET, como C# ou VB.NET.

Procedures criadas em .NET permitem a realização de ações não suportadas por rotinas equivalentes escritas em Transact-SQL. Construções deste tipo são conhecidas como CLR Stored Procedures e, por serem baseadas em recursos da plataforma .NET, permitem a realização de operações como o acesso a diretórios, leitura/escrita de arquivos, criptografia, manipulação de arrays ou o tratamento de strings por meio de expressões regulares.

O objetivo deste artigo é demonstrar como procedures do SQL Server 2012 podem ser implementadas a partir de código .NET. Buscando cumprir essa meta, será apresentado um exemplo de implementação de uma stored procedure deste tipo através do Visual Studio.

Criando uma stored procedure através do Visual Studio

A solução apresentada neste artigo foi criada no .NET framework 4.5, através da utilização do Microsoft Visual Studio 2012 Professional. Basicamente, será construída biblioteca em que constará um método público definido em uma classe estática. Essa operação corresponde, por sua vez, à implementação da stored procedure a ser acionada a partir de comandos enviados a uma base do SQL Server.

Quanto ao funcionamento desta procedure, essa rotina receberá como parâmetro um diretório capaz de ser acessado pelo servidor SQL Server, produzindo como resultado de sua execução lista dos arquivos contidos em tal caminho (incluindo arquivos vinculados a subdiretórios da pasta-base).

Para gerar o projeto de testes será necessário, dentro do Visual Studio, acessar o menu File, opção New, sub opção Project. Dentro da tela New Project (Figura 1) selecionar o template SQL Server Database Project (que deve estar em “Installed > Templates > Other Languages > SQL Server”), informando no campo Name o nome da aplicação a ser gerada (“TesteCLRProcedure”, neste caso); no campo Location é possível ainda definir o diretório no qual serão criados os arquivos para este projeto.

Figura 1: Criando o projeto TesteCLRProcedure

Prosseguiremos agora com a implementação da procedure baseada em recursos do .NET Framework.

Clicar dentro do Solution Explorer com o botão direito do mouse sobre o projeto TesteCLRProcedure, escolhendo em seguida no menu de atalho a opção Add, sub opção New Item. Neste momento será exibida a tela Add New Item; selecionar do lado esquerdo o item “SQL CLR C#” e, após isto, o template “SQL CLR C# Stored Procedure”; preencher o campo Name com “SP_ListarArquivosDiretorio.cs”.

Figura 2: Criando a procedure SP_ListarArquivosDiretorio

Como resultado dessa última ação, será criado o arquivo SP_ SP_ListarArquivosDiretorio.cs, em que estará o código que implementa uma procedure com este mesmo nome.

A operação SP_ListarArquivosDiretorio (Listagem 1) deve fazer parte de uma classe parcial chamada StoredProcedures. O uso desse tipo de construção (classes parciais) é próprio de projetos envolvendo a implementação de procedures por meio do Visual Studio, cabendo ao desenvolvedor apenas o trabalho de codificar as ações que serão disparadas ao se invocar tais rotinas.

Além do fato de estarem associadas a uma partial class, stored procedures baseadas em recursos do CLR possuem ainda as seguintes características:

• O método que corresponde à procedure deve ser estático, assim como não devolver um valor (indicar isto através do uso da palavra-chave “void”);


• Essas operações estarão obrigatoriamente marcadas com o atributo SqlProcedureAttribute (namespace Microsoft.SqlServer.Server);
• Os tipos empregados na definição de parâmetros de entrada devem possuir equivalência dentro do SQL Server. Normalmente, isto não representará um problema, desde que partindo do pressuposto que serão utilizados apenas tipos primitivos ou mais simples (int, string, DateTime etc.).

No caso da procedure SP_ListarArquivosDiretorio, o método que define esta construção receberá como parâmetro de entrada uma string (referência “diretorio”) contendo um diretório a ser tomado como base. Foi especificado ainda o parâmetro de saída “qtdeArquivos”, no qual constará a quantidade de arquivos que existem no caminho informado ao se acionar a stored procedure.

Quanto ao funcionamento de SP_ListarArquivosDiretorio, é possível observar:

• Inicialmente é criada uma variável chamada “nomeTabelaTemporaria”, atribuindo-se à mesma o nome da tabela temporária que servirá de base para a manipulação de dados sobre arquivos;
• Uma referência para o tipo SqlConnection (namespace System.Data.SqlClient) é então gerada. A string de conexão informada a este objeto faz uso do parâmetro “connection context”, associando o valor “true” a este último; isto significa que o código da procedure fará uso da mesma sessão que acionou a procedure SP_ListarArquivosDiretorio (evitando assim a abertura de uma nova conexão);


• Gerada a instância para a classe SqlConnection (variável “conexao”), é invocado o método Open a partir deste objeto, a fim de que a conexão à base permaneça aberta para a execução de instruções SQL;
• O processamento continua com uma chamada ao método CriarTabelaTemporaria. Esta operação obtém um objeto SqlCommand a partir de uma referência do tipo SqlConnection (namespace System.Data.SqlClient); a finalidade disto é executar um comando criando a tabela temporária, para posterior manipulação de informações sobre arquivos através desta estrutura;
• O próximo passo consiste em obter dados sobre os arquivos contidos no diretório informado à procedure SP_ListarArquivosDiretorio. Isso acontecerá através da operação CarregarInformacoesDiretorio, empregando para isto uma instância da classe DirectoryInfo (namespace System.IO). Informações sobre arquivos são retornadas pelo método GetFiles de DirectoryInfo, o qual devolve uma coleções de objetos do tipo FileInfo; serão essas referências que fornecerão os dados necessários para a inclusão de registros na tabela temporária (via objeto SqlCommand). Por fim, é realizada uma chamada à operação GetDirectories de DirectoryInfo, para repetir este processo com cada um dos subdiretórios que estão abaixo do diretório principal; isto implica em chamadas recursivas ao método SP_ListarArquivosDiretorio (reaproveitando neste caso o objeto SqlCommand criado no início da execução de CarregarInformacoesDiretorio);
• Com a tabela temporária preenchida, o método ExecutarConsultaInformacoesArquivos é invocado, a fim de executar uma consulta com todos os registros relativos a arquivos. Os dados correspondentes são devolvidos como resultado do processamento da stored procedure, envolvendo neste caso a utilização da operação ExecuteAndSend, a partir da propriedade estática Pipe. Esta última estrutura faz parte da classe estática SqlContext (namespace Microsoft.SqlServer.Server), a qual representa o ambiente a partir do qual foi acionada uma CLR procedure;
• Já a chamada à operação ObterQuantidadeArquivos permite preencher o parâmetro de saída “qtdeArquivos” com a quantidade de arquivos encontrados;
• Finalmente, a tabela temporária é excluída, invocando-se para isto o método RemoverTabelaTemporaria.

Listagem 1: Implementação da procedure SP_ListarArquivosDiretorio

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SP_ListarArquivosDiretorio(
        string diretorio,
        out int qtdeArquivos)
    {
        string nomeTabelaTemporaria = "#FEED_" +
            DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");

        using (SqlConnection conexao =
            new SqlConnection("context connection=true"))
        {
            conexao.Open();

            CriarTabelaTemporaria(
                conexao, nomeTabelaTemporaria);
            CarregarInformacoesDiretorio(
                conexao,
                null,
                nomeTabelaTemporaria,
                diretorio);
            ExecutarConsultaInformacoesArquivos(
                conexao,
                nomeTabelaTemporaria);
            qtdeArquivos = ObterQuantidadeArquivos(
                conexao,
                nomeTabelaTemporaria);
            RemoverTabelaTemporaria(
                conexao,
                nomeTabelaTemporaria);
        }
    }

    private static void CriarTabelaTemporaria(
        SqlConnection conexao,
        string nomeTabelaTemporaria)
    {
        SqlCommand cmdCreateTable = conexao.CreateCommand();
        cmdCreateTable.CommandText = String.Format(
            "CREATE TABLE {0} " +
            "( " +
                "NomeArquivo VARCHAR(2000), " +
                "DataCriacao DATE, " +
                "TamanhoBytes INT " +
            ")", nomeTabelaTemporaria);
        cmdCreateTable.ExecuteNonQuery();
    }

    private static void CarregarInformacoesDiretorio(
        SqlConnection conexao,
        SqlCommand cmdInsert,
        string nomeTabelaTemporaria,
        string diretorio)
    {
        if (cmdInsert == null)
        {
            cmdInsert = conexao.CreateCommand();
            cmdInsert.CommandText = String.Format(
                "INSERT INTO {0} VALUES " +
                    "(@NomeArquivo, @DataCriacao, @TamanhoBytes)",
                nomeTabelaTemporaria);
            cmdInsert.Parameters.Add(
                "@NomeArquivo", SqlDbType.VarChar);
            cmdInsert.Parameters.Add(
                "@DataCriacao", SqlDbType.Date);
            cmdInsert.Parameters.Add(
                "@TamanhoBytes", SqlDbType.Int);
        }

        DirectoryInfo informacoesDiretorio =
            new DirectoryInfo(diretorio);
        foreach (FileInfo arquivo in
            informacoesDiretorio.GetFiles())
        {
            cmdInsert.Parameters["@NomeArquivo"].Value =
                arquivo.FullName;
            cmdInsert.Parameters["@DataCriacao"].Value =
                arquivo.CreationTime;
            cmdInsert.Parameters["@TamanhoBytes"].Value =
                arquivo.Length;
            cmdInsert.ExecuteNonQuery();
        }

        foreach (DirectoryInfo subDiretorio in
            informacoesDiretorio.GetDirectories())
        {
            CarregarInformacoesDiretorio(
                conexao,
                cmdInsert,
                nomeTabelaTemporaria,
                subDiretorio.FullName);
        }
    }

    private static void ExecutarConsultaInformacoesArquivos(
        SqlConnection conexao,
        string nomeTabelaTemporaria)
    {
        SqlCommand cmdSelect =
           conexao.CreateCommand();
        cmdSelect.CommandText =
            "SELECT * FROM " + nomeTabelaTemporaria;
        SqlContext.Pipe.ExecuteAndSend(cmdSelect);
    }

    private static int ObterQuantidadeArquivos(
        SqlConnection conexao,
        string nomeTabelaTemporaria)
    {
        SqlCommand cmdSelect =
           conexao.CreateCommand();
        cmdSelect.CommandText =
            "SELECT COUNT(1) FROM " + nomeTabelaTemporaria;
        return Convert.ToInt32(cmdSelect.ExecuteScalar());
    }

    private static void RemoverTabelaTemporaria(
        SqlConnection conexao,
        string nomeTabelaTemporaria)
    {
        SqlCommand cmdCreateTable = conexao.CreateCommand();
        cmdCreateTable.CommandText =
            "DROP TABLE " + nomeTabelaTemporaria;
        cmdCreateTable.ExecuteNonQuery();
    }
}

Após esta etapa de codificação, compilar o projeto, a fim de permitir que a DLL obtida seja registrada na base de testes.

Configurando o banco de dados de dados para a utilização da procedure criada

Terminada a construção da stored procedure SP_ListarArquivosDiretorio, será necessário agora a configuração/deploy da mesma numa base de dados (este artigo parte do pressuposto que se estará utilizando um banco chamado “TesteCLRProcedure”).

Antes de prosseguir com os ajustes na base TesteCLRProcedure, é preciso se certificar de que no servidor do SQL Server está habilitado o uso do Common Language Runtime da plataforma .NET. A Listagem 2 contém um script que ativa este mecanismo, empregando para isto as rotinas SP_CONFIGURE e RECONFIGURE.

Listagem 2: Habilitando o uso do CLR num servidor SQL Server

sp_configure 'clr enabled', 1
go
reconfigure
go

Como o banco de dados TesteCLRProcedure depende de uma procedure que acessa informações sobre diretórios e arquivos, o mesmo deverá ser marcado com o parâmetro TRUSTWORTHY (Listagem 3) por um usuário com direitos de administrador: esta alteração indica que a base em questão “confia” no conteúdo do banco de dados (incluindo aqui a stored procedure SP_ListarArquivosDiretorio). Caso isto não aconteça, uma exceção será lançada durante a tentativa de se ler o conteúdo de uma pasta.

Listagem 3: Configurando um banco de dados como TRUSTWORTHY

ALTER DATABASE TesteCLRProcedure SET TRUSTWORTHY ON
GO

O próximo passo agora é registrar a biblioteca (arquivo .dll) correspondente ao projeto TesteCLRProcedure. Embora este procedimento possa ser feito através do Visual Studio, a execução de um script permite não apenas configurar opções adicionais que se fazem necessárias (incluindo nisto parâmetros de segurança), como também facilita a vida de DBAs durante a implantação deste tipo de recurso (reaproveitando neste caso os scripts gerados anteriormente).

Na Listagem 4 é apresentado um exemplo disto. O comando CREATE ASSEMBLY gera uma referência que aponta para a .dll gerada anteriormente (deverá ser fornecido o caminho completo deste arquivo). Já a opção PERMISSION_SET definida como “EXTERNAL_ACCESS” permite ao SQL Server acessar recursos externos como o sistema de arquivos, o registro do Windows ou variáveis de sistema se a procedure em questão depender disto.

Listagem 4: Registrando a biblioteca em que foi implementada uma procedure

USE TesteCLRProcedure
GO

CREATE ASSEMBLY TesteCLRProcedure FROM
	'C:\Devmedia\TesteCLRProcedure\TesteCLRProcedure\bin\Debug\TesteCLRProcedure.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

Já na Listagem 5 está o script responsável por registrar a procedure SP_ListarArquivosDiretorio no banco de dados de testes. Os parâmetros informados devem corresponder às definições utilizadas na implementação do método correspondente em .NET, com a cláusula EXTERNAL NAME contendo a referência para esta operação.

Listagem 5: Registrando uma CLR stored procedure numa base de dados

USE TesteCLRProcedure
GO

CREATE PROCEDURE SP_ListarArquivosDiretorio(
	@Diretorio NVARCHAR(600),
	@QtdeArquivos INT OUTPUT)
AS EXTERNAL NAME TesteCLRProcedure.StoredProcedures.SP_ListarArquivosDiretorio
GO

Na Figura 3 já constam as referências para o Assembly TesteCLRProcedure.dll e a procedure SP_ListarArquivosDiretorio, após a execução dos scripts aqui demonstrados.

Figura 3: Stored procedure SP_ListarArquivosDiretorio já configurada

Efetuando o teste da procedure SP_ListarArquivosDiretorio

Concluídos os ajustes da seção anterior, será efetuado agora um teste envolvendo a stored procedure . Conforme pode ser observado na Listagem 6, as instruções necessárias para a execução desta procedure em nada diferem de construções equivalentes baseadas em T-SQL (invocação de uma procedure, passagem de parâmetros, definição de uma variável de retorno etc.).

Listagem 6: Executando a procedure SP_ListarArquivosDiretorio

USE TesteCLRProcedure
GO

DECLARE @QtdeArquivosEncontrados INT

EXEC [dbo].[SP_ListarArquivosDiretorio]
	@Diretorio = 'C:\Devmedia\TesteCLRProcedure\TesteCLRProcedure\',
	@QtdeArquivos = @QtdeArquivosEncontrados OUTPUT

SELECT @QtdeArquivosEncontrados as QtdeArquivos

Como resultado da execução do script da Listagem 6, foram retornadas informações referentes ao diretório em que está o projeto de testes (conforme pode ser visto na Figura 4).

Figura 4: Resultado da execução da procedure SP_ListarArquivosDiretorio

Conclusão

Procurei com este artigo demonstrar como o .NET pode ser integrado ao SQL Server, considerando para isto a construção de stored procedures a partir de código C#. Este tipo de construção estende a capacidade do código T-SQL convencional, possibilitando que recursos da plataforma .NET sejam consumidos dentro de uma base de dados SQL Server. Isto pode, em muitos casos, suprir a falta de funcionalidades não oferecidas nativamente (como a manipulação de arquivos, por exemplo).

Espero que o conteúdo aqui abordado possa auxiliá-lo no dia-a-dia. Até uma próxima oportunidade!