INTEGRAR SQL SERVER CON C#

Este artículo está relacionado con los famosos assemblies también conocidos como CLRs (Common Language Runtime).
En este artículo usted aprenderá:

• Aprenderá a instalar los ejemplos de CLR
• Cómo compilar los ejemplos CLR
• Cómo agregar su CLR a SQL Server
• Cómo ejecutar su CLR en SQL server

Muchas personas quieren extender la funcionalidad de SQL Server mediante dlls (Dynamic Link Libaries). Por ejemplo, si yo quisiera mandar email en sql server necesito usar funciones externas.

En sql server 2000 se utilizaban los procedimientos almacenados extendidos (extended stored procedures):

http://msdn.microsoft.com/es-es/library/ms187389(SQL.90).aspx

Sin embargo ahora el SQL SERVER 2005 o superior, está muy integrado con el visual studio. Si queremos agregar nuevas librerías y funciones se trabaja con el CLR. Este artículo trabajará con c#, pero alguien que quiera agregar CLR en visual basic también puede leer el artículo. La variación es muy sutil.

Para gente que no usa el visual studio, no es nada fácil trabajar con assemblies. Cuando uno averigua la documentación de SQL Server, se encuentra con códigos de ejemplo de CLR:
http://msdn.microsoft.com/es-bo/library/ms160894(SQL.90).aspx

Estos ejemplos no se instalan con la instalación por defecto de SQL Server. Para ver si están instalados estos, revise su máquina. En mi caso se instalaron en la siguiente dirección:

C:\Archivos de programa\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR

Seguramente en su máquina variará un poco la dirección (en la carpeta program files o si su sql es 2008 sera carpeta 100 en vez de 90).

Figura 1. Ejemplos de CLR

Instalación de ejemplos.

Ahora si usted no los instaló tiene que insertar el CD de instalación o el ISO de SQL Server, ir al panel de control>Agregar o quitar programas.
Elija sql server y coloque cambiar. Coloque next varias veces hasta llegar a Cambiar y seleccione Sample code and applications:

Figura 2. Panel de control
Si todo salió bien debería tener los ejemplos instalados. Si tiene problemas para encontrar los ejemplos haga una búsqueda de las carpetas mostradas en la figura 1.

Compilación de los ejemplos.
Por algún extraño motivo que no entiendo hasta ahora, estos ejemplos no instalan ni crean los dlls y hay que hacer varios pasos adicionales para hacerlos funcionar. Los pasos para hacer funcionar están especificados en cada ejemplo. Son archivos readme*.htm
Para fines didácticos voy a hacer correr el ejemplo user defined data type. Usted puede encontrar los pasos para ejecutarlo en español:

http://msdn.microsoft.com/es-bo/library/ms160738.aspx

Varios amigos me preguntan que cuernos es el SDK y otras herramientas. Si usted tiene el visual studio instalado, crear assemblies es más visual y sencillo. En este caso, voy a asumir que usted tiene una instalación de sql server 2005 y no tiene visual studio instalado. Si ese es el caso, lo primero que hay que hacer es correr el ejecutable sn.exe para generar un certificado.
Compilar requiere 2 pasos:
1. Generar una clave con el comando sn
2. Compilar y generar las librerías con el comando MSBuild

Generar una clave con el comando sn.
Entonces, lo que tiene que hacer usted es buscar donde se instala el archivo sn.exe
Si usted tiene sql server instalado, debería tener el archivo instalado. En mi caso estaba en la siguiente dirección:
C:\Archivos de programa\Microsoft Visual Studio 8\SDK\v2.0\Bin
Entonces en mediante línea de comandos haga cd hasta la dirección donde este el sn.exe y ejecute el siguiente comando:

C:\Archivos de programa\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn -k temp.snk

Si todo salió bien se debería haber generado un archivo llamado temp.snk
Compilar y generar las librerías con el comando MSBuild
Generar dll con el MSBuild
Ahora lo que debe hacer es buscar donde se encuentra su ejecutable MSBuild.exe y copiar el archivo temp.snk a la carpeta donde este el proyecto. En mi caso estaba en la siguiente dirección:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
Posteriormente, lo que tiene que hacer es correr la línea de comandos en donde está el MSBuild:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\msbuild /nologo /verbosity:quiet /property:Configuration=Debug c:\CS\ComplexNumber.sln
Lo que está haciendo esta línea de comandos es compilar el archivo complexNumber.sln. Asegúrese de que su archivo ComplexNumber.sln coincide con la dirección especificada. Una falla típica es que no se copia el archivo temp.snk en la carpeta del proyecto.
Para c# se usa la carpeta CS. Para visual basic la carpeta VB con el msbuild. En este caso voy a usar c# (carpeta CS), pero el procedimiento es similar.
Si compiló correctamente, debería usted tener el siguiente dll creado:


Figura 3. DLL de CLR

Cómo agregar su CLR a SQL Server.
Si ya tiene su DLL lo que viene es agregarlo a SQL Server.
En el ejemplo del libro se usa al sqlcmd que es el sql server mediante línea de comandos. Para más información sobre sqlcmd, puede ver mi artículo relacionado:

http://elpaladintecnologico.blogspot.com/2009/04/sqlcmd-la-linea-de-comandos-para-sql.html

Para los que odian la línea de comandos, les recomiendo abrir el archivo installCS.sql haciendo doble click en el mismo.

/*Copyright (C) Microsoft Corporation. All rights reserved.*/
USE AdventureWorks
GO
DECLARE @SamplesPath nvarchar(1024)
-- Es posible que se deba cambiar la dirección para apuntar al dll
SELECT @SamplesPath = replace(physical_name,
'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf',
'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\')
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY ComplexNumber
FROM @SamplesPath + 'UserDefinedDataType\CS\ComplexNumber\bin\debug\ComplexNumber.dll'
WITH permission_set=Safe;
GO

CREATE TYPE ComplexNumber
EXTERNAL NAME [ComplexNumber].[Microsoft.Samples.SqlServer.ComplexNumber];
GO

Para hacer funcionar lo que se debe hacer es cambiar la dirección:
Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\'UserDefinedDataType\CS\ComplexNumber\bin\debug\ComplexNumber.dll
A la dirección donde este el dll o mover el dll a esa dirección.
Si todo salió bien, al ejecutar se debería crear un assemblie llamado complex number:

Figura 4. Assemblie complex number


• Cómo ejecutar su CLR en SQL server
Finalmente, lo que resta es ver si funciona nuestro CLR
El archivo que hay que abrir es el test.sql en el Management Studio.
/*---------------------------------------------------------------------
This file is part of the Microsoft SQL Server Code Samples.
Copyright (C) Microsoft Corporation. All rights reserved.
======================================================= */
USE AdventureWorks
GO
-- Crea una variable compleja
DECLARE @c ComplexNumber;

SET @c = CONVERT(ComplexNumber, '(1, 2i)');

SELECT @c.ToString() as [numbero complejo];
GO
Si todo sale bien, mostrará los números complejos creados.
(1,2i)

Como pueden ver, correr CLRs no es nada trivial. Si tienen dudas, escriban a este blog.