SQL 2005 - CLR Integration (C#)

March 14, 2010 by 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):

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

Once all of that works, you'll need to reference the CLR function, like so:
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:
	@separator NVARCHAR(10),
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:
public static void viewFriends()
    using (SqlConnection connection = new SqlConnection("context connection=true"))
        SqlCommand command = new SqlCommand("SELECT firstname, lastname FROM friends", connection);
        SqlDataReader reader = command.ExecuteReader();

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:
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.
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;

    for (int i = 0; i < DayNames.Length; i++)
        record.SetInt32(0, i);
        record.SetSqlString(1, DayNames[i]);

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:
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

Dependency Injection using Ninject and PostSharp

April 26, 2016

Aspect Orientated Programming (AOP) - A Brief look at PostSharp

April 15, 2016

How to become a better programmer.

April 5, 2016

JavaScript Fundamentals - Hoisting

March 22, 2016

Quick and dirty look at ECMAScript 6

March 17, 2016

JavaScript Promises

March 9, 2016

CSS Combinators

March 7, 2016

Shingles Awareness

March 4, 2016

Google Analytics : Measurement Protocol

February 24, 2016

Interesting JavaScript - SQLite (sql.js)

February 18, 2016

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

ASP.NET(C#) : Autocomplete TextBox - Part 1 (From Scratch)

Part one of how to create an autocomplete textbox using ASP.NET(C#)
March 27, 2011

Angular   Architecture   ASP.NET   Astronomy   Breaking News   C#   C++   Coding Horrors   Comedy   Cross Browser   CSS   Design Patterns   ES6   Google API   IIS   Integration   JavaScript   Jquery Mobile   Love and War   Microsoft Office   Ninject   Personal   PHP   PostSharp   SEO   South Africa   SQL   Threading   TypeScript   Visual Basic   XML