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


Related Downloads

Add missing XQuery functions to SQL using C# CLR Procedure

Simple C# SQL CLR Stored Procedure and Function Demo


Leave a Comment



Latest Posts

Getting my hands dirty with TypeScript

February 17, 2015

Quick and dirty look at TypeScript

January 29, 2015

My personal life in review

January 21, 2015

Quick rundown of possible new C# 6.0 features (perhaps, maybe - I think)

January 19, 2015

Proof of Concept - JavaScript Mediator Framework

January 13, 2015

JavaScript Tip - Benchmarking

January 7, 2015

Google's new and improved reCAPTCHA - simple PHP implementation

January 5, 2015

NASA missions to follow in 2015

January 3, 2015

Online content censorship - South Africa

January 2, 2015

Enhance Customer Registration Process with Cutting-Edge Solutions from Card Scanning Solutions Inc.

November 4, 2013


Most Commented on Posts

Moving items between listboxes in ASP.net/PHP example

Move items between two listboxes in ASP.net(C#, VB.NET) and PHP
June 12, 2008

Simple WYSIWYG Editor

Creating a WYSIWYG textbox for your website is actually quite simple.
February 1, 2007

C# YouTube : Google API

Post on how to integrate with YouTube using the Google Data API
March 12, 2011

Populate a TreeView Control C#

Populate a TreeView control in a windows application.
August 27, 2009

Cross Browser Issues: Firefox Word Wrapping

Firefox word wrapping issues
June 9, 2008


Angular   ASP.NET   Astronomy   C#   C++   Coding Horrors   Comedy   Cross Browser   Design Patterns   Google API   IIS   Integration   JavaScript   Love and War   Microsoft Office   Personal   PHP   South Africa   SQL   Threading   TypeScript   Visual Basic   XML