Faz um longo tempo que tenho estudado e trabalhado com a integração CLR do SQL Server, o que tem me permitindo um bom aprendizado sobre o assunto, principalmente pela necessidade de “descobrir por si mesmo” quando problemas tornam a documentação escassa.

As possibilidades deste recurso são diversas, mas percebi um pouco de carência no que diz respeito a materiais e documentação para o desenvolvimento destas funcionalidades, principalmente quando nos aprofundamos um pouco mais no uso deste recurso.

Para transmitir um pouco deste conhecimento, vou tratar nos próximos artigos passo a passo como se cria funções (functions), procedimentos armazenados (stored procedures), tipos (types) e gatilhos (triggers). Mas antes vamos a uma introdução, na qual demonstrarei como habilitar esta integração e uma visão geral dos processos desenvolvimento como debug e deploy.

Habilitando a integração CLR no SQL Server

Para quem trabalhou e trabalha com o SQL Server 2005, já deve estar acostumado com o Surface Area Configuration, mas como esta ferramenta não existe nas versões pós-2005, então vamos aprender como habilitar a integração CLR por meio de script.

Basicamente o script é o seguinte:

sp_configure "clr enabled", 1 GO RECONFIGURE GO

Funcionamento da “sp_configure”

Antes de avançarmos com CLR, vamos entender como funciona a “sp_configure”. Esta stored procedure possui duas funcionalidades no SQL Server, a primeira é exibição das configurações da instancia do SQL Server e a segunda é a alteração das configurações.

Caso você execute o comando “EXEC sp_configure” ou simplesmente “sp_configure”, você terá como resultado uma consulta de todas as configurações não-avançadas do SQL Server. Mas você pode especificar qual configuração que deseja exibir, como demonstrado abaixo:

sp_configure "clr enabled" GO

E como resultado:

Repare que temos as seguintes colunas:

· Name: o nome da configuração propriamente dita.

· Minimum e Maximum: os valores mínimos e máximos desta configuração.

· Config_value e Run_value: os valores atuais desta configuração.

Mas como temos dois valores atuais para esta configuração? Para entendermos isso, precisaremos alterar o valor desta configuração, utilizando a mesma stored procedure, mas passando como argumento o nome da configuração e um valor válido.

sp_configure "clr enabled" GO sp_configure "clr enabled", 1 GO sp_configure "clr enabled" GO

Como resultado:

Repare que após a execução da procedure de alteração “sp_configure ‘clr enabled’, 1”, o valor da coluna config_value foi alterado para o mesmo valor o argumento da procedure de alteração. Isso quer dizer que após a execução desta procedure de alteração, o SQL Server simplesmente entendeu que as configurações atuais do servidor serão as que você passou como argumente, mas ainda não aplicará aos processos atuais da instância, assim ainda não será possível utilizar a integração CLR.

Para aplicar estas configurações será necessário chamar pós a procedure de alteração o “RECONFIGURE”, adicionando ao exemplo anterior as seguintes linhas:

RECONFIGURE GO sp_configure "clr enabled" GO

Como resultado, teremos o SQL Server habilitado com a nova configuração.

Sobre os acessos para alterar as configurações

Caso não tenha conseguido configurar a integração CLR, verifique se seu usuário possui permissão “ALTER SETTINGS” ou esta na roles de “sysadmin” ou “serveradmin”. Mesmo que a integração CLR somente pode ser habilitada com as permissões anteriores, lembre-se que caso você dê este nível de acesso a um usuário, você estará dando acesso completo ao SQL Server. Então se for necessário que o usuário utilize desta integração, deixe-a previamente configurada.

Sobre a visualização das configurações

Uma das configurações interessantes é a “show advanced option” que permite trabalhar com opções avançadas com a procedure “sp_configure”.

Para visualizar as configurações também é possível utilizar a system view/catalog “sys.configurations”, que possui descrições sobre as configurações.

SELECT * FROM sys.configurations

Forçando configurações não recomendadas

Caso deseje passar um valor não recomendado para alguma das configurações do servidor, é possível chamar “RECONFIGURE WITH OVERRIDE”, mas tome muito cuidado.

Habilitando permissões para UNSAFE e EXTERAL_ACCESS

Caso precise trabalhar com assemblies CLR com opções UNSAFE ou EXTERNAL_ACCESS, será necessário habilitar a configuração “TRUSTWORTHY” do banco de dados.

ALTER DATABASE [MeuBancoDeDados] SET TRUSTWORTHY ON GO

Criando funções CLR para o SQL Server

Configurada a integração CLR, vamos a um exemplo prático de como criar suas próprias funções CLR para o SQL Server.

Para este exemplo estarei utilizando o Visual Studio 2008 (executando como administrador, visto que o banco de dados que estarei me conectando esta na máquina local por Windows Authentication).

Agora vamos abrir o Visual Studio e criar um projeto a partir do template utilizado será o “Visual C#\Database\SQL Server Project”. Não esqueçamos que por padrão o nome do projeto será o nome do assembly (DLL) gerada para o SQL Server, então defina um nome adequado para esta finalidade.

Configuremos corretamente o banco de dados de desenvolvimento, não esquecendo de habilitar a integração CLR antes de mais nada.

Caso o banco de dados não existir, você pode criar um novo informando somente o nome do banco de dados. Se você tiver as permissões, ele será criado após confirmar a seguinte mensagem:

Caso aparecer uma mensagem de erro por falta de privilégios/acessos no SQL Server, este problema pode ser resolvido se você execute o Visual Studio como Administrador do sistema (se for Windows Authentication) ou utilizando um usuáriocom mais privilégios (se for SQL Authentication).

Para permitir visualizar a execução do Assembly em tempo de execução, você pode habilitar a opção de debug, ao aparecer a seguinte mensagem:

Mas evite realizar debug diretamente em um servidor utilizado por outras pessoas alem de você, principalmente se for um servidor de produção, visto a exclusividade que será dada pelo servidor ao seu Assembly quando estiver em debug.

No projeto (janela Soluntion Explorer), será possível identificar três referências padrões do projeto, primeiramente a System, responsável por todas as estruturas fundamentais da .Net Framework, a System.Data, responsável pelo ADO.Net, por fim a System.XML, responsável neste projeto por manipular os objetos na comunicação entre o Assembly CLR e o SQL Server.

Na janela Solution Explorer, teremos toda a estrutura de sua solução, como as classes CLR, referências e scripts para testes. Para criar uma função (user-defined function), cliquemos com o botão direito do mouse sobre o seu projeto e na opção Add, escolhemos o tipo de “item” a ser criado, no caso “User-Defined Function…” ou se preferir “New Item…” para ir à janela de seleção do “item”.

Na janela de criação de um novo item, caso não esteja selecionado, selecione o template que você utilizará (no caso, user-defined function) e informe o nome a ser utilizado pelo template:

Criado o nosso primeiro item, teremos a seguinte estrutura de código:

// As bibliotecas padrões using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; // A classe que será responsável por armazenar as funções desenvolvidas. public partial class UserDefinedFunctions {     // Função modelo.     [Microsoft.SqlServer.Server.SqlFunction]     public static SqlString Pitagoras()     {         return new SqlString("Hello");     } };

Para demonstrar o comportamento das funções CLR no SQL, a partir da função modelo, desenvolvi um método que recebe como argumento duas variáveis SqlDouble, dentro do escopo do método transformei estas variáveis em variáveis do tipo double e realizei o calculo de Pitágoras sobre elas, retornando por fim o resultado deste cálculo como uma variável do tipo SqlDouble.

// As bibliotecas padrões using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; // A classe que será responsável por armazenar as funções desenvolvidas. public partial class UserDefinedFunctions {     // Função alterada.     [Microsoft.SqlServer.Server.SqlFunction]     public static SqlDouble Pitagoras(SqlDouble ab, SqlDouble bc)     {         double AB = ab.Value;         double BC = bc.Value;         double AC = Math.Sqrt((AB * AB) + (BC * BC));         return new SqlDouble(AC);     } };

Publicando o assembly no SQL Server

Para realizar a publicação de seu assembly CLR no servidor, clique com o botão direito sobre o projeto, em seguida clique na opção “Properties”.

Na aba “Database”, você terá a connection string utilizada para conectar ao banco de dados, o nível de permissão utilizada pelo assembly (no caso, SAFE) e um espaço para definir o usuário do banco de dados responsável pelo assembly (como padrão “dbo”).

Realizado os ajustes clique novamente com o botão direito sobre o projeto e chame a opção “Deploy”.

Agora conecte-se no seu banco de dados e execute o seguinte comando SQL:

SELECT dbo.Pitagoras(3,2) AS Resultado;

Se tudo foi executado corretamente, você terá como resultado:

Debug de CLR no SQL Server

Continuando com o mesmo exemplo anterior, colocarei um breakpoint na linha “double AB = ab.Value”.

Para quem não conhece o breakpoint, ele é basicamente uma ferramenta utilizada para pausar a execução do código, no momento em que a instrução será passada para o servidor, tornando possível observar os estados das variáveis naquele “momento”.

Para inserir um breakpoint, basta clicar sobre a margem esquerda do código que aparecerá o breakpoint (bolinha vermelha), como no exemplo abaixo:

Na pasta “Test Scripts” do projeto, existe um arquivo SQL (ou você pode criar o seu), o qual eu alterarei para executar o seguinte comando SQL:

SELECT dbo.Pitagoras(3,2) AS Resultado;

Após salvar as alterações, executarei o debug deste arquivo clicando com o botão esquerdo sobre o mesmo e chamando o item “Debug Script”, conforme a figura abaixo.

Se você preferir, é possível optar pelo item “Set as Default Debug Script”, permitindo chamar a execução deste arquivo com o botão F5 ou clicando sobre o botão de debug (semelhante a um botão de “play” na cor verde).

Caso aparecer uma mensagem de erro por falta de privilégios/acessos no SQL Server, este problema pode ser resolvido se você executar o Visual Studio como Administrador do sistema (se a conexão for Windows Authentication) ou utilizando um usuário com mais privilégios (se a conexão for SQL Authentication).

Se houver algum bloqueio de seu firewall, você receberá um aviso, desta forma será necessário que selecionar uma das opções “Unblock remote debugging…”.

Feito todos os procedimentos, será possível acompanhar o debug da função no SQL Server, como observado na figura abaixo (para prosseguir utilize o botão F10 ou F11).

As mensagens e os resultados fornecidos pelo SQL Server podem ser verificadas na janela de Output, “Show output from: Debug”, conforme verificado abaixo.

Considerações finais

Se o calculo de Pitágoras estiver errado no meu exemplo, é porque devo ter faltado à aula de trigonometria na época do colégio. Para esta demonstração estive utilizando o SQL Server 2008 e o Windows 7, mas não encontrei incompatibilidades com o SQL Server 2005 e o SQL Server 2008 R2, assim como com outras versões do Windows suportadas por estas versões do SQL Server.

No próximo artigo, estarei trabalhando a estrutura básica dos procedimentos armazenados (Store Procedures) que servirão para entender como o Visual Studio estrutura o deploy (instalação) das funcionalidades que desenvolvemos em CLR para o SQL Server.

Referências:
http://sqlfromhell.wordpress.com/category/trabalhando-com-clr