Uso de CLR stored procedure para llamar a un web service desde SQL Server 2008

Hola gente! Pues acá les traigo una forma de acceder a un servicio web desde un procedimiento almacenado usando el CLR.

Let's begin!

Para hacer práctico el ejemplo, lo haremos con el servicio web del Banco Central de Costa Rica que nos permite consumir los diferentes tipos de cambio monetario. Para este caso escogeremos el tipo de cambio del dólar estadounidense (venta del USD), cuyo valor de parámetro es 318.

Para que vayan comprendiendo les coloco la arquitectura que vamos a utilizar:

La idea es consumir el valor dela venta en USD y la fecha de dicho valor e ingresarlos a una tabla, la cual, puede ser consultada posteriormente para realizar ajustes, cálculos, etcétera en sistemas que dependan de las fluctuaciones de ésta moneda.

Vamos al grano, lo primero es ir a nuestro servidor (físico) que tiene SQL Server 2008 y habilitarle el CLR:

sp_configure 'clr enabled', 1
go
reconfigure
go

Como segundo paso es permitirle a la base de datos que vas a utilizar como repositorio, acceder a recursos externos del motor de SQL:

alter database TU_BASE_DATOS set trustworthy on

El tercer paso (asumiendo que tienen .NET o al menos Framework 2.0) es crear un proxy local del servicio web del banco para convertirlo luego en una dll que será empotrada al SQL. Para lo cual necesitamos de la utilería WSDL.exe que se ubica en Visual Studio SDK/Bin:

wsdl /o:CambioDolar.cs /n:cambioDolar.Test http://indicadoreseconomicos.bccr.fi.cr/IndicadoresEconomicos/WebServices/wsIndicadoresEconomicos.asmx

Lo anterior creará una clase en C# (CambioDolar.cs) que contendrá el código proxy y el namespace de la clase creada (cambioDolar.Test).

El cuarto paso (en el IDE de .NET), es crear el cuerpo del CLR stored procedure:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using cambioDolar.Test;

namespace ConsumidorCambioDolar
{
public class consumidorCambioDolar
{
  private static void insertarIndicador(SqlDateTime Fecha, SqlDouble Valor)
  {
   ...
  }

  [SqlProcedure]
  public static void obtenerIndicador(SqlString Indicador)
  {
   ...
     insertarIndicador(DateTime.Parse(x.ToString()), double.Parse(y.ToString()));
  }
}
}

Observese la invocación del namespace que permite instanciar la referencia de la clase proxy  y la etiqueta [SqlProcedure] sobre la firma del método obtenerIndicador que le permite a SQL Server invocar al método a través de la firma del procedimiento almacenado que construiremos más adelante.

En el quinto paso vamos a utilizar otra utilería (CSC.exe) para compilar el CLR Stored Procedure y la clase proxy en una sola dll, además, crearemos el XML Serialization Code debido a que la serialización XML dinámica no está permitida en SQL Server, por lo que debemos ejecutar la utiliría SGEN.exe para generar un ensamblado de serialización estática.

// Crea ConsumidorCambioDolar.dll

csc /t:library ConsumidorCambioDolar.cs CambioDolar.cs

// Genera el XML Serialization Code
// Crea ConsumidorCambioDolar.XmlSerializers.dll
sgen /a:ConsumidorCambioDolar.dll

Por último, sería empotrar los esamblados y crear el store procedure en SQL Server:

CREATE ASSEMBLY consumidorCambioDolar
FROM 'E:\ConsumidorCambioDolar.dll'
WITH PERMISSION_SET = UNSAFE;
GO

--DROP ASSEMBLY consumidorCambioDolar

CREATE ASSEMBLY [consumidorCambioDolar.XmlSerializers]
FROM 'E:\ConsumidorCambioDolar.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

--DROP ASSEMBLY [consumidorCambioDolar.XmlSerializers]
CREATE PROCEDURE prc_InsertarIndicadorEconomico(Indicador nvarchar(5))
AS
EXTERNAL NAME consumidorCambioDolar.consumidorCambioDolar.obtenerIndicador
GO

Bueno llegamos al final, recuerda que los dos dll's debes colocarlos en un lugar seguro, debes colocar el store procedure en la invocación de un job para que sea desatendido y si quieres quitar todo aplica los drop's de abajo para arriba. Nos vemos...