Common Language Run-time Integration: crie e utilize métodos .NET em procedures

No desenvolvimento de uma aplicação que realize acesso a base de dados, geralmente a comunicação é unilateral. Ou seja, a aplicação conhece e realiza acessos ao banco de dados, enquanto que o banco de dados desconhece a aplicação.

Dependendo do nível de complexidade da aplicação, visando evitar a repetição de código, parte da lógica da aplicação (regras de negócio) pode ser encapsulada, geralmente em uma DLL.

Quando se conhece o negócio, é possível a manipulação e consulta dos objetos do banco de dados através de procedures e comandos SQL. Porém, quando o acesso a DLL encontra-se ofuscado ou criptografado, fica a questão: e se fosse possível utilizar um método de uma DLL em uma procedure?

Por mais exótica que aparente ser (e é) esta solução, sim, é possível consumir uma DLL e realizar a chamada de seus métodos a partir de uma procedure ou função no banco de dados. Para realizar este acesso, basta seguir os seguintes passos:

Primeiramente é necessário configurar o banco de dados habilitando o CLR e ativar o TRUSTWORTHY:

sp_configure 'clr enable', 1

GO

RECONFIGURE

ALTER DATABASE TreinamentoNET

SET TRUSTWORTHY ON

1 - Abra o Visual Studio e clique em: File > New > Project ...> Visual C# > Windows > Class Library

2 - No menu "Projeto", selecione a opção "Add New Item" e em seguida "Class". 

3 - Segue abaixo a classe utilizada neste exemplo:

using System;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
namespace ProcDotNet.Classes
{
    public class ProcedureDotNET
    {
        [SqlProcedure()]
        public static void InsertProduct(SqlString nome, SqlString observacao)
        {
            using (SqlConnection conn = new SqlConnection(@"Data Source = localhost\SQL; Initial Catalog = TreinamentoNET; User ID = sa ; Password = P@$$w0rd"))
            {
                SqlCommand objCommand = new SqlCommand();
                objCommand.Parameters.AddWithValue("@nome", nome);
                objCommand.Parameters.AddWithValue("@observacao", observacao);
                objCommand.CommandText = "INSERT INTO tbProduto (nome, observacao) VALUES (@nome, @observacao)";
                objCommand.Connection = conn;
                conn.Open();
                objCommand.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
}

Na classe acima, o método InsertProduct recebe dois parâmetros e realiza o insert em tabela. É importante lembrar que o projeto class library deve utilizar o framework .NET correspondente a versão suportada pelo SQL Server utilizado. No caso do SQL Server 2008 por exemplo, a versão do framework suportada é a 2.0

Após a compilação do projeto, é necessário registrar a DLL no banco de dados. Para isso, basta executar os scripts abaixo:

CREATE ASSEMBLY ProcedureDotNET

AUTHORIZATION dbo FROM 'C:\Users\bruno\Desktop\ProcDotNet\ProcDotNet.Classes\bin\Debug\ProcDotNet.Classes.dll'

WITH PERMISSION_SET = UNSAFE

GO

Após a execução do script acima, o SQL Server cria um assembly no banco de dados. A partir deste assembly é possível efetuar as chamadas aos métodos da DLL a partir de procedures e funções.
No exemplo abaixo, uma procedure é criada para receber os dois parâmetros requisitados pelo método e invocá-lo: 

CREATE PROCEDURE sp_insert_product

  @nome nvarchar(50),

  @observacao nvarchar(50)

AS EXTERNAL NAME ProcedureDotNET.[ProcDotNet.Classes.ProcedureDotNET].InsertProduct

GO

Feito isso, é chegada a hora de testar nossa criação. Ao executar a procedure enviando os parâmetros, as informações são persistidas na base de dados:

sp_insert_product 'Carro', '4 portas'

Ao realizar uma consulta na tabela, é possível verificar que tudo funciona perfeitamente e que os dados enviados para a procedure foram persistidos:

select * from tbProduto