[Tutorial] CLR Stored Procedures (Sql Server)

[[D4N93R]]

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:

Código (csharp) [Seleccionar] Expandir


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:

Código (csharp) [Seleccionar] Expandir

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:

Código (sql) [Seleccionar] Expandir

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.

Código (sql) [Seleccionar] Expandir


ALTER DATABASE TESTING

SET TRUSTWORTHY ON

Resultado



Espero les sea de ayuda,

Un saludo!