Integração SQL Server x .NET: criando functions a partir de código C#

Além de construções típicas da linguagem SQL padrão (como as instruções baseadas em comandos como SELECT, INSERT, UPDATE e DELETE), a extensão Transact-SQL (T-SQL) do SQL Server também possibilita a implementação de estruturas mais elaboradas. Objetos como stored procedures, triggers e functions representam outros exemplos bem conhecidos de recursos suportados por este SGBD.

Importante destacar ainda o fato da extensão T-SQL contar com um número bem abrangente e diversificado de functions. Tais elementos facilitam, sem sombra de dúvidas, o trabalho de desenvolvedores na manipulação dos mais variados tipos de dados. Contudo, haverá situações nas quais os recursos nativos oferecidos pelo SQL Server não atenderão a demandas mais específicas. Este é o caso de cenários que envolvam a validação de informações seguindo formatos que devam seguir algum padrão (como e-mail, CEP), criptografia, dentre outros tipos de ações relacionadas à transformação de dados.

O SQL Server conta, desde a versão 2005, com um mecanismo que possibilita a utilização de recursos do .NET Framework a partir de código T-SQL. Esta integração permite inclusive a construção de procedures, functions e triggers a partir do Visual Studio, empregando para isto linguagens como C# ou VB.NET. Graças a tal característica, torna-se possível ampliar as capacidades disponibilizadas nativamente pela extensão T-SQL.

A meta deste artigo é demonstrar, em linhas gerais, o processo de criação de functions em .NET para uso a partir de bases de dados no SQL Server 2014. Isto será feito a partir da implementação de um exemplo no Visual Studio 2013.

Criando uma CLR Function no Visual Studio

A solução apresentada neste artigo foi criada no .NET Framework 4.0, através da utilização do Microsoft Visual Studio Professional 2013 Update 2. Em termos práticos, será construída biblioteca em que constará um método público definido em uma classe estática. Essa operação corresponde, basicamente, à implementação da function a ser acionada a partir de comandos enviados a uma base do SQL Server.

Quanto ao funcionamento deste objeto de banco de dados, a função criada receberá como parâmetros:

• Um primeiro valor a ser verificado (no caso, uma sequência de texto);
• Uma expressão regular como segundo parâmetro, com a mesma determinando o padrão que se espera para o primeiro argumento.

Expressões regulares (também conhecidas pelo termo em inglês “regular expressions”) são sequências de caracteres utilizadas na validação ou, mesmo, na manipulação de cadeias de strings. Surgido ainda no ambiente UNIX, este mecanismo é utilizado em larga escala por plataformas como Java, .NET e PHP em operações que envolvam o tratamento de textos.

