Como sabemos, utilizamos o T-SQL (Transact Structure Query Language) para acessar e manipular dados em um Banco de Dados SQL Server. Como o T-SQL é uma linguagem de Banco, ela é bastante limitada além de procedural; muitas vezes precisamos fazer algo dentro da Base de Dados onde ficamos impossibilitados, tendo que trazer os dados para a aplicação e assim fazer as devidas manipulações e/ou consistências ali.
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 como, por exemplo, Visual Basic .NET ou Visual C# .NET, ou seja, em código gerenciado (managed code).
A integração com o Common Language Runtime (CLR) traz uma série de benefícios, pois agora os desenvolvedores não estarão mais limitados a utilizar o T-SQL quando quiserem fazer interações com o SQL Server, onde podem escrever códigos em linguagens .NET e ter toda a riqueza que estas nos oferecem, como por exemplo: tratamento de erros estruturados, arrays, coleções fortemente tipadas, laços For...Next e For Each e até mesmo utilizarmos uma Regular Expression para validar um determinado campo. Podemos também usufruir do CLR, indicando nos em compile-time erros de sintaxe até mesmo buffer overflows que possam vir a acontecer. O mais interessante ainda é que, para termos boa performance, o runtime do .NET é lazy loading para o usuário do SQL Server, ou seja, somente carregará quando for realmente necessário, portanto, quando invocar pela primeira vez uma Stored Procedure ou qualquer outro objeto que lá dentro se encontra.
Vamos ver no decorrer deste artigo como fazer para criar esses tipos de objetos utilizando uma linguagem .NET. O artigo se baseia na versão Beta 2 do SQL Server 2005 e versão Beta 1 do Visual Studio .NET 2005. Vale lembrar que, pelo fato destes softwares estarem ainda em suas versões betas, é possível que até a sua versão final alguma característica possa vir a mudar.
No Visual Studio .NET foi criado uma série de novos Templates para projetos. Um deles é o SQL Server Project , que é justamente para esta finalidade: criar objetos para o SQL Server. Para isso, ao iniciar o Visual Studio .NET 2005, basta criar um novo projeto e, ao selecionar a linguagem desejada, terá os templates para projetos para Base de Dados (Database). A Figura 1 ilustra este processo.
Quando o projeto é criado, é apresentada uma caixa de diálogo para informar o servidor de Banco de Dados pelo qual queremos criar os objetos. Neste momento temos que informar os dados para acesso, como: Nome do Servidor, Login e Password e a Base de Dados em si.
Criado o projeto e configurado a conexão com a Base de Dados, podemos já iniciar a construção dos objetos. Mas antes disso, vamos entender um pouco mais sobre cada um destes objetos:
| Objeto |
Descrição |
| Stored Procedures |
Uma Stored Procedure (ou Procedimento Armazenado) é uma coleção de instruções T-SQL que é armazenada no servidor de Banco de Dados. A Stored Procedure nada mais é que um método qual encapsula a lógica de uma tarefa repetitiva, fornecendo suporte à declaração de variáveis, condicionais, laços e outros recursos de programação. |
| Triggers |
Uma Trigger é um Procedimento Armazenado que é executado quando os dados de uma tabela específica são modificados. É bastante utilizado para impor integridade referencial ou consistência entre dados relacionados (claro, em tabelas diferentes). |
| User-Defined Functions |
Com o SQL Server você pode criar suas próprias funções para estender as funcionalidades do mesmo. Ele pode conter um ou mais parâmetros de entrada, podendo retornar um valor escalar ou uma tabela. |
| User-Defined Types |
O SQL Server fornece vários tipos de dados de sistema, mas não se limita a eles. Você pode criar tipos de dados específicos para a sua aplicação, baseando-se obrigatóriamente nestes tipos de dados do sistema. |
|
Criando objetos no .NET - Stored Procedures
Depois de entendermos o que é cada um desses objetos veremos como criá-los no Visual Studio .NET 2005. Para criar um novo objeto do tipo Stored Procedure dentro do projeto SQL Server devemos clicar com o botão direito em cima do Projeto no Solution Explorer >> Add >> Strored Procedure. Um arquivo é criado com a extensão *.vb (ou *.cs se o projeto tiver como linguagem o Visual C# .NET). 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:
|
|
|
| Imports System |
| Imports System.Data |
| Imports System.Data.Sql |
| Imports System.Data.SqlServer |
| Imports System.Data.SqlTypes |
| |
| Partial Public Class StoredProcedures |
| <SqlProcedure()> Public Shared Sub StoredProcedure1() |
| ' Add your code here |
| End Sub |
| End Class |
|
|
|
Código 1 - Código gerado pela IDE quando solicitamos uma nova Stored Procedure.
|
O Atributo <SqlProcedure()> determina que o procedimento é uma Stored Procedure. O Visual Studio .NET usa esta determinação para criar a Stored Procedure dentro do SQL Server quando o Deployment é feito. Como já vimos acima, uma Partial Class chamada
StoredProcedures é criada e dentro dela colocamos uma ou mais Stored Procedures. O interessante é colocar uma Stored Procedure por arquivo para facilitar a manutenção, mas isso não é uma regra obrigatória.
Vale ressaltar também que, independentemente se criarmos as Stored Procedures em um mesmo arquivo ou em arquivos separados, ao compilar, as Partial Class são mescladas e, com isso, as Stored Procedures ficam todas dentro de uma mesma classe, como já mencionamos acima. Para certificarmos que isso realmente acontece, podemos visualizar através do Class View do Visual Studio .NET, como vemos na Figura 4 abaixo:
|
|
| Figura 4 - Class View do Visual Studio .NET 2005. |
Depois de entendido a estrutura de como isso funciona, vamos então ao código que mostrará realmente a construção da Stored Procedure. O nosso cenário consiste em três tabelas, onde na primeira são armazenados os Fabricantes de Veículos. Já a segunda se encarrega de armazenar os Veículos dos respectivos Fabricantes e, por fim, temos uma tabela chamada Log que guardará os Logs de inserção, deleção e atualização efetuados na tabela Veiculo. A nossa tabela já está pré-configurada com os fabricantes. Um "select" na mesma nos retornará aos seguintes dados:
|
|
|
| SELECT * FROM Fabricante |
| |
| FabricanteID Nome |
| -------------- -------------------------------------------------- |
| 1 Audi |
| 2 Fiat |
| 3 Ford |
| 4 Volkswagen |
| 5 Chevrolet |
| |
| (5 row(s) affected) |
|
|
|
Código 2 - Dados pré-configurados na Tabela Fabricante para os exemplos do artigo.
|
Para complementar temos ainda a tabela de Veiculos, qual contém os veículos relacionados com os seus respectivos fabricantes, qual também já temos uma pré-carga com alguns dados:
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
|
|
| SELECT |
| Fabricante.Nome As Fabricante |
| , Veiculo.Nome As Veiculo |
| FROM Veiculo |
| INNER JOIN Fabricante ON |
| Fabricante.FabricanteID = Veiculo.FabricanteID |
| |
| Fabricante Veiculo |
| ------------------- -------------------------------------------------- |
| Audi A3 |
| Fiat Palio |
| Ford Fiesta |
| Volkswagen Golf |
| Chevrolet Corsa |
| |
| (5 row(s) affected) |
|
|
|
Código 3 - Dados pré-configurados na Tabela Veiculo para os exemplos do artigo.
|
Portanto, agora vamos transpor a última query, que retorna os Veículos e seus respectivos Fabricantes para uma Stored Procedure em .NET. A mesma vai chamar-se
RetornaVeiculos. O código abaixo exemplifica isso:
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
|
|
| Imports System |
| Imports System.Data |
| Imports System.Data.Sql |
| Imports System.Data.SqlServer |
| Imports System.Data.SqlTypes |
| |
| Partial Public Class StoredProcedures |
| <SqlProcedure()> Public Shared Sub RetornaVeiculos() |
| Dim cmd As SqlCommand = SqlContext.GetCommand() |
| Dim pipe As SqlPipe = SqlContext.GetPipe() |
| |
| Dim query As String = "SELECT " |
| query &= "Fabricante.Nome, Veiculo.Nome " |
| query &= "FROM Veiculo " |
| query &= "INNER JOIN Fabricante ON " |
| query &= "Fabricante.FabricanteID = Veiculo.FabricanteID" |
| |
| cmd.CommandText = query |
| pipe.Send(cmd.ExecuteReader()) |
| End Sub |
| End Class |
|
|
|
Código 4 - Stored Procedure RetornaVeiculos().
|
Analisando o código acima, temos algums novos objetos. No caso do exemplo do código 4 nos é apresentado o SqlContext e o SqlPipe. Veremos a funcionalidade de cada um desses objetos logo abaixo:
-
SqlContext: Através da classe SqlContext você 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é mesmo erros que possam vir a ocorrer durante a execução para o cliente. O conceito deste objeto é 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 classe SqlContext representa o contexto corrente e, com isso, elimina a necessidade de abrir 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):
|
|
|
| USE BetaTester |
| CREATE ASSEMBLY ObjetosSQL |
| FROM 'C:\SQLCLR.NET\ObjetosSQL\bin\ObjetosSQL.dll' |
| WITH PERMISSION_SET = SAFE |
|
|
|
Código 5 - Catalogando o Assembly dentro do SQL Server 2005.
|
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_SET permite 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:
| Tipo de Permissão |
Descrição |
| SAFE |
É o default. Neste modo o Assembly somente poderá rodar no contexto local, mas não através do SqlClient. Previne também o acesso através de recursos externos e de código não gerenciado. |
| EXTERNAL_ACCESS |
É o mesmo que SAFE, somente habilitando o acesso aos recursos externos. |
| UNSAFE |
Acesso irrestrito, desde que o Assembly seja assinado e catalogado por um usuário que seja membro do grupo sql_admins. |
|
Depois de catalogado o Assembly dentro do nosso Banco de Dados, o que temos 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 se utiliza atualmente, ou seja, utilizando a linguagem DDL. Exemplo:
|
|
|
| USE BetaTester |
| CREATE PROCEDURE RetornaVeiculos |
| AS EXTERNAL NAME |
| ObjetosSQL.[ObjetosSQL.StoredProcedures].RetornaVeiculos |
|
|
|
Código 6 - Mapeando os métodos/objetos dentro do SQL Server 2005.
|
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 exibido no código 3 deste artigo. Para executar a Stored Procedure pode-se fazer como já era feito nas versões anteriores do SQL Server:
|
|
|
| USE BetaTester |
| EXECUTE RetornaVeiculos |
|
|
|
Código 7 - Executando a Stored Procedure.
|
Se analisarmos agora o Object Browser que encontra-se dentro do SQL Server Management Studio, veremos lá a Stored Procedure criada e 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. 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>".
TriggersComo 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:
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
|
|
| Imports System |
| Imports System.Data |
| Imports System.Data.Sql |
| Imports System.Data.SqlServer |
| Imports System.Data.SqlTypes |
| |
| Partial Public Class Triggers |
| <SqlTrigger(Event:="FOR INSERT, UPDATE, DELETE", Name:="GeraLog", Target:="Veiculo")> _ |
| Public Shared Sub GeraLog() |
| Dim context As SqlTriggerContext = SqlContext.GetTriggerContext() |
| Dim cmd As SqlCommand = SqlContext.GetCommand() |
| |
| Select Case context.TriggerAction |
| Case TriggerAction.Insert |
| cmd.CommandText = "INSERT INTO [Log] (Descricao) VALUES ('INSERÇÃO.')" |
| Case TriggerAction.Delete |
| cmd.CommandText = "INSERT INTO [Log] (Descricao) VALUES ('DELEÇÃO.')" |
| Case TriggerAction.Update |
| cmd.CommandText = "INSERT INTO [Log] (Descricao) VALUES ('ATUALIZAÇÃO.')" |
| End Select |
| cmd.ExecuteNonQuery() |
| End Sub |
| End Class |
|
|
|
Código 8 - Trigger GeraLog().
|
Analisando o código acima vemos que o construtor do atributo 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 lembrar que a cláusula "FOR" não é necessária para cada uma dessas ocasiões.
-
Name: O nome em si da Trigger.
-
Target: A tabela que será o "alvo", ou melhor, a tabela em que a Trigger será anexada. A tabela em que é detectada alguma açã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, utilizando a linguagem DDL (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:
|
|
|
| USE BetaTester |
| CREATE TRIGGER GeraLog |
| ON Veiculo |
| FOR INSERT, UPDATE, DELETE |
| AS EXTERNAL NAME |
| ObjetosSQL.[ObjetosSQL.StoredProcedures].GeraLog |
|
|
|
Código 9 - Criando a Trigger dentro do SQL Server 2005.
|
User-Defined Functions (UDFs)
A construção e deploymet de User-Defined Functions é da mesma forma que as explicadas anteriormente. Como é necessário, o atributo para o método agora é o <SqlFunction(...)>. Vamos ver abaixo o código para termos um exemplo de uma UDF criada pela linguagem .NET:
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
|
|
| Imports System |
| Imports System.Data |
| Imports System.Data.Sql |
| Imports System.Data.SqlServer |
| Imports System.Data.SqlTypes |
| |
| Partial Public Class UserDefinedFunctions |
| <SqlFunction(DataAccess:=DataAccessKind.Read)> _ |
| Public Shared Function RetornaQtdeVeiculos() As Integer |
| Dim cmd As SqlCommand = SqlContext.GetCommand() |
| cmd.CommandText = "SELECT COUNT(*) FROM Veiculo" |
| Return Convert.ToInt32(cmd.ExecuteScalar()) |
| End Function |
| End Class |
|
|
|
Código 10 - UDF - User-Defined Function RetornaQtdeVeiculos().
|
Como vemos, agora o procedimento é do tipo Function, justamente porque um valor é retornado. Utilizando a função COUNT(*) para retornar a quantidade de registros e através do método ExecuteScalar(), recuperamos este valor da Base de Dados e utilizando o Return, retornamos o valor ao cliente.
T-SQL vs. Managed Code
Uma das grandes dúvidas, com este novo recurso que será disponibilizado nas próximas versões do Visual Studio .NET e SQL Server 2005, é saber quando utilizar código gerenciado e quando utilizar o T-SQL. Com esta questão temos que analisar alguns fatores para a escolha. O T-SQL é interessante utilizar onde o código executará em sua maior parte diretamente acessando os dados, sem nenhuma lógica complexa ou procedural; já a utilização do código gerenciado facilita quando você necessita tirar proveito ao máximo das capacidades de linguagens de programação como, por exemplo, Visual Basic .NET ou Visual C# .NET, inclusive conceitos de orientação à objetos, integrando assim com recursos que dificilmente conseguimos em uma linguagem de Banco de Dados, que é muito limitada em relação à estas linguagens genuínas. Temos, além disso, a vantagem de usufruirmos da biblioteca do .NET Framework para trabalharmos.
Conclusão: Neste artigo foram apresentadas algumas das novas features do novo SQL Server. Para que não ficasse muito extenso, inúmeros outros recursos não foram abordados neste artigo como, por exemplo, suporte à chamadas assíncronas, métodos para paginação de dados que retornam SqlDataReaders. Inclusive uma nova característica muito interessante, pois não precisamos mais de várias conexões com a base de dados para múltiplos comandos, podendo compartilhar a mesma conexão entre os comandos, fornecendo um grande ganho de performance e escalabilidade. E, como vimos no decorrer do artigo, o código é muito semelhante ao que já é utilizado atualmente em aplicações .NET que fazem acesso aos dados utilizando o ADO.NET. Agora temos mais de uma opção quando quisermos escrever códigos de acesso e manipulação de dados, ou seja, podemos escolher .NET (managed code) ou ainda continuar utilizando o T-SQL.
SQLCLR.NET.zip (208.69 kb)
Subscribe