Artigo .net Magazine 65 - Usando código gerenciado no SQL Server

Programadores habituados ao uso do .NET Framework têm agora novos horizontes, assim como os programadores habituados ao uso do Transact SQL. Veremos neste artigo que o CLR executa tarefas que não são nativas do Transact SQL, sendo assim um completa o outro. Quando falamos da integração CLR devemos ter em mente que são necessários três passos (que serão abordados neste artigo):

1. Criar o seu código usando uma das linguagens suportadas pelo .NET Framework;
2. Compilar o assembly .NET;
3. Importar o assembly para o banco de dados.

O que é CLR?

A sigla quer dizer Common Language Runtime - Linguagem comum de execução - e é graças a ela que podemos em nosso programa VB.Net acessar uma classe escrita em C#. A CLR é a parte mais importante do .NET Framework, em muitos lugares na Web referem-se à CLR como o coração da plataforma .NET.

Trata-se de um ambiente de tempo de execução (runtime) que realiza tarefas tais como: gerenciamento de memória, coleta de lixo, segurança, tratamento de erro, controle de versão e suporte de instalação. Realiza o intermédio entre a aplicação e o sistema operacional. O código que é executado nesse ambiente de runtime é chamado de Código Gerenciado (“Manage Code”), enquanto aquele que é executado fora é chamado de código não gerenciado (“Unmanaged Code”).

A CLR consiste de duas partes: “execution engine” que é responsável pela execução do código, e a “base class libraries”, que é responsável pelas classes que compõem o .Net Framework e que são reutilizadas praticamente em todo seu código, pois contém a base para a construção de aplicativos.

Sendo assim, o código Transact SQL é um código não gerenciado, executado fora do ambiente CLR.

Vantagens do uso do CLR

A partir da versão 2005 do SQL Server, podemos usar o código escrito com qualquer linguagem suportada pela plataforma . NET dentro do nosso banco de dados. Temos então a possibilidade de usar todas as facilidades do código gerenciado em nossas stored procedures, triggers, user defined types e functions. O código das camadas intermediárias agora pode ser escrito na mesma linguagem do código hospedado em nosso servidor. Para usar o CLR dentro de nosso banco de dados, usaremos intensamente o namespace Microsoft.SqlServer.Server que inclui as principais funcionalidades para este tipo de programação. Para o uso do código gerenciado dentro de nosso servidor é preciso usar no mínimo o NET Framework 2.0.

Os programadores habituados ao uso do Transact SQL devem saber que ele é ideal, e foi construído para o acesso direto aos dados hospedados em nosso banco de dados. O Transact SQL trabalha com conjuntos (embora também seja possível trabalhar com uma linha). Podemos simplificar dizendo que para operações de Select, Insert, Delete ou Update o Transact SQL é a melhor escolha.

Mas, e se for preciso trabalhar com arrays, collection, estruturas do tipo For…Each, como devemos proceder?

Nestas situações devemos optar pelo uso do código gerenciado. Com ele podemos usar as vantagens da orientação objeto, organizar seu código em classes e namespaces.

Outra situação onde o desempenho do código gerenciado é superior ao desempenho do Transact SQL, é quando precisamos executar no servidor operações com cálculos intensos, manipulações de string, manipulação de expressões regulares e códigos com lógica complexa.

Um dos facilitadores para o desenvolvimento de soluções que usam o código gerenciado é a possibilidade de usar os recursos disponíveis na Base Class Library (BCL).

As principais vantagens no uso do código gerenciado dentro de nosso banco de dados SQL Server são:

• Escolha a linguagem de programação

  Com o uso da integração CLR podemos escolher a linguagem de programação da nossa preferência, de acordo com os requerimentos de negócio e com o nosso conhecimento.

• Um modelo de programação melhor

  O uso das linguagens disponíveis no .NET framework permite o uso de construções mais complexas e recursos anteriormente indisponíveis com o uso do Transact SQL.

  Os desenvolvedores também podem aproveitar a potência da biblioteca do .NET Framework, que fornece um abrangente conjunto de classes que podem ser usadas para resolver problemas de programação, fazendo isso de forma rápida e eficiente.

• Proteção e segurança aprimoradas

  O código gerenciado é executado em um ambiente CLR hospedado dentro do engine do banco de dados. O que oferece maior segurança aos nossos códigos.

• Desenvolvimento simplificado por meio de um ambiente padronizado

  O desenvolvimento do banco de dados será integrado em versões futuras do ambiente de desenvolvimento do Microsoft Visual Studio .NET. Os desenvolvedores usam as mesmas ferramentas para desenvolver e depurar scripts e objetos do banco de dados que usavam para escrever componentes e serviços de camada intermediária ou da camada de cliente do .NET Framework.

• Potencial para desempenho e escalabilidade aprimorados

  Em muitas situações, os modelos de compilação e execução da linguagem do .NET Framework oferecem um desempenho aprimorado em relação ao Transact-SQL.

Arquitetura da integração CLR

Usando uma linguagem de programação suportada pelo .NET framework, um programador escreve o código para criar classes e definir a estrutura destas classes. A compilação do código escrito pelo programador gera um tipo de arquivo chamado Assembly. Nele temos o código compilado em uma linguagem denominada Microsoft Intermediate Language (MSIL) e um manifesto que contém todas as referências para as classes que o assembly necessita. O MSIL é executado pelo CLR.

Até este momento a compilação do código ocorre da mesma forma para os códigos que serão executados na aplicação cliente e no servidor SQL Server.

Porém é necessário garantir que o código escrito em CLR possa ser executado usando os recursos do servidor. Temos aqui a diferenciação do nosso assembly.

Quando é encontrada uma diretiva que indique para o compilador que o código MSIL faz parte de uma integração CLR, as otimizações realizadas respeitam a arquitetura usada pelo servidor e garantem a existência de uma única unidade de compilação, ou seja, temos só um assembly escrito em uma das linguagens suportadas pelo .NET Framework e não uma tradução do assembly criado com código gerenciado para o Transact SQL.

Aspectos relevantes na elaboração da política de segurança

Antes de começarmos a criação do nosso código temos que estar atentos a alguns fatores muito importantes na criação de recursos que usam código gerenciado. Alguns destes fatores podem parecer óbvios, mas desconsiderá-los coloca em risco a segurança do seu banco de dados e o sucesso da sua implementação.

Ao definir as opções de acesso que o seu código gerenciado terá, é preciso utilizar a política do menor privilégio, ou seja, o seu código gerenciado deve ter somente as permissões que ele realmente necessita.

Um código gerenciado não pode colocar em risco a estabilidade do banco de dados. Esta afirmação não deve ser esquecida quando falamos da integração CLR.

Se não for considerado o fato de que o SQL Server e o CLR têm modelos diferentes de gestão de memória (por exemplo) podemos desestabilizar o nosso banco de dados. Vale lembrar que o CLR não faz qualquer distinção entre memória física e virtual, enquanto o SQL Server gerencia diretamente a memória física.

Ao executar um código CLR no banco de dados o usuário obrigatoriamente deve seguir regras de autenticação e autorização para acessar os objetos do banco de dados (tabelas, funções, triggers, stored procedures). Além disso, o DBA responsável e os administradores do sistema como um todo devem ser capazes de controlar o acesso a recursos do sistema operacional (arquivos, diretórios, recursos de rede). Veremos neste artigo que é muito mais simples acessar os recursos citados com o uso da integração CLR, porém a simplicidade no uso pode causar diversos problemas se a política de autenticação e autorização não estiver muito bem definida.

Quando usar código gerenciado e quando usar Transact SQL?

Quando estamos elaborando a nossa aplicação devemos definir quando usaremos um ou outro recurso. Esta definição tem impacto direto na performance e segurança da nossa aplicação. O uso do código gerenciado é mais indicado que o uso do Transact SQL quando:

• Necessitamos desenvolver lógicas muito complexas

  Quando existe a necessidade de desenvolver um lógica de negócios muito complexa, devemos fazer uso dos benefícios oferecidos pelo CLR, pois nele podemos usar a orientação a objeto, manipulação de erros etc. O fato de que a lógica do nosso negócio ficará encapsulada dentro do assembly, aumentando a segurança da aplicação;

• Necessitamos usar alguma das classes da Base Class Library

  Quando precisamos acessar informações de um web service, acessar o sistema de arquivos ou usar algum recurso já definido na Base Class Library, devemos usar o código gerenciado, pois com ele temos prontos códigos que seriam muito difíceis ou impossíveis de construir usando Transact SQL;

• Necessitamos de uso intenso de CPU

  Quando a nossa lógica necessita do uso intenso do processador, devemos optar pelo código gerenciado, pois o fato dele ser compilado oferece maior performance nas tarefas.

Como já foi exposto neste artigo, o Transact SQL foi elaborado para trabalhar com conjuntos de dados onde existe a necessidade do desenvolvimento de lógica procedural. Para acessar, inserir, atualizar, excluir dados, sempre devemos fazer uso do Transact SQL.

Namespaces necessários para a integração CLR

Quando instalamos o SQL Server 2005, alguns componentes requeridos para o desenvolvimento de objetos CLR já são instalados. Dentro do .NET Framework temos um assembly chamado system.data.dll, dentro deste assembly temos toda a funcionalidade básica para a criação do código gerenciado. Este assembly deve estar registrado no Global Assembly Cache (GAC) e deve existir no diretório do .NET Framework. A referência para este assembly é adicionada automaticamente aos projetos, sejam eles criados através de linha de comando ou através do Visual Studio.

O assembly system.data.dll contêm os seguintes namespaces (que são indispensáveis para a compilação de objetos CLR):

• System.Data
• System.Data.Sql
• Microsoft.SqlServer.Server
• System.Data.SqlTypes

Namespaces compatíveis com a integração CLR

Alguns namespaces são compatíveis com a integração CLR e outros não. Afinal não tem sentido tentar usar um message box quando estamos trabalhando com a integração CLR e SQL Server. Algumas bibliotecas essenciais para a programação de objetos de banco de dados devem respeitar as regras de segurança e confiabilidade para a integração com o SQL Server. Essas bibliotecas são:

• CustomMarshalers
• Microsoft.VisualBasic
• Microsoft.VisualC
• mscorlib
• System
• System.Configuration
• System.Data
• System.Data.OracleClient
• System.Data.SqlXml
• System.Deployment
• System.Security
• System.Transactions
• System.Web.Services
• System.Xml
• System.Core.dll
• System.Xml.Linq.dll

Para conhecer os namespaces que não são suportados pela integração CLR, temos que primeiro conhecer os níveis de acesso que podemos atribuir ao nosso assembly.

Níveis de acesso

Quando criamos um código gerenciado devemos atribuir a ele um nível de acesso aos recursos do nosso sistema. Este é um passo delicado da criação de nosso assembly, já que podemos colocar em risco a estabilidade e segurança dos nossos recursos. Veja os níveis:

• SAFE

  É o nível de acesso mais restritivo e também o mais recomendado pela Microsoft (seguindo a política do menor acesso). O código que é executado por um assembly com esse tipo de permissão não pode acessar recursos externos. Esta é a opção default quando criamos um assembly de integração.

• EXTERNAL_ACCESS

  Possui todas as permissões do nível SAFE e a capacidade de acessar recursos externos.

• UNSAFE

  Quando atribuímos este nível de permissão ao nosso assembly, devemos estar cientes que o assembly tem acesso irrestrito aos recursos do sistema, dentro e fora do banco de dados. Podendo até chamar códigos não gerenciados.

Agora que conhecemos os níveis de acesso que podemos atribuir ao nosso assembly, podemos entender um pouco sobre os namespaces que não são compatíveis com a integração CLR. Isso porque a falta de compatibilidade de um assembly com alguns namespaces está diretamente relacionada ao nível de acesso que foi atribuído a ele.

Por exemplo, supondo que o nosso assembly foi criado em nosso servidor com o nível de acesso SAFE, neste caso ele não terá permissões para usar as classes do namespace System.DirectoryServices.

Habilitando a Integração CLR

Por padrão a integração CLR está desabilitada no servidor SQL Server e para fazer uso deste recurso é necessário habilitá-la. Para isso é necessário que o usuário faça parte das roles sysadmin ou serveradmin. A Listagem 1 mostra como habilitamos a integração CLR usando a linguagem Transact SQL.

Importando o assembly para o SQL Server

Depois que criamos e compilamos o nosso assembly é preciso importá-lo para o banco de dados, tornando-o assim disponível para uso, veja Listagem 2.

Se for necessário excluir o assembly devemos ter a certeza que ele não está sendo referenciado em nenhum objeto presente em nosso banco de dados, pois caso contrário nosso banco de dados não o encontrará e uma exceção ocorrerá. O comando para excluir o assembly é apresentado a seguir:

Pode ser necessário também alterar o assembly, para isso usamos a mesma sintaxe apresentada na Listagem 2, porém trocamos o CREATE por ALTER.

Criando um projeto no Visual Studio

Abra o Visual Studio 2005, clique no botão New Project, na janela que será exibida encontre a linguagem Visual Basic (ou C#, caso prefira) e selecione o item Database, preencha o nome do projeto e escolha o diretório onde ele será armazenado. A Figura 1 mostra a criação do novo projeto.

Após clicar no botão OK vamos escolher o banco de dados que será referenciado em nosso projeto. Se o banco de dados não aparece na janela, basta clicar no botão Add New Reference para que possamos configurar a nossa conexão. A Figura 2 mostra a nova conexão que será criada e adiciona as referências do projeto que estamos criando.

Após configurarmos a conexão, ela estará disponível para uso como mostra figura 3. Basta então clicar no botão OK.

Veja na Figura 4 que ao clicarmos com o botão direito sobre a nossa solução e no menu Add, temos templates específicos para a criação de objetos de banco de dados.

Criando CLR Stored Procedures

O primeiro objeto que criaremos será uma stored procedure. Esta procedure receberá como parâmetro um ID de produto, efetuará um cálculo de juros sobre o valor do item informado e devolverá uma mensagem com o resultado do cálculo. O script da Listagem 3 contém o código para a criação da tabela que usaremos em nossa stored procedure. Na Listagem 4 temos o script criado para preencher a tabela que acabamos de criar.

Agora vamos adicionar ao nosso projeto uma stored procedure. Para isto basta clicar sobre a nossa solução com o botão direito do mouse, no menu, clicar na opção Add, no menu seguinte, basta escolher a opção “Stored Procedure” e informar o nome da stored procedure. Dessa maneira nosso projeto deverá estar igual à Figura 5.

É interessante observar que no código gerado, temos a diretiva . Esta diretiva é usada pelo Visual Studio .NET para fazer o deployment dentro do SQL Server. Temos também a Partial Class chamada StoredProcedures, dentro desta classe colocamos uma ou mais stored procedures.

Na Listagem 5 podemos ver o código da nossa stored procedure. Acredito que o leitor esteja acostumado com o ADO.NET, então o que você notará de diferente no código são algumas classes do namespace do SqlServer. SqlDataRecord, por exemplo, serve para criar um registro a ser enviado à aplicação cliente. Usamos duas classes muito úteis quando trabalhamos com a integração CLR, são elas SQLContext e SQLPipe. A classe SQLContext permite que o código gerenciado acesse informações do banco de dados que está executando o comando. A classe SQLPipe, permite o envio de mensagens, dados tabulares ou até mesmo erros que possam vir a ocorrer durante a execução. Este objeto é semelhante ao objeto HttpResponse (Response) do ASP.NET. Tomei o cuidado de comentar bem o código para não haver dúvida.

Agora que criamos a nossa stored procedure podemos importar o assembly para o SQL Server diretamente pelo Visual Studio através da tecla de atalho F5, podemos usar o menu Build – Deploy ou podemos usar o menu Build – Build e através do comando apresentado na Listagem 2 (CREATE ASSEMBLY). Usando o Visual Studio, o deploy do assembly é muito simples, por isso usaremos o comando Create Assembly, como mostrado no código a seguir:

Pronto, importamos o nosso assembly para o SQL Server. Mas como devemos proceder para usar este assembly? Devemos criar um objeto (no nosso caso uma Stored Procedure) com um link para o assembly. O seguinte código mostra como devemos criar a stored procedure que usará o assembly que importamos:

Agora é só proceder como de costume, testando e executando a nossa stored procedure.

Criando CLR Functions

Funções que apenas retornam um valor (Scalar Functions) na maior parte dos cenários apresentam melhor performance quando são criadas usando CLR. Elas são implementadas como métodos de uma classe dentro de um assembly .NET Framework. Os tipos de parâmetro de entrada e de saída de uma function podem ser de qualquer tipo suportado pelo SQL Server, exceto os tipos varchar, char, rowversion, text, ntext, image, timestamp, table e cursor.

Quando estamos implementando uma função usando o .NET Framework devemos observar a diretiva SQLFunction que fornece algumas informações adicionais sobre a função que estamos criando. Os atributos da diretiva informam se a nossa função acessa ou modifica dados, se é ou não uma função determinística e se a função efetua operações com ponto flutuante.

Em nosso projeto vamos adicionar uma function. Para isso clique com o botão direito sobe a solução. No menu clique sobre a opção ADD e em seguida sobre a opção User Defined Function. Defina o nome de sua function e clique no botão Add. O template da Figura 6 estará disponível para a criação da nossa function.

Com o nosso template criado, vamos criar uma função para validar endereços IP. Veja na Listagem 6 que usando CLR manipular expressões regulares é bem simples.

O outro tipo de função que podemos criar são funções que retornam tabelas. Este tipo de função também pode ser construída usando CLR graças ao objeto IEnumerable. Quando usamos o Transact SQL para criar este tipo de função, os dados são enviados para uma tabela intermediária, o que obriga o nosso processo a aguardar até que todas as linhas estejam disponíveis para utilizar o retorno desta função. No caso do CLR não existe uma tabela intermediária, o que permite ao processo consumir o retorno da função a partir do momento que a primeira linha estiver disponível. Isso é muito útil quando a função retorna uma grande quantidade de linhas, pois não existe a necessidade de que todas as linhas estejam disponíveis na memória para serem usadas.

Funções do tipo Table Valued também são implementadas como métodos de uma classe de nosso assembly. Este tipo de função sempre deve implementar a interface IEnumerable, isso facilita a gravação de funções com valor de tabela que convertem uma coleção ou uma matriz em um conjunto de resultados.

Este tipo de função tem algumas particularidades em sua implementação. Vou comentar sobre elas antes de apresentar o código de exemplo.

A primeira particularidade é que a diretiva que indica a função possui um novo atributo, o FillRowMethodName. Neste atributo informamos o nome do método que preencherá as linhas da tabela retornada pela função. Observe que o método que possui a diretiva que indica a função, implementa a interface IEnumerable.

Outro ponto interessante é que no método usado para preencher as linhas da tabela devemos definir cada uma das colunas da nossa tabela. A Listagem 7 mostra o código da nossa function, que manipula o log de eventos do Windows.

Agora que criamos nossas functions, vamos compilar o nosso assembly e importá-lo para o nosso banco de dados, como é mostrado a seguir:

Como vimos em nosso primeiro exemplo, temos que criar um objeto no SQL Server que faça referência às functions criadas em nosso assembly. Na Listagem 8 criamos um link para a Scalar Valued Function,e na Listagem 9 criamos o objeto que faz o link com a nossa Table Valued Function.

Criando Aggregate Functions com CLR

Este tipo de função é bem interessante, ela efetua uma operação em um conjunto de valores e retorna um único valor. Como os outros objetos criados com o CLR, ao compilar e importar o assembly que possui uma função de agregação ele fica disponível para ser usado junto com o objetos Transact SQL ou com outros objetos construídos com código gerenciado.

Para criar este tipo de função é necessário criar um contrato de agregação. O contrato de agregação inclui o mecanismo para salvar o estado intermediário da agregação e o mecanismo para acumular novos valores, o qual consiste em quatro métodos: Init, Accumulate, Merge e Terminate.

• Método Init: Este método é usado para inicializar a agregação;
• Método Accumulate: Este método é usado para acumular os valores da agregação;
• Método Merge: Este método é usado para mesclar o resultado de outra instância da classe de agregação com o resultado da instância atual;
• Método Terminate: Este método completa a computação da agregação e retorna o seu resultado.

Para codificar este exemplo vamos usar o template do Visual Studio, clicando com o botão direito em nossa solução, escolhendo a opção ADD do menu, e em seguida a opção Aggregate. A Figura 7 mostra o template. Vamos então criar uma função de agregação para concatenar valores de uma determinada coluna (Listagem 10). Basicamente usamos a já nossa conhecida classe StringBuilder.

Após compilar o assembly, vamos importá-lo para o banco de dados usando o comando a seguir, perceba que vamos excluir o assembly através do comando DROP e depois importá-lo para o banco de dados com o comando CREATE:

Com o nosso assembly criado, basta criar a função de agregação dentro do banco de dados (Listagem 11). Um exemplo do uso da função que acabamos de criar pode ser visto na Figura 8.

Criando CLR Trigger

Trigger é um tipo de stored procedure que é automaticamente executada em resposta a um determinado evento. Podemos criar este tipo de objeto com o Transact SQL sem grandes dificuldades, mas também podemos aproveitar as facilidades que o código gerenciado nos oferece para criar triggers.

Com uma CLR Trigger podemos referenciar dados nas tabelas INSERTED e DELETED, verificar as colunas que foram alteradas por um comando do tipo UPDATE, acessar informações sobre objetos do banco de dados que foram alterados em função da execução de comando DDL. Todas estas informações estão disponíveis no objeto SQLTriggerContext.

Vamos adicionar em nossa solução o template de uma trigger (clicar com o botão direito sobre a solução, ADD, Trigger). A Listagem 12 mostra uma trigger que é executada em resposta a alguns comandos DDL e fornece informações sobre o comando que está sendo executado. Veja que informamos quando a trigger será executada através de atributos da diretiva SQLTrigger.

Podemos fazer o deploy do nosso assembly com o Visual Studio, ou através do comando CREATE ASSEMBLY como já foi feito anteriormente. Após importar o nosso assembly, usamos o código da Listagem 13 para criar um link entre o nosso código CLR e o banco de dados SQL.

Conclusão

A possibilidade de usar o código gerenciado no banco de dados vem para enriquecer e aproximar o horizonte de quem trabalha com o .NET Framework com o horizonte de quem trabalha com o Transact SQL. Crescemos muito em possibilidades! Podemos criar aplicações mais robustas com menos linhas de código.

Assim temos mais tempo para avaliar corretamente o uso dos nossos recursos. Na minha opinião, os bons desenvolvedores se destacam não só por saber usar diversas tecnologias, mas por saber quando aplicar uma e outra tecnologia disponível.

Quando estabelecemos corretamente que devemos usar a integração CLR temos inúmeros ganhos de performance, segurança e escalabilidade do nosso código. Temos um novo leque de possibilidades à nossa disposição, cabe a nós (bons desenvolvedores) fazer um bom uso dele.