Como integrar código CLR en SQL Server I

En este artículo voy a mostrar cómo podemos extender fácilmente el SQL de nuestros servidores SQL Server con funciones y tipos de datos del CLR (Common Language Runtime) de .NET framework. Para ello, voy a implementar una función con valor de retorno escalar, otra función con valor de tabla, un par de funciones de agregación y un procedimiento almacenado.

En este enlace puedes descargar el código fuente del proyecto CLRIntegration, escrito en CSharp usando Visual Studio 2015.

Lo primero que necesitamos hacer es activar el uso de ensamblados CLR en nuestro servidor de base de datos, para ello basta con ejecutar el siguiente código:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

A continuación, podemos crear un proyecto de base de datos SQL Server con Visual Studio. En la configuración del proyecto, podremos seleccionar la versión de la base de datos de destino y el nombre del esquema donde se crearán las funciones y procedimientos. En principio no es necesario realizar ninguna configuración más.

Funciones definidas por el usuario

En primer lugar crearemos una función definida por el usuario, usando la opción Agregar Nuevo Elemento del menú contextual del proyecto. Esto creará una clase dentro de la cual podremos ver un método estático básico a partir del cual deberemos construir nuestra función.

public partial class UserDefinedFunctions
{
[SqlFunction]
public static SqlString SqlFunction1()
{
// Inserte el código aquí
return new SqlString (string.Empty);
}
}

Podemos cambiar el nombre de la clase y el de la función por el que queramos. Fijaos en el atributo SqlFunction. Este atributo es necesario para indicar el tipo de objeto de base de datos que vamos a definir. También os debéis fijar en que la función es de tipo static y en el tipo de datos que tienen tanto la función como sus parámetros, que llevan el prefijo Sql. Esto es importante de cara al interfaz con el motor de base de datos, por lo demás, no es muy diferente de trabajar con los tipos a los que estamos acostumbrados.

Como ejemplo, he escrito una función que utiliza expresiones regulares para encontrar patrones en una cadena, con el mismo nombre y parámetros que REGEXP_INSTR, la función homónima que podemos encontrar en Oracle y MySQL.

public static SqlInt32 REGEXP_INSTR(SqlString data, SqlString model)
{
Regex reg = new Regex(model.ToString());

Match rm = reg.Match(data.ToString());
if ((rm != null) && (rm.Success))
{
return rm.Index + 1;
}
return 0;
}

Solo hay que acordarse de que los índices de las cadenas en SQL Server empiezan en 1, mientras que en C# en 0.

Ahora bastaría con publicar el proyecto, indicando los datos de conexión con el servidor y la base de datos en la que queremos realizar la publicación, y ya podemos utilizar esta función como cualquier otra función SQL. La encontraremos al desplegar la carpeta de funciones con valor escalar.

Para crear una función que devuelva el resultado de una consulta, el procedimiento es más complejo. Como ejemplo he escrito una función que consulta en el catálogo de la base de datos (así no tenemos que crear ninguna tabla de ejemplo ni llenarla de datos) todas las tablas que tienen algún campo cuyo nombre contenga un determinado patrón, lo que comprobaremos llamando a la función anterior.

En primer lugar, el atributo SqlFunction cambia, pues hay que añadirle algunos parámetros:

[SqlFunction(
DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "FindTableFields_FillRow",
TableDefinition = "Schema_name nvarchar(128), " +
"Table_name nvarchar(128), " +
"Column_name nvarchar(128)")]

El parámetro DataAccess se utiliza para indicar el tipo de acceso a datos que usará la función, en nuestro caso, solo lectura. Como vamos a acceder a tablas del sistema (el catálogo), es necesario indicarlo con el parámetro SystemDataAccess, en caso contrario, la función fallará al ejecutarse, produciendo una excepción.

Con FillRowMethodName indicamos el nombre del método que se utilizará para rellenar los datos de cada una de las filas que devuelva la función, este método estará en la misma clase que la función principal. En este ejemplo voy a ponerla dentro de la misma clase que la del ejemplo anterior.

Por último, con TableDefinition indicamos la estructura de los registros devueltos, en este caso, tres columnas con los nombres del esquema, la tabla y el campo.

La función debe ser también static y tener IEnumerable como tipo de retorno:

private class FieldResult
{
public FieldResult(SqlString schema, SqlString table, SqlString column)
{
Schema = schema;
Table = table;
Column = column;
}
public SqlString Schema { get; set; }
public SqlString Table { get; set; }
public SqlString Column { get; set; }
}

public static IEnumerable FindTableFields(SqlString model)
{
ArrayList resultCollection = new ArrayList();

using (SqlConnection connection =
new SqlConnection("context connection=true"))
{
connection.Open();

using (SqlCommand selectColumns = new SqlCommand(
"SELECT " +
"[TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME] " +
"FROM [INFORMATION_SCHEMA].[COLUMNS] " +
"WHERE dbo.REGEXP_INSTR([COLUMN_NAME], @model) >0",
connection))
{
selectColumns.Parameters.AddWithValue(
"model",
model);

using (SqlDataReader columnReader =
selectColumns.ExecuteReader())
{
while (columnReader.Read())
{
resultCollection.Add(new FieldResult(
columnReader.GetSqlString(0),
columnReader.GetSqlString(1),
columnReader.GetSqlString(2)));
}
}
}
}
return resultCollection;
}

