[SQL SERVER] CLR Procedure para consumir un Servicio HTTP REST

En Sql Server ser puede crear un procedimiento almenado cuyo código este alojado en un ensamblado NET desarrollado por nosotros mismos (CLR Procedure).
Vamos a crear un ejemplo, que realice peticiones HTTP POST sobre un servicio REST para envió de SMS.

Las tareas que vamos a realizar son:

  1. Registrarse en un servicio de envió de SMS que tenga una API HTTP REST. Para nuestro ejemplo hemos usado InstaSent, el cual nos da un crédito gratuito de 30 céntimos (podremos enviar 6 SMS), y un modo de pruebas, donde podremos realizar las pruebas que necesitemos
  2. Crear un ensamblado .NET que realizará las peticiones HTTP
  3. Para probar nuestro CLR procedure, vamos a crear un trigger que se lance al insertar un nuevo registro en la tabla de clientes.

Registro InstaSent

No vamos a entrar en detalles, pues simplemente hay que registrarse, y crear el API TOKEN necesario para realizar peticiones HTTP.

Ensamblado .NET

Abrimos Visual Studio y creamos un nuevo proyecto usando la plantilla Proyecto de Base de Datos Sql Server

A continuación, vamos a agregar un elemento del tipo “Procedimiento Almacenado SQL CR#”, haciendo clic derecho sobre el proyecto y seleccionando la opcion “Agregar->Nuevo Elemento”

A continuación, mostramos el código que nos ha creado la plantilla.

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ClrSms ()
    {
        // Inserte el código aquí
    }
}

el cual vamos a modificarlo para enviar un SMS, creando una petición HTTP POST.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Text;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void EnviarSMS(SqlInt32 idCliente, SqlString mensaje, out SqlInt32 resultado)
    {
        resultado = 0;
        object objResultado = null;
        string telefono = string.Empty;
        int nErrores = 0;
        string cadPeticion = string.Empty;
        string contenidoRespuesta = string.Empty;
        string cadSql = string.Empty;
        string idEnvio = string.Empty;
        try
        {
            using (SqlConnection conexion = new SqlConnection("context connection=true"))
            {
                conexion.Open();
                SqlContext.Pipe.Send("La conexion ha sido abierta");
                //recupero el telefono del cliente
                cadSql = "select Telefono from Clientes Where Id=@IdCliente";
                using (SqlCommand comando = new SqlCommand(cadSql, conexion))
                {
                    SqlParameter parametro = new SqlParameter("@IdCliente", idCliente);
                    comando.Parameters.Add(parametro);
                    objResultado = comando.ExecuteScalar();
                    if (objResultado != null)
                    {
                        telefono = objResultado.ToString();
                    }
                    else
                    {
                        SqlContext.Pipe.Send($"El cliente {idCliente} no existe");
                        nErrores++;
                    }
                }

                if (nErrores == 0)
                {
                    if (!string.IsNullOrEmpty(telefono))
                    {
                        SqlContext.Pipe.Send($"El número de telefono del cliente es {telefono}");
                    }
                    else
                    {
                        SqlContext.Pipe.Send($"El cliente {idCliente} no tiene telefono");
                        nErrores++;
                    }
                }

                if (!string.IsNullOrEmpty(mensaje.ToString()))
                {
                    SqlContext.Pipe.Send($"El mensaje a enviar es {mensaje.ToString()}");
                }
                else
                {
                    SqlContext.Pipe.Send("No se ha especificado el mensaje a enviar");
                    nErrores++;
                }

                //envio una peticion HTTP
                if (nErrores == 0)
                {
                    SqlContext.Pipe.Send("Preparando peticion HTTP...");
                    HttpWebRequest peticionHttp = (HttpWebRequest)HttpWebRequest.Create("https://api.instasent.com/sms/");
                    peticionHttp.ContentType = "application/json";
                    peticionHttp.Accept = "application/json";
                    peticionHttp.Headers.Add("Authorization", "Bearer d7bc8b2e0024cec444f4a529baa3cd67d13b908d");
                    peticionHttp.Method = "POST";
                    cadPeticion = "{\"from\": \"ProgApps\",\"to\": \"" + telefono + "\",\"text\": \"" + mensaje.ToString() + "\"}";
                    peticionHttp.ContentLength = cadPeticion.Length;
                    using (StreamWriter sw = new StreamWriter(peticionHttp.GetRequestStream()))
                    {
                        sw.Write(cadPeticion);
                        sw.Flush();
                        sw.Close();
                    }
                    SqlContext.Pipe.Send("Enviando petición http...");
                    HttpWebResponse respuestaHttp = (HttpWebResponse) peticionHttp.GetResponse();
                    if (respuestaHttp != null)
                    {
                        if (respuestaHttp.StatusCode== HttpStatusCode.Created)
                        {
                            SqlContext.Pipe.Send("El SMS ha sido enviado");
                            using (Stream stream = respuestaHttp.GetResponseStream())
                            {
                                StreamReader reader = new StreamReader(stream, Encoding.UTF8);
                                contenidoRespuesta = reader.ReadToEnd();
                            }
                        }
                        else
                        {
                            SqlContext.Pipe.Send("El SMS ha sido enviado");
                            nErrores++;
                        }
                    }
                }

                //Utilizo el metodo JSON_VALUE para deserializar la respuesta JSON y recuperar el campo Id
                if ((nErrores == 0) && (!string.IsNullOrEmpty(contenidoRespuesta)))
                {
                    SqlContext.Pipe.Send("Recuperando el Id del envio del SMS...");
                    cadSql = "select JSON_VALUE('" + contenidoRespuesta + "','$.entity.id')";
                    using (SqlCommand comando = new SqlCommand(cadSql,conexion))
                    {
                        objResultado = comando.ExecuteScalar();
                        if (objResultado != null)
                        {
                            idEnvio = objResultado.ToString();
                            SqlContext.Pipe.Send("El Id de Envio es " + idEnvio);
                        }
                        else
                        {
                            SqlContext.Pipe.Send("No se ha recibido la respuesta del API");
                            nErrores++;
                        }
                        if (string.IsNullOrEmpty(idEnvio))
                        {
                            SqlContext.Pipe.Send("No se ha podido recuperar el Id del Envio del mensaje");
                            nErrores++;
                        }
                    }
                }

                //retorno un registro a sql server con 3 columnas
                if (nErrores == 0)
                {
                    SqlDataRecord registro = new SqlDataRecord(
                        new SqlMetaData("Telefono", SqlDbType.NVarChar, 20),
                        new SqlMetaData("IdCliente", SqlDbType.Int),
                        new SqlMetaData("IdEnvio", SqlDbType.NVarChar,50));

                    //establezco el inicio del recorset 
                    SqlContext.Pipe.SendResultsStart(registro);

                    registro.SetString(0, telefono);
                    registro.SetInt32(1, (Int32)idCliente);
                    registro.SetString(2, idEnvio);

                    //retorno el registro
                    SqlContext.Pipe.SendResultsRow(registro);

                    //establezco el final del recorset
                    SqlContext.Pipe.SendResultsEnd();
                }

            }
        }
        catch
        {
            throw;
        }
        if (nErrores == 0)
        {
            resultado = 1;
        }
        else
        {
            resultado = 0;
        }
    }
}

En el fragmento de código anterior hemos realizado las siguientes operaciones:

  • Creamos una conexión usando la base de datos activa en el contexto (“context connection=true“)
  • Recuperamos el teléfono en base al Id del cliente
  • Creamos una petición POST donde incluimos las cabeceras HTTP:
    • Acccept: application/json
    • Content-Type: application/json
    • Authorization: Bearer < >

    y como cuerpo de la petición usamos la entidad JSON

    {
      "from": "ProgApps",
      "to": "TELEFONO",
      "text": "MENSAJE"
    }
    

    Nota: la estructura y las cabeceras del mensaje, dependen del proveedor. En este caso Instasent.

    Una vez que la petición ha sido procesada recibimos una cadena JSON con el siguiente formato

    {
        "entity": {
            "id": "594d16a7355b6c789649465a",
            "clientId": null,
            "status": "enqueued",
            "statusCode": null,
            "from": "ProgApps",
            "country": "ES",
            "to": "+34123123123",
            "normalizedTo": "+34123123123",
            "charsCount": 18,
            "text": "Bienvenido SMONTES",
            "deliveredText": "Bienvenido SMONTES",
            "messagesCount": 1,
            "concatsLimit": 10,
            "finalMessagesCount": 1,
            "encoding": "GSM_7BIT",
            "unicode": false,
            "allowUnicode": false,
            "isSanitized": true,
            "isTruncated": false,
            "charged": true,
            "pricePerSms": 0,
            "priceUser": 0,
            "deliveryReportMask": 0,
            "scheduledAt": null,
            "chargedAt": "2017-06-23T21:24:55+0200",
            "sentAt": null,
            "deliveredAt": null,
            "createdAt": "2017-06-23T21:24:55+0200"
        }
    }
    
  • Usando la funcion JSON_VALUE recuperamos el Id del envio del SMS.
    Nota: Esta función esta incluida en Sql Server 2016.
  • Por último creamos un SqlDataRecord con 3 columnas: Telefono, IdCliente, IdEnvio y lo devolvemos al cliente que invoco el procedimiento almacenado.

Nota: También hemos usado el método SqlContext.Pipe.Send para enviar un texto a la ventana de salida mensajes de Sql Server.
En la clase SqlContext.Pipe hay varios métodos que permiten comunicar nuestro ensamblado con sql server.

Una vez que tenemos nuestro ensamblado preparado, vamos a compilarlo para generar la DLL correspondiente.

Registrar ensamblado .NET en Sql Server

Antes de registrar el ensamblado, es necesario realizar las siguientes tareas:

  1. Activamos la ejecución de ensamblados definidos por el usuario
    		sp_configure 'clr enabled',1
    		go
    		RECONFIGURE
    
  2. Debido a que nuestro ensamblado accede a recursos HTTP externos, es necesario que establezcamos la propiedad TRUSTWORTHY a ON
    ALTER DATABASE ClientesProgrammingApps SET TRUSTWORTHY ON;
    

    y asegurarnos que el usuario de sql server tenga el permiso EXTERNAL ACCESS ASSEMBLY

A continuación, vamos a registrar el ensamblado .NET

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'EnvioSMSInstasent')
    DROP ASSEMBLY EnvioSMSInstasent;
go
CREATE ASSEMBLY EnvioSMSInstasent FROM 'c:\CLR Procedures\ProgrammingApps.SqlServer.EnvioSMS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;

Es muy importante especificar la directiva WITH PERMISSION_SET = EXTERNAL_ACCESS, ya que permitirá que nuestro ensamblado acceda al API del servicio HTTP REST.

Una vez registrado, vamos a crear un procedimiento almacenado y lo enlazamos con un método de nuestro ensamblado.

IF OBJECT_ID ('dbo.EnviarSMS','PC') IS NOT NULL
    DROP PROCEDURE dbo.EnviarSMS;
go
CREATE PROCEDURE dbo.EnviarSMS 
	@IdCliente int,
	@Mensaje nvarchar(200),
	@resultado int OUTPUT
AS EXTERNAL NAME EnvioSMSInstasent.StoredProcedures.EnviarSMS; 

Nota: Si en el ensamblado, usamos espacios de nombres, tenemos que modificar la sentencia de creacion como

CREATE PROCEDURE EnviarSMS 
	@IdCliente int,
	@Mensaje nvarchar(200),
	@resultado int OUTPUT
AS EXTERNAL NAME EnvioSMSInstasent.[MIESPACIONOMBRES.StoredProcedures].EnviarSMS; 

Ahora podemos ejecutar el siguiente script, para probar nuestro procedimiento,

declare @resultado int;
exec [dbo].[EnviarSMS] 1,"Bienvenido",@resultado;
go

Esta sentencia retornará un registro que contendrá el IdCliente, el teléfono y el Id de Envio (generado por el API InstaSend)

Para completar el ejemplo, vamos a crear un trigger sobre la tabla de clientes de forma que al insertar un nuevo registro, se envie un SMS automaticamente

IF OBJECT_ID ('dbo.EnvioSMSClienteAuto','TR') IS NOT NULL
    DROP TRIGGER dbo.EnvioSMSClienteAuto;
GO
CREATE TRIGGER dbo.EnvioSMSClienteAuto
ON dbo.Clientes
AFTER INSERT
AS
   declare @resultado int;
   declare @idCliente int;

   DECLARE CursorClientes CURSOR FAST_FORWARD FOR SELECT Id FROM INSERTED;

   OPEN CursorClientes FETCH NEXT FROM CursorClientes into @idCliente

   WHILE @@FETCH_STATUS = 0 
   BEGIN 

     EXEC [dbo].[EnviarSMS] @idCliente, 'Biennvenido', @resultado;
	 
	 FETCH NEXT FROM CursorClientes into @idCliente  
   END

   CLOSE CursorClientes
   DEALLOCATE CursorClientes
GO

Si ahora intentamos añadir uno o mas registros a la tabla de clientes, veremos como se envia el SMS correspondiente.

Por últimos os dejamos los enlaces para descargar el material necesario

  1. Script de creación de tabla.
  2. Consultas T-SQL usadas durante el desarrollo del articulo.
  3. Proyecto ProgrammingApps.SqlServer.EnvioSMS.