Banco de Dados - SQL Server

Utilizando código gerenciado no SQL Server 2005

O SQL Server 2005 sofreu grandes mudanças. Um dos grandes atrativos desta versão é a forte integração com o Common Language Runtime (CLR), que é o ambiente gerenciado da plataforma .NET e inclui uma série de recursos para gerenciamento de recursos de sua aplicação.

por Marcel H. Pestana



O SQL Server 2005 sofreu grandes mudanças. Um dos grandes atrativos desta versão é a forte integração com o Common Language Runtime (CLR), que é o ambiente gerenciado da plataforma .NET e inclui uma série de recursos para gerenciamento de recursos de sua aplicação. No entanto, mesmo com esta forte integração, o Transact-SQL não morreu; continua existindo e teve, inclusive, mudanças visando a melhoria. Por outro lado, a criação de objetos CLR pode ser uma boa opção em algumas situações. Este artigo apresenta as comparações entre o Transact-SQL e o CLR, demonstrando também a forma de implementação de objetos CLR no SQL Server 2005.

T-SQL x CLR

O Transact-SQL é uma linguagem específica do SQL Server usada para estender as funcionalidades do SQL Server e possui uma série de comandos para manipulações de dados, tais como Select, Insert e Update.

Em versões antigas do SQL Server, qualquer linguagem capaz de criar componentes COM/DLLs podia ser utilizada para estender ainda mais as funcionalidades. Podia-se utilizar, para este fim, a stored procedure sp_OACreate, que funciona de maneira similar ao famoso CreateObject do VB/VBA. Entretanto, utilizar esta procedure via T-SQL é uma tarefa que requer um bom conhecimento do desenvolvedor e, pior, afeta a confiabilidade, segurança e, muitas vezes, a performance da aplicação.

O SQL-Server 2005 acaba com este problema. Qualquer linguagem que roda sob a plataforma .NET pode ser utilizar para estender as funcionalidades do SQL-Server dentro de um ambiente gerenciado – via Common Language Runtime (CLR). É possível criar funções em uma linguagem familiar do .NET, por exemplo o C#, e utilizá-la para estender procedure, função, trigger, agregações e tipos definidos pelo usuário no SQL-Server, com o mesmo nível de confiabilidade, segurança e até mesmo, em muitos casos, com uma melhor performance – além de apresentar uma quantidade de recursos muito maior e de forma mais fácil e acessível. A seguir temos um breve comparativo entre o CLR e o Transact-SQL.

Quando o CLR é indicado:

· Quando a stored procedure necessitar utilizar recursos externos que não são possíveis quando são utilizadas stored procedures T-SQL. Estes recursos podem ser, por exemplo, funções para acessar arquivos, Event Log, Event Viewer, WebService, Registro do Windows, dentre outros.

· Quando a stored procedure contém código procedural. Por exemplo, um processador de arquivos XSLT, uma biblioteca para expressões regulares, uma aplicação que faz diversos cálculos a partir dos dados de várias tabelas.

· Quando a aplicação possuir funções. Geralmente as funções contêm diversos cálculos, que funcionam melhor quando criados em CLR.

· Quando a stored procedure for procedural e utilizar cursores. Os cursores, comprovadamente, têm uma performance bastante ruim. Via CLR podem ser substituídos por laços “for”.

Quando o T-SQL é recomendado:

· Quando há um modelo de programação baseado em conjuntos de dados e há manipulação de dados. Neste caso, a vantagem é pelo fato do otimizador ser empregado e, conseqüentemente, ter uma execução mais rápida.

· Quando a stored procedure necessita executar uma única instrução Transact-SQL. Neste caso, não há necessidade da aplicação chamar o método CLR, que chamará a instrução T-SQL e retornará o valor – uma única chamada é necessária; portanto, neste caso, deve-se utilizar o T-SQL.

Criação de funções CLR

O SQL-Server 2005, ao ser instalado, disponibiliza um novo tipo de projeto no Visual Studio 2005, o “SQL-Server Project”, como mostra a Figura 1.

Figura 1 – Criação de novo projeto “Database”

Crie um novo projeto do tipo “SQL-Server Project”. Surgirá uma tela para que seja informado o banco de dados com o qual desejamos fazer nossos testes (Figura 2).

Figura 2 – Nova referência ao Banco de Dados

Após adicionar nova referência ao banco de dados, o Visual Studio criará o projeto.

Ao selecionar a opção para Adicionar um novo item ao projeto, haverão opções como “User-Defined Function”, “Agreggate”, “Stored Procedure”, “Trigger”, “User-Defined Type”, como mostra a Figura 3.

Figura 3 – Criação de novo item

Para criar uma classe contendo funções, selecione “User-Defined Function”.

Automaticamente o Visual Studio gera um código, como mostra o Quadro 1.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString MyFunction()

{

// Put your code here

return new SqlString("Hello");

}

};

Quadro 1 – Função criada automaticamente

Importante: Para que a stored procedure, função definida pelo usuário ou trigger possa sere acessada pelo SQL-Server, o método deve ser público e estático e estar contido em uma classe pública.

Na criação do projeto, um arquivo que auxilia nos testes é criado automaticamente. Este arquivo, nomeado Test.sql, é de grande importância, haja vista que permite com que sejam realizados os testes de funções, procedures e qualquer outro método que tenha sido criado, antes de, efetivamente, fazer a inclusão para uso dentro do SQL-Server.

Faça a inclusão de um trecho de script, conforme o Quadro 2, que chama a função “MyFunction”, descrita anteriormente.

-----------------------------------------------------------------------------------------

-- User defined function

-----------------------------------------------------------------------------------------

select dbo.FunctionName()

Quadro 2 – Chamada à função

Antes de testar a aplicação é necessário efetuar a alteração de uma opção de configuração. Para isto, é necessário acessar o menu Microsoft SQL Server 2005 / Configuration Tools / SQL Server Surface Area Configuration. Quando a janela de configuração surgir, é necessário clicar em Surface Area Configuration for Features.

No item “CLR Integration”, deve-se marcar a opção “Enable CRL Integration”, como mostra a Figura 4.

Figura 4 – Habilitando integração com o CLR

Após definir este item de configuração, pode-se voltar para o projeto do Visual Studio e executar a aplicação para verificar o resultado. Nota-se que na janela “Output” o resultado da função “MyFunction” é exibido – verificar a Figura 5.

Figura 5 – Executando a aplicação e verificando o resultado

Criação de Stored Procedures CLR

Para criar uma nova stored procedure no projeto, na janela Solution Explorer, clique com o botão direito sobre o projeto e, em seguida, Add – Add New Stored Procedure. Automaticamente o Visual Studio cria a classe e um código inicial, como mostra o Quadro 3.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data.Sql;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StoredProcedure1()

{

// Put your code here

}

};

Quadro 3 – Código gerado automaticamente pelo Visual Studio

Quando estamos criando uma stored procedure, certamente nos deparamos com uma situação comum: o acesso a dados de uma determinada tabela ou view.

A primeira opção é criar uma nova conexão utilizando o objeto SqlConnection e especificar a conexão apontando para o servidor local. Contudo, este tipo de conexão requer que sejam especificadas as credenciais para login, visto que a conexão é tratada como uma outra sessão do banco de dados.

Para resolver esta situação, o Sql-Server possui uma conexão chamada “context connection”, que permite que sejam executadas instruções Transact-SQL no mesmo contexto que o código foi chamado. Neste caso, deve-se definir o “context connection” na string de conexão do objeto SqlConnection. A conexão estará disponível e poderão ser criados novos objetos, por exemplo, o SqlCommand, que têm visibilidade aos dados de tabelas, views e outros objetos pertencentes ao banco de dados. Veja, no Quadro 4, um exemplo de criação da conexão.

// Cria uma nova conexão utilizando o atual contexto

using (SqlConnection cn = new SqlConnection("context connection = true"))

{

// Abre a conexão

cn.Open();

// Cria um novo command que faz uma consulta a uma tabela

SqlCommand cmd = new SqlCommand("select * from MinhaTabelaTeste", cn);

SqlDataReader reader = cmd.ExecuteReader();

reader.Close();

}

Quadro 4 – Criação de nova conexão

Ao analisar com atenção o código do Quadro 5, pode-se notar que existe um objeto SqlCommand que faz uma consulta ao banco de dados. Entretanto, este trecho de código não tem nenhum retorno. Para complementar o código, pode-se utilizar um novo objeto: o SqlPipe. Este objeto permite que, dentro de um código CLR, seja possível retornar algum conteúdo para o chamador. Este retorno é feito por meio do método Send, que aceita sobrecarga e pode ter como parâmetro um DataReader, uma linha representando um único registro ou um texto simples. No exemplo do Quadro 5, retorna-se um objeto do tipo DataReader, contendo o resultado de uma consulta realizada no banco de dados.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data.Sql;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StoredProcedure1()

{

// Cria uma nova conexão utilizando o atual contexto

using (SqlConnection cn = new SqlConnection("context connection = true"))

{

cn.Open();

// Cria um novo command que faz uma consulta a uma tabela

SqlCommand cmd = new SqlCommand("select * from MinhaTabelaTeste", cn);

SqlDataReader reader = cmd.ExecuteReader();

// Cria um objeto Pipe e retorna o DataReader criado

SqlPipe pipe = SqlContext.Pipe;

pipe.Send(reader);

reader.Close();

}

}

};

Quadro 5 – Implementação de Stored Procedure CLR

Criação de Triggers CLR

As Triggers são procedimentos com uma característica em particular: não retornam nenhum valor e são automaticamente disparadas quando o usuário e/ou sistema fazem alguma ação do tipo inserção, exclusão ou alteração de dados de uma tabela. As Triggers foram criadas para permitir ao desenvolvedor garantir a integridade dos dados ou manter um histórico de alterações em uma tabela. É possível, por exemplo, manter um histórico de alterações de dados dos pedidos de uma empresa.

Veja, no Quadro 6, um exemplo de implementação de Trigger CLR.

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class Triggers

{

// A trigger é executada para a tabela Pedido e no evento de inserção de novos registros

[Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Pedido", Event="FOR INSERT")]

public static void Trigger1()

{

// Retorna um texto no atual contexto

SqlContext.Pipe.Send("Você atualizou um registro na tabela Pedido!");

}

}

Quadro 6

Criação de agregações

Para se implementar agregações no SQL Server 2000 era necessário ler os dados de interesse, percorrê-los em um cursor e calcular a fórmula desejada.

No SQL Server 2005 é possível criar agregações que serão processadas pelo query processor, resultando em desempenho muito superior ao da utilização de cursores. Nestas agregações, o desenvolvedor deve se ater apenas ao código para a acumulação em si, implementando os seguintes métodos:

  • Init: onde os valores de acumulação são inicializados;

  • Accumulate: calcula a agregação na fase de acumulação. Neste ponto é possível realizar cálculos que dependem dos dados. Por exemplo, para o cálculo de saldo, podemos somar os lançamentos do tipo crédito e subtrair os do tipo débito;

  • Merge: o processador de consulta pode quebrar o cálculo de uma agregação, a fim de aumentar a eficiência. Este método existe para que o desenvolvedor implemente a estratégia de unir os resultados parciais;

  • Terminate: para a execução de tarefas ao final do processamento dos registros a acumular.

O exemplo do Quadro 7 ilustra o código de uma função de agregação para concatenação de strings:

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.IO;

using System.Text;

[Serializable]

[SqlUserDefinedAggregate(

Format.UserDefined, //use clr serialization to serialize the intermediate result

IsInvariantToNulls = true, //optimizer property

IsInvariantToDuplicates = false, //optimizer property

IsInvariantToOrder = false, //optimizer property

MaxByteSize = 8000) //maximum size in bytes of persisted value

]

public class Concatenate : IBinarySerialize

{

/// <summary>

/// A variável armazena o resultado intermediário da concatenação

/// </summary>

private StringBuilder intermediateResult;

/// <summary>

/// Inicializa a estrutura interna dos dados

/// </summary>

public void Init()

{

this.intermediateResult = new StringBuilder();

}

/// <summary>

/// Acumula o próximo valor. Não faz se o valor for nulo

/// </summary>

/// <param name="value"></param>

public void Accumulate(SqlString value)

{

if (value.IsNull)

{

return;

}

this.intermediateResult.Append(value.Value).Append(",");

}

/// <summary>

/// Merge o conteúdo parcialmente agregado com este aggregate.

/// </summary>

/// <param name="other"></param>

public void Merge(Concatenate other)

{

this.intermediateResult.Append(other.intermediateResult);

}

/// <summary>

/// Chamado no final da agregação, retorna os resultados da agregação

/// </summary>

/// <returns></returns>

public SqlString Terminate()

{

string output = string.Empty;

// Apaga a última vírgula, caso exista

if (this.intermediateResult != null

&& this.intermediateResult.Length > 0)

{

output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);

}

return new SqlString(output);

}

public void Read(BinaryReader r)

{

intermediateResult = new StringBuilder(r.ReadString());

}

public void Write(BinaryWriter w)

{

w.Write(this.intermediateResult.ToString());

}

}

Quadro 7

Criação de User Defined Types (UDT)

Por fim, o último objeto que pode ser implementado em SQLCLR são tipos definidos pelo usuário. Oferecem a possibilidade de estender o conjunto de tipos escalares do SQL Server 2005, indo além de simplesmente renomear tipos pré-existentes, como em versões anteriores.

O Quadro 8 apresenta um exemplo de UDT.

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]

public struct ItemStock : INullable

{

//these three variables hold the value of physical stock, system stock,

//and the indicator whether the value of UDT is null respectively.

private int _physicalStock;

private int _systemStock;

private bool _isNull;

//The INullable readonly property.

public bool IsNull { get { return _isNull; } }

//This method returns null-valued UDT.

public static ItemStock Null

{

get

{

ItemStock itemStock = new ItemStock();

itemStock._isNull = true;

return itemStock;

}

}

//Coversion from string to UDT.

//If the value is null, return null-valued UDT.

//If not, validate and create a new ItemStock UDT.

public static ItemStock Parse(SqlString value)

{

if (value.IsNull)

return ItemStock.Null;

ItemStock itemStock = new ItemStock();

try

{

string[] values = value.ToString().Split("|");

itemStock._physicalStock = Convert.ToInt32(values[0]);

itemStock._systemStock = Convert.ToInt32(values[1]);

itemStock._isNull = false;

return itemStock;

}

catch (Exception exception)

{

throw exception;

}

}

//String representation of the UDT.

public override string ToString()

{

if (this.IsNull)

return "NULL";

else

return this._physicalStock + " | " + this._systemStock;

}

//Returns the physical stock. This is a read-only property

public int PhysicalStock { get { return this._physicalStock; } }

//Returns the system stock. This is a read-only property

public int SystemStock { get { return this._systemStock; } }

//Returns the difference between physical and system stock. This is a read-only property

public int Difference { get { return _physicalStock - _physicalStock; } }

}

Quadro 8

Métodos CLR incluídos no SQL-Server

Após criar os métodos CLR e ter efetuado todos os testes necessários, pode-se incluir o Assembly dentro do SQL-Server 2005.

Abra o SQL Server 2005 Managment Studio e selecione a base de dados com o qual deseja-se trabalhar. Selecione a pasta “Programmability” e clique com o botão direito do mouse sobre a pasta “Assemblies”. Selecione a opção “New Assembly” e surgirá uma janela como mostra a Figura 6.

Figura 6 – Criação de novo Assembly

No campo “Path to Assembly” selecione o nome do arquivo DLL que contém as funções criadas.

Após a importação do Assembly para a base de dados, é necessário incluir o método dentro do SQL-Server. Para isto, utilize o comando CREATE FUNCTION, que deve ser a assinatura da função, possuindo exatamente os mesmos parâmetros e tipos definidos. É necessário utilizar também o comando EXTERNAL NAME, que, dependendo da linguagem que a DLL foi criada, é case-sensitive. O formato a ser utilizado deve ser o seguinte:

NomeAssembly.[Namespace.Classe].Metodo

No caso do exemplo deste artigo, pode-se utilizar o código T-SQL conforme o Quadro 9 para criar a função.

CREATE FUNCTION MyFunction() RETURNS nvarchar(4000)

EXTERNAL NAME [CLRTestes].[UserDefinedFunctions].MyFunction

Quadro 9

Após a função ter sido criada, pode-se chamá-la via Transact-SQL como uma função normal do SQL-Server, conforme mostra o Quadro 10.

SELECT dbo.MyFunction()

Quadro 10

Veja que o resultado apresentado é o mesmo que havia sido encontrado no momento em que a função estava sendo desenvolvida pelo Visual Studio.

A criação da procedure pode ser feita utilizando o código SQL conforme Quadro 11.

CREATE PROC StoredProcedure1

AS

EXTERNAL NAME [CLRTestes].[StoredProcedures].StoredProcedure1

Quadro 11

A execução é feita da mesma forma que chamamos uma stored procedure comum.

EXEC StoredProcedure1

Quadro 12

A criação da Trigger pode ser feita por meio do código T-SQL, conforme o Quadro 13.

CREATE TRIGGER Trigger1

on dbo.master

FOR INSERT

AS

EXTERNAL NAME CLRTestes.Triggers.Trigger1

Quadro 13

A criação do Aggregate pode ser feita utilizando o código T-SQL, descrito no Quadro 14.

CREATE AGGREGATE Aggregate1

(@input nvarchar(200)) RETURNS nvarchar(max)

RETURNS int

EXTERNAL NAME CLRTestes.Triggers.Trigger1

Quadro 14

A criação de tipos pode ser feita utilizando o código T-SQL, descrito no Quadro 15.


    CREATE TYPE
        ItemStock EXTERNAL NAME SQLServerUDTs.ItemStock

Quadro 15

Conclusão

Sem dúvida alguma, a integração do SQL Server 2005 com o CLR é uma das grandes atrações desta versão. Há situações facilmente identificáveis para utilização do SQL CLR, cabendo ao desenvolvedor escolher pela melhor opção a ser implementada. Ao optar pela utilização de criação de código gerenciado, podemos contar com o Visual Studio 2005, que oferece incontáveis recursos em um ambiente integrado bastante rico – permitindo criar uma aplicação bastante confiável e segura.

Marcel H. Pestana

Marcel H. Pestana - Desenvolvedor da Systems Advisers Group (www.systemsadvisers.com.br) e atualmente tem se dedicado no desenvolvimento em Microsoft Dynamics AX.
É Microsoft Certified Professional (MCP), Microsoft Certified Solution Developer (MCSD) e Microsoft Certified Technology Specialist (MCTS) em SQL Server 2005.