Hire my services

If you would like to hire my services, you can now do so by visiting the following link:

Website Design Darlington

Article Statistics

ArticlesLatest Entry
2121-May-2008

Code Bank Statistics

CategoryTipsLatest Entry
Applications420-Feb-2008
Cache118-Apr-2007
Controls926-Jul-2007
CSS107-Sep-2007
Database428-Aug-2007
Dates128-Aug-2007
Email105-Mar-2007
Files123-Apr-2007
HTML101-Mar-2007
Images226-Mar-2007
Pages115-Oct-2007
Sessions122-Nov-2007
SQL Server1518-Mar-2008
Strings309-May-2007
Skip Navigation LinksHome > Articles > Using CLR to access .NET functions in SQL Server

Using CLR to access .NET functions in SQL Server

Summary: An example of how we can create a function in .NET and then use them in SQL Server.Socialize it

According to Microsoft, SQL Server 2005 significantly enhances the database programming model by hosting the Microsoft .NET Framework 2.0 Common Language Runtime (CLR). This enables developers to write procedures, triggers, and functions in any of the CLR languages, particularly Microsoft Visual C# .NET, Microsoft Visual Basic .NET, and Microsoft Visual C++.

How do we implement this functionality?

In order to use CLR we have to:

  1) Write a .NET class with a public function
  2) Compile the class to a DLL
  3) Register the DLL with SQL Server
  4) Create a SQL Server function to access the .NET function

Creating an example function and DLL

To provide a simple example that you can use, we need to first create a new project. In Visual Studio, start a new Class Library project and call it SQLServerCLRTest. Then, create a new Class called CLRFunctions and add the following code:

You'll notice that we have a simple function (which is Public Shared so that SQL Server can access it) which accepts a name as parameter and returns a simple message saying "Hello" to whichever name is passed in.

Now, we need to create a DLL out of this project so that we can register it with SQL Server. The easiest way to do this, is to simply click the Build->Build Solution menu item in VS which will build the application and create the DLL. As your project will be in debug mode the DLL will usually be found at a location such as:

Once you've located this DLL we can copy it over to our SQL Server machine or simply make a note of this path if that happens to be the same machine as our development machine.

Turn on CLR functionality

By default, CLR is disabled in SQL Server so to turn it on we need to run this command against our database:

Registering the DLL

In order to use the function we wrote, we first have to register the DLL with SQL Server. To do this we have to create an assembly, assign it a name and point the assembly at the DLL. Using the path to the DLL we created, run the following command against the database:

Accessing our function from SQL Server

In order to access our .NET function, we need to create a SQL Server function which makes use of the "EXTERNAL NAME" option which informs SQL Server that we will be using a CLR function. The function we will be creating will look like this:

There's two things to note about the above function. The first is that we use an nvarchar which will be the equivilant of the .NET string (it will produce an error if you try to use a varchar and the second is the format of the "EXTERNAL NAME" path. From the reading I've done, the format should be:

MyAssemblyName.MyClassName.MyMethodName

However, when I tried this I received an error from SQL Server stating that it couldn't find the type, so in order to get it to work I had to use the format:

MyAssemblyName.[MyAssemblyName.MyClassName].MyMethodName

Calling the function

Now that we've registered our function, we should be able to call it by using the following statement:

Hopefully, when you run this code you'll get a simple "Hello Mark" returned in your results.

This is obviously just an example to show you how to implement CLR, but to create something useful you could look at creating functions that deal with Regular Expressions, interact with the File System or Registry, send emails or simply access any resources that are located outside of SQL Server.

Reader Comments

1. Jason 19/05/2007 05:39:48

2. Mark Smith 19/05/2007 06:33:00

3. Rani 19/05/2007 23:09:21

4. Mark Smith 22/05/2007 00:34:32

5. Dimitar Voynov 06/06/2007 11:00:47

6. Prawin 06/06/2007 22:08:15

7. Asif Nadeem 07/06/2007 04:43:47

8. praveen 07/06/2007 05:34:55

9. Voltaire SR 13/06/2007 22:20:02

10. Steven Bey 09/08/2007 02:47:43

11. Mark Smith 13/08/2007 07:14:34

12. lauri bellamy 24/04/2008 23:57:08

13. Perry 21/05/2008 16:08:34

14. Abbass Mustafa 25/09/2008 00:54:41

15. Nate 05/02/2009 17:14:51

Add your comments

Please keep your comments relevant to this page. Any inappropriate or purely promotional comments may be removed. Email addresses are never displayed but are required so you can confirm your comments.

Your Name:
 
Your Email:
 
Add your comments: