SQLCLR, Triggers e Web Services no SQL Server 2005

Neste tutorial estaremos falando sobre uma tecnologia lançada com o advento do SQL Server 2005 chamada SQL CLR, e sua integração com Web Services.

por Raphaël Désalbres

Nos aplicativos de hoje em dia é cada vez mais importante a integração dos dados entre diferentes aplicativos, vindos de servidores e/ou plataformas diferentes.

Com o advento dos Web Services nos é oferecido um mundo repleto de possibilidades. O desenvolvedor precisa mais que nunca estar a par das novas tecnologias oferecidas pelas ferramentas recentemente lançadas. Neste tutorial estaremos falando sobre uma tecnologia lançada com o advento do SQL Server 2005 chamada SQL CLR, e sua integração com Web Services.

Utilizaremos para tanto o banco de dados Northwind da Microsoft. Se você não tiver ele instalado ainda (já que o exemplo não vem com o SQL Server 2005), vá para:
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en. Estes scripts são para o SQL Server 2000, mas funcionam também para o 2005.

Para quem não sabe o SQL CLR é a integração entre o SQL Server 2005 e a CLR do Visual Studio, ou seja, podemos criar triggers, functions e procedures usando nossa linguagem preferida (aqui no caso C#).

O exemplo que mostrarei a seguir não é baseado nas melhores práticas e desenhos recomendados pela Microsoft, serve apenas para ilustrar uma das possibilidades do SQL Server. Na verdade nem é bom acessar um Web Service através de uma trigger, pela performance.

Para executar este exemplo precisaremos dos seguintes softwares:

- Visual Studio 2005 Professional
- SQL Server 2005 Developer

Vamos imaginar o seguinte cenário: Temos nosso Banco de Dados SQL Server da companhia Northwind Traders, e a cada vez que alteramos o preço de um produto em dólar, queremos atualizar uma outra coluna (que criaremos) chamada "PriceInReal" com o valor do câmbio da internet atual. Surge a pergunta: porque esta conversão não é feita pelos aplicativos clientes que acessam a aplicação? É que supomos que haja a modificação de tabelas por vários aplicativos, por exemplo um aplicativo Windows Forms e uma aplicação Web. Desta forma teríamos que alterar o preço em Reais dentro do aplicativo, o que não é prático. Bom, e de toda maneira como já disse acima, trata-se apenas de um exemplo! Portanto nosso trigger pegará o câmbio da internet, atualizando instantaneamente todos os preços em Reais.

Para realizar a conversão da moeda de dólar para Reais usaremos um web service gratuito, que já usei há algum tempo atrás, e que por sinal é muito bom e fácil de usar. O site é www.webservicex.net.

E agora? Mão na massa...

Primeiramente precisaremos criar uma coluna chamada de "PriceInReal" no banco de dados Northwind. Isso deveria ser sem mistérios, basta usar o SQL Server Management Studio, abrir nosso banco de dados e acrescentar uma coluna na tabela "Products".

Criaremos então um aplicativo SQL Server Project:

Chamaremos nosso projeto de SQLCLRTest.

A seguir será pedido a você que escolha um Banco de Dados. Escolha o Banco de Dados Northwind.
Então você deve adicionar uma referência para o site de conversão de moedas.

Digite a seguir http://www.webservicex.net/CurrencyConvertor.asmx.

Clique em Go e a seguir "Add Reference"

Pronto! Já temos o nosso Web Service!

A seguir: Clique direito na solução, Add > Trigger

Chamaremos nosso trigger de "UpdateRates.cs"

E este será o código da nossa classe:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using SQLCLRTest.net.webservicex.www;

public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger (Name="UpdateRates", 
	    Target="Products", Event="FOR UPDATE")]
    public static void UpdateRates()
    {
        SqlTriggerContext ctx = SqlContext.TriggerContext;
        if (ctx.IsUpdatedColumn(5))
        {
            CurrencyConvertor cc = new CurrencyConvertor();
            Double rate = cc.ConversionRate(Currency.USD, Currency.BLR);
            SqlConnection cnn = new SqlConnection("context connection=true");
            cnn.Open();
            SqlCommand cmd = 
              new SqlCommand("UPDATE Products SET PriceInReal=" + rate + "*UnitPrice", cnn);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}

Agora explicarei passo a passo o código acima. Primeiramente necessitamos do atributo [SqlTrigger], que irá dizer para o Visual Studio considerar esta classe como um trigger.

A seguir, precisamos criar uma variável ctx para determinar que a coluna que é modificada é a coluna "5" (Unit Price). Chamaremos o Web Service de conversão de moeda, da moeda USD para BLR.

E a seguir daremos um update. Não tem mistério!

O mais complicado é fazer este código funcionar, já que o SQL Server 2005 tem um mecanismo de segurança para chamar os Web Services. Necessitaremos para tal definir nossos assemblies como "unsafe" (não seguro).

Então vamos ver os problemas que surgem, para isto não lhe darei tudo de "mão-beijada", vamos analisar os erros.

Bom, já que o código acima compila, daremos um "Deploy" na solução, ou seja, a nossa DLL estará no Banco de Dados.

Clique direito na solução, "Deploy", como somos preguiçosos, não precisamos registrar a DLL "na mão"!

Se formos suficientemente curiosos, veremos que nosso assembly está no SQL Server.

Agora se modificarmos algum preço na coluna UnitPrice obteremos nossa conversão, certo?
Calma lá, Jacaré, um erro será gerado:

Isso porque o SQL Server necessita de um outro assembly para acessar o web services, um tal de "Serialization Assembly". Como fazer isso? Simples, basta ir nas propriedades do projeto, e setar o recurso "generate serialization assembly" como "on". Mas além disso também é necessário setar o botão "Allow unsafe code".

Importante: vá também para a aba "Database" e altere a opção "Permission Level" como "Unsafe". Note que você precisa fazer todas estas alterações porque você está acessando um Web Service externo, e o SQL Server considera isso como uma possível ameaça...

Tem mais algo a fazer? Sim, você precisa alterar uma opção do SQL Server, chamada Trustworthy. Como? Simplismente abra uma nova query, e digite:

ALTER DATABASE Northwind SET TRUSTWORTHY ON

Aperte F5.

Acabou? Ainda não, pois precisamos registar no banco a nossa DLL "Serialization Assembly"...Primeiramente damos um build na solução. Note que você talvez precisará modificar o path da DLL.

CREATE ASSEMBLY xmlSerializer FROM "D:\Visual Studio 
2005\Projects\SQLCLRTest\SQLCLRTest\bin\Debug\SQLCLRTest.XmlSerializers.dll"
WITH PERMISSION_SET=EXTERNAL_ACCESS

Ufa? Graças a Deus, terminamos!!! Se você seguiu os passos acima, tudo deveria funcionar.
Agora a fase de testes, a mais interessante.
Abra a tabela de produtos, e altere qualquer produto, e a seguir, pressione o botão Execute Query, para refrescar a lista de produtos atualizados.

Sei que estas configurações são chatas até se achar o ponto certo. E olha que eu demorei bastante até descobri-las!

Lhe desejo sucesso e até a próxima!