O SQL Server não dispõe de nenhuma função nativa que permita a validação de valores pré-formatados como e-mails, CEP, CPF ou outros campos que sigam um padrão específico. Logo, a implementação de uma função conforme descrito nesta seção acaba por se constituir em uma alternativa capaz de suprir este tipo de necessidade.

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 está localizado em “Installed > Templates > SQL Server”), informando no campo Name o nome da aplicação a ser gerada (“TesteCLRFunction”, 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 TesteCLRFunction

A próxima etapa agora será a implementação da function que permitirá a realização de validações, fazendo uso para isto de expressões regulares.

Clicar dentro do Solution Explorer com o botão direito do mouse sobre o projeto TesteCLRFunction, 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 (Figura 2); selecionar do lado esquerdo o item “SQL CLR C#” e, após isto, o template “SQL CLR C# User Defined Function”; preencher o campo Name com “FN_Regex_IsMatch.cs”.

Figura 2. Criando a function FN_Regex_IsMatch

O resultado desta última ação será a criação do arquivo FN_Regex_IsMatch.cs, no qual constará o código que define uma function de mesmo nome.

A operação FN_Regex_IsMatch (Listagem 1) deve fazer parte de uma classe parcial chamada UserDefinedFunctions. O uso desse tipo de construção (classes parciais) é próprio de projetos envolvendo a implementação de funções baseadas no CLR por meio do Visual Studio, sendo de responsabilidade do desenvolvedor apenas o trabalho de codificar as ações esperadas para tais estruturas.

Listagem 1. Implementação da function FN_Regex_IsMatch

  using System;
  using System.Data;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using Microsoft.SqlServer.Server;
  using System.Text.RegularExpressions;
   
  public partial class UserDefinedFunctions
  {
      [Microsoft.SqlServer.Server.SqlFunction]
      public static SqlBoolean FN_Regex_IsMatch(
          SqlString valor, SqlString expressaoRegular)
      {
          if (valor.IsNull || expressaoRegular.IsNull)
              return new SqlBoolean(false);
   
          return new SqlBoolean(Regex.IsMatch(
              valor.Value, expressaoRegular.Value));
      }
  }

Além de fazer parte de uma partial class, CLR Functions apresentam ainda as seguintes características:

• O método que corresponde à função deve ser estático, assim como devolver um valor baseado em um tipo do namespace System.Data.SqlTypes (no caso deste exemplo, um valor booleano é representado por SqlBoolean). No final deste artigo há um link com uma relação completa dos tipos possíveis para utilização em functions;
• Tais operações estarão obrigatoriamente marcadas com o atributo SqlFunction (namespace Microsoft.SqlServer.Server);
• Para os tipos empregados na definição de parâmetros de entrada também utilizar estruturas definidas no namespace System.Data.SqlTypes (ambos os parâmetros esperados para a function FN_Regex_IsMatch representam sequências de texto, logo a escolha óbvia foi o tipo SqlString).

Quanto ao funcionamento da function FN_Regex_IsMatch, é possível observar:

• Uma verificação é realizada inicialmente (utilizando para isto a propriedade IsNull), a fim de determinar se os parâmetros “valor” e “expressaoRegular” foram preenchidos. Em caso negativo, a execução da função FN_Regex_IsMatch é encerrada, produzindo como retorno o valor “false” (a partir de uma referência do tipo SqlBoolean);
• Caso tenham sido associados valores aos dois parâmetros de entrada, o método IsMatch da classe Regex (namespace System.Text.RegularExpressions) é acionado. O resultado desta ação indicará se a string analisada atende ou não ao padrão definido para a expressão regular em uso, sendo que o valor desta verificação é então retornado como uma referência do tipo SqlBoolean.

Encerrada a codificação da function FN_Regex_IsMatch, será necessário então compilar o projeto, de forma a se permitir o registro do assembly correspondente na base de testes.

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

Concluída a implementação da function FN_Regex_IsMatch, o próximo passo agora consistirá na configuração/deploy da mesma numa base de dados (este artigo parte do pressuposto que se estará utilizando um banco chamado “TesteCLRFunction”).

Antes de prosseguir com os ajustes na base TesteCLRFunction, é preciso se certificar de que no servidor do SQL Server está habilitado o uso do Common Language Runtime da plataforma .NET. Na Listagem 2 está o script que ativa este mecanismo, fazendo-se uso para isto das 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 TesteCLRFunction depende de um assembly externo, 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 função FN_Regex_IsMatch). Se isto não for feito, poderão acontecer problemas ao se invocar a function criada na seção anterior.

Listagem 3. Configurando um banco de dados como TRUSTWORTHY

  ALTER DATABASE TesteCLRFunction SET TRUSTWORTHY ON
  GO

A biblioteca (arquivo .dll) gerada como resultado da compilação do projeto TesteCLRFunction precisará também ser registrada. Muito embora este procedimento possa ser realizado através do Visual Studio, a execução de um script possibilita não apenas configurar opções adicionais que se fizerem necessárias (incluindo nisto parâmetros de segurança), como também simplifica o trabalho de DBAs durante a implantação deste tipo de recurso (reaproveitando em tais situações os scripts gerados anteriormente).

O processo de registro de uma biblioteca a partir do SQL Server está exemplificado na Listagem 4. O comando CREATE ASSEMBLY cria uma referência que aponta para a .dll gerada anteriormente (deverá ser informado para isto o caminho completo deste arquivo). Já a opção PERMISSION_SET configurada com o valor “SAFE” indica que o assembly que se está registrando terá acesso apenas a dados locais; na prática, isto significa que a biblioteca não poderá acessar recursos externos como o sistema de arquivos, o registro do Windows ou variáveis de sistema (não é o caso da function detalhada na seção anterior).

Listagem 4. Registrando a biblioteca em que foi implementada uma function

  USE TesteCLRFunction
  GO
   
  CREATE ASSEMBLY TesteCLRFunction FROM
        'C:\Desenvolvimento\TesteCRLFunction\TesteCRLFunction\bin\Debug\TesteCRLFunction.dll'
  WITH PERMISSION_SET = SAFE
  GO

Já a Listagem 5 apresenta o script responsável por registrar a function FN_Regex_IsMatch na base de testes. Os parâmetros esperados para esta função 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 a implementação desta operação no arquivo com extensão .dll.

Listagem 5. Registrando uma CLR Function numa base de dados

  USE TesteCLRFunction
  GO
   
  CREATE FUNCTION dbo.FN_Regex_IsMatch(
      @Valor NVARCHAR(MAX),
      @ExpressaoRegular NVARCHAR(MAX))
  RETURNS BIT
  AS 
  EXTERNAL NAME TesteCLRFunction.UserDefinedFunctions.FN_Regex_IsMatch
  GO

Na Figura 3 é possível observar as referências para o Assembly TesteCLRFunction.dll e a função FN_Regex_IsMatch, as quais foram geradas após a execução dos scripts aqui discutidos.

Figura 3. Function FN_Regex_IsMatch

Efetuando o teste da function FN_Regex_IsMatch

Com a function FN_Regex_IsMatch devidamente criada e registrada, será realizado agora um teste demonstrando como este objeto pode ser útil em tarefas de validação. O objetivo será verificar a validade do conteúdo com o qual foi preenchido um campo de e-mail, sendo este último parte integrante de uma tabela em que constam informações de clientes. Na Listagem 6 está o script para a criação e inclusão de informações na tabela Clientes.

Listagem 6. Scripts para criação e preenchimento da tabela Clientes

  CREATE TABLE [dbo].[Clientes](
      [IdCliente] [int] IDENTITY(1,1) NOT NULL,
      [CPF] [char](11) NOT NULL,
      [NomeCliente] [varchar](50) NOT NULL,
      [Email] [varchar](50) NOT NULL,
      CONSTRAINT PK_Clientes PRIMARY KEY ([IdCliente]),
        CONSTRAINT UK_Clientes UNIQUE ([CPF])
  )
  GO
   
  INSERT INTO dbo.Clientes(CPF, NomeCliente, Email)
  VALUES('01771455527', 'JOSE DA SILVA', 'jose.silva@acme.com.br')
   
  INSERT INTO dbo.Clientes(CPF, NomeCliente, Email)
  VALUES('21784665169', 'PEDRO DE OLIVEIRA', 'pedro_oliveira@acme')
   
  INSERT INTO dbo.Clientes(CPF, NomeCliente, Email)
  VALUES('91728365805', 'JOAQUIM GONÇALVES', 'joaquim.goncalves@fast-internet.com')
   
  INSERT INTO dbo.Clientes(CPF, NomeCliente, Email)
  VALUES('51867483661', 'JOÃO CARLOS PRATES', 'joaoprates@sdruws.com.br')
   
  INSERT INTO dbo.Clientes(CPF, NomeCliente, Email)
  VALUES('14678882227', 'MARIA TEIXEIRA', 'maria_teixeira')
  GO

Já na Listagem 7 é possível observar um exemplo de uso da função FN_Regex_IsMatch. Esta construção está recebendo como parâmetros o conteúdo do campo “Email”, assim como uma variável em que consta uma expressão regular para a validação deste tipo de informação. Dependendo do resultado desta function, uma mensagem será exibida indicando a validade ou não do valor associado à coluna “Email”.

Listagem 7. Utilizando a função FN_Regex_IsMatch em uma consulta

  USE TesteFunction
  GO
   
  DECLARE @REGEX_EMAIL VARCHAR(255) = '^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)
   
  SELECT
      IdCliente, CPF, NomeCliente, Email,
      ResultadoVerificacaoEmail =
            CASE
                  WHEN dbo.FN_Regex_IsMatch(Email, @REGEX_EMAIL) = 1 THEN 'Formato de e-mail válido'
                    ELSE 'E-mail com erro de formatação'
              END
  FROM dbo.Clientes
  

A Figura 4 apresenta o resulta obtido após a execução do script da Listagem 7. Conforme é possível observar, dois registros (destacados em vermelho) foram preenchidos com sequências de texto inválidas no campo “Email”.

Figura 4. Validação de e-mails utilizando a função FN_Regex_IsMatch

Este artigo procurou demonstrar como acontece integração entre o SQL Server 2014 e o .NET Framework, por meio da construção de uma função baseada em recursos do Common Language Runtime (CLR). Graças a este mecanismo é possível expandir as capacidades da extensão T-SQL, através da criação de objetos como functions, stored procedures e triggers que empregam estruturas nativas da plataforma .NET.

Espero que o conteúdo aqui abordado possa ser útil no seu dia a dia. Até uma próxima oportunidade!

Links

CLR Integration Code Access Security
http://msdn.microsoft.com/en-us/library/ms345101.aspx

Create CLR Functions
http://msdn.microsoft.com/en-us/library/ms189876.aspx

RegExLib.com - Regular Expression Library
http://www.regxlib.com/

SQL Server Data Tools
http://msdn.microsoft.com/en-us/data/hh297027

System.Data.SqlTypes Namespace
http://msdn.microsoft.com/en-us/library/System.Data.SqlTypes(v=vs.110).aspx