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  " +
            "( " +
                "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  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!

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

Veja neste artigo como criar um stored procedure para uma base do SQL Server 2012 através de código-fonte escrito em C#. Construções deste tipo são conhecidas como CLR Stored Procedures, permitindo a criação de rotinas a partir do .NET framework

Criando uma stored procedure atrav�s do Visual Studio

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

Efetuando o teste da procedure SP_ListarArquivosDiretorio

Conclus�o

Artigos relacionados