La función acepta como parámetro el modelo de expresión regular a buscar. Internamente utiliza una conexión cuya cadena e conexión es “context connection=true”, esto indica que se debe utilizar la misma conexión que esté utilizando el código que llama a esta función. Si intentamos realizar una conexión con otra base de datos o servidor, o acceder a algún recurso como un fichero en disco, se producirá una excepción, a menos que hayamos configurado el ensamblado como EXTERNAL ACCESS o UNSAFE, pero nos quedaremos en el caso por defecto, que es usar el modo SAFE.

Por lo demás, los datos que recogemos los acumulamos en cualquier IEnumerable, en este caso un ArrayList, y devolvemos este objeto como resultado de la función. La clase FieldResult nos sirve para almacenar los datos.

Ahora debemos implementar la función encargada de devolver los registros de la consulta a partir de este objeto:

public static void FindTableFields_FillRow(object fieldResultObj,
out SqlString schema,
out SqlString table,
out SqlString column)
{
FieldResult fieldResult = (FieldResult)fieldResultObj;

schema = fieldResult.Schema;
table = fieldResult.Table;
column = fieldResult.Column;
}

El primer parámetro siempre es el objeto con los datos, y el resto son parámetros out en los que devolvemos los diferentes campos del registro.

Procedimientos almacenados

Escribir un procedimiento almacenado es muy parecido, pero en este caso utilizamos el atributo SqlProcedure para indicarlo. El ejemplo que he preparado recibe como parámetro un identificador y un valor y trata de actualizar con ellos el registro correspondiente de una tabla. Si el registro no existe, lo inserta como nuevo.

Primero, crearemos la tabla en el servidor de base de datos.

Create table sample_table(id int, data_value nvarchar(128))

Y el procedimiento, en la misma clase que las funciones:

[SqlProcedure()]
public static void InsertOrUpdateTableProc(SqlInt32 id, SqlString data)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand InsertCommand = new SqlCommand(
"insert into sample_table(id, data_value) " +
"values(@id, @data)", conn);
InsertCommand.Parameters.AddWithValue("id", id);
InsertCommand.Parameters.AddWithValue("data", data);
SqlCommand UpdateCommand = new SqlCommand(
"update sample_table " +
"set data_value = @data " +
"where id = @id", conn);
UpdateCommand.Parameters.AddWithValue("id", id);
UpdateCommand.Parameters.AddWithValue("data", data);
conn.Open();
if (UpdateCommand.ExecuteNonQuery() == 0)
{
InsertCommand.ExecuteNonQuery();
}
conn.Close();
}
}

Funciones de agregación

Para terminar esta primera parte, vamos a ver cómo definir funciones de agregación de datos, similares a sum, avg o count, por ejemplo.

En este caso, debemos construir estructuras de datos (struct) en lugar de una clase, y deben implementar obligatoriamente cuatro métodos:

• Init: Se utiliza para inicializar los datos al principio de la agregación.
• Accumulate: A este método se le llamará una vez por cada uno de los registros a agregar, puede tener más de un parámetro.
• Merge: En ocasiones, el sistema puede decidir realizar una agregación en varios bloques, de cara a optimizar. Este método será llamado para realizar la agregación de estos bloques.
• Terminate: Con este método devolveremos el resultado de la agregación.

La struct deberá estar decorada con el atributo Serializable, y con el atributo SqlUserDefinedAggregate indicaremos su uso como función de agregación.

El primer ejemplo solo toma un parámetro, que será el campo a agregar, el campo debe ser de tipo int y la función realizará un AND binario. Resulta muy sencillo modificar este código para realizar un OR o XOR binario.

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct BAND
{
private SqlInt32 _value;
public SqlInt32 Value
{
get
{
return _value;
}
set
{
_value = value;
}
}
public void Init()
{
_value = new SqlInt32(-1);
}
public void Accumulate(SqlInt32 value)
{
_value = SqlInt32.BitwiseAnd(value, _value);
}
public void Merge(BAND Group)
{
_value = SqlInt32.BitwiseAnd(Group.Value, _value);
}
public SqlInt32 Terminate()
{
return _value;
}
}

En el segundo y último ejemplo, volvemos a utilizar expresiones regulares para contar todos los registros que cumplen con un determinado modelo, que es el segundo parámetro de la función Accumulate:

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountMatches
{
private SqlInt32 _matches;
public SqlInt32 Matches
{
get
{
return _matches;
}
set
{
_matches = value;
}
}
public void Init()
{
_matches = new SqlInt32(0);
}
public void Accumulate(SqlString data, SqlString model)
{
Regex reg = new Regex(model.ToString());

Match rm = reg.Match(data.ToString());
if ((rm != null) && (rm.Success))
{
_matches = SqlInt32.Add(_matches, 1);
}
}
public void Merge(CountMatches Group)
{
_matches = SqlInt32.Add(Group.Matches, _matches);
}
public SqlInt32 Terminate()
{
return _matches;
}
}

Y eso es todo por el momento. En el siguiente artículo os mostraré como implementar disparadores y tipos de datos definidos por el usuario.