SQL 2005 - CLR Integration (C#)

March 14, 2010 by Christoff Truter C#   SQL  

Back in 2004 - 2005 I created extended stored procedures for SQL 2000 using C++, soon afterwards we migrated to SQL 2005, which allows us to create stored procedures/functions/triggers/types etc via the .net CLR (using C#) - providing similar functionality.

Its quite powerful functionality, that allows us to do things we can't normally do within SQL e.g. my first CLR Procedure we used to send messages to mobile phones - which functioned as a notification about certain events happening within a customer account.

Thinking a bit further - we can add some proper regex functionality to SQL, perhaps improve XQuery support to SQL XML types - I've seen some code where devs added functionality to query the Sharepoint webservice from CLR procedures.

The API which aid us with CLR integration is located within the Microsoft.SqlServer.Server namespace

Lets have a quick look at creating some functions:

Trim function:

If you're one of those guys bugging microsoft into creating a trim function (and not into the whole ltrim+rtrim thing), why not write your own trim function? (Which provides me the opportunity to give an example of a simple scalar function)

[SqlFunction(DataAccess = DataAccessKind.None)]
public static String Trim([SqlFacet(MaxSize = -1)]String value)
{
	return value.Trim();
}

How do we deploy this function? Well, I know that it can be deployed via Visual Studio, but if you're stuck with a version that doesn't allow this (like me), you will need to create a class library, include the appropriate namespace (Microsoft.SqlServer.Server) and copy the compiled assembly to a folder of your choice.

Next you need to register the assembly within SQL (in the database you wish to use it):

CREATE ASSEMBLY CLRDemos FROM 'C:\procs\CLRDemo.dll' WITH PERMISSION_SET = SAFE;

If this fails, make sure you've got CLR enabled within SQL (restart required):

sp_configure'clr enabled', 1
RECONFIGURE

Once all of that works, you'll need to reference the CLR function, like so:

CREATE FUNCTION TRIM
(
	@value NVARCHAR(MAX)
) 
RETURNS NVARCHAR (MAX)
AS 
EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[Trim]

In this example I named my assembly CLRDemos, the class named functions lives in the namespace called CSTruter.com.

Split function:

Ever felt the need/desire/longing to split a string within SQL? The following table valued function will provide that functionality, basically we pass a separator and string and this function returns a table with the segments it managed to split.

[SqlFunction(DataAccess = DataAccessKind.None, FillRowMethodName = "FillRow")]
public static IEnumerable Split([SqlFacet(MaxSize = 10)]String separator, [SqlFacet(MaxSize = -1)]String value)
{
    Int32 i = 0;
    String[] values = value.Split(new String[] { separator }, StringSplitOptions.None);
    foreach (String v in values)
    {
        yield return new Object[] { i++, v };
    }
}

public static void FillRow(Object sender, out Int32 id, out String value)
{
    Object[] values = (Object[])sender;
    id = (Int32)values[0];
    value = (String)values[1];
}

Notice the FillRow method, the first parameter contains the current value of our iteration, the rest of the values represent fields that will be returned to the result set - in this case an unique id and a segment value.

Like the previous function, we need to reference the CLR function:

CREATE FUNCTION SPLIT
(
	@separator NVARCHAR(10),
	@value NVARCHAR(MAX)
) 
RETURNS TABLE(id INT, value NVARCHAR(MAX))
AS 
EXTERNAL NAME [CLRDemos].[CSTruter.com.functions].[Split]

viewFriends Procedure:

What if we need to access tables/data within our database from within our C# methods? Its quite simple, have a look at the following CLR Stored Procedure:

[SqlProcedure]
public static void viewFriends()
{
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT firstname, lastname FROM friends", connection);
        SqlDataReader reader = command.ExecuteReader();
        SqlContext.Pipe.Send(reader);
    }
}

Notice the connectionstring context connection=true, this simply tells the SqlConnection object to use the connection within context of our method - context being SQL.

The pipe class allows us to send rows to the resultset.

Like the functions we need to reference this procedure:

CREATE PROCEDURE viewFriends
AS EXTERNAL NAME [CLRDemos].[CSTruter.com.Procedures].[viewFriends]

getDayNames Procedure:

Lets imagine we want to return a resultset of data/collection from C#, e.g. a list of day names according to language culture - which can be found in the System.Globalization namespace.

[SqlProcedure]
public static void getDayNames([SqlFacet(MaxSize = 10)]String name)
{
    SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] 
    { 
        new SqlMetaData("id", System.Data.SqlDbType.Int), 
        new SqlMetaData("value", System.Data.SqlDbType.VarChar, 255)
    });

    CultureInfo culture = new CultureInfo(name);
    String[] DayNames = culture.DateTimeFormat.DayNames;

    SqlContext.Pipe.SendResultsStart(record);
    for (int i = 0; i < DayNames.Length; i++)
    {
        record.SetInt32(0, i);
        record.SetSqlString(1, DayNames[i]);
        SqlContext.Pipe.SendResultsRow(record);
    }
    SqlContext.Pipe.SendResultsEnd();
}

Notice SqlDataRecord & SqlMetaData, these classes allow us to define our resultset, once the developer passes a culture name e.g. en-GB (or af-ZA in my case), it returns a list of day names in that language.

Referencing this function will look something like this:

CREATE PROCEDURE getDayNames(@name AS NVARCHAR(10))
AS EXTERNAL NAME [CLRDemos].[CSTruter.com.Procedures].[getDayNames]

All in all it is quite easy to use this technology, hopefully this post gave you a quick look at this functionality.

Further reading