Como implementar código CLR en SQL Server II

En este artículo voy a continuar con la integración de objetos CLR en una base de datos SQL Server hablando sobre la implementación de disparadores y la creación de tipos de datos definidos por el usuario. Se trata de una manera sencilla de extender la funcionalidad de nuestras aplicaciones de base de datos con toda la potencia que nos proporciona el CLR de .NET framework.

En este otro artículo puedes ver cómo implementar funciones y procedimientos almacenados definidos por el usuario. En este otro enlace puedes descargar el código fuente del proyecto CLRIntegration, escrito en CSharp con Visual Studio 2015.

Implementación de Disparadores

Los disparadores se implementan como métodos static de una clase. Debemos decorar estos métodos con el atributo SqlTriggerAttribute para indicar que se trata de un objeto de base de datos de este tipo, la tabla sobre la que actúan y el evento que los dispara.

[SqlTrigger (Name="UpdateSampleTable",
Target="sample_table",
Event="FOR UPDATE")]
public static void UpdateSampleTableTrigger()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
string updatemsg = "updated columns: ";
string comma = "";
for (int ic = 0; ic < SqlContext.TriggerContext.ColumnCount; ic++)
{
if (SqlContext.TriggerContext.IsUpdatedColumn(ic))
{
updatemsg += comma + ic.ToString();
comma = ",";
}
}
SqlCommand LogCommand = new SqlCommand(
"insert into log_table(table_name,log_message,log_date) " +
"values('sample_table', @msg, getdate())", conn);
LogCommand.Parameters.AddWithValue("msg", updatemsg);
conn.Open();
LogCommand.ExecuteNonQuery();
conn.Close();
}
}

Con el parámetro Name del atributo SqlTriggerAttribute indicaremos el nombre del disparador, Target indicará el nombre de la tabla a la que se asocia, y Event el tipo de evento que lo dispara. Se puede indicar FOR o AFTER (son sinónimos), seguido de la operación INSERT, UPDATE o DELETE, o bien INSTEAD OF seguido de la operación, si el disparador va a sustituir a la ejecución de la operación indicada.

En el ejemplo anterior, cada vez que se actualice una fila de la tabla sample_table, se guardará un registro en la tabla log_table para indicar las columnas que se han modificado y la fecha y hora del evento. La tabla log_table se crea con el siguiente comando:

create table log_table(table_name nvarchar(128),
log_message nvarchar(300),
log_date datetime)

Yo he implementado las tablas en el código del proyecto para evitar que se produzcan errores de compilación a la hora de implementarlo. Como en los ejemplos del artículo anterior, utilizamos la misma conexión que esté utilizando el comando SQL que haya lanzado el disparador, y para ello utilizamos la cadena de conexión “context connection=true”.

La clase SqlContext proporciona datos sobre el contexto de ejecución actual. En concreto, en el miembro TriggerContext podemos encontrar el método IsUpdatedColumn que nos permitirá saber que columnas en concreto se han modificado. Yo me he limitado a listar sus índices, pero podríamos obtener fácilmente sus nombres para dar más información.

En este otro ejemplo implemento un disparador que se ejecutará en lugar de una operación DELETE, guardando un mensaje en la tabla log_table con el nombre del usuario que ha intentado realizarlo:

[SqlTrigger(Name = "InsteadOfDeleteSampleTableTrigger",
Target = "sample_table",
Event = "INSTEAD OF DELETE")]
public static void InsteadOfDeleteSampleTableTrigger()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand LogCommand = new SqlCommand(
"insert into log_table(table_name,log_message,log_date) " +
"values('sample_table', " +
"SYSTEM_USER + ' tried to delete record', getdate())", conn);
conn.Open();
LogCommand.ExecuteNonQuery();
conn.Close();
}
}

Implementar nuevos tipos de datos

También es posible extender nuestras aplicaciones con nuevos tipos de datos, que podrán formar parte de las tablas de la base de datos y podremos manejar como cualquier objeto CLR, con sus propiedades y métodos particulares.

Estos tipos de datos pueden implementarse utilizando una clase o una estructura (struct). Yo he utilizado una estructura para crear el tipo SqlComplex, que implementa un número complejo con funcionalidad básica. Solo tenemos que decorar la clase o la estructura con el atributo SqlUserDefinedTypeAttribute, y se recomienda utilizar también el atributo SerializableAttribute. Debemos implementar el interfaz INullable para que el tipo de datos pueda manejar valores nulos. En el caso de implementar una clase, debe definirse también al menos un constructor sin parámetros.

[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct SqlComplex : INullable

El parámetro IsByteOrdered permite que se puedan realizar comparaciones lógicas entre los valores de este tipo de datos.

El interfaz INullable tiene un único miembro, la propiedad IsNull:

public bool IsNull
{
get
{
return _null;
}
}

Se debe implementar el método static Null, que debe devolver un valor nulo apropiado para el tipo:

public static SqlComplex Null
{
get
{
SqlComplex h = new SqlComplex();
h._null = true;
return h;
}
}

También debemos implementar métodos para convertir el objeto en una cadena de texto, ToString, o para construirlo a partir de una cadena de texto, el método static Parse:

public override string ToString()
{
if (_null)
{
return "NULL";
}
return _real.ToString() + (_imaginary < 0 ? "-" : "+") + _imaginary.ToString() + "i";
}
public static SqlComplex Parse(SqlString s)
{
if (s.IsNull)
return Null;
SqlComplex u = new SqlComplex();
string ss =
¨ s.ToString().Replace("i", "").Replace("I", "").Replace(" ", "");
double sign = 1;
if (ss.StartsWith("+"))
{
ss = ss.Substring(1);
}
else if (ss.StartsWith("-"))
{
sign = -1;
ss = ss.Substring(1);
}
int p2 = ss.IndexOfAny(new char[] { '+', '-' });
double v = sign * double.Parse(ss.Substring(0, p2));
u._real = v;
ss = ss.Substring(p2);
sign = 1;
if (ss.StartsWith("+"))
{
ss = ss.Substring(1);
}
else if (ss.StartsWith("-"))
{
sign = -1;
ss = ss.Substring(1);
}
v = sign * double.Parse(ss);
u._imaginary = v;
u._null = false;
return u;
}

En este enlace podéis encontrar detalles sobre los requisitos para la serialización de tipos definidos por el usuario.

Se pueden implementar propiedades y métodos a medida para estos tipos de datos, en este ejemplo, el tipo tiene una propiedad para exponer la parte real (Real) y la parte imaginaria del número (Imaginary):

public double Real
{
get
{
return _real;
}
set
{
_real = value;
}
}
public double Imaginary
{
get
{
return _imaginary;
}
set
{
_imaginary = value;
}
}

El método Module devuelve el módulo del número complejo:

[SqlMethod(OnNullCall = false)]
public double Module()
{
return Math.Sqrt(_real * _imaginary);
}

El atributo SqlMethodAttribute permite definir el comportamiento del método. En el ejemplo anterior hemos especificado que no se llame al método para valores nulos. SI un método va a cambiar el valor del objeto, se debe indicar en este atributo con el parámetro IsMutating, como en el siguiente ejemplo, que da un valor a la parte real e imaginaria del objeto:

[SqlMethod(OnNullCall = false, IsMutator = true)]
public void SetValue(double r, double i)
{
_real = r;
_imaginary = i;
_null = false;
}

Este método también es necesario para poder hacer un UPDATE de las dos propiedades del objeto a la vez, ya que la sintaxis de esta instrucción no permite referirse al mismo campo más de una vez.

En este enlace podéis encontrar más información sobre codificación de tipos definidos por el usuario.

Una vez implementado el tipo en SQL Server, se puede probar, por ejemplo, de la siguiente manera:

create table complex_table(id int, value SqlComplex)
GO
insert into complex_table(id, value) values(1, '20+10i')
GO
select * from complex_table where value = '20+10i'
GO
update complex_table set value.SetValue(5,5) where id = 1
GO
select value.ToString() from complex_table
GO