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

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Como criar stored procedures, functions e triggers usando código .NET

[lead]Do que trata o artigo

Neste artigo veremos como criar objetos de banco de dados usando a plataforma .NET. Conheceremos as facilidades que este tipo de objeto pode nos oferecer e conheceremos as suas particularidades para que a escolha pelo código gerenciado nos dê mais ferramentas na construção de aplicações melhores.

Para que serve

Veremos como podemos criar e usar Stored Procedures e Function usando código gerenciado. Veremos também que o CLR não substitui o Transact SQL, mas faz de maneira superior aquilo que não é nativo do Transact SQL.

Em que situação o tema é útil

CLR pode ser usado em diversas situações, sempre que precisamos usar o servidor de banco de dados para efetuar manipulação de strings, cálculos, criar rotinas que fazem uso de lógicas complexas, o uso do código gerenciado é muito útil.

Resumo do DevMan

Neste artigo veremos como podemos usar código VB.NET para criar objetos diretamente no banco de dados. Vamos fazer um mergulho no processo de integração de duas tecnologias tão interessantes (SQL Server e o transact SQL com o Código Gerenciado), veremos aqui algumas dicas para que fique claro quando a melhor alternativa é o uso do Transact SQL e quando a melhor alternativa é o uso do código gerenciado.[/lead]

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.

[subtitulo]O que é CLR?[/subtitulo]

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.

[subtitulo]Vantagens do uso do CLR[/subtitulo]

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.

[subtitulo]Arquitetura da integração CLR[/subtitulo]

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.

[nota]Nota: Quando falamos de CLR sempre devemos lembrar-nos dos assemblies. Eles são a unidade de encapsulamento, deploy e versionamento do nosso código gerenciado. Quando criamos uma procedure, por exemplo, compilamos o código gerando um assembly. Então é feito o deploy da procedure em nosso banco de dados.[/nota]

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.

[subtitulo]Aspectos relevantes na elaboração da política de segurança[/subtitulo]

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.

[subtitulo]Quando usar código gerenciado e quando usar Transact SQL?[/subtitulo]

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.

[subtitulo]Namespaces necessários para a integração CLR[/subtitulo]

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

[subtitulo]Namespaces compatíveis com a integração CLR[/subtitulo]

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.

[subtitulo]Níveis de acesso[/subtitulo]

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.

[subtitulo]Habilitando a Integração CLR[/subtitulo]

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.

Listagem 1. Habilitando a Integração CLR, usando o Transact SQL

sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'clr enabled', 1;
 GO
 RECONFIGURE;
 GO
 

[subtitulo]Importando o assembly para o SQL Server[/subtitulo]

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.

Listagem 2. Importando o assembly

CREATE ASSEMBLY [ nome do assembly ]
 [ AUTHORIZATION proprietário do assembly ]
 FROM { path do assembly}
 [ WITH PERMISSION_SET = { nível de acesso (SAFE | EXTERNAL_ACCESS | UNSAFE) } ]

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:

DROP ASSEMBLY [ nome do assembly ]

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.

[subtitulo]Criando um projeto no Visual Studio[/subtitulo]

[nota]Nota: Neste artigo usarei o SQL Server 2008 e o Visual Studio 2005. Para que não ocorram erros na conexão do Visual Studio com o SQL Server 2008, faça o download do executável disponibilizado pela Microsoft no link http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&displaylang=en[/nota]

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.

Figura 1. Criando um novo projeto no Visual Studio

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.

Figura 2. Configurando a conexão que será usada pelo projeto

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

Figura 3. Adicionando a referência

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.

Figura 4. Templates específicos da Integração CLR

[subtitulo]Criando CLR Stored Procedures[/subtitulo]

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.

Listagem 3. Criando a tabela de produtos

