Criando objetos SQL com .NET (CLR - Managed Code)

Nesta nova versão do SQL Server e do Visual Studio .NET (SQL Server 2005 e Visual Studio 2005, respectivamente), a Microsoft integrou o CLR (Common Language Runtime) do .NET com o SQL Server 2005, podendo assim desenvolver Stored Procedures, Triggers, User-Defined Functions, User-Defined Types utilizando uma linguagem .NET.

por Israel Aéce

A Figura 3 ilustra o processo. Depois de adicionado, podemos ver que a IDE criou uma Partial Class, chamada StoredProcedures. Na medida que você for criando objetos deste tipo, outras Partial Class são também criadas e, quando compilado o projeto, estas, por sua vez são mescladas (merge) em uma única classe chamada StoredProcedures. Ao adicionar um novo objeto do tipo Stored Procedure, terá o código semelhante ao abaixo:

Figura 4 - Class View do Visual Studio .NET 2005.

• SqlContext:Através da classeSqlContextvocê consegue interagir com o Banco de Dados daquele contexto/conexão do ambiente em que está executando-o, resgatando as informações e objetos necessários para a execução de um determinado comando no Banco de Dados como, por exemplo, GetConnection(), GetCommand(), GetTransaction() , etc..

• SqlPipe: Este por sua vez envia mensagens, dados tabulares ou até mesmoerros que possam vir a ocorrer durante a execuçãopara o cliente. O conceito desteobjeto é bastante similar ao objeto HttpResponse (Response) do ASP.NET, que envia informações para quem o está requisitando (Browser).

O método GetPipe() resgata o canal de comunicação entre o objeto e a aplicação cliente que o chama, ou seja, é a comunicação entre o cliente e o servidor. Através do método Send deste objeto você envia as informações para o cliente. Este mesmo método tem vários overloads (sobrecargas) que recebe diversos tipos de objetos para retornar ao cliente. Jáa classeSqlContext representa o contexto corrente e, com isso, elimina a necessidade deabrir outra conexão com a Base de Dados. Com exceção dessas novidades, o resto do código é bastante parecido com o que temos hoje ao desenvolvermos através de código ADO.NET.
Adicionando o Assembly no SQL Server 2005

Depois de criado a(s) Stored Procedure(s), ou qualquer outro objeto,é necessário catalogar o Assembly gerado pelo Visual Studio .NET dentro do SQL Server 2005. Este processo consiste em dois passos: no primeiro deles você deve catalogar o Assembly, ou seja, "inserí-lo" dentro do SQL Server.No segundo passo você precisa mapear os objetos (Stored Procedures, Triggers, etc) para os métodos que estão dentro do Assembly que, nesta altura, já estará catalogado.Veremos como funciona o processo de catálogo de Assemblies através da DDL (Data Definition Language):

No SQL Server 2005 temos agora uma nova instrução dentro da linguagem DDL que chamamos de "CREATE ASSEMBLY". Esta instrução é utilizada para adicionarmos o Assembly dentro da Base de Dados. Como podemos ver, não existem muitos segredos: informamos um nome qualquer que identificará o Assembly e, na cláusula FROM, informarmos o path completo até o arquivo DLL gerado pelo Visual Studio .NET. Já a cláusula PERMISSION_SETpermite especificar o nível de segurança em que seu código será executado/acessado. Existem três opções para esta cláusula,como veremos abaixo:

Depois de catalogado o Assembly dentro do nossoBanco de Dados, o quetemos que realizar agora éa definição da Stored Procedure, mapeando o método RetornaVeiculos() que está dentro do Assembly.Para isso, utilizamos o código semelhante ao que seutiliza atualmente, ou seja, utilizando a linguagem DDL. Exemplo:

Uma observação importante é que, se a Stored Procedure tiver parâmetros, você deverá também específicá-los quando for criá-la dentro do SQL Server. Depois de mapead, podemos executar a Stored Procedure normalmente. Se tudo ocorrer corretamente o resultado será o mesmo que está sendo exibidono código 3 deste artigo.Para executar a Stored Procedure pode-se fazer comojá era feito nas versões anteriores do SQL Server:

Se analisarmos agora o Object Browserque encontra-se dentro doSQL Server Management Studio, veremos lá a Stored Procedure criadae o Assembly também já catalogado. Através da tabela sys.assembly_files você pode recuperar os Assemblies que estão catalogados dentro de um determinado Banco de Dados. A Figura 5 exibe o Object Browser do SQL Server, com a Stored Procedure e o Assembly já registrados. Além dessa forma que foi explicado acima para catalogar o Assembly no SQL Server, pode ser feito automaticamente pelo Visual Studio .NET onde, depois de compilar o projeto, faz o Deployment do mesmo e com isso, os passos que foram efetuados acima ele faz automaticamente, incluindo todos os objetos dentro da Base de Dados em questão. Para realizar isso dentro do Visual Studio .NET 2005 vá até o menu Build e clique na opção "Build <Project>".

Figura 5

Triggers

Como já sabemos, Triggers são disparadas quando uma ação de INSERT, UPDATE ou DELETE é executada em uma determinada tabela e as utilizamos quando necessitamos criar integridade ou mesmo fazer consistências dos dados da Base de Dados. Mas os eventos (ações, como são tratados em .NET) não se restringem somente à estas opções, tendo inclusive ações que detectam, por exemplo, a criação de tabelas dentro da Base de Dados. Para o uso das CLR Triggers,quando você cria uma Trigger no Visual Studio, o nome da Partial Classé Triggers.

No restante é bem semelhante, mesmo a construção das Stored Procedures como já vimos acima. A principal diferença está na recuperação do Contexto onde o comando corrente é executado, ou seja, dentro da Trigger você recupera o contexto através do método GetTriggerContext do objeto SqlContext. Este método fornece as mesmas informações do anterior,incluindo o acesso às tabelas virtuais que são criadas durante a execução da Trigger, tabelas quais armazenam os dados que causaram o disparo da mesma.

Outra diferença também é que o atributo para o método também muda. Agora temos que utilizar o seguinte atributo: <SqlTrigger(...)>, o qual veremos detalhadamente mais abaixo. No cenário dos nossos testes criaremos uma Trigger vinculada à tabela Veiculo onde, a cada Inserção, Atualização ou Deleção um Log deve ser gerado na tabela Log. Abaixo o código da mesma para analisarmos:

Analisando o código acima vemos que o construtor doatributo SqlTrigger têm alguns parâmetros:

• Event: É o evento em que a Trigger vai ser disparada caso o mesmo aconteça. Se quisermos que a Trigger seja disparada toda vez em que uma inserção de um novo registro seja efetuada na Tabela, definimos como "FOR INSERT". O mesmo acontece para atualização e deleção de registros.É importante lembrarque a cláusula "FOR" não é necessária para cada uma dessas ocasiões.

• Name: O nome em si da Trigger.
  

• Target:A tabelaque será o "alvo", ou melhor, a tabela em que a Trigger será anexada. A tabela em queé detectada algumaação e esta estiver sendo capturada.

Vale lembrar que é perfeitamente possível acessar as tabelas virtuais "INSERTED" e "DELETED", que são criadas pela Triggers quando a mesma é executada. Recuperando o contexto da Trigger através do método GetTriggerContext e através da ação da Trigger (verificado com o enumerador System.Data.Sql.TriggerAction) executamos um código específico para aquele processo. No caso do exemplo que é exibido no Código 8 inserimos uma nova linha na tabela Log informando a ação que foi executada.

O processo de criação dentro do SQL Server funciona da mesma forma que é feito para a Stored Procedure, ou seja, utilizandoa linguagemDDL (Data Definition Language). O que muda é apenas o nome, agora sendo TRIGGER e tendo que informar em qual evento o mesmo será disparado. O exemplo abaixo exemplifica como a criação é realizada: