Como e quando programar em CLR no Microsoft SQL Server 2005

Alexandre Ricardo Nardi

Arquitetura de Soluções

Microsoft Brasil

Publicado em: Outubro/2005

Resumo: Este artigo apresenta os recursos de integração entre SQL e CLR para implementação de stored procedures, triggers, funções, agregações e tipos definidos pelo usuário.

Recomendamos um conjunto de considerações que devem ser levadas em conta para a utilização mais adequada de cada recurso, comparando com outras possibilidades, como Transact_SQL e extended stored procedures, sob a ótica de aplicações distribuídas.

Nesta página

	Introdução
	Programação com Transact-SQL no Microsoft SQL Server
	Microsoft SQL Server 2005: Integração com CLR
	Conclusão

Introdução

No próximo mês será lançada a nova versão do SQL Server, que contém um grande número de novos recursos e melhorias, tornando o servidor ainda mais robusto.

De modo integrado ao Visual Studio 2005, será possível desenvolver código de stored procedures, triggers, user defined functions, user defined aggregates e user defined data types, usando linguagens que sigam a Common Language Specification (CLS), como C# e VB.NET, entre outras. Isso é possível por meio do SQLCLR, resultado da integração entre o SQL e a Common Language Runtime (CLR).

Veremos neste artigo as possibilidades de implementação em SQLCLR, e compararemos com o equivalente em Transact-SQL e extended stored procedures. Este artigo não pretende ser uma referência nesses assuntos, que pode ser obtida no SQL Books Online.

O público alvo são arquitetos, administradores e desenvolvedores. Consideramos que o leitor esteja familiarizado com programação para bancos de dados e com a plataforma .NET.

Início da página

Programação com Transact-SQL no Microsoft SQL Server

Vamos começar revendo as alternativas de programação do SQL Server 2000, também disponíveis na nova versão. Primeiro devemos ter em mente os dois grupos de construções que podem ser necessárias em uma aplicação que acessa bancos de dados:

O restante desta seção descreve as alternativas para a escrita de código e aponta as vantagens e desvantagens de cada uma. Além disso, comentamos ainda a utilização ou não do SQL Server como local de execução de lógica de negócio em dois cenários: aplicações cliente-servidor e aplicações em três camadas.

Utilização de stored procedures

No passado os SGBDs não eram extensíveis, ou seja, possuíam apenas comandos para: definição de estruturas (DDL - Data Definition Language), como "CREATE TABLE"; manipulação de dados (DML), como "SELECT"; e controle de acesso (DCL - Data Control Language), como "GRANT".

Desse modo, não havia outra forma: toda lógica para controle de fluxo era implementada pela aplicação cliente e todo acesso aos dados era realizado por meio de ad-hoc queries.

O Transact-SQL, por sua vez, possui comandos para implementação de stored procedures e para controle de fluxo.

A utilização de stored procedures ao invés de ad-hoc queries, possui as seguintes vantagens:

Para o desenvolvimento de software, o uso de stored procedures traz ainda outro importante benefício: ao isolar o desenvolvimento de código de acesso a dados do código de negócio e de apresentação, é possível utilizar programadores com especializações diferentes, resultando na melhoria da qualidade do código sendo desenvolvido. Esse fator é particularmente relevante dado que o paradigma de programação de banco de dados é diferente daquele para aplicações procedurais ou orientadas a objetos. Enquanto no primeiro caso o elemento básico é a utilização de conjuntos, no segundo é o uso de itens individuais. Se essa diferença de paradigma não for observada, o risco de utilização inadequada de recursos do gerenciador de banco de dados é aumentado. O trecho de código abaixo ilustra uma atualização de dados em conjunto e item-a-item:

-- Atualização do conjunto de produtos de limpeza
UPDATE produtos
SET preco = preco * 1.1
WHERE categoria = 'limpeza'


-- Atualização item-a-item dos produtos de limpeza
DECLARE cursor_produtos CURSOR FOR
    SELECT preco
    FROM produtos
    WHERE categoria = 'limpeza'
OPEN cursor_produtos
FETCH FROM cursor_produtos
WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE produtos
   SET preco = preco * 1.1 
   WHERE CURRENT OF cursor_produtos
   
   FETCH NEXT FROM cursor_produtos
END
CLOSE cursor_produtos
DEALLOCATE cursor_produtos
GO

O exemplo acima é um tanto óbvio: essa atualização nunca deveria ser realizada com cursores, seja no servidor, como no caso ilustrado, ou no cliente. Todavia, em cenários onde as atualizações ou consultas não são triviais, um programador acostumado a escrever código procedural tenderá a utilizar cursores, enquanto um programador experiente em SQL, ou seja, que pense em termos de conjuntos, procurará reduzir a utilização dos cursores sempre que possível, garantindo melhor desempenho e utilização de recursos do servidor.

Extended stored procedures: mais recursos de programação

A Microsoft Extended Stored Procedures API, que fazia parte do Open Data Services em versões mais antigas do SQL Server (até a versão 7.0), permite a criação de DLLs em C ou C++, de modo a estender os recursos oferecidos pelo Transact-SQL. Com esta API é possível utilizar o potencial de aplicações Win32.

Observe o exemplo de uma extended stored procedure simples, que devolve a famosa frase "Hello world!", no site http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_26hx.asp.

Um dos principais desafios para se implementar extended stored procedures, contudo, é a diferença existente entre as linguagens Transact-SQL e C/C++: a primeira é de mais alto nível, sendo específica para acesso a dados, enquanto a segunda é de uso geral, em um nível muito mais próximo do hardware. Tal diferença se refletiu na baixa oferta de profissionais no mercado com proficiência nas duas tecnologias.

Por serem executadas no mesmo processo da instância do SQL Server, extended stored procedures que possuam memory leaks (não raros em aplicações em C/C++) podem facilmente comprometer a estabilidade e, conseqüentemente, a confiabilidade do servidor.

Importante: embora disponível no SQL Server 2005, a Microsoft Extended Stored Procedures API será descontinuada em versões futuras do SQL Server. Dessa forma, recomenda-se que novos desenvolvimentos sejam realizados em SQLCLR, que oferece a possibilidade de escrever stored procedures com os mesmos recursos disponibilizados pelas extended stored procedures. Para procedimentos (extended) existentes, a recomendação é estudar a viabilidade de migração para SQLCLR.

Início da página

Microsoft SQL Server 2005: Integração com CLR

Common Language Runtime (CLR) é o ambiente de execução do .NET Framework. É responsável pelo gerenciamento da execução de aplicações - daí vêm as denominações código gerenciado (managed code) para as aplicações que são gerenciadas pelo CLR e não gerenciado (unmanaged code) para as aplicações que são executadas diretamente sobre o sistema operacional.

Uma das principais características do .NET é a diversidade de linguagens de programação que podem ser utilizadas para a escrita de código. Isso é possível porque tais linguagens seguem uma definição comum, a Common Language Specification (CLS).

No processo de compilação de código escrito em qualquer linguagem .NET, são gerados artefatos denominados assemblies (DLLs ou EXEs), expressos em uma linguagem intermediária independente de hardware, a Microsoft Intermediate Language (MSIL).

No momento da execução, o CLR utiliza um compilador de MSIL para o hardware em questão: é a chamada compilação just-in-time (JIT).

Para que fosse possível a escrita de código .NET a ser executado dentro do SQL Server 2005, algumas questões tiveram de ser resolvidas, como o modelo de execução de threads, que é cooperativo no SQL Server e preemptivo no CLR. Em outras palavras, foi necessário integrar recursos do SQL e recursos do CLR. Como resultado dessa integração, o SQL Server 2005 oferece uma alternativa, o SQLCLR, muito superior às extended stored procedures, motivo pelo qual estas serão descontinuadas em versões futuras.

A seguir, vamos apresentar a relação entre o Transact-SQL e o SQLCLR, e discutiremos como e quando é apropriada a utilização de cada um.

O Transact-SQL ainda é necessário?

Essa é uma das primeiras perguntas que vem à mente quando pensamos na possibilidade de programar em .NET para o SQL Server.

No início deste artigo, comentamos os dois grupos de construções em uma aplicação de acesso a dados: para manipulação e para controle de fluxo. Pois bem, a manipulação de dados continua sendo implementada em Transact-SQL, que é uma linguagem madura e eficiente para esta finalidade. Desse modo, a resposta à pergunta é: SIM, o Transact-SQL é necessário.

A implementação de código em SQLCLR é uma alternativa para o segundo grupo: construções para controle de fluxo. Aqui o uso de SQLCLR pode oferecer ganhos importantes em termos de disponibilidade de recursos, facilidade de desenvolvimento e desempenho (mais à frente veremos em quais casos).

Como programar usando SQLCLR

Existem diversas possibilidades em que o SQLCLR pode ser utilizado para a escrita de código, conforme veremos adiante.

No entanto, o processo de criação, compilação e carga é muito parecido para todos. A utilização, por sua vez, é praticamente transparente para o usuário, obedecendo a sintaxe do Transact-SQL. Como um primeiro exemplo, o código a seguir apresenta um procedimento escrito em C# que devolve um texto.

Para a criação de código em SQLCLR, os namespaces necessários estão todos no assembly system.data.dll, presente no Global Assembly Cache, e cuja referência já é inserida automaticamente pelo Visual Studio na criação de projetos.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class HelloWorldProc
{
   public static void HelloWorld()
   {

      SqlContext.Pipe.Send("Hello world!\n");

   }
}

Uma vez que esse código seja compilado em um assembly (hello.dll, em nosso caso), este deve ser carregado no SQL Server:

CREATE ASSEMBLY hello from 'c:\hello.dll' WITH PERMISSION_SET = SAFE

Em seguida, é necessário criar um objeto no SQL, que pode ser, conforme o caso:

CREATE PROCEDURE hello
AS
EXTERNAL NAME hello.HelloWorldProc.HelloWorld
ASSEMBLY hello from 'c:\hello.dll' WITH PERMISSION_SET = SAFE

Uma vez criado o objeto, a utilização é como a de objetos criados em Transact-SQL.

EXEC hello

A seguir veremos algumas especificidades de cada tipo de objeto que pode ser criado em SQLCLR. Sempre que apropriado, vamos comparar o objeto em SQLCLR com o seu equivalente em Transact-SQL.

Stored procedures

São os objetos mais simples de serem implementados, do ponto de vista da integração SQL e CLR, uma vez que oferecem interface composta apenas de parâmetros de entrada e saída, e do retorno de um valor simples opcional (inteiro), sendo que não há métodos adicionais a serem implementados, como é o caso de outros objetos que apresentaremos a seguir.

Apesar disso, vale uma sugestão: não se esqueça que o acesso aos dados continua sendo realizado em Transact-SQL. Desse modo, procedimentos que tipicamente acessam dados devem ser codificados em Transact-SQL ao invés de em SQLCLR. Discutiremos este assunto em maiores detalhes mais adiante.

O trecho de código abaixo ilustra o cenário mais comumente encontrado em stored procedures, ou seja, procedimento com parâmetros de entrada e de saída, que devolve um conjunto de linhas e um valor de retorno:

[SqlProcedure()]
public static SqlInt32 ProcessaPedidos(SqlInt32 filialID, out SqlString observacoes)
{
	SqlConnection conn = new SqlConnection("context connection
 = true");
	conn.Open();

	SqlCommand sprocCommand = conn.CreateCommand();
	sprocCommand.CommandText = "sel_Pedidos";
	sprocCommand.CommandType = CommandType.StoredProcedure;

	sprocCommand.Parameters.Add(new SqlParameter("@FilialID",
 SqlDbType.Int));
	sprocCommand.Parameters[0].Value = filialID;
	SqlDataReader sqlReader = sprocCommand.ExecuteReader();
while (sqlReader.Read())
	{
		// Realiza cálculos específicos para cada pedido
	}
	
	// Devolve as linhas lidas
	SqlContext.Pipe.Send(sqlReader);

	observacoes = "Processamento realizado em: " + 
 DateTime.Now.ToString();

	return (SqlInt32)(sqlReader.HasRows ? 1 : 0);
}

Triggers

São procedimentos com algumas características em particular: não devolvem valor e possuem acesso às tabelas INSERTED e DELETED.

No SQL Server 2005, além de ser possível a criação de triggers para a execução dos comandos INSERT, DELETE e UPDATE, é possível também criar triggers para comandos DDL, como CREATE TABLE, por exemplo.

O código a seguir ilustra o envio de email a partir de um trigger quando forem criadas tabelas em um banco de dados.

public static void EmailCriaTabela()
{
	SqlTriggerContext triggContext =
	 SqlContext.GetTriggerContext();
	if (triggContext.TriggerAction == triggerAction.CreateTable) 
	{
		MailMessage msg = new MailMessage();
		msg.From = "sqlserver@empresa.com";
		msg.To = "admin@empresa.com"; 
		msg.Subject = "Criação de tabela...";
		msg.Body = (String)
		 (triggContext.EventData.ToSqlString());
		SmtpMail.SmtpServer = "mail.empresa.com";
		SmtpMail.Send(msg);
	}
}

A criação do trigger associado ao procedimento acima é semelhante à criação de uma stored procedure:

CREATE TRIGGER EmailCriaTabela
ON UsersDB
FOR CREATE_TABLE
AS
EXTERNAL NAME empresa.triggers.EmailCriaTabela

User defined functions (UDF)

São semelhantes a stored procedures, mas podem ser utilizadas a partir de comandos DML. Existem dois tipos de funções: as que retornam um valor escalar e as que retornam valores tabulares. Abaixo segue um exemplo de cada tipo:

[SqlFunction()] // Escalar
public static int NumeroDePedidos()
{
using(SqlConnection conn = new SqlConnection("context
 connection=true"))
{
        conn.Open();
        SqlCommand cmd = new SqlCommand("SELECT COUNT(*) AS
  'Num Pedidos' FROM Pedidos", conn);
   		return (int) cmd.ExecuteScalar();
        }
}

Note que essa função poderia ter sido implementada em Transact-SQL, o que lhe conferiria melhor desempenho, por se tratar simplesmente de acesso a dados.

[SqlFunction()] // Tabular
public static SqlResultSet ItensDePedido(SqlInt32 PedidoID)
{
String s = "SELECT ItemID, Descricao, Valor FROM Itens
 WHERE PedidoID = @PedidoID";

using(SqlConnection conn = new SqlConnection("context 
 connection=true"))
{
        conn.Open();
        SqlCommand cmd = new SqlCommand(s, conn);
        cmd.Parameters.AddWithValue("@PedidoID", PedidoID);
        SqlDataReader dr = cmd.ExecuteReader();
        SqlResultSet res = SqlContext.GetReturnResultSet();
        while (dr.read())
        {
                res.Insert(dr);
        }
   		 return res;
        }
}

A sintaxe para definição da função tabular em Transact-SQL é semelhante às anteriores:

CREATE FUNCTION ItensDePedido()
RETURNS TABLE (ItemID smallint, Descricao varchar(8000), Valor smallmoney)
EXTERNAL NAME contoso.udf.ItensDePedido

User defined aggregates (UDA)

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:

O exemplo a seguir ilustra o código de uma função de agregação para concatenação de strings:

[Serializable]
[SqlUserDefinedAggregate()]
public class Concat : IBinarySerialize
{
    private StringBuilder acum;

    public void Init()
    {
        acum = new StringBuilder();
    }

    public void Accumulate(SqlString valor)
    {
        acum.Append(valor.Value).Append(',');
    }

    public void Merge(Concat elem)
    {
        acum.Append(elem.acum);
    }

    public SqlString Terminate()
    {
        string saida = string.Empty;
        //remove a vírgula do final, se houver
        if (acum != null && acum.Length > 0)
            saida = acum.ToString(0, acum.Length - 1);
        return new SqlString(saida);
    }
}

Observe que esta implementação é mais elegante do que o uso de cursores.

Para a execução da agregação, considere uma tabela com os funcionários de uma empresa. Abaixo estão representadas apenas as colunas para o nome e o departamento do funcionário:

Deseja-se obter listas com os funcionários agrupados por departamento:

SELECT DeptoID, dbo.Concat(Nome)
FROM Funcionarios
GROUP BY DeptoID

O resultado da pesquisa é:

User defined data types (UDT)

Finalmente, 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.

A estrutura geral de um UDT é a seguinte:

[SqlUserDefinedType()] 
public struct MeuUdt : INullable 
{ 
	public override string ToString() { ... } 
	public bool IsNull { ... } 
	public static MeuUdt Null { get { ... } } 
	public static MeuUdt Parse(SqlString s) { ... } 
} 

É importante notar que um UDT deve ser criado para representar tipos de dados atômicos, embora possivelmente complexos.

Quando programar e quando NÃO programar em CLR

A integração entre SQL e CLR traz, conforme os diversos exemplos acima, novas e atraentes possibilidades para o desenvolvimento de aplicações. Neste momento, contudo, um desenvolvedor desatento poderia ser induzido a implementar tudo que for possível em SQLCLR.

Um conceito importante a ter sempre com clareza é que o Transact-SQL é mais eficiente para acesso aos dados (manipulação) e o SQLCLR é mais eficiente para controle de fluxo.

Desse modo, independentemente do código estar em stored procedures, triggers ou funções, sempre que o principal for manipulação de dados, o código deverá ser escrito em Transact-SQL, e quando for realização de cálculos deverá ser escrito em SQLCLR. Note que os UDAs sempre são escritos em SQLCLR exatamente por esse motivo.

Outro ponto extremamente importante, e que hoje tem sido causa de insucesso de um grande número de projetos, é a distribuição da lógica de negócio em uma aplicação com múltiplas camadas. Por herança do modelo cliente-servidor, em que freqüentemente se optou por deixar a execução de regras de negócio no SGBD, não é raro encontrar stored procedures com milhares de linhas de código, muitas vezes realizando operações com pouca manipulação de dados e muito processamento.

Por outro lado, é comum encontrar textos afirmando que lógica de negócio deve ser executada em uma camada à parte. No entanto, diversas vezes tal lógica envolve diversos e freqüentes acessos aos dados, o que pode comprometer o desempenho da solução caso executada fora do SGBD.

As duas afirmações acima parecem ser conflitantes: afinal, a lógica de negócio deve ser executada dentro ou fora do SGBD? Uma sugestão que pode auxiliar nessa decisão é observar a natureza do código: manipulações intensas de dados devem ficar na camada de dados (geralmente em Transact-SQL); processamento intenso com pouca interação com o SGBD deve ficar na camada de negócios; e processamento intenso com muita interação com o SGBD devem ficar na camada de dados (possivelmente em SQLCLR).

Início da página

Conclusão

A integração entre SQL e CLR que acompanha o Microsoft SQL Server 2005 é um dos recursos mais atraentes desta nova versão. O potencial para desenvolvimento de aplicações mais robustas foi ampliado além das fronteiras das extended stored procedures.

Procuramos ilustrar como se dá essa integração por meio de exemplos, e recomendamos que o leitor complemente seus conhecimentos com o Books Online e os exemplos que acompanham o SQL Server.

Apresentamos ainda uma série de boas práticas na forma de sugestões no decorrer do texto, para auxiliar em decisões como: uso de Transact-SQL ou SQLCLR; implementação de código na camada de dados ou de negócio; uso de stored procedures ou ad-hoc queries.

Alexandre Ricardo Nardi
Arquiteto de soluções na Microsoft desde 2004, tem como objetivo a divulgação de tecnologia em clientes empresariais. Dentre as atividades que desenvolve estão revisões de arquitetura de sistemas e recomendação de boas práticas e guias para desenvolvimento de software. Anteriormente, atuou como desenvolvedor, consultor e instrutor. Está atualmente cursando o doutorado na área de banco de dados, com foco em mineração de dados.

Início da página