USE [Artigos]
 GO
  
  
 SET ANSI_NULLS ON
 GO
  
 SET QUOTED_IDENTIFIER ON
 GO
  
 SET ANSI_PADDING ON
 GO
  
 CREATE TABLE [dbo].[nmProduto](
          [ID_Prod] [bigint] IDENTITY(1,1) NOT NULL,
          [Nome_Prod] [varchar](50) NOT NULL,
          [Preco_Prod] [money] NOT NULL,
  CONSTRAINT [PK_nmProduto] PRIMARY KEY CLUSTERED 
 (
          [ID_Prod] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
  
 GO
  
 SET ANSI_PADDING OFF
 GO
 

Listagem 4. Preenchendo a tabela de produtos

begin 
          declare @i int
          
          set @i = 0
          
          while @i < 1000
          begin
                    INSERT INTO [Artigos].[dbo].[nmProduto]
            ([Nome_Prod]
            ,[Preco_Prod])
                    VALUES
           ('PRODUTO ' + CONVERT(varchar,@i)
            ,11 * @i
            )
  
                    set @i +=1
          end 
          
 end

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.

Figura 5. Criando uma stored procedure

É 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.

[nota]Nota: É um bom hábito ter um arquivo para cada stored procedure. Essa prática facilita o processo de manutenção do seu código.[/nota]

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.

Listagem 5. Stored procedure

Imports System
 Imports System.Data
 Imports System.Data.SqlClient
 Imports System.Data.SqlTypes
 Imports Microsoft.SqlServer.Server
  
  
 Partial Public Class StoredProcedures
      _
     Public Shared Sub AplicaJuros_DevolveMsg(ByVal ID As Int32, ByVal taxa As Double)
  
         Using cn As New SqlConnection("context connection=true")
  
             'Declarando objetos usados pela procedure
             Dim pipe As SqlPipe = SqlContext.Pipe
             Dim cmd As New SqlCommand
             Dim oDr As SqlDataReader
             Dim valor As Double
             Dim record As SqlDataRecord
  
             'comando que será executado
             cmd.CommandText = "Select * from nmProduto where ID_Prod = " & ID
  
             'atribuindo uma conexão ao command
             cmd.Connection = cn
  
             'abrindo a conexão
             cmd.Connection.Open()
  
             'atribuindo o resultado do comando ao objeto data reader
             oDr = cmd.ExecuteReader()
  
             'se existem linhas no data reader, efetuar a operação
             If oDr.HasRows = True Then
  
                 oDr.Read()
  
                 'atribuir o valor do preço mutiplicado pela taxa a uma variável
                 valor = CDbl(oDr(2)) * taxa
  
                 'Criando o objeto SQLDataRecord que será exibido no retorno
                 record = New SqlDataRecord(New SqlMetaData("ID", SqlDbType.Int), _
                                                New SqlMetaData("Nome", SqlDbType.VarChar, 50), _
                                                New SqlMetaData("Preco", SqlDbType.Float), _
                                                New SqlMetaData("Taxa", SqlDbType.Float), _
                                                New SqlMetaData("Valor_Com_Taxa", SqlDbType.Float))
  
  
                 'atribuindo os valores ao retorno
                 record.SetInt32(0, CInt(oDr(0)))
                 record.SetString(1, CStr(oDr(1)))
                 record.SetDouble(2, CDbl(oDr(2)))
                 record.SetDouble(3, taxa)
                 record.SetDouble(4, valor)
  
                 oDr.Close()
  
                 'enviar o valor
                 pipe.Send(record)
  
             Else
                 pipe.Send("Nenhum Valor Encontrado")
             End If
  
             
         End Using
  
     End Sub
  
    End Class

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:

Create Assembly NetMagazine
 From 'D:\Artigos\CLR\Projeto VB\NetMagazine\NetMagazine\bin\NetMagazine.dll'
 WITH PERMISSION_SET  = SAFE

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:

CREATE PROCEDURE AplicaJuros (@ID int, @taxa float)
 AS
 EXTERNAL NAME [NetMagazine].[NetMagazine.StoredProcedures].[AplicaJuros_DevolveMsg]

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

[nota]Nota do DevMan

Um aspecto que deve ser conhecido por grande parte dos programadores é que podemos criar nossos códigos gerenciados fora do Visual Studio. Neste caso devemos saber como compilar nosso código, gerando o nosso assembly.

Quando instalamos o SQL Server os arquivos necessários para a compilação de código .NET são instalados também. Os compiladores são os arquivos csc.exe e vbc.exe, que estão disponíveis no diretório C:\Windows\Microsoft.NET\Framework\número da versão instalada.

Após escrever seu código .NET você pode fazer a compilação usando a opção /target dos compiladores descritos acima.

Se escrevemos nosso código gerenciado usando a linguagem C# usamos o seguinte comando para compilar o nosso código:

csc /target:library NOME DO ARQUIVO.cs

Se a linguagem escolhida foi a VB.NET então o comando usado é o seguinte:

vbc /target:library NOME DO ARQUIVO.vb
[/nota]

[subtitulo]Criando CLR Functions[/subtitulo]

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.

Figura 6. Criando uma 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.

Listagem 6. Código da nossa function

(DataAccess:=DataAccessKind.None, IsDeterministic:=True, IsPrecise:=True)> _
 Public Shared Function clr_fn_ValidaIP(ByVal Value As String) As Boolean
  
         Dim rx As New Regex( _
         "((2[0-4]\d|25[0-5]|[01]?\d\d?)\.){3}(2[0-4]\d|25[0-5]|[01]?\d\d?)", _
         RegexOptions.IgnoreCase _
         Or RegexOptions.IgnorePatternWhitespace)
  
         Return rx.Match(CType(Value, String)).Success
     End Function

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.

Listagem 7. Código da nossa Table Valued Function

  <Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName:="FillRow", TableDefinition:=
"timeWritten DateTime,message  nvarchar(4000),category  nvarchar(4000),instanceId  int")> _
    Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
        Return New EventLog(logname).Entries
    End Function

    Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Integer)
        Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
        timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
        message = New SqlChars(eventLogEnTry.Message)
        category = New SqlChars(eventLogEnTry.Category)
        instanceId = CInt(eventLogEnTry.InstanceId)
    End Sub

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

 Drop Assembly NetMagazine;
 Create Assembly NetMagazine
 From 'D:\Artigos\CLR\Projeto VB\NetMagazine\NetMagazine\bin\NetMagazine.dll'
 WITH PERMISSION_SET  = External_Access

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.

