Esse é um tópico que irá mostrar algo que definitivamente você já precisou mas não sabia como fazer e que facilitará (e muito) alguns trabalhos. Programadores que usam o Bando de Dados para programar sendo por Stored Procedure, Functions e Triggers já passaram por maus bocados para preparar um algoritmo que realizasse um determinado processo, mas que esse mesmo algoritmo é mais fácil de ser escrito (ou possuir funcionalidades que o SGDB não possui) em outra linguagem de programação. Por exemplo: validar e-mail. É possível no SQL Server fazermos um Function que receba um VARCHAR e retorne um BIT informando que um e-mail é válido ou não, contudo seria uma função um tanto que trabalhosa para quem não está familiarizado com programação em banco. Para quem desenvolve em ASP.NET (C# ou VB.NET) basta usarmos expressão regular que, em duas linhas, resolve o problema. Portanto iremos utilizar uma função escrita em C# compilada em uma DLL e usarmos em um Function no SQL Server. Isso é possível através do CLR.
Primeiramente vamos criar a DLL contendo a função (no nosso exemplo vamos fazer a que valida e-mail - mas você pode desenvolver a que quiser). Abra o Visual Studio e crie um Project. Em Project Type escolha Visual C# Windows e o Template opte por uma Class Libary. Note que quando você cria uma classe vem com o nome Class1.cs. Para facilitar a compreensão, renomeie para FuncoesSql.cs (ou outra) e remova o namespace criado. Agora, escreva a função que deseja e adicione os namespaces necessários ficando da seguinte forma:
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class FuncoesSql
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean IsMailValido(SqlString email)
{
// Cria um objeto de expressões regulares para validar e-mail
Regex expressaoRegular = new Regex(@"^(([^<>()[\]\\.,;:\s@\""]+"
+ @"(\.[^<>()[\]\\.,;:\s@\""]+)*)(\"".+\""))@"
+ @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
+ @"\.[0-9]{1,3}\])(([a-zA-Z\-0-9]+\.)+"
+ @"[a-zA-Z]{2,}))$");
// Realiza um teste na validação da expressão
return new SqlBoolean(expressaoRegular.IsMatch(email.ToString()));
}
}
No caso, precisamos explicitar os tipos de entrada e saída utilizados pelo banco de dados para que não haja problemas de tipagem. Poderia ser qualquer função ou quantas que quisesse, contanto que respeite os tipos. Agora compile a DLL pressionando F6 ou pelo menu Build. Já temos a DLL com a função. Copie a DLL (que está na pasta bin\Debug de sua solução) e coloque-a em um local onde seu Banco de Dados possa buscá-la. Para exemplo, colocarei em C:\SqlDlls\ no Servidor.
O próximo passo é verificar se no SQL Server está ativo a funcionalidade CLR, para isso vá em Iniciar > Todos os Programas > Microsft SQL Server 2008 (ou 2005) > Configuration Tools > SQL Server Surface Area Configuration. Entre em Surface Area Configuration for Features e na guia CLR Integration verifique se a função está habilitada. Se não estiver, habilite.
Dê OK e agora já poderá usar CLR no SQL Server. Abra o SQL Server Management Studio, conecte-se na base desejada e execute as seguintes instruções SQL (abra um New Query para isso):
-- CRIA O ASSEMBLY INDICANDO SUA ORIGEM
CREATE ASSEMBLY FuncoesSql FROM 'C:\SqlDlls\FuncoesSqlServerASPNET.dll'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- CRIA A FUNÇÃO
CREATE FUNCTION dbo.Fun_Valida_Mail (@email NVARCHAR(350))
RETURNS BIT
-- INDICA A ORIGEM DA FUNÇÃO SQL: ASSEMBLY > CLASSE > FUNÇÃO
AS EXTERNAL NAME [FuncoesSql].FuncoesSql.IsMailValido
GO
Agora execute a Query (ou pressione F5)... Foi criada a função! Pronto... agora é só usar como se fosse uam função do SQL Server normalmente. Abaixo tem alguns exemplos de consulta e seus retornos:
Execuções:
SELECT dbo.Fun_Valida_Mail('tmarcal@gmail')
SELECT dbo.Fun_Valida_Mail('tmarcal@gmail.com')
SELECT dbo.Fun_Valida_Mail('tmarcal.. @gmail.com')
Retorno:
0
1
0
Apesar do trabalho inicial, pode-se aproveitar muito de ambos os recursos e criar cada vez mais funções complexas. Quanto ao desempenho da CLR, em comparação ao T-SQL, deve-se ficar a mesma coisa. A diferença é praticamente imperceptível. Espero que tenham gostado... Até breve!
Utilizando função de uma DLL Externa no SQL Server
Postado por
Thiago Marçal
on sábado, 21 de agosto de 2010
/
Marcadores:
clr,
dicas,
dll,
sql server,
visual studio
4 comentários:
Tiago,
Muito bom seu Post, me ajudou bastante.
Porém o retorno da função estava retornando sempre como falso.
Fazendo algumas pesquisas encontrei outro regex que deu a resposta correta. Não sei se o retorne poderia ter algo a ver por eu estar usando o SQL 2012.
enfim segue abaixo o que alterei na função. Mas de qualquer forma muito Obrigado!!
/* Não estava Dando o Retorno esperado:
Regex expressaoRegular = new Regex(@"^(([^<>()[\]\\.,;:\s@\""]+"
+ @"(\.[^<>()[\]\\.,;:\s@\""]+)*)(\"".+\""))@"
+ @"((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
+ @"\.[0-9]{1,3}\])(([a-zA-Z\-0-9]+\.)+"
+ @"[a-zA-Z]{2,}))$");
*/
// Return true if strIn is in valid e-mail format.
return Regex.IsMatch(email.ToString(),
@"^(?("")(""[^""]+?""@)|(([0-9a-z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-z])@))" +
@"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-z][-\w]*[0-9a-z]*\.)+[a-z0-9]{2,17}))$",
RegexOptions.IgnoreCase);
// Realiza um teste na validação da expressão
//return new SqlBoolean(expressaoRegular.IsMatch(email.ToString()));
Edimar, agradeço sua contribuição.
Thiago, boa tarde.
Saberia me dizer se existe a possibilidade de criar funções em C, criar uma DLL e utilizar no SQL Server?
Agradeço a atenção, irei pesquisando por aqui para ver se acho algo e caso ache eu posto aqui para futuros visitantes da sua página ;)
Reynaldo, em C não posso te confirmar mas em C++ você consegue gerar uma DLL com recursos externos que assim o SQL Server pode consumir.
Postar um comentário