Criando funções CLR para o SQL Server

Olá pessoas!

Continuando a série de posts sobre a integração CLR, vamos a um exemplo prático de como criar suas próprias funções CLR para o SQL Server. Para esta demonstração, você necessitará do Visual Studio 2005 ou 2008, nas edições Professional ou superior.

 

Criando funções CLR para o SQL Server

Após abrir o Visual Studio, crie seu projeto conforme a figura abaixo:

Nesta demonstração estarei utilizando um projeto Visual C#, mas poderia ser feito com VB, C++ (gerenciado) ou J# (sem opção de debug diretamente no servidor). O template utilizado será o “Visual C#\Database\SQL Server Project”.

Depois de escolher o template informe o nome que o projeto terá, que será o mesmo do Assembly no servidor.

Configure corretamente o banco de dados que você se conectar. Não se esqueça de habilitar a configuração de integração com CLR do servidor com o qual você estará trabalhando (ver. Habilitando CLR no SQL Server).

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ário com 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.

Agora, na janela Solution Explorer, você terá 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), clique com o botão direito do mouse sobre o seu projeto e na opção Add, escolha o tipo de “item” que você deseja criar, 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á (user-defined function) e informe o nome a ser utilizado para sua função.

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 (equivalente ao float do SQL Server), 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.

    // 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 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:

Se o calculo estiver errado no meu exemplo, é porque devo ter faltado à aula de trigonometria na época do colégio.

Estes foram os passos necessários para desenvolver suas funções no SQL, utilizando CLR. Acredito que o post esteja bem detalhado e exemplificado. Mas qualquer dúvida ou comentário, estejam a vontade em me contatar.

No próximo post, estarei trabalhando como testar estas funções em tempo de execução (debug).

 

Post relacionado:

Habilitando CLR no SQL Server:
https://sqlfromhell.wordpress.com/2009/09/19/habilitando-clr/

13 pensamentos sobre “Criando funções CLR para o SQL Server

  1. Paulo,

    Estou com uma dúvida e até agora não encontrei nenhuma resposta.

    Criei um projeto do tipo SQLCLR e estou tentando adicionar um projeto já existente (do mesmo modo que já tenho em meu projeto WEB) chamado STM.

    O STM tem todas as classes que utilizo nos meu projetos. Sendo fundamental para esse novo projeto CLR.
    Porem não consigo adicioná-lo nas referências do SQLCLR.
    Mensagem: “A reference to ‘STM’ could not be added. SQL Server projects can reference only other SQL Server projects.”

    É possível realizar esta operação?
    Alguma alternativa?

    grato,
    GH

    • Guilherme,

      Realmente o Visual Studio proíbe este tipo de ação.

      Projetos SQL-CLR somente podem referenciar outros projetos do mesmo tipo, (obs.: mas eles podem ser referenciados em outros tipos de projetos) assim se STM for um projeto SQL-CLR não haverá incompatibilidade.

      Uma forma de contornar este problema, é criar um assembly no SQL Server manualmente para sua dll STM, sendo possível adicionar no seu projeto SQL-CLR a referência da STM no Visual Studio por meio do SQL Server.

      • Meu STM é um ClassLibrary.
        E foi feito dessa maneira pois tenho vários aplicativos (WEB, WebService, WinApp) que usam esse projeto.

        Ao meu ver o SQL-CLR deveria funcionar da mesma maneira.

        Vou pesquisar a opção do assembly.

        grato pela ajuda,
        GH

  2. Pingback: Debug de funções CLR no SQL Server « Sql From Hell.com

  3. Pingback: F# no SQL Server « Sql From Hell.com

  4. Pingback: WebService e funções CLR no SQL Server « Sql From Hell.com

  5. Pingback: WebService e funções CLR no SQL Server « Sql From Hell.com

  6. Pingback: Publicando Assemblies CLR no SQL Server pelo Visual Studio « Sql From Hell.com

  7. Pingback: O que mudou nos projetos SQL CLR do Visual Studio 2010? « Sql From Hell.com

  8. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

  9. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s