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
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
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 //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); 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
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
ALTER DATABASE TESTING SET TRUSTWORTHY ON
Resultado
Espero les sea de ayuda,
Un saludo!