Augusto Humire Martinez web blog

algo de web-in

CLR en Sql Server

Esta vez comentaré acerca de mi experiencia con respecto de como trabajar con los ensablados en SQL Server  integrando una CLR que realiza llamadas a una base de datos Oracle

Muchas veces hemos tenido que lidiar con integracion no solo entre aplicaciones si no entre bases de datos, esta vez comentaremos una integración entre bases de datos.

Escenario:
Se requiere dibujar un reporte (generado desde Sql Reporting Services) cuyos datos se encuentran en SQL Server y Oracle, adicionalmente en el gestor de Sql Server existe un link (linked server) entre Sql Server y Oracle.

Problema:
La lentitud y la complejidad de las consultas hacen que el link que existe entre Sql Server y Oracle no sea veloz (es decir es lento), (la razon se desconoce, habra que preguntarle al equipo de SqlServer el porque).

Planteamiento de la Solucion:
– Los datos que esten en Oracle que lo procese y los devuelva su propio gestor
– Los datos que esten en Sql Servecr lo procese y los devuelva su propio gestor (no se usara linked server)
– La interfaz de comunicacion entre los dos gestores sera un ensamblado CLR sobre Sql Server y este realizará llamadas de datos a Oracle.

Solucion:

– Crearemos un ensamblado .NET en Visual Studio que realize las llamadas de data a Oracle y se las entregue a SqlServer
– Configuraremos Sql para que permita trabajar con ensamblados que realizan llamadas fuera del entorno de Sql Server
– Instaleremos el ensamblado (instalar es un decir)
– Crearemos los objetos que hacen la llamada al ensamblado

Manos a la obra

1.- CREANDO EL ENSAMBLADO

using System.Data.OracleClient;
namespace Inspeccion
{

public class Interprete
{
//[Microsoft.SqlServer.Server.SqlProcedure]
[Microsoft.SqlServer.Server.SqlFunction()]
public static string GetCodigosCliente(SqlString nombre)
{
string result = “”;
using (OracleConnection cn = new OracleConnection(“Data Source=ORATEST.BD;Persist Security Info=True;User ID=MIUSER;PASSWORD=MIPASSWORD;Unicode=True”))
{
cn.Open();
using (OracleCommand cmd = new OracleCommand(“package.miprocedure”, cn))
{
cmd.CommandType = CommandType
.StoredProcedure;
string nombreParametro = nombre.ToString();
cmd.Parameter.Add(
new OracleParameter(“SPARAM”, OracleType.VarChar, 140)).Value = nombreParametro;
cmd.Parameter.Add(
new OracleParameter(“RC1”, OracleType.Cursor)).Direction = ParameterDirection.Output;
using (OracleDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
result +=
string.Format(“‘{0}’,”, reader[1].ToString());
}
}
}
return result.Trim(‘,’);
//SqlContext.Pipe.Send(mensaje);
}

Atencion al uso de SqlOracleClient, se uso este database provider puesto que viene nativo del Framework, si desea usar otra libreria primero deberia registrarla en sql server.

SqlString, parametro de la function es porque el tipo debe coindicir con el de la base de datos

Atención a la clase, puesto que se ha definido como static

Fijese que es importante definir desde aqui que funcionalidad de SqlServer usará en ensamblado, si un procedimiento almacenado o una funcion usando Microsoft.Sqlserver.server.SqlProcedure o Microsoft.SqlServer.Server.SqlFunction
Como vemos el resultado que necesito obtener es tan solo un escalar del tipo string.

2.- CONFIGURANDO SQL SERVER PARA QUE ACEPTE ENSAMBLADOS

Configurar Sql Server (ingrese a Management) y digite lo siguiente

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

3.- INSTALANDO EL ENSAMBLADO

Compile su libreria (*.dll) y peguela en C:\

/*
USE master
GO
CREATE ASYMMETRIC KEY AsymmetricKeyName FROM EXECUTABLE FILE = ‘{put your .dll file path here}’
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY AsymmetricKeyName
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin
GO
*/

ALTER DATABASE MiBaseDeDatos SET TRUSTWORTHY ON

–CREATE ASSEMBLY ass_InterpreteOracle from ‘c:\inspeccion.dll’ WITH PERMISSION_SET = SAFE
–CREATE ASSEMBLY ass_InterpreteOracle from ‘c:\inspeccion.dll’ WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE ASSEMBLY ass_InterpreteOracle from ‘c:\inspeccion.dll’ WITH PERMISSION_SET = UNSAFE

Donde:

– TRUSTWORTHY: Indica al gestor que esta es una base de datos de confianza
– UNSAFE: Indica que le permita hacer las llamadas al exterior del entorno de SQL Server (recuerde que haremos llamadas a Oracle)
– ass_InterpreteOracle: es el nombre que tendra el ensamblado
– ‘c:\inspeccion.dll’ : es el nombre del ensamblado generado en el punto anterior

– En caso de no querer utilizar TRUSTWORTHY y UNSAFE investigue acerca de ASIMETRIC KEY

4.- CREANDO LOS OBJETOS QUE REALIZAN LAS LLAMADAS AL ENSAMBLADO

A) Para usarlo desde una funcion, crearemos lo siguiente

CREATE FUNCTION ufn_GetCodigosCliente(@nombre nvarchar(max))
RETURNS nvarchar(max)
AS
EXTERNAL NAME ass_InterpreteOracle.[Inspeccion.Interprete].GetCodigosCliente;
GO

y para llamar al procedimiento recuerde que la sentencia es “select dbo.ufn_GetCodigosCliente(‘RODRIGUEZ’)”

B) Para usarlo desde un procedimiento, crearemos lo siguiente

CREATE PROCEDURE usp_Miprocedimiento
AS
EXTERNAL NAME ass_InterpreteOracle.[Inspeccion.Interprete].GetCodigosCliente;

y para llamar al procedimiento recuerde que la sentencia es “exec usp_Miprocedimiento”

enero 23, 2010 - Posted by | Base de Datos

1 comentario »

  1. Grande Gurú!!!!

    Comentario por Wilmer Salgado | agosto 25, 2010


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Salir /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Salir /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Salir /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Salir /  Cambiar )

Conectando a %s

A %d blogueros les gusta esto: