How to integrate CLR code in SQL Server I
In this article I will show you how you can easily extend the SQL of your SQL Server servers with functions and data types of the .NET framework CLR (Common Language Runtime). To do this, I will implement a function with scalar return value, another function with table return value, a pair of aggregation functions and a stored procedure.
In this link you can download the source code of the CLRIntegration project, written in CSharp using Visual Studio 2015.
The first thing you have to do is to activate the use of CLR assemblies in your database server, for this it is enough to execute the following code:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Then you have to create a SQL Server database project with Visual Studio. In the project configuration, you can select the version of the target database and the name of the schema where the functions and procedures will be created. In principle no further configuration is necessary.
User defined functions
First you can create a user defined function using the Add New Item option from the context menu of the project. This will create a class within which you can see a basic static method starting with which you can build your function.
public partial class UserDefinedFunctions
{
[SqlFunction]
public static SqlString SqlFunction1()
{
// Inserte el código aquí
return new SqlString (string.Empty);
}
}
You can change the name of the class and the function if you want. Let's look at the SqlFunction attribute. This attribute is necessary to indicate the type of database object that you are going to define. You should also note that the function is a static one, look also at the data type of both the function and its parameters, with the Sql prefix. This is important in order to interface with the database engine, for the rest, it is not very different from working with the usual CLR data types.
As an example, I have written a function that uses regular expressions to find patterns in a string, with the same name and parameters as REGEXP_INSTR, the homonymous function that you can find in Oracle and 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;
}
Just remember that the indexes of the strings in SQL Server start at 1, whereas in C# at 0.
Now it would be enough to publish the project, indicating the connection data with the server and the database in which you want to publish the assembly, and you can already use this function like any other SQL function. You can find it by opening the functions with scalar return value folder.
To create a function that returns the result of a query, the procedure is a bit more complex. As an example I have written a function that queries the database catalog of the(thus we do not have to create any sample table and fill it with data) all the tables that have some field whose name contains a certain pattern, which you can check by calling the previous function.
First, the SqlFunction attribute changes, because you have to add some parameters:
[SqlFunction(
DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.Read,
FillRowMethodName = "FindTableFields_FillRow",
TableDefinition = "Schema_name nvarchar(128), " +
"Table_name nvarchar(128), " +
"Column_name nvarchar(128)")]
The DataAccess parameter is used to indicate the type of access to data that the function will use; in this case, it is read only. As you are going to access tables of the system (the catalog), it is necessary to allow this with the parameter SystemDataAccess, otherwise the function will fail to execute, raising an exception.
With FillRowMethodName you indicate the name of the method that will be used to fill the data for each of the rows that the function return, this method will be in the same class as the main function. In this example I have put it all into the same class as that in the previous example.
Finally, with TableDefinition you indicate the structure of the returned rows. In this case, they have three columns, with the names of the schema, the table and the field.
The function must be also static and have IEnumerable as his return type:
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;
}
The function accepts as a parameter the regular expression model to look for. Internally it uses a connection whose connection string is "context connection = true". This indicates that the same connection that is using the code that calls this function must be used. If you attempt to make a connection to another database or server, or access a resource such as a disk file, an exception will raise, unless you have configured the assembly as EXTERNAL ACCESS or UNSAFE. The default case is the SAFE mode, in which you cannot have access of any external resources or connections.
Otherwise, the data you collect accumulates in any IEnumerable object, in this case an ArrayList, and you return this object as the result of the function. The FieldResult class is used to store the data.
Now you have to implement the function responsible for returning the rows of the query from this object:
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;
}
The first parameter is always the object with the data, and the rest are out parameters in which you return the different fields of the row.
Stored procedures
Writing a stored procedure is very similar, but in this case you have to use the SqlProcedure attribute to indicate the kind of database object. The example I have prepared receives an identifier and a value as parameters, and tries to update with them the corresponding row of a table. If the record does not exist, inserts a new one.
First, you have to create the table on the database server.
Create table sample_table(id int, data_value nvarchar(128))
And then write the procedure. In this example, in the same class as the functions:
[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();
}
}
Aggregation Functions
To end this first part, let's see how to define data aggregation functions, similar to sum, avg or count, for example.
In this case, you have to write data structures (struct) instead of classes, and you must implement four methods:
- Init: Used to initialize data at the beginning of the aggregation operation.
- Accumulate: This method will be called once for each of the rows in the query. It can have one or more parameters.
- Merge: sometimes, the system may decide to perform an aggregation in several blocks, in order to optimize execution. This method will be called to perform the aggregation of these blocks.
- Terminate: This method returns the final result of the aggregation.
The struct must be decorated with the Serializable attribute, and, with the SqlUserDefinedAggregate attribute you indicate that this is as an aggregation function.
The first example only takes one parameter, which will be the field to aggregate, this field must be of type int and the function will perform a binary AND. It is very easy to modify this code to perform a binary OR or XOR.
[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;
}
}
In the second and last example, I return to using regular expressions to count all the rows that match with a certain model, which is the second parameter of the Accumulate method:
[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;
}
}
And that's all for now. In the next article I will show you how to implement triggers and user defined data types.
