Trabalhando com SQL CLR – Resumo

Então pessoas, uma das features que mais gosto do SQL Server é o suporte a CLR, que permite muitas possibilidades na criação de Stored Procedures, Funções, Types e Triggers com as linguagens C# e Visual Basic, e outras linguagens .NET, como o Delphi e F#.

No blog, busquei explorar de forma introdutória como trabalhar com esta feature e uma visão geral de cada objeto que é possível desenvolver. Em paralelo também tratei algumas extensibilidades, como trabalhar como F#, Web Services, referenciar projetos de terceiros nos projetos CLR e novidades no lançamento do Visual Studio 2010.

Introdução:

Este é um passo-a-passo de como iniciar com SQL CLR, desde questões mais á nível de SQL Server, até questões á nível de desenvolvimento (debug e publicação).

Integração SQL CLR no SQL Server – Uma Visão Geral [NOVO]

https://sqlfromhell.wordpress.com/2012/04/20/integracao-sql-clr-no-sql-server-visao-geral/

Habilitando CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/09/19/habilitando-clr

Criando funções CLR para o SQL Server:

https://sqlfromhell.wordpress.com/2009/09/26/funcoes-clr

Debug de funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/03/debug-clr

Publicando Assemblies e Funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/10/publicando-clr

Publicando Assemblies no SQL Server pelo Visual Studio:

https://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio

Visão Geral:

Para quem quer conhecer cada um dos objetos que permitem desenvolvimento na integração SQL CLR, elaborei uma visão geral orientada á muitos exemplos de cada um.

Stored Procedure:

https://sqlfromhell.wordpress.com/2010/07/03/clr-stored-procedure

https://sqlfromhell.wordpress.com/2010/07/13/clr-stored-procedure-2

User Defined Function Scalar:

https://sqlfromhell.wordpress.com/2010/07/25/trabalhando-com-clr-user-defined-function-scalar

Table-value User Defined Functions:

https://sqlfromhell.wordpress.com/2010/12/02/sql-clr-table-value-user-defined-functions

User Defined Aggregate Functions:

https://sqlfromhell.wordpress.com/2010/12/14/user-defined-aggregate-functions

User Defined Type:

https://sqlfromhell.wordpress.com/2010/12/22/sql-clr-user-defined-type

Triggers:

https://sqlfromhell.wordpress.com/2010/12/31/sql-clr-trigger

Estendendo:

Atendendo alguns pedidos e uma “aventura” pessoal (F#).

Web Services:

https://sqlfromhell.wordpress.com/2010/03/07/webservice-e-funes-clr-no-sql-server

F# no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/17/fsharp

Referenciando uma Class Library em Projetos SQL-CLR (Draft):

https://sqlfromhell.wordpress.com/2009/09/30/crl-classlibrary

Trabalhando com SQL CLR e Spatial Data [NOVO]

https://sqlfromhell.wordpress.com/2011/07/27/trabalhando-com-sql-clr-e-spatial-data/

Novidades:

Como com cada lançamento do Visual Studio temos o suporte SQL CLR permitindo a utilização das novas .NET Framework, mostrei que no caso do Visual Studio 2010 tivemos até algumas novidades mais interessantes.

Novidades do Visual Studio 2010:

https://sqlfromhell.wordpress.com/2010/05/16/o-que-mudou-nos-projetos-sql-clr-do-visual-studio-2010

Anúncios

Integração SQL CLR no SQL Server – Uma Visão Geral

De que se trata o artigo:

Este artigo permite o entendimento do potencial da integração da CLR com o SQL Server. Considerando o entendimento de como o SQL Server trabalha com a CLR para execução de rotinas desenvolvidas em C# e VB.NET, quando é recomendável e como utilizar desta integração para desenvolver rotinas, e por fim, como administrar o que foi desenvolvido.

Para que serve:

A integração CLR do SQL Server permite basicamente desenvolver funções, stored procedures, tipos e triggers com C# ou VB.NET. Sendo uma alternativa mais prática e ágil do que a linguagem T-SQL pode oferecer para o desenvolvimento. Além de contar com uma diversidade de recursos e possibilidades de novas soluções.

Em que situação o tema útil:

No momento em que as limitações da linguagem T-SQL se tornam um problema para o desenvolvimento de rotinas complexas, o .NET Framework vem permitir possibilidades, além uma diversidade de recursos que ampliam a capacidade de oferecer soluções.

Introdução:

Desde o lançamento do SQL Server 2005, se tornou possível utilizar C# ou VB.NET para criar stored procedures, functions, types e triggers. Esta possibilidade é denominada integração CLR, que consiste basicamente em utilizar bibliotecas desenvolvidas em C# ou VB.NET, para criar objetos em nível de banco de dados. Estas bibliotecas, conhecidas como assemblies, estão presentes no computador como arquivos na extensão DLL e são traduzidas em rotinas para o sistema operacional pela CLR (Common Language Runtime), um componente do .NET Framework.

Pelo fato de que estes assemblies são traduzidos, ou melhor, executados pela CLR, eles tambamém contam com todo o potencial da .NET Framework neste processo.

A .NET Framework é a principal plataforma de desenvolvimento da Microsoft, utilizada para criar aplicações em nível de sistema operacional, aplicações para internet, equipamentos móveis e outras soluções. O que faz do desenvolvimento de assemblies para o SQL Server somente uma das suas possibilidades desta plataforma.

No desenvolvimento com a .NET Framework, é possível contar com bibliotecas que permitem criar soluções que manipulam em nível de sistema operacional (manipulação de registros do sistema, diretivas de segurança, sistema de arquivos), manipular de dados em diversos formatos, trabalhar com vetores, fluxos de memória e realizar cálculos complexos, além de facilitar a interação com WebServices e protocolos em nível de rede.

Também é comum no desenvolvimento com a .NET Framework, a utilização de interfaces de desenvolvimento, como o Visual Studio, que facilita e agiliza todo o processo de desenvolvimento.

Em resumo, trabalhar com assemblies no SQL Sever não é somente de desenvolver objetos em nível de banco de dados com C# ou VB.NET ao invés de T-SQL, mas ter todo o potencial da .NET Framework para criar soluções.

Para permitir um bom entendimento do potencial desta feature, este artigo abordará como funciona a CLR e sua integração com o SQL Server, quando é recomendável utilizá-la, como desenvolver assemblies para o SQL Server e por último como administrar o que foi desenvolvido.

Como funciona a integração CLR?

No desenvolvimento de soluções com a .NET Framework, independentemente do tipo de solução, como aplicações Windows e Web, o compilador transforma o código de acordo com a linguagem utilizada (VB.NET, C#) em um assembly. Este assembly pode ser uma biblioteca (arquivo de extensão DLL) ou um arquivo executável (extensão EXE).

Conforme a Figura 1, o assembly é composto por duas estruturas básicas, sendo a primeira constituída de meta-dados (Metadata), que funcionam como um breve descritivo do código (namespaces, classes, métodos, propriedades, atributos e referências á outros assemblies), e a segunda camada, uma linguagem de alto-nível, denominada linguagem intermediária (IL ou MSIL), que é o código preparado para ser executado pela CLR.

Figura 1. Estruturas básicas que compõem um assembly.

Quando um assembly é adicionado a um banco de dados, assunto abordado nos tópicos seguintes, o SQL Server utilizará a camada de meta-dados para entender como este assembly é constituído. Neste processo de entender como o assembly é constituído, o SQL Server verificará se este assembly possui todas as dependências necessárias no banco de dados (como referências a outros assemblies) e se obedece a determinados critérios de segurança para ser executado.

Após o assembly ser adicionado ao banco de dados, será possível criar procedures, funções, triggers ou tipos a partir de referências a determinados métodos ou estruturas do assembly que possuam assinaturas compatíveis com o SQL Server. Estas assinaturas são definidas por atributos durante a fase de desenvolvimento (assunto a ser tratado nos próximos tópicos) e permitem definir quais os tipos de objetos que podem ser criados por meio destes métodos e estruturas.

Como relacionado pela Figura 2, as estruturas (structs) desenvolvidas em C# e VB.NET poderão receber assinaturas/atributos para a criação de tipos e funções de agregação, e os métodos, assinaturas/atributos para stored procedures, funções escalares ou tabulares e triggers.

Figura 2. Relação entre assinaturas/atributos do Assembly com objetos do banco de dados.

A execução de assemblies pela CLR é gerenciada pela .NET Framework em aspectos de segurança, memória, processamento e erros de execução.

Nos aspectos de segurança, um assembly poderá ser configurado como código seguro (SAFE), seguro com acesso a recursos externos (EXTERNAL_ACCESS) ou um código inseguro (UNSAFE). E a integração CLR interpretará esta configuração da seguinte forma:

SAFE: Rotinas que possuem restrição de acesso a recursos externos ao SQL Server e não referenciam assemblies com configuração de segurança diferente;

EXTERNAL_ACCESS: Rotinas que possuem restrição a códigos não-gerenciados, como objetos COM+ e assemblies UNSAFE, mas com a possibilidade de acessar recursos externos ao SQL Server, como arquivos, Web Services e serviços de e-mail SMTP;

UNSAFE: Rotinas que podem utilizar todo o potencial do .NET Framework, sem restrições de manipular até mesmo recursos e configurações do próprio sistema operacional, como registros, processamento, memória, drivers e objetos COM+.

Em relação à memória, o CLR possui uma ferramenta chamada Garbage Collector, que libera da memória as variáveis e objetos não utilizados durante a execução. Esta ação de liberar estas variáveis da memória permite que as rotinas utilizem somente a quantidade de memória necessária para serem executadas. Em contrapartida, esta ação pode exigir um pouco mais de processamento.

Diferente dos assemblies de aplicações tradicionais, os assemblies da integração CLR no SQL Server são gerenciadas pelo próprio SQL Server. Desta forma, cabe ao SQL Server controlar a demanda por recursos para execução dos assemblies para não comprometer a estabilidade do serviço de banco de dados.

Visto que se recursos utilizados por aplicações tradicionais são gerenciados pelo próprio sistema operacional, se da mesma maneira fosse com a integração CLR, a execução dos assemblies poderia concorrer por memória ou processamento com o próprio SQL Server, criando algum gargalo de desempenho e prejudicando o serviço de banco de dados.

Em relação aos erros de execução, como rotinas com laços de repetição infinitos (loops infinitos) e variáveis que recebem valores maiores que os suportados (overflow), a CLR possui total controle sobre estes erros, garantindo que, caso ocorram, seja possível identifica-los e trata-los programaticamente.

Quando é recomendável utilizar CLR ao invés de T-SQL?

Na época do SQL Server 2000, era comum usar objetos COM+ no SQL Server para acessar recursos externos, como Web Services, arquivos e pastas locais ou de rede, serviços de e-mail e informações internas do sistema operacional.

Também nesta época haviam muitas rotinas, denominadas Extended Stored Procedures, que faziam uso de código não-gerenciado (código sem a supervisão de um componente como a CLR, para ser executado). Visto que o uso de código não-gerenciado pode interferir tanto na estabilidade quanto na segurança do servidor, o surgimento do .NET Framework permitiu solucionar este problema.

Com o lançamento do .NET Framework 2.0 e a integração CLR do SQL Server 2005, muitas das Extended Stored Procedures nativas do SQL Server foram migradas para CLR, e foi recomendado que o que era desenvolvido anteriormente em código não-gerenciado fosse migrado para CLR.

Esta recomendação de utilizar CLR não foi somente válida para rotinas que fazem uso de COM+ e Extended Stored Procedures, mas também para rotinas T-SQL que possuem um alto grau de complexidade, como regras de negócio e cálculos matemáticos. Também considerando que rotinas complexas podem ser mais performáticas e manuteníveis com a utilização da CLR e linguagens já comuns ao ambiente de desenvolvimento, como VB.NET e C#.

No que se refere às regras de negócio e aos procedimentos que podem ser executados em nível de aplicação, recomenda-se que estes sejam executados em nível de aplicação. Pois mesmo que seja possível utilizar todo o potencial do .NET Framework dentro do SQL Server, não pode ser deixado de lado o papel do SQL Server como sistema de gerenciamento de banco de dados (SGBD) para torná-lo um hospedeiro para as aplicações. Considerando também que para manter a integridade e desempenho do acesso e manipulação de dados, os recursos do SQL Server serão concorridos para permitir a execução da CLR. Além do fato da CLR ser mais bem gerenciada pelo sistema operacional e por serviços dedicados para aplicação (como o IIS no caso das aplicações Web).

Quando for necessária a utilização de códigos não seguros no SQL Server, é recomendável avaliar se não é possível isolar este código em outro ambiente.

A Figura 3 demonstra um ambiente onde a utilização de códigos não seguros pode comprometer a segurança do banco de dados, dado o fato que eles exigem a criação de um assembly não seguro. Também considerando que o SQL Server pode ter sua estabilidade comprometida pelo fato de executar rotinas não seguras não gerenciadas pela CLR.

Figura 3. Exemplo de um ambiente que pode comprometer a segurança do SQL Server

Uma forma simples de isolar a execução de códigos não seguros é a utilização de Web Service, e códigos seguros com acesso a este Web Services.

A Figura 4 apresenta uma rotina que utiliza objetos COM+ intermediado por um Web Service, exigindo da integração CLR somente fazer a ponte entre o Web Service e o SQL Server.

Figura 4. Exemplo de um ambiente onde a execução de rotinas não seguras é isolada.

Também é interessante ressaltar que os Web Services não precisam ser desenvolvidos em VB.NET ou C# para ser compatíveis com a CLR, graças à diversidade de bibliotecas e recursos da .NET Framework para se comunicar com ambientes externos por meio de padrões universais, como XML, SOAP e RSS, ou padrões específicos, como ODBC, JSON e SMTP/POP3.

Na escolha entre T-SQL e CLR no desenvolvimento de novas rotinas, conforme ilustrado pela Figura 5, as abordagens com T-SQL permitem maior eficiência no acesso e manipulação de dados e recursos do próprio SQL Server. Por outro lado, a CLR permite maior eficiência no desenvolvimento de rotinas complexas ou que exijam acesso a recursos externos ao SQL Server.

Figura 5. Quando escolher uma abordagem T-SQL ou CLR.

Outro o fato a ser considerado na utilização da CLR, é que a utilização de assemblies pode ser limitada ou não permitida por alguns serviços de hospedagem de sites/banco de dados, serviços de virtualização, cloud computing e soluções de alta disponibilidade.

Como utilizar a integração CLR?

Compreendido como funciona e quando utilizar a integração CLR, o primeiro passo antes do desenvolvimento das rotinas com esta integração é verificar se algumas configurações em nível de SQL Server precisam ser realizadas.

A primeira configuração esta relacionada à integração CLR está habilitada. Para verificar se a integração CLR está habilitada por meio da stored procedure sp_configure.

A partir da execução da stored procedure sp_configure será possível obter as seguintes informações de uma determinada configuração:

name: o nome da configuração propriamente dita;

minimum e maximum: os valores mínimo e máximo possíveis desta configuração. No caso da integração CLR, o valor “0” significa que ela está desabilitada e “1” que ela está habilitada;

config_value: o valor da configuração antes de ser aplicado;

run_value: valor atual da configuração.

De acordo com os valores obtidos pela sp_configure, a Figura 6 demonstra uma situação onde da integração CLR está desabilitada.

Figura 6. Integração CLR desabilitada.

Um fato interessante é que mesmo com a integração CLR desabilitada, os tipos CLR nativos do SQL Server, como os tipos hierárquicos e espaciais (hierarchyid, geometry e geongraphy), funcionam sem problemas.

A Figura 7 demonstra como habilitar a integração CLR, primeiramente utilizando a função sp_configure seguida do nome da configuração e o valor desejado, alterando o valor config_value. Em um segundo passo, o comando RECONFIGURE é executado, aplicando o valor config_value sobre o run_value da configuração. Por fim, o comando sp_configure é executado novamente para verificar o valor atual da configuração – no caso, se a integração CLR foi habilitada com sucesso.

Figura 7. Habilitando a integração CLR.

Ao se pensar em códigos seguros com acesso externo ou códigos não seguros, a propriedade Trustworthy (is_trustworthy_on) do banco de dados determinará se estes códigos podem ser utilizados. A Figura 8 demonstra uma situação onde o banco de dados não permite estes tipos de código.

Figura 8. A propriedade Trustworthy desabilitada em um determinado banco de dados.

Além de utilizar códigos seguros com acesso externo ou códigos não seguros em um determinado banco de dados, a propriedade Trustworthy permitirá outras questões de segurança que não se limitam à integração CLR.

Para entender melhor os impactos de habilitar esta propriedade, e se ela está adequada ao seu ambiente, consulte o link: http://msdn.microsoft.com/en-us/library/ms187861.aspx .

A Figura 9 demonstra como habilitar a propriedade Trustworthy de um determinado banco de dados.

Figura 9. Habilitando a propriedade Trustworthy.

Terminadas as configurações no nível de banco de dados, será possível utilizar o Visual Studio como ferramenta para desenvolver, com C# ou VB.NET, assemblies para o SQL Server.

O primeiro passo para desenvolver assemblies para o SQL Server com o Visual Studio é criar um projeto a partir do template Visual C# SQL CLR Database Project, como apresenta a Figura 10.

Figura 10. Criando um projeto SQL CLR no Visual Studio 2010.

Depois de criado o projeto, aparecerá uma tela para referenciar o banco de dados onde os códigos serão instalados e executados (veja a Figura 11). Assim, adicione uma nova referência ou utilize uma já existente.

Figura 11. Adicionando uma referencia a um determinado banco de dados ao projeto.

Ao referenciar o banco de dados ao projeto, evite trabalhar diretamente com ambientes de produção, para não causar problemas de desempenho quando forem executados os testes dos códigos em desenvolvimento.

Após referenciado o banco de dados, outra tela que aparecerá, conforme a Figura 12, possibilitando habilitar a depuração do código em tempo de execução (Debug). O Debug nada mais é que permitir, durante a execução do projeto no Visual Studio, acompanhar a execução no SQL Server.

Figura 12. Habilitando o Debug no projeto.

Também não será possível utilizar o Debug se o login utilizado para referenciar o banco de dados não possuir as permissões necessários, no caso logins com permissão de “sysadmin”.

Terminadas as configurações do projeto, na janela Solution Explorer será possível ver a estrutura do projeto SQL CLR (Figura 13).

Os projetos SQL CLR possuem inicialmente scripts de PreDeploy e PostDeploy (executados antes e depois da publicação do projeto) e um script de Test, que poderá ser utilizado durante o Debug. No script de Test é possível informar alguns procedimentos para testar se as funcionalidades do assembly durante o Debug.

Figura 13. Estrutura inicial do projeto SQL CLR.

Clicando com o botão direito sobre o projeto é possível adicionar um novo item, como pode ser visto na Figura 14. Este novo item nada mais é do que um arquivo contendo alguma espécie de código, normalmente em C#, VB.NET ou T-SQL.

Figura 14. Adicionando um novo item ao projeto

Este código poderá ser criado a partir de templates, evitando a necessidade de iniciar o código de um arquivo vazio, facilitando assim o desenvolvimento. Conforme a Figura 15, os templates disponíveis para criação deste código.

Para um primeiro exemplo, será utilizado o template User-Defined Function (ou função escalar) para desenvolver uma função de pouca complexidade, no caso o cálculo de fatoriais.

Figura 15. Templates disponíveis para ao projeto SQL CLR

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 GetFatorialCLR()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Listagem 1. Template de uma função escalar.

A Listagem 1 traz o template inicial de uma função escalar em C#. Esta função é basicamente um método que retorna ao SQL Server o texto “Hello”. O SQL Server entenderá que este método se trata de uma função escalar por causa do atributo/assinatura Microsoft.SqlServer.Server.SqlFunction e adotará o nome da função como GetFatorialCLR() por ser este nome do método.

Para entender o funcionamento desta função, clique com o botão direito sobre o projeto, e conforme a Figura 16, utilize a opção Deploy para instalar este assembly no banco de dados referenciado na criação do projeto.

Figura 16. Executando o Deploy de um projeto SQL CLR

Se a instalação do assembly for realizada com sucesso, será possível obter mensagens de sucesso na janela Output do Visual Studio, conforme a Figura 17.

Figura 17. Mensagens sucesso de uma instalação bem sucedida.

Após a instalação do assembly, abra o SQL Server Management Studio e se conecte ao banco de dados onde este assembly foi instalado. Por fim, execute a função GetFatorialCLR(), como demonstrado na Figura 18.

Figura 18. Resultado da execução da função GetFatorialCLR.

Visto que a publicação ocorreu com sucesso e a função está funcionando, será possível customizar a função ao ponto de ser possível obter o resultado desejado, no caso, o cálculo da fatorial, conforme a Listagem 2.

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

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "GetFatorialCLR")]
    public static SqlDouble GetFatorialCLR(SqlInt32 Value)
    {
        if (Value.IsNull || Value <= 1) return 1;

        var fatorial = Enumerable.Range(1, Value.Value)
            .Select(i => (double)i)
            .Aggregate((i, j) => i * j);

        return double.IsInfinity(fatorial) ? SqlDouble.Null : fatorial;
    }
};

Listagem 2. Código da função GetFatorialGLR() ajustado para recuperar o fatorial.

Após instalar o assembly novamente pelo Visual Studio, será possível obter como resultado da função GetFatorialCLR(), o valor de um determinado fatorial.

Figura 19. Resultado da execução da função GetFatorialCLR após os ajustes.

Como com o CLR existe maior controle sobre valores numéricos que o T-SQL, é possível tratar valores decimais que sofreram alguma forma de overflow (quando uma variável recebe um valor que ela não pode suportar).

Como exemplo de overflow, o fatorial do número 171 resulta em um valor que ultrapassa o suportado pelo tipo float do SQL Server, e por meio da CLR será possível tratar este valor antes de ser submetido ao SQL Server e gere alguma forma de erro. Conforme a Listagem 2, foi utilizado o método IsInfinity() do tipo double da CLR para verificar se houve ou não overflow, e retornar um valor fixo (como NULL), resultado demonstrado pela Figura 20.

Figura 20. Resultado da execução da função GetFatorialCLR() em uma situação de overflow

Graças às diversidades das bibliotecas do .NET Framework, principalmente às nativas da plataforma, será possível criar funcionalidades mais complexas do que as permitidas com o simples T-SQL, como criar uma função que utiliza expressões regulares (Regex).

A Listagem 3 demonstra um código que utiliza Regex para remover determinados elementos de um texto, conforme o resultado da Figura 21.

using System;
using System.Data.SqlTypes;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "RegexReplaceCLR")]
    [return: SqlFacet(MaxSize = -1)]
    public static SqlString RegexReplaceCLR(
        [SqlFacet(MaxSize = -1)]SqlString Input,
        [SqlFacet(MaxSize = 50)]SqlString Pattern,
        [SqlFacet(MaxSize = 50)]SqlString Replacement)
    {
        if (Input.IsNull || Pattern.IsNull || Replacement.IsNull) return null;

        var regEx = new Regex(Pattern.Value);
        return regEx.Replace(Input.Value, Replacement.Value);
    }
};

Listagem 3. Código de uma função para substituir caracteres por meio de uma expressão regular.

Figura 21. Resultado da execução da função para substituir caracteres por meio de uma expressão regular.

Verifique que na Listagem 3 foi utilizado o atributo SqlFacet, que possui basicamente a função de definir como a integração CLR interpretará os valores recebidos e retornados do SQL Server. Sendo uma utilização comum deste atributo definir o tamanho das variáveis. Já que por padrão as variáveis do tipo SqlString resultam em um varchar(8000), com a configuração do atributo SqlFacet, estas variáveis poderão variar de um tamanho fixo á varchar(max), no caso “MaxSize=-1”.

Outra possibilidade interessante da CLR é criar funções tabulares para obter sequência de valores sem a necessidade de utilizar muitas linhas de código ou lógicas complexas com tabelas temporárias ou CTE.

O principal diferencial das funções tabulares (que retornam resultado em formato de “tabela”) e as funções escalares (que retornam um único valor), é o uso de um método auxiliar para formatação dos valores, referenciado pela propriedade FillRowMethodName (exemplo, Int32Fill) e a definição da estrutura do resultado por meio da propriedade TableDefinition.

As Listagem 4 e Figura 22, demonstram o código e a execução de uma função tabular.

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

public partial class UserDefinedFunctions
{
    [SqlFunction(Name = "RangeCLR"
        , FillRowMethodName = "Int32Fill"
        , TableDefinition = "Value INT")]
    public static IEnumerable RangeCLR(SqlInt32 Value)
    {
        return Value.IsNull || Value < 1
            ? null
            : Enumerable.Range(1, Value.Value);
    }

    public static void Int32Fill(object obj, out SqlInt32 value)
    {
        value = (int)obj;
    }
};

Listagem 4. Exemplo de função tabular que retorna uma sequencia de valores iniciando de 1 até um valor informado.

Figura 22. Resultado da função tabular que retorna uma sequencia de valores iniciando de 1 até um valor informado.

Também é possível desenvolver funcionalidades que podem ser desenvolvidas de maneiras mais simples com CLR, como funções de validação de CPF e CNPJ.

Outra funcionalidade que pode justificar a utilização de CLR é a possibilidade de criar funções de agregação, como recuperar o primeiro ou último registro de uma agregação ou concatenar textos.

Na Figura 23 e Listagem 5, uma função de agregação desenvolvida em CLR, que possui resultado semelhante à função COUNT nativa do SQL Server, permitindo customizações para o desenvolvimento de soluções mais específicas.

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

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountCLR
{
    public SqlInt32 Ticks { get; set; }

    public void Init() { Ticks = 0; }

    public void Accumulate(SqlInt32 Value) { Ticks += 1; }

    public void Merge(CountCLR Group) { if (!Group.Ticks.IsNull) Ticks += Group.Ticks; }

    public SqlInt32 Terminate() { return Ticks; }
}

Listagem 5. Exemplo de função de agregação semelhante à função COUNT.

Figura 23. Execução da função COUNT e da função de agregação desenvolvida em CLR.

Tanto as funções de agregação, quanto os tipos CLR são desenvolvidos por meio de uma struct serializável. Isso quer dizer que os valores durante são armazenados em memória, e no caso dos tipos CLR, também em disco, no formado binário. Este formato binário e até mesmo a forma de serialização podem ser customizadas, ou para armazenar até o limite de um tipo varbinary(max) a partir do SQL Server 2008 (ou varbinary(8000) no SQL Server 2005) ou até mesmo, se realmente for necessário, expandir este armazenamento de forma distribuída (Web Services, objetos COM ou arquivos).

Ao término do processo de desenvolvimento de sua solução CLR, passando para a instalação do assembly em um servidor de produção, serão necessários alguns ajustes das configurações no projeto.

Então, clique com o botão direito sobre o projeto e abra as propriedades do projeto.

Conforme a Figura 24, altere a referência do banco de dados para o banco de dados de produção na aba Database, propriedade Connection String, botão Browse….

Figura 24. Propriedades de um projeto SQL CLR, aba Database.

Nesta aba, também será possível ajustar os aspectos de segurança do Assembly (SAFE, EXTERNAL e UNSAFE) e o dono (owner) deste Assembly.

Ajustadas as referências ao banco de dados de produção, altere o modo de compilação do Visual Studio para Release, conforme a Figura 25. Por fim, execute o Deploy do projeto, de acordo com a Figura 26.

Figura 25. Ajustando o modelo de compilação do Visual Studio.

Figura 26. Executando o Deploy do projeto após as configurações para o ambiente de produção

A Figura 27 demonstra que é possível encontrar no banco de dados o assembly após a instalação, assim como as stored procedures, funções, tipos e triggers desenvolvidas por meio deste.

Figura 27. Assembly e objetos CLR publicados no SQL Server.

A partir de algumas catalog views, será possível obter informações dos assemblies no SQL Server, conforme relacionadas na Tabela 1.

Catalog View Descrição
sys.assemblies Informações   básicas sobre os assemblies existentes no banco de dados.
sys.assembly_files Informações sobre assemblies formados   por um ou mais arquivos.
sys.assembly_references Informações   sobre assemblies que possuem alguma forma de referência a outros assemblies.
sys.assembly_modules Informações de objetos desenvolvidos a   partir de assemblies. Passível de obter maiores informações em conjunto com   outras views do sistemas, como sys.objects.
sys.assembly_types

sys.type_assembly_usages

Informações   sobre os tipos CLR e seus respectivos assemblies.
sys.dm_os_workers

sys.dm_clr_tasks

sys.dm_clr_appdomains

Úteis para obter informações sobre a   execução de assemblies no SQL Server.

Tabela 1. Relação de catalog views que possuem informações sobre assemblies no SQL Server.

A Figura 28 segue com uma demonstração de como acompanhar a execução de seus assemblies.

Figura 28. Modelo de código para acompanhar a execução de assemblies de um determinado banco de dados.

Conclusão

Este artigo permitiu uma visão geral do desenvolvimento com a integração CLR. Considerando o entendimento de como o SQL Server trabalha com a CLR para execução de rotinas desenvolvidas em C# e VB.NET, quando é recomendável e como utilizar desta integração para desenvolver rotinas, e por fim, como administrar o que foi desenvolvido.

Mesmo entendendo como a integração CLR funciona e conhecendo seu potencial, é importante estar ciente de quando desenvolver funcionalidades. Mas não deixando de lado, a necessidade de realizar testes unitários e de stress para evitar problemas em ambientes de produção e conhecer os limites da utilização do que foi desenvolvido.

Por se tratar de uma funcionalidade que combina várias tecnologias em evolução, a cada versão do SQL Server, do Visual Studio e do .NET Framework, mais recursos são disponibilizados. Recursos estes, que facilitam o desenvolvimento e ampliam o leque de possibilidades para novas soluções. Sendo uma das melhorias esperadas para o SQL Server 11 “Denali”, a integração com o .NET Framework 4.

Query From Hell? – SQL Server Spatial Data e um cubo 3D

Quando lançaram a feature de Spatial Data do SQL Server fiquei impressionado com as possibilidades de trabalhar com tipos geométricos e geográficos no SQL Server, principalmente pelo fato de que naquele ano, eu estava na equipe de desenvolvimento de um projeto de geolocalização e roteirização, onde usávamos bastante features como Spatial Data e aplicações com mapas (Web e Mobile).

Só que desde aquela época, via que além das propriedades X e Y, existia a propriedade Z, que não afetava em nada os métodos para calculo de distancia e outros. Pois, logo que existe X Y Z, seria possível gravar dados geométricos não somente 2D no banco de dados, mas também 3D… Mas não, a propriedade Z realmente não permitia isso.

Para matar minha vontade de gerar “algo em 3D” no SQL Server, peguei uma biblioteca de uma aplicação Windows Forms do site (Visual C# Kicks – http://www.vcskicks.com), adequei a biblioteca para ser SAFE no SQL Server e trabalhar com Spatial Data, assim criei um pequeno monstro:

Infelizmente não consegui implementar 100% o algoritmo de rotação do objeto 3D que funcionou perfeitamente na aplicação Windows (http://www.vcskicks.com/3d-graphics-improved.php). Quem quiser conhecer a biblioteca modificada, pode conferi-la abaixo:

Continuar lendo

Trabalhando com SQL CLR e Spatial Data

[UPDATE: 2011-08-07] Se você se deparou com este artigo e não sabe o que é SQL CLR ou Spatial Data, dê uma olhada antes nos links abaixo:

Trabalhando com SQL CLR – Resumo (Introdução, Stored Procedures, Funções, Tipos, WebService)

Spatial Data: Introdução aos tipos geométricos

Spatial Data: STGeomFromText – Árvore de Natal

Spatial Data: Envolvendo e cercando polígonos

Spatial Data: Desmembrando Polígonos!?

Spatial Data: Uniões e Intersecções

[/UPDATE]

Foi me apresentado um cenário com a utilização de Spatial Data do SQL Server 2008, só que com um pequeno problema… Havia algumas funções e procedure com cálculos complexos que demoravam muito para retornar os dados que o cenário precisava. Assim, propus a utilização de funções CLR para fazer estes cálculos (e alguns acessos a webservices que eles precisavam), dai surgiu outro problema… Como utilizar SQL CLR com Spatial Data?

O primeiro passo foi adicionar o Assembly “Microsoft.SqlServer.Types” ao projeto CLR:

Desta forma, se tornou praticável a utilização de Spatial Data (SQL CLR nativo) no projeto SQL CLR de forma bem transparente, ex.:

[SqlFunction]
public static SqlGeometry Buffer5(SqlGeometry geo)
{
    return geo.STBuffer(5);
}

Mas ai outro problema surgiu, como criar pontos, linhas e linhas com Spatial Data no CLR? Assim começou a busca por entender o funcionamento da classe SqlGeometryBuilder:

Gerando um ponto:

[SqlFunction]
public static SqlGeometry GetPoint()
{
    // -- Instanciando o objeto
    var g = new SqlGeometryBuilder();

    // -- Definido o SRID
    g.SetSrid(0);

    // -- Iniciando o geométrico do tipo Ponto
    g.BeginGeometry(OpenGisGeometryType.Point);

    // -- Ponto inicial da figura
    g.BeginFigure(1, 1);

    // -- Término da figura
    g.EndFigure();

    // -- Término do tipo geométrico
    g.EndGeometry();

    // -- Recuperando o tipo geométrico gerado
    return g.ConstructedGeometry;
}

// -- OU

[SqlFunction]
public static SqlGeometry GetPointSimple()
{
    return SqlGeometry.Point(1, 1, 0);
}

Gerando uma linha:

[SqlFunction]
public static SqlGeometry GetLine()
{
    var g = new SqlGeometryBuilder();

    g.SetSrid(0);

    g.BeginGeometry(OpenGisGeometryType.LineString);

    // -- Ponto inicial da figura
    g.BeginFigure(1, 1);

    // -- Adicionando ponto seguinte da figura
    g.AddLine(50, 50);

    g.EndFigure();

    g.EndGeometry();

    return g.ConstructedGeometry;
}

Gerando um polígono:

[SqlFunction]
public static SqlGeometry GetSquare()
{
    var g = new SqlGeometryBuilder();

    g.SetSrid(0);

    g.BeginGeometry(OpenGisGeometryType.Polygon);

    // -- Ponto inicial da figura
    g.BeginFigure(0, 0);

    // -- Adicionando pontos seguintes da figura
    g.AddLine(0, 10);
    g.AddLine(10, 10);
    g.AddLine(10, 0);

    // -- Adicionando ponto inicial para fechar o polígono
    g.AddLine(0, 0);

    g.EndFigure();

    g.EndGeometry();

    return g.ConstructedGeometry;
}

Gerando um multiponto:

[SqlFunction]
public static SqlGeometry GetPoint2()
{
    var g = new SqlGeometryBuilder();

    g.SetSrid(0);

    // -- Iniciando o geométrico do tipo Multiponto
    g.BeginGeometry(OpenGisGeometryType.MultiPoint);

    // -- Iniciando o primeiro ponto
    {
        g.BeginGeometry(OpenGisGeometryType.Point);

        g.BeginFigure(1, 1);

        g.EndFigure();

        g.EndGeometry();
    }

    // -- Iniciando o segundo ponto
    {
        g.BeginGeometry(OpenGisGeometryType.Point);

        g.BeginFigure(2, 1);

        g.EndFigure();

        g.EndGeometry();
    }

    g.EndGeometry();

    // -- Recuperando o tipo geométrico gerado
    return g.ConstructedGeometry;
}

Por fim o resultado de cada função:

Trabalhando com SQL CLR – Resumo

Então pessoas, uma das features que mais gosto do SQL Server é o suporte a CLR, que permite muitas possibilidades na criação de Stored Procedures, Funções, Types e Triggers com as linguagens C# e Visual Basic, e outras linguagens .NET, como o Delphi e F#.

No blog, busquei explorar de forma introdutória como trabalhar com esta feature e uma visão geral de cada objeto que é possível desenvolver. Em paralelo também tratei algumas extensibilidades, como trabalhar como F#, Web Services, referenciar projetos de terceiros nos projetos CLR e novidades no lançamento do Visual Studio 2010.

Introdução:

Este é um passo-a-passo de como iniciar com SQL CLR, desde questões mais á nível de SQL Server, até questões á nível de desenvolvimento (debug e publicação).

Integração SQL CLR no SQL Server – Uma Visão Geral [NOVO]

https://sqlfromhell.wordpress.com/2012/04/20/integracao-sql-clr-no-sql-server-visao-geral/

Habilitando CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/09/19/habilitando-clr

Criando funções CLR para o SQL Server:

https://sqlfromhell.wordpress.com/2009/09/26/funcoes-clr

Debug de funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/03/debug-clr

Publicando Assemblies e Funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/10/publicando-clr

Publicando Assemblies no SQL Server pelo Visual Studio:

https://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio

Visão Geral:

Para quem quer conhecer cada um dos objetos que permitem desenvolvimento na integração SQL CLR, elaborei uma visão geral orientada á muitos exemplos de cada um.

Stored Procedure:

https://sqlfromhell.wordpress.com/2010/07/03/clr-stored-procedure

https://sqlfromhell.wordpress.com/2010/07/13/clr-stored-procedure-2

User Defined Function Scalar:

https://sqlfromhell.wordpress.com/2010/07/25/trabalhando-com-clr-user-defined-function-scalar

Table-value User Defined Functions:

https://sqlfromhell.wordpress.com/2010/12/02/sql-clr-table-value-user-defined-functions

User Defined Aggregate Functions:

https://sqlfromhell.wordpress.com/2010/12/14/user-defined-aggregate-functions

User Defined Type:

https://sqlfromhell.wordpress.com/2010/12/22/sql-clr-user-defined-type

Triggers:

https://sqlfromhell.wordpress.com/2010/12/31/sql-clr-trigger

Estendendo:

Atendendo alguns pedidos e uma “aventura” pessoal (F#).

Web Services:

https://sqlfromhell.wordpress.com/2010/03/07/webservice-e-funes-clr-no-sql-server

F# no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/17/fsharp

Referenciando uma Class Library em Projetos SQL-CLR (Draft):

https://sqlfromhell.wordpress.com/2009/09/30/crl-classlibrary

Trabalhando com SQL CLR e Spatial Data [NOVO]

https://sqlfromhell.wordpress.com/2011/07/27/trabalhando-com-sql-clr-e-spatial-data/

Novidades:

Como com cada lançamento do Visual Studio temos o suporte SQL CLR permitindo a utilização das novas .NET Framework, mostrei que no caso do Visual Studio 2010 tivemos até algumas novidades mais interessantes.

Novidades do Visual Studio 2010:

https://sqlfromhell.wordpress.com/2010/05/16/o-que-mudou-nos-projetos-sql-clr-do-visual-studio-2010

Trabalhando com SQL CLR: Trigger

Neste último artigo do ano, trataremos o desenvolvimento de trigger em CLR, que basicamente são gatilhos disparados quando há alguma inserção, atualização ou exclusão de dados de uma tabela.

Assim como qualquer implementação CLR, triggers em CLR também têm que ser justificáveis, pois assim como as tradicionais triggers em T-SQL, elas também fazem uso das estruturas INSERTED e DELETED para identificar o que foi realizado, isso quer dizer que são necessárias consultas ao banco de dados para recuperar estas informações, e consultas ao banco de dados é um processo que as triggers tradicionais fazem melhor que as desenvolvidas em CLR, já que não dependem de uma estrutura de conexão.

Recomendações feitas, vamos ao desenvolvimento da nossa trigger, o primeiro passo é criar uma tabela para os testes, no caso a tabela “Modelo”.

CREATE TABLE Modelo (
	Codigo INT IDENTITY PRIMARY KEY,
	Nome VARCHAR(40)
)

Criada a tabela, utilizamos o template de trigger do Visual Studio, definindo como Target a nossa tabela de teste, para responder os eventos de INSERT, UPDATE e DELETE.

Através da classe SqlContext é possível recuperar informações do contexto da trigger (TriggerContext) e descobrir qual é o tipo de ação/evento que a trigger esta respondendo (TriggerAction), e nesta implementação simples retornamos esta informação pelo já conhecido método Pipe.Send:

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

public partial class Triggers
{
    [SqlTrigger(Name = "TRG_Quinto"
        , Target = "Modelo"
        , Event = "FOR INSERT, UPDATE, DELETE")]
    public static void Quinto()
    {
        var action = SqlContext.TriggerContext
            .TriggerAction
            .ToString()
            .ToUpper();

        SqlContext.Pipe.Send(action);
    }
}

Após feito o deploy da trigger, basta testar:

INSERT INTO Modelo
SELECT Nome FROM dbo.Cliente

UPDATE Modelo
SET Nome = UPPER(Nome)

DELETE FROM Modelo

Para o segundo exemplo, desenvolveremos uma trigger que gravará em arquivos as alterações realizadas.

Visto que é necessário acesso a um recurso externo ao SQL Server, alteramos o Assembly para ter permissão a este recurso:

E também alteramos o banco de dados para permitir isso:

ALTER DATABASE MeuBancoDeDados SET TRUSTWORTHY ON

Assim nesta implementação, recuperaremos informações das estruturas INSERTED e DELETED em formato XML, em seguida salvamos estas informações em arquivos na pasta “C:\TEMP”:

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
    [SqlTrigger(Name = "TRG_Quinto"
        , Target = "Modelo"
        , Event = "FOR INSERT, UPDATE, DELETE")]
    public static void Quinto()
    {
        string result;

        var action = SqlContext.TriggerContext.TriggerAction;
        var guid = Guid.NewGuid();
        var date = DateTime.Today.ToString("yy_MM_dd");

        var pathFolder = string.Format(@"C:\TEMP\{0}", date);
        var path = string.Format(@"{0}\{1}_{2}.txt", pathFolder, action, guid);

        var query =
@"SELECT
  ISNULL(I.Codigo, D.Codigo) AS Codigo
, D.Nome AS Antes
, I.Nome AS Depois
, SUSER_NAME() AS Usuario
FROM INSERTED I
FULL OUTER JOIN DELETED D
ON I.Codigo = D.Codigo
FOR XML AUTO";

        using (var conn = new SqlConnection("Context Connection = True"))
        {
            conn.Open();

            var cmd = new SqlCommand(query, conn);

            result = (string)cmd.ExecuteScalar();
        }

        if (!Directory.Exists(pathFolder))
            Directory.CreateDirectory(pathFolder);

        File.WriteAllText(path, result);
    }
}

Após feito o deploy da trigger, testamos novamente:

INSERT INTO Modelo
SELECT Nome FROM dbo.UDF_Segundo()

UPDATE Modelo
SET Nome = UPPER(Nome)

DELETE FROM Modelo

E o resultado esperado:

Então pessoas, este foi o ultimo arquivo que eu preparei desta série sobre a integração CLR do SQL Server, permitindo uma visão geral desta integração. Agora fica por parte de vocês a criativadade para novas implementações.

Ainda não tive novas ideias para novos artigos sobre CLR SQL, mas se você tiver alguma dúvida ou sugestão, fique a vontade em comentar!

Feliz Ano Novo!!

Trabalhando com SQL CLR: User Defined Type

Já imaginaram criar seus próprios tipos para colunas, variáveis e parâmetros ou manipular objetos dentro do SQL Server? Com o suporte CLR do SQL Server isso é possível! Nativamente no SQL Server 2008 já encontramos muitos tipos desenvolvidos em CLR, como os spatial types (geometry, geography) e os tipos hierárquicos (hierarchyid), mas por onde começar?

O primeiro passo antes de desenvolver um tipo em CLR é justificar a utilização do mesmo, pois se não há interação com objetos COM, necessidade de comunicação com a rede ou realização de cálculos complexos, a necessidade de desenvolver seus próprios tipos CLR pode ser descartada em virtude dos tipos binários, que podem prover a necessidade de armazenar objetos serializados da aplicação, ou dos tipos XML, que podem prover necessidades de utilizar objetos serializados em XML e que permitem consultas com xquery e indexação.

Os tipos CLR são estruturas (ou struct), e tem as mesmas limitações das funções de agregação desenvolvidas em CLR para trabalhar com tipos por referência (exemplo: string e vetores), mas isso não limita sua utilização, somente pode deixar um pouco mais complexo o desenvolvimento.

Uma das vantágens destes tipos CLR é possibilidade de criar e acessar pelo SQL Server, atributos, propriedades, métodos e métodos estáticos customizados, estas estruturas podem ser desconhecidas para DBAs, mas são muito importantes para os desenvolvedores.

Abaixo um exemplo bem simples de um tipo CLR:

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

[Serializable]
[SqlUserDefinedType(Format.Native)]
public struct UDT_Point3D : INullable
{
    // Atributo
    private bool isNull;

    // Propriedade
    public double X { get; set; }

    public double Y { get; set; }

    public double Z { get; set; }

    public bool IsNull { get { return isNull; } }

    // Métodos
    public static UDT_Point3D Null
    {
        get { return new UDT_Point3D() { isNull = true }; }
    }

    public override string ToString()
    {
        return string.Format("{0};{1};{2}", X, Y, Z);
    }

    public static UDT_Point3D Parse(SqlString str)
    {
        var quarto = new UDT_Point3D();

        if (str.IsNull) return Null;

        var spt = str.ToString().Split(';');

        double aux;
        quarto.X = double.TryParse(spt[0], out aux) ? aux : 0;
        if (spt.Length > 1)
            quarto.Y = double.TryParse(spt[1], out aux) ? aux : 0;
        if (spt.Length > 2)
            quarto.Z = double.TryParse(spt[2], out aux) ? aux : 0;

        return quarto;
    }

    public static UDT_Point3D New() { return new UDT_Point3D(); }
}

Não se assustem com o método Parse ou com o método ToString, pois estes métodos são basicamente responsáveis para transformar um texto em no seu tipo CLR e o tipo CLR em um texto, sendo muito útil quando sua aplicação não é uma solução desenvolvida em .NET.

Existem duas formas comuns de utilizar estes tipos CLR no SQL Server, primeiramente a forma mais simples, trabalhando com texto (utilizando de forma transparente o método Parse):

DECLARE @Pnt UDT_Point3D
SET @Pnt = '1.01;21.2;21.7'

SELECT @Pnt.ToString() AS String
, @Pnt.X AS X
, @Pnt.Y AS Y
, @Pnt.Z AS Z
, @Pnt AS Bin
, CAST(@Pnt AS VARCHAR) AS String2

Ou criando seus próprios métodos estáticos para gerar uma instância de um objeto, como no caso do método “New”:

DECLARE @Pnt UDT_Point3D
SET @Pnt = UDT_Point3D::New()
SET @Pnt.X = 1.01
SET @Pnt.Y = 21.2
SET @Pnt.Z = 21.7

SELECT @Pnt.ToString() AS String
, @Pnt.X AS X
, @Pnt.Y AS Y
, @Pnt.Z AS Z
, @Pnt AS Bin
, CAST(@Pnt AS VARCHAR) AS String2

E quando houver necessidade de recuperar este tipo CLR em uma aplicação, basta adicionar a referência do projeto SQL CLR a esta aplicação, e da mesma forma que se trabalha com os outros tipos, podemos recuperar os tipos CLR com uma simples conversão, exemplo:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT CAST('1.01;21.2;21.7' AS UDT_Point3D)";
    var point = (UDT_Point3D)cmd.ExecuteScalar();
}

A partir de agora é questão de utilizar a criatividade ao identificar situações onde a utilização de tipos CLR é justificável no SQL Server.