Búfalo Informática - Utilizando Classes .NET no SQL Server 2005

É hora de se aprimorar!
Veja o Calendário de Treinamentos da Búfalo!

Pesquisa personalizada

Utilizando classes .NET no SQL Server 2005

Uma das grandes novidades do SQL Server 2005 é a possibilidade de utilizarmos código .NET dentro do SQL Server na forma de stored procedures, functions, triggers ou outros recursos semelhantes.

Isso não substitui de forma alguma o TSQL, apenas o complementa. Existem muitas tarefas que não podem ser realizadas em TSQL, exigindo código externo para realiza-las. Nas versões anteriores do SQL Server tinhamos um recurso conhecido como Extended Stored Procedures, que nos permitia criar DLLs e utiliza-las dentro do SQL Server.

O problema é que a criação de extended stored procedures sempre foi muito complexa, podia apenas ser realizada em C++. Agora, com o SQL Server 2005 podemos criar código no .NET e utiliza-lo dentro da base de dados de forma bem simples. Podemos por exemplo aproveitar os recursos de criptografia do .NET dentro do SQL Server.

Vamos ver um exemplo criando uma função para realizar a criptografia de uma senha. Veja abaixo como fica o codigo em VB.NET :

    1 Imports System.Data.SqlTypes
    2 Imports System.Security.Cryptography
    3 Imports System.Text.UnicodeEncoding
    4 
    5 Public Class dbSenha
    6     Public Shared Function EncryptPassword(ByVal senha As SqlString) As SqlBinary
    7         Dim pass As Byte()
    8         Dim passHash As Byte()
    9         Dim sha As SHA1
   10 
   11         pass = [Unicode].GetBytes(CType(senha, String))
   12 
   13         sha = New SHA1CryptoServiceProvider
   14         passHash = sha.ComputeHash(pass)
   15         Return (CType(passHash, SqlBinary))
   16     End Function
   17 
   18 End Class

Existem muitos pequenos detalhes importantes de serem observados :

A entrada e saida da funcao são definidas como SQLTypes, precisam ser compatíveis com o SQL Server.
A função está definida como Shared, para que possa ser acessada diretamente na classe sem a criação de instâncias, do contrario não funcionaria.
Foi usado o unicode para transformar a string em array de bytes. Isso porque não existe mapeamento do tipo varchar para o .NET, apenas o nvarchar é mapeado como sqlString, obrigando a entrada e saida de strings a ser em unicode.

O proximo passo é registrarmos o Assembly dentro do SQL Server. Observe que a própria estrutura do Assembly fornece uma forma de organizarmos o código : Um Assembly pode ter diversas classes, uma classe diversas funções e subs.

O registro do assembly pode ser feito diretamente pelo management studio, de forma visual. Durante o registro o Assembly é interligado com PermissionSets que definirão o que ele podera realizar. Existem 3 permissionSets default : Safe, External Access e unrestricted

Podemos tambem fazer o registro do assembly por código TSQL, veja como fica :

CREATE ASSEMBLY [clSenha]
AUTHORIZATION [dbo]
FROM 'path'
WITH PERMISSION_SET = UNSAFE

Depois do registro do Assembly este é copiado para dentro da base de dados, não é utilizado a partir do disco.

Com o assembly registrado podemos então começar a registrar as funções existentes neste assembly. Isso é semelhante a criação dos objetos do SQL Server, de fato as funções e subs dentro do assembly serão reconhecidas como objetos dentro do SQL Server, tal como stored procedures, functions, triggers, etc. A única diferença é que ao inves de codificarmos em T-SQL apontamos a origem externa. Veja como fica :

CREATE FUNCTION [dbo].[CriptSenha](@senha [nvarchar](30))
RETURNS [varbinary](30) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [clSenha].[clSenha.dbSenha].[EncryptPassword]

Observe neste código a forma de representar um nameSpace ao indicar o nome da classe.

Ao contrário do que ocorre com os Assemblies, não existe no management studio uma opção para fazer uma criação visual desta function, com o mapeamento externo. Assim sendo isso tem realmente que ser feito por T-SQL. Como ainda estamos na Beta 2, ainda é possivel que isso seja corrigido até a versao final.

A partir dai podemos fazer uso desta função dentro do T-SQL. Veja um exemplo bem simples, apenas para testar a funcao :

select dbo.criptsenha('senhateste')

Com este exemplo podemos programar stored procedures que gravem o hash da senha nas tabelas, ao inves de gravarem a senha abertamente. Neste caso seria importante tambem termos um metodo para testar se a senha é válida. Veja como fica :

    1 Imports System.Data.SqlTypes
    2 Imports System.Security.Cryptography
    3 Imports System.Text.UnicodeEncoding
    4 
    5 Public Class dbSenha
    6     Public Shared Function EncryptPassword(ByVal senha As SqlString) As SqlBinary
    7         Dim pass As Byte()
    8         Dim passHash As Byte()
    9         Dim sha As SHA1
   10 
   11         pass = Unicode.GetBytes(CType(senha, String))
   12 
   13         sha = New SHA1CryptoServiceProvider
   14         passHash = sha.ComputeHash(pass)
   15         Return (CType(passHash, SqlBinary))
   16     End Function
   17 
   18     Public Shared Function ValidaSenha(ByVal senha As SqlString, ByVal hash As SqlBinary) As SqlInt16
   19         Dim hashSenha As Byte()
   20         hashSenha = EncryptPassword(senha).Value()
   21 
   22         If hash.CompareTo(CType(hashSenha, SqlBinary)) = 0 Then
   23             Return (CType(1, SqlInt16))
   24         Else
   25             Return (CType(0, SqlInt16))
   26         End If
   27     End Function
   28 End Class

Porém estamos com isso alterando um assembly já registrado no SQL Server. Não existe um Alter Assembly, apenas create e drop. Fazendo o drop do assembly teremos também que fazer o drop de todos os objetos dependentes deste assembly e, posteriormente, recria-los, perdendo com isso as definições de permissionamento destes objetos. É possível que na versao final venha a existir um Alter Assembly.

Para não termos esse trabalho todo com T-SQL, vamos então começar a utilizar os recursos do Visual Studio 2005. O Visual Studio 2005 tem um tipo de projeto especifico para o desenvolvimento de objetos para o SQL Server 2005. Utilizando este tipo de projeto, o próprio Visual Studio 2005 faz o deployment para o servidor SQL Server.

Quando iniciamos um novo projeto o Visual Studio nos pede para definirmos a base de dados para a qual iremos realizar o deployment. Já com o projeto criado, temos a opção de adicionarmos os objetos do SQL Server ao projeto. Na verdade são apenas classes, templates que já trarão os imports e a definição de atributos adequados.

Nas propriedades de projeto encontramos uma propriedade para configurarmos a segurança que o Assembly terá no SQL Server.

Esses atributos são outro recurso interessante. Eles não são utilizados pelo SQL Server, pois como vimos, podemos criar os objetos sem eles. Esses atributos são utilizados para o deployment, para que o Visual Studio saiba de que forma deve configurar os objetos no SQL Server.

Veja como fica o código com os atributos :

    1 Imports System
    2 Imports System.Data.Sql
    3 Imports System.Data.SqlTypes
    4 Imports System.Text.UnicodeEncoding
    5 Imports System.Security.Cryptography
    6 
    7 Public Class dbSenha
    8     <SqlFunction(Name:="CryptSenha")> _
    9     Public Shared Function EncryptPassword(ByVal senha As SqlString) As SqlBinary
   10 
   11 
   12         Dim pass As Byte()
   13         Dim passHash As Byte()
   14         Dim sha As SHA1
   15 
   16         pass = Unicode.GetBytes(CType(senha, String))
   17 
   18         sha = New SHA1CryptoServiceProvider
   19         passHash = sha.ComputeHash(pass)
   20         Return (CType(passHash, SqlBinary))
   21     End Function
   22 
   23     <SqlFunction()> _
   24     Public Shared Function ValidaSenha(ByVal senha As SqlString, ByVal hash As SqlBinary) As SqlInt16
   25 
   26 
   27         Dim hashSenha As Byte()
   28         hashSenha = EncryptPassword(senha).Value()
   29 
   30         If hash.CompareTo(CType(hashSenha, SqlBinary)) = 0 Then
   31             Return (CType(1, SqlInt16))
   32         Else
   33             Return (CType(0, SqlInt16))
   34         End If
   35     End Function
   36 End Class

Feito o código, encontraremos no menu de contexto do projeto a opção Deploy, que irá disparar a instalação do Assembly no SQL Server, bem como configuração dos objetos de acordo com os atributos utilizados.

Veja como ficam os objetos após seu registro no SQL Server :

Veja um teste bem bobinho em T-SQL :

select dbo.validasenha('senhateste',dbo.cryptsenha('senhateste'))

Os recursos que o uso de código gerenciado dentro do SQL Server podem gerar são incontáveis e teremos diversos outros artigos sobre este tema.

Dennes Torres
MCAD,MCSD,MCSE,MCDBA

Envie seus comentários sobre este artigo

Veja abaixo os comentários já enviados :

Nome : Thiago Pastorello	E-Mail : tpastorello@yahoo.com.br
Boa Coluna Dennes Realmente as Extended Stored Procedures eram muito dificeis de serem criadas e de serem utilizadas, creio que este recurso vem para ajudar e muito nos projetos de bancos mas em casos restritos. Att,
Nome : Luciano Dória	E-Mail : lucianodoria@gmail.com
Muito bom!!!!!

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Conheça mais sobre o nosso site :

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Quer saber mais?
Faça um curso na Búfalo Informática, Treinamento e Consultoria e
Prepare-se para o Mercado!
Veja o que a Búfalo tem para você.

© Búfalo Informática, Treinamento e Consultoria - Rua Álvaro Alvim, 37 Sala 920 • Cinelândia • Rio de Janeiro • RJ
Tel.: (21)2262-1368 • e-Mail: contato@bufaloinfo.com.br

4	Arquiteturas e Tecnologias
4	.Net
4	SQL Server
4	Visual Basic
4	DHTML / Java Script
4	ASP

4	Formação Web
4	Desenvolvimento
4	Infraestrutura
4	Banco de Dados

4	MCP
4	MCTS/MCPD
4	MCAD
4	MCSD
4	MCSD.NET
4	MCDBA
4	MCSE