Oi! Seja bem-vindo!

Hoje vamos trabalhar com CLR no SQL Server usando C#. Veremos 8 exemplos de código que incluem praticamente todos os cenários que você encontrará nas empresas.

O que é CLR?

CLR significa Common Runtime Language. De acordo com o Wikipedia, é o componente de máquina virtual da plataforma .NET da Microsoft que gerencia a execução de programas .NET. Um processo conhecido como compilação just-in-time converte o código compilado em linguagem de máquina que a CPU do computador executa.

 

É lógico que esse tipo de descrição não diz nada!! Então simplificando bastante: CLR do ponto de vista de programação, é um conjunto de rotinas desenvolvidas pela Microsoft que podemos usar através de linguagens como C# e VB.NET. Usar o framework da Microsoft é bom porque economiza trabalho, já que usamos coisas que já estão pré-prontas…

 

No mundo do SQL, é um código criado com linguagens .NET que podemos usar diretamente do SQL.

 

Porque usar CLR no SQL?

Por mais incrível que seja a plataforma SQL, ela não tem a flexibilidade de linguagens .NET, ou seja, no .NET podemos fazer qualquer coisa em termos de programação!! Logo, aliar o poder do SQL para trabalhar com dados, a flexibilidade de linguagens .NET, transforma o SQL em uma plataforma onde você pode fazer qualquer coisa!

 

Como usar CLRs no SQL Server

Código SQL utilizado

[sociallocker id=5114]

------------------------------------------------------------------
-- CLRs (Common Runtime Language) no SQL Server
------------------------------------------------------------------
--------------------------------------------------
-- Requisitos para nosso teste
--------------------------------------------------
-- Banco de testes
use master
if db_id('curso') is not null
	drop database curso
go
create database curso
go
use curso
go

-- Tabela de testes
create table colaboradores (id smallint identity(1, 1), nome varchar(200), salario money)
insert into colaboradores (nome, salario) values
	('João', 20000),
	('Maria', 20000),
	('José', 20000)

--------------------------------------------------
-- Criando CLRs
--------------------------------------------------
/*
-- Notícias boas sobre C#!
C# é fácil de aprender porque tem muita documentação na internet!
Para compilar suas DLLs você não precisa nada além do próprio SQL
A DLL que eu criei possui tudo que você precisa para começar

-- Para compilar uma DLL C# usando dotnet framework 4.0:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc /nologo /target:library /out:minhas_clrs.dll minhas_clrs.cs

-- DICA:
No C# TUDO é CASE SENSITIVE! Cuidado!

*/

--------------------------------------------------
-- Registrando CLRs
--------------------------------------------------
-- Ativar trustworthy database (requisito para CLRs unsafe e external_access sem assinatura)
alter database curso set trustworthy on

-- Ativar CLRs no SQL
execute sp_configure 'show advanced options',1
reconfigure
execute sp_configure 'clr enabled', 1
reconfigure

-- Registrar a DLL (assembly) no SQL:
if exists (select 1 from sys.assemblies where name = 'minhas_clrs') drop assembly minhas_clrs
create assembly minhas_clrs from 'c:\tmp\minhas_clrs.dll' with permission_set = unsafe
go

-- Criar as SPs e FCs de acesso:
create procedure usp_msg_texto @msg nvarchar(max) as external name minhas_clrs.myclr.msg_texto
go
create procedure usp_msg_grid @msg nvarchar(max) as external name minhas_clrs.myclr.msg_grid
go
create procedure usp_soma_folha (@soma money output) as external name minhas_clrs.myclr.soma_folha
go
create procedure usp_colaboradores as external name minhas_clrs.myclr.lista_colaboradores
go
create procedure usp_cidades_pais @nome_pais nvarchar(200) as external name minhas_clrs.myclr.cidades_por_pais
go
create function ufc_msg(@msg nvarchar(max)) returns nvarchar(max) as external name minhas_clrs.myclr.ufc_msg;
go
create function ufc_versaosql() returns nvarchar(max) as external name minhas_clrs.myclr.versao_sql;
go
create function ufc_converte_temperatura(@temperatura nvarchar(8), @de nvarchar(20), @para nvarchar(20)) returns nvarchar(max) as external name minhas_clrs.myclr.converter_temperatura
go

--------------------------------------------------
-- Utilizando CLRs
--------------------------------------------------
-- sp: Recebe texto
execute usp_msg_texto 'Procedure: msg em texto'

-- sp: Recebe grid
execute usp_msg_grid 'Procedure: msg em grid'

-- fc: Recebe grid ou texto
select dbo.ufc_msg('Function: msg em grid ou texto - control+d e control+t')

-- fc: Recebe texto ou grid com informações do próprio SQL
select dbo.ufc_versaosql() as versao

-- sp: Recebe texto + grid
execute usp_colaboradores

-- sp: Envia nulo | recebe variável via output
declare @soma_folha money
execute usp_soma_folha @soma_folha out
print @soma_folha

-- fc: Recebe resultado de webservice em grid (degreeCelsius, degreeFahrenheit, degreeRankine, degreeReaumur, kelvin)
select dbo.ufc_converte_temperatura ('100', 'degreeCelsius', 'kelvin') as c_k
select dbo.ufc_converte_temperatura ('373.15', 'kelvin', 'degreeCelsius') as k_c

-- sp: Recebe resultado de webservice em grid
execute usp_cidades_pais 'Brazil'
execute usp_cidades_pais 'Sri Lanka'
execute usp_cidades_pais 'United Kingdom'

--------------------------------------------------
-- Consultando CLRs
--------------------------------------------------
-- Assemblies
select * from sys.assemblies

-- Assemblies e dependências
select
	schema_name(so.schema_id) as schema_name,
	af.name as file_name,
	a.clr_name,
	a.name as assembly_name,
	am.assembly_class,
	am.assembly_method,
	so.object_id as object_id,
	so.name as object_name,
	so.type as object_type,
	a.permission_set_desc,
	a.create_date
from sys.assemblies a
inner join sys.assembly_modules am on a.assembly_id = am.assembly_id
inner join sys.assembly_files af on a.assembly_id = af.assembly_id
inner join sys.objects so on so.object_id = am.object_id

--------------------------------------------------
-- Excluindo dados de teste
--------------------------------------------------
go
drop procedure usp_msg_texto, usp_msg_grid, usp_soma_folha, usp_colaboradores, usp_cidades_pais
drop function ufc_msg, ufc_versaosql, ufc_converte_temperatura
drop assembly minhas_clrs

use master
drop database curso
<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

[/sociallocker] 
Código C# utilizado
[sociallocker id=5114]
//
// Classe para testes de CLR no SQL Server 2016
// Desenvolvida por Josué Viana - https://dba-pro.com - em 12/2017
// Licença de uso GNU (para usar é preciso mensionar a fonte/autor)
// 

using System;
// sql
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
// web
using System.Net;
using System.Web;
// msdos
using System.IO;
using System.Diagnostics;
using System.Text;
// xml
using System.Xml;

public class myclr
{
	// Procedure: retornando em texto
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void msg_texto(SqlString mensagem)
    {
        SqlContext.Pipe.Send(mensagem.Value);
    }

	// Procedure: retornando em grid
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void msg_grid(SqlString mensagem)
    {
		SqlDataRecord grid_retorno;
		grid_retorno = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("mensagem", SqlDbType.VarChar, -1)}); //varchar -1 = varchar(max)
		grid_retorno.SetString(0, mensagem.Value);
		SqlContext.Pipe.Send(grid_retorno);
    }

	// Function: retornando em grid ou texto
	[SqlFunction(
		DataAccess=DataAccessKind.None,
		SystemDataAccess=SystemDataAccessKind.None,
		IsDeterministic=true,
		IsPrecise=true)]
	public static string ufc_msg(string mensagem)
	{
		return mensagem;
	}

	// Function: acessando o SQL para retornar dados obtidos de um select no SQL
	[SqlFunction(
		DataAccess=DataAccessKind.Read,
		IsDeterministic=true,
		IsPrecise=true)]
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static string versao_sql()
	{
		using(SqlConnection conexao = new SqlConnection("context connection=true"))
		{
			conexao.Open();
			SqlCommand comando = new SqlCommand("select @@version", conexao);
			return (string)comando.ExecuteScalar();
		}
	}

	// Procedure: Retorno de mensagem em texto + grid de resultados obtido do SQL
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void lista_colaboradores()
	{
		SqlContext.Pipe.Send("Início processamento: " + System.DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff")+"\n");
		using(SqlConnection conexao = new SqlConnection("context connection=true"))
		{
		 conexao.Open();
		 SqlCommand comando = new SqlCommand("select * from colaboradores", conexao);
		 SqlDataReader grid_resultado = comando.ExecuteReader();
		 SqlContext.Pipe.Send(grid_resultado);
		}
		SqlContext.Pipe.Send("Fim processamento: " + System.DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.fff")+"\n");
	}

	// Procedure: soma de valores / trabalha com resultset / retorna variável como output
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void soma_folha(out SqlMoney value)
	{
		using(SqlConnection conexao = new SqlConnection("context connection=true"))
		{
			value = 0;
			conexao.Open();
			// Deixei "select *" para demonstrar a soma de valores com inumeras colunas. O ideal seria "select salario from colaboradores" dentro de uma função
			SqlCommand comando = new SqlCommand("select * from colaboradores", conexao);
			SqlDataReader grid_resultado = comando.ExecuteReader();
			// Para cada item do resultado somo o valor da coluna 2 (coluna 0: id, coluna 1: nome, coluna 2: salario)
			using (grid_resultado)
			{
				while( grid_resultado.Read() )
				{
					value += grid_resultado.GetSqlMoney(2);
				}
			}
		}
	}

	// Function: Recebe parâmetros, chama webservice, devolve resultset a partir do XML do WS
	[SqlFunction(
		DataAccess=DataAccessKind.Read,
		IsDeterministic=true,
		IsPrecise=true)]
    public static string converter_temperatura (string temperatura, string de, string para)
    {
        // Executa o GET do webservice (ex: http://www.webservicex.net/ConvertTemperature.asmx/ConvertTemp?Temperature=100&FromUnit=degreeCelsius&ToUnit=kelvin)
		HttpWebRequest requisicao = (HttpWebRequest)WebRequest.Create("http://www.webservicex.net/ConvertTemperature.asmx/ConvertTemp?Temperature=" + temperatura + "&FromUnit=" + de + "&" + "ToUnit=" + para);
        requisicao.Method = "GET";
        requisicao.ContentLength = 0;
        requisicao.Credentials = CredentialCache.DefaultCredentials;
        HttpWebResponse resposta = (HttpWebResponse)requisicao.GetResponse();

		// Lê o retorno que volta em XML
        StreamReader leitura_retorno = new StreamReader(resposta.GetResponseStream(), Encoding.UTF8);
		String xml_retorno = System.Net.WebUtility.HtmlDecode(leitura_retorno.ReadToEnd());

		// Fecha conexão
		resposta.Close();
        leitura_retorno.Close();

		// String de testes caso o WS não esteja funcionando:
		//String xml_retorno = "<double xmlns=\"http://www.webserviceX.NET/\">373.15</double>"

		// Lê o XML e retorna apenas o valor da temperatura convertida ao invés do XML inteiro retornado pelo WS
		XmlDocument xmldoc = new XmlDocument();
        xmldoc.LoadXml(xml_retorno);
		string temperatura_convertida = xmldoc.LastChild.InnerText;

		// Retorna temp convertida
		return (temperatura_convertida);
    }

	//Procedure: Recebe parâmetro, acessa WS e devolve resultset
	[Microsoft.SqlServer.Server.SqlProcedure]
    public static void cidades_por_pais(string nomepais)
    {
		// Get do ws (ex: http://www.webserviceX.NET/globalweather.asmx/GetCitiesByCountry?CountryName=Brazil)
		HttpWebRequest requisicao = (HttpWebRequest)WebRequest.Create("http://www.webserviceX.NET/globalweather.asmx/GetCitiesByCountry?CountryName=" + nomepais);
		requisicao.Method = "GET";
		requisicao.ContentLength = 0;
		requisicao.Credentials = CredentialCache.DefaultCredentials;
		requisicao.ContentType = "application/xml";
		requisicao.Accept = "application/xml";
		HttpWebResponse resposta = (HttpWebResponse)requisicao.GetResponse();
		StreamReader leitura_retorno = new StreamReader(resposta.GetResponseStream());
		String xml_retorno = System.Net.WebUtility.HtmlDecode(leitura_retorno.ReadToEnd());
		resposta.Close();
		leitura_retorno.Close();

		// Se o webservice acima estiver fora do ar ou tiver mudado o layout, comente o código acima e use a string fixa abaixo para fins de teste
		// Com namespace
		//String xml_retorno = "<string xmlns=\"http://www.webserviceX.NET\"><NewDataSet>
<Table> <Country>Sri Lanka</Country> <City>Katunayake</City> </Table>
<Table> <Country>Sri Lanka</Country> <City>Anuradhapura</City> </Table>
<Table> <Country>Sri Lanka</Country> <City>Batticaloa</City> </Table>
<Table> <Country>Sri Lanka</Country> <City>Ratmalana</City> </Table>
<Table> <Country>Sri Lanka</Country> <City>Trincomalee</City> </Table>

 </NewDataSet></string>";
		// Sem namespace
		//String xml_retorno = "<string><NewDataSet>
<Table><Country>Brazil</Country><City>Conceicao Do Araguaia</City></Table>
<Table><Country>Brazil</Country><City>Afonsos Aeroporto</City></Table>

</NewDataSet></string>";
		// debuginfo (só funciona para xmls menores que 4000 chars)
		//SqlContext.Pipe.Send(xml_retorno);

		// Carrega retorno do ws como xmldocument
		XmlDocument xmldoc = new XmlDocument();
		xmldoc.LoadXml(xml_retorno);
		// Inclui namespace para gerenciamento do xml. Se não tivesse o xmlns não precisaria
		XmlNamespaceManager xml_namespace = new XmlNamespaceManager(xmldoc.NameTable);
		xml_namespace.AddNamespace("wsx", "http://www.webserviceX.NET");

		// Obtem o nó root (nesse exemplo chama-se "string")
		XmlNode no_root_string = xmldoc.DocumentElement;
		// debuginfo (só funciona para xmls menores que 4000 chars)
		//SqlContext.Pipe.Send(no_root_string.OuterXml);

		// Cria resultset a ser retornado
		SqlDataRecord registro = new SqlDataRecord(
			new SqlMetaData("Country", SqlDbType.NVarChar, 200),
			new SqlMetaData("City", SqlDbType.NVarChar, 200));

		// Inicio do retorno de dados
		SqlContext.Pipe.SendResultsStart(registro);

		// Navega nos filhos do nó root "String" (nós filhos: <NewDataSet>)
		foreach(XmlNode no_root_newdataset in no_root_string.ChildNodes)
		{
			// debuginfo (só funciona para xmls menores que 4000 chars)
			//SqlContext.Pipe.Send(no_root_newdataset.OuterXml);

			// Navega nos filhos do nós newdataset
			foreach(XmlNode no_xml in no_root_newdataset.ChildNodes)
			{
				// Debuginfo (só funciona para xmls menores que 4000 chars)
				//SqlContext.Pipe.Send(no_xml.OuterXml);

				// Lê o nó xml (sem namespace)
				//registro.SetSqlString(0, no_xml.SelectSingleNode("Country").InnerText);
				//registro.SetSqlString(1, no_xml.SelectSingleNode("City").InnerText);

				// Lê o nós xml (com namespace)
				registro.SetSqlString(0, no_xml.SelectSingleNode("wsx:Country", xml_namespace).InnerText);
				registro.SetSqlString(1, no_xml.SelectSingleNode("wsx:City", xml_namespace).InnerText);

				// Retorna + 1 linha
				SqlContext.Pipe.SendResultsRow(registro);
			}
		}
		// Finaliza o retorno de dados
		SqlContext.Pipe.SendResultsEnd();
    }
}

[/sociallocker] 
CONCLUSÃO
Como vimos, usar CLRs no SQL abre portas incríveis para nós DBAs. Usar o bloco de notas para criar, em poucos minutos, uma integração via WEBSERVICE é realmente empolgante!! E com certeza é esse tipo de coisa que alavancará sua carreira e o destacará da multidão.
Já imaginou a agilidade que isso pode trazer no dia a dia de uma empresa? Para mim qualquer coisa boa que possamos fazer com o bloco de notas vale a pena aprender!! Espero que tenha gostado do artigo, nos vemos no próximo!
Abraço do seu amigo Josué 🙂