Listagem 8. Criando a function que possui o link para a nossa Scalar Valued Function

 USE [Artigos]
 GO
  
 CREATE FUNCTION [dbo].[clr_fn_ValidaIP](@Value [nvarchar](4000))
 RETURNS [bit] WITH EXECUTE AS CALLER
 AS 
 EXTERNAL NAME [NetMagazine].[NetMagazine.UserDefinedFunctions].[clr_fn_ValidaIP]
 GO

Listagem 9. Criando a function que possui o link para a nossa Table Valued Function

 USE [Artigos]
 GO
  
 CREATE FUNCTION [dbo].[clr_fnt_LOG](@logname [nvarchar](4000))
 RETURNS  TABLE (
          [timeWritten] [datetime] NULL,
          [message] [nvarchar](4000) NULL,
          [category] [nvarchar](4000) NULL,
          [instanceId] [int] NULL
 ) WITH EXECUTE AS CALLER
 AS 
 EXTERNAL NAME [NetMagazine].[NetMagazine.UserDefinedFunctions].[InitMethod]
 GO

[subtitulo]Criando Aggregate Functions com CLR[/subtitulo]

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.

Figura 7. Criando um Aggregate

Listagem 10. Criando uma função de agregação para concatenar valores

 Imports System
 Imports System.Data
 Imports System.Data.SqlClient
 Imports System.Data.SqlTypes
 Imports Microsoft.SqlServer.Server
 Imports System.IO
 Imports System.Text
  
  
 
 SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> _
 Public Structure Aggregate1
     Implements IBinarySerialize
  
     Public Sub Init()
         Me.var1 = New StringBuilder()
  
     End Sub
  
     Public Sub Accumulate(ByVal value As SqlString)
         If value.IsNull Then
             Return
         End If
  
         Me.var1.Append(value.Value).Append(","c)
  
     End Sub
  
     Public Sub Merge(ByVal value As Aggregate1)
         Me.var1.Append(value.var1)
     End Sub
  
     Public Function Terminate() As SqlString
         Dim output As String = String.Empty
  
         'delete the trailing comma, if any
         If Not (Me.var1 Is Nothing) AndAlso Me.var1.Length > 0 Then
             output = Me.var1.ToString(0, Me.var1.Length - 1)
         End If
  
         Return New SqlString(output)
  
     End Function
  
     Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
         var1 = New StringBuilder(r.ReadString())
     End Sub
  
     Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
         w.Write(Me.var1.ToString())
     End Sub
  
     ' This is a place-holder field member
     Private var1 As StringBuilder
  
 End Structure

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:

 Drop Assembly NetMagazine;
  
 Create Assembly NetMagazine
 From 'D:\Artigos\CLR\Projeto VB\NetMagazine\NetMagazine\bin\NetMagazine.dll'
 WITH PERMISSION_SET  = External_Access

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.

Listagem 11. Criando o objeto de agregação no banco de dados

 USE [Artigos]
 GO
  
 CREATE AGGREGATE [dbo].[Concatenar]
 (@value [nvarchar](4000))
 RETURNS[nvarchar](4000)
 EXTERNAL NAME [NetMagazine].[NetMagazine.Aggregate1]
 GO

Figura 8. Utilizando a função de agregação

[subtitulo]Criando CLR Trigger[/subtitulo]

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.

Listagem 12. Criando uma Trigger

 Imports System
 Imports System.Data
 Imports System.Data.SqlClient
 Imports System.Data.SqlTypes
 Imports Microsoft.SqlServer.Server
  
  
 Partial Public Class Triggers
  
     
 (Name:="clr_trg_DDL", _
 Target:="DATABASE", Event:="for drop_table, alter_table, create_table")> _
     Public Shared Sub clr_trg_DDL()
         Dim pipe As SqlPipe = SqlContext.Pipe
         pipe.Send(SqlContext.TriggerContext.TriggerAction.ToString)
         pipe.Send(SqlContext.TriggerContext.EventData.Value.ToString)
     End Sub
  
 End Class

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.

Listagem 13. Criando uma Trigger no banco de dados que acessa a trigger criada no assembly

 USE [Artigos]
 GO
  
 CREATE TRIGGER [clr_trg_DDL] ON DATABASE WITH EXECUTE AS CALLER
  FOR ALTER_TABLE, CREATE_TABLE, DROP_TABLE AS 
  EXTERNAL NAME [NetMagazine].[NetMagazine.Triggers].[clr_trg_DDL]
  
 GO

[subtitulo]Conclusão[/subtitulo]

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.

[nota]Referências

Books on Line – SQL Server 2008[/nota]

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?