How to integrate CLR code in SQL Server II
In this article I will continue with the integration of CLR objects into a SQL Server database by showing how to implement triggers and create user-defined data types. It is a simple way to extend the functionality of our database applications with all the power provided by the CLR of the .NET framework.
In this other article you can see how to implement user defined functions and stored procedures. In this other link you can download the source code of the CLRIntegration project, written in CSharp with Visual Studio 2015.
Implementing triggers
The triggers are implemented as static methods of a class. You must decorate these methods with the SqlTriggerAttribute attribute to indicate that it is a database object of this type, the table on which they act and the event that fires them.
[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();
}
}
With the Name parameter of the SqlTriggerAttribute attribute, you indicate the name of the trigger; Target indicates the name of the table to which it is associated, and Event is for the type of event that fires it. You can use FOR or AFTER (they are synonymous), followed by INSERT, UPDATE or DELETE, or INSTEAD OF followed by the operation, if the trigger is intended to replace the execution of the indicated operation.
In the previous example, each time a row in the simple_table table is updated, a record is stored in the log_table table indicating the columns that have been modified and the date and time of the event. The log_table table is created with the following command:
create table log_table(table_name nvarchar(128),
log_message nvarchar(300),
log_date datetime)
I have implemented the tables in the project code to prevent compile errors from occurring when implementing it. As in the examples in the previous article, we use the same connection that is using the SQL command that fired the trigger, by providing the connection string "context connection = true".
The SqlContext class provides data about the current execution context. Specifically, in the TriggerContext member you can find the IsUpdatedColumn method that will allow you to know which columns in particular have been modified. I have simply listed their indexes, but you could easily get their names to give more information.
In this other example I have implemented a trigger that will execute instead of a DELETE operation, storing a message in the log_table table with the name of the user who tried to do it:
[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();
}
}
Implementing new data types
It is also possible to extend the applications with new data types, which may be part of the database tables and can be handled as any CLR object, with its particular properties and methods.
These data types can be implemented using a class or a struct. I have used a struct to create the SqlComplex type, which implements a complex number with basic functionality. You only have to decorate the class or the struct with the SqlUserDefinedTypeAttribute attribute, and it is also recommended to use the SerializableAttribute attribute. You have to implement the INullable interface so that the data type can handle null values. In the case of implementing a class, at least one constructor without parameters must be defined.
[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct SqlComplex : INullable
The IsByteOrdered parameter allows logical comparisons between values of this data type.
The INullable interface has a single member, the IsNull property:
public bool IsNull
{
get
{
return _null;
}
}
You must implement the Null static method, which must return a null value appropriate for the type:
public static SqlComplex Null
{
get
{
SqlComplex h = new SqlComplex();
h._null = true;
return h;
}
}
You have also to implement methods to convert the object to a text string, ToString, or to construct it from a string of text, the static method 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;
}
In this link you can find details about the requirements for the serialization of user defined types.
You can implement custom properties and methods for these data types, in this example, the type has a property to expose the real part (Real) and the imaginary part of the number (Imaginary):
public double Real
{
get
{
return _real;
}
set
{
_real = value;
}
}
public double Imaginary
{
get
{
return _imaginary;
}
set
{
_imaginary = value;
}
}
The Module method returns the complex number module:
[SqlMethod(OnNullCall = false)]
public double Module()
{
return Math.Sqrt(_real * _imaginary);
}
The SqlMethodAttribute attribute allows you to define the behavior of the method. In the previous example I have specified that the method is not used with null values. If a method is going to change the value of the object, you have to indicate it in this attribute with the parameter IsMutating, as in the following example, that changes the value of the real and imaginary part of the object:
[SqlMethod(OnNullCall = false, IsMutator = true)]
public void SetValue(double r, double i)
{
_real = r;
_imaginary = i;
_null = false;
}
This method is also necessary to be able to do an UPDATE of the two properties of the object at the same time, since the syntax of this instruction does not allow to reference to the same field more than once.
In this link you can find more information about coding user defined types.
Once the type is implemented in SQL Server, it can be tested, for example, in the following way:
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
