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 limitadaalém de
procedural;muitas vezes precisamosfazer algo dentro da Base de Dados
onde ficamos impossibilitados, tendo que trazer os dados para aaplicaçã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 2005e
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 .NETou
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
oT-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 Eache 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 termosboa 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 2005e versão Beta 1 do Visual Studio .NET 2005. Vale lembrar que,
pelo fato destes softwaresestarem aindaem suas versõesbetas,
é 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.
Figura 1
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. Para isso, veja a Figura 2.
Figura 2
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 destesobjetos:
| 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íficospara a sua aplicação, baseando-se
obrigatóriamente nestes tiposde 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).
Figura 3
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:
|
|
|
| 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 SQLServerquando 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 Figura4 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 emtrês tabelas, ondena primeirasão armazenados os
Fabricantes de Veículos. Já a segunda se encarrega de armazenar os Veículos dos
respectivos Fabricantese, 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 |
| ---------------------------------------------------------------- |
| 1Audi |
| 2Fiat |
| 3Ford |
| 4Volkswagen |
| 5Chevrolet |
|
| (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 |
|
| FabricanteVeiculo |
| --------------------------------------------------------------------- |
| AudiA3 |
| FiatPalio |
| FordFiesta |
| VolkswagenGolf |
| ChevroletCorsa |
|
| (5 row(s) affected) |
| |
|
Código3 - Dados
pré-configurados na TabelaVeiculo 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
queryAs 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ódigo4 - 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
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):
|
|
|
| USE BetaTester |
| CREATE ASSEMBLY
ObjetosSQL |
| FROM
"C:\SQLCLR.NET\ObjetosSQL\bin\ObjetosSQL.dll" |
| WITH PERMISSION_SET =
SAFE |
| |
|
Código5 - 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_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:
| 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,
somentehabilitando o acessoaos 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 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:
|
|
|
| USE BetaTester |
| CREATEPROCEDURERetornaVeiculos |
| AS EXTERNAL
NAME |
| ObjetosSQL.[ObjetosSQL.StoredProcedures].RetornaVeiculos |
| |
|
Código6 -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 exibidono código 3
deste artigo.Para executar a Stored Procedure pode-se fazer comojá
era feito nas versões anteriores do SQL Server:
|
|
|
| USE BetaTester |
| EXECUTERetornaVeiculos |
| |
|
Código7 -Executando
a Stored Procedure.
|
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:
| 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() |
| Dimcontext
AsSqlTriggerContext =
SqlContext.GetTriggerContext() |
| Dimcmd
AsSqlCommand = 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ódigo8 -Trigger GeraLog().
|
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:
|
|
|
| USE BetaTester |
| CREATETRIGGERGeraLog |
| ON Veiculo |
| FOR INSERT,
UPDATE, DELETE |
| AS EXTERNAL
NAME |
| ObjetosSQL.[ObjetosSQL.StoredProcedures].GeraLog |
| |
|
Código9 -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étodoagora é 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
SharedFunction RetornaQtdeVeiculos()
As Integer |
| Dimcmd
AsSqlCommand = SqlContext.GetCommand() |
| cmd.CommandText
= "SELECT COUNT(*) FROM Veiculo" |
| Return
Convert.ToInt32(cmd.ExecuteScalar()) |
| EndFunction
|
| End Class |
| |
|
Código10 -UDF -
User-Defined FunctionRetornaQtdeVeiculos().
|
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
valorao 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 complexaou 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, inclusiveconceitos 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 deusufruirmos 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,
poisnão precisamos mais de várias conexões com a base de dadospara
múltiplos comandos, podendo compartilhar a mesma conexão entre os comandos,
fornecendo um grande ganho de performance e escalabilidade. E, comovimos
no decorrer do artigo, o código é muito semelhante aoque já
éutilizado atualmente em aplicações .NET que fazem acessoaos dados
utilizando o ADO.NET. Agora temosmais de umaopção
quandoquisermos escrever códigos de acesso e manipulação de dados, ou
seja, podemos escolher .NET (managed code) ou ainda continuar utilizandoo
T-SQL.
Artigo desenvolvido utilizando:
* Visual Studio .NET 2005 - Beta 1
* SQL Server 2005 - Beta 2
* .NET Framework 2.0
* Windows XP Professional
Clique aqui para fazer Download do Código.
