Foro de elhacker.net

Programación => .NET (C#, VB.NET, ASP) => Mensaje iniciado por: [D4N93R] en 23 Julio 2010, 16:00 pm



Título: [Tutorial] CLR Stored Procedures (Sql Server)
Publicado por: [D4N93R] en 23 Julio 2010, 16:00 pm
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.

(http://r.i.elhacker.net/cache?url=http://img714.imageshack.us/img714/1265/projectx.jpg) (http://img714.imageshack.us/i/projectx.jpg/)

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
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  6.  
  7.  
  8. public partial class StoredProcedures
  9. {
  10.    [Microsoft.SqlServer.Server.SqlProcedure]
  11.    public static void ProcedureDemo()
  12.    {
  13.        // Put your code here
  14.    }
  15. };
  16.  

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
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using System.Data.SqlTypes;
  5. using Microsoft.SqlServer.Server;
  6. using System.IO;
  7.  
  8.  
  9. public partial class StoredProcedures
  10. {
  11.    //este es el atributo que marca el método como SP, los parámetros del método
  12.    //serán los parámetros del SP, Noten que el valor de retorno es VOID.
  13.    [Microsoft.SqlServer.Server.SqlProcedure]
  14.    public static void ProcedureDemo(string path, string filter)
  15.    {
  16.        // Crea el record y especifica la metadata para las columnas
  17.        SqlDataRecord record = new SqlDataRecord(
  18.            new SqlMetaData("FileName", SqlDbType.NVarChar, 500),
  19.            new SqlMetaData("Size", SqlDbType.Int));
  20.  
  21.  
  22.        //SqlContext es nuestra vía de comunicación con Sql Server
  23.        //Si quieren validar si el ensamblado está corriendo en Sql Server
  24.        // Y existe una conexión disponible pueden validarlo con la propiedad
  25.        // IsAvailable de SqlContext que devuelve true o false dependiendo del caso.
  26.  
  27.        // Marca el inicio de la incorporación de registros
  28.        SqlContext.Pipe.SendResultsStart(record);
  29.  
  30.        DirectoryInfo dInfo = new DirectoryInfo(path);
  31.        foreach (FileInfo file in dInfo.GetFiles(filter))
  32.        {
  33.            //Le damos valor a las columnas
  34.            record.SetString(0, file.FullName);
  35.            // y enviamos el registro
  36.            SqlContext.Pipe.SendResultsRow(record);
  37.        }
  38.  
  39.        // Marca el fin y envia los resultados
  40.        SqlContext.Pipe.SendResultsEnd();
  41.    }
  42. };

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
  1. sp_configure 'clr enabled', 1;
  2. RECONFIGURE;
  3. GO
  4.  

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
  1. ALTER DATABASE TESTING
  2.  
  3. SET TRUSTWORTHY ON

Resultado

(http://r.i.elhacker.net/cache?url=http://img831.imageshack.us/img831/5958/resultj.jpg) (http://img831.imageshack.us/i/resultj.jpg/)

Espero les sea de ayuda,

Un saludo!