CLR Stored Procedures

El CLR está integrado a SqlServer, y gracias a eso podemos crear objetos de base de datos usando cualquier lenguaje de .Net como C#, VB.Net, F# entre otros. Entre las mejores prácticas de uso en para acciones que no conlleven acceso a datos, como string parsing, comunicación TCP/IP, Webservices, leer o escribir archivos, etc, es recomendable utilizar objetos CLR.


Crear el Proyecto
Ahora veremos como crear un proyecto sql server desde visual studio:

1.- Hacemos click en New Project o Nuevo Proyecto, y selecionamos DataBase > Sql Server > Visual C# SQL CLR Database Proyect. Le damos un nombre al proyecto y aceptamos.
2.- Se nos abre una ventana para configurar la conexión a la base de datos, si no aparece o la cerraron, pueden sacarla de nuevo en las propiedades del proyecto y luego en la pestaña Database.
3.- Seguimos los pasos del asistente y llenamos los datos de conexión en la ventana que nos apare y hacemos click en OK, con ésto enlazamos el proyecto con la base de datos.
4.- Por último, de hacemos click derecho en el Proyecto > Añadir > Procedimiento Almacenado (Stored Procedure), Le damos un nombre y Aceptar.



Con eso tenemos la clase lista para desarrollar los Stored Procedures que queramos.


DEMO:

Le damos doble click al archivo de clase que creamos y vemos que está de esta manera:

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 ProcedureDemo()
    {
        // Put your code here
    }
};
 

En esa clase van a ir todos los Stored procedures que querramos crear, puede ir uno por clase, o una clase con varios métodos, en donde cada método es un SP.

Supongamos que necesito hacer un SP que me liste los archivos de determinado directorio del servidor y los pueda filtrar. La manera correcta y más fácil de hacer esto es con un CLR SP, ya que podemos usar las clases que nos da .Net.

El Stored Procedure quedaría de esta manera:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
 
 
public partial class StoredProcedures
{
    //este es el atributo que marca el método como SP, los parámetros del método 
    //serán los parámetros del SP, Noten que el valor de retorno es VOID.
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ProcedureDemo(string path, string filter)
    {
        // Crea el record y especifica la metadata para las columnas
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("FileName", SqlDbType.NVarChar, 500),
            new SqlMetaData("Size", SqlDbType.Int));
 
 
        //SqlContext es nuestra vía de comunicación con Sql Server
        //Si quieren validar si el ensamblado está corriendo en Sql Server
        // Y existe una conexión disponible pueden validarlo con la propiedad 
        // IsAvailable de SqlContext que devuelve true o false dependiendo del caso.
 
        // Marca el inicio de la incorporación de registros
        SqlContext.Pipe.SendResultsStart(record);
 
        DirectoryInfo dInfo = new DirectoryInfo(path);
        foreach (FileInfo file in dInfo.GetFiles(filter))
        {
            //Le damos valor a las columnas
            record.SetString(0, file.FullName);
            // y enviamos el registro
            SqlContext.Pipe.SendResultsRow(record);
        }
 
        // Marca el fin y envia los resultados
        SqlContext.Pipe.SendResultsEnd();
    }
};

Cuando ya terminemos el SP, Simplemente click derecho sobre el proyecto y click en Deploy y se va directo a Sql Server y se crea el SP donde se podrá usar sin problema alguno.  

Otro paso importante es habilitar el CLR dentro de Sql Server, lo podemos hacer de la siguiente manera:

sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
 

Aunque hay casos en donde el assembly requiere privilegios elevados, cuando éste sea el caso necesitan marcar el assembly como UNSAFE, es decir sin restricciones. Click derecho en el proyecto > Propiedades > En la pestaña Database seleccionamos el Permission Level como Unsafe.

Y luego pueden ejecutar el siguiente query, el cual debería habilitar la ejecución de Ensamblados UNSAFE.

ALTER DATABASE TESTING
 
SET TRUSTWORTHY ON

Resultado



Espero les sea de ayuda,

Un saludo!