[Development Simply Put] How To Call C# Methods In SQL!!!!

How To Call C# Methods In SQL!!!!

Posted by Ahmed Tarek Hasan on 10/05/2012 05:18:00 PM with No comments

Have you ever needed to use regular expressions in a SQL SELECT statement?

Me myself, I have. One day I needed to write a SQL script to fix a data issue on the production environment DB. Fixing this issue required to write a complex script which will carry out complex logic including comparing some columns data values with other using some patterns. It was a nightmare for me and some of my team members to write such script especially that SQL doesn't heavily support such operations.

So, I dreamed that I can use regular expressions in my SQL UPDATE statement, this will spare my life. But, how?? SQL doesn't know regular expressions.

So, i decided to search for any alternatives and I found it. You can write C# code and fire it from SQL!!!

I couldn't believe my eyes, this means that the problem is now fixed and now I can do magic. I freed my mind a bit and tried to think of what I can do with such thing. Here are some of the ideas i got in mind then, i can write C# methods which can:

Carry out complex string manipulations
Sending mails
Dealing with NTFS (Note: requires some permissions and policies)
Calling web services (WOW!!!)

And many other wild ideas. So, now let's see how we can do this using a simple example with two functions, one to just return "Hello " followed by a name which is passed to the method, and the other replaces a string with another using regular expressions.

So, to achieve this, we need to follow these steps:

Open visual studio
Create a class library
Change the name of the class to "CLRFunctions"
Change the namespace to "SQLServerClrNS" (Note: make sure the name is updated in the project properties not just in the cs file)
Add "using System.Text.RegularExpressions;" to the using block
Now you should write the two methods so that the code will look like that

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Text.RegularExpressions;  
   
 namespace SQLServerClrNS  
 {
   public class CLRFunctions  
   {  
     public static string HelloWorld(string name)  
     {  
       return "Hello " + name;  
     }  
   
     public static string ReplaceWithRegExp(string SourceStr, string RegExp, string ReplacementStr)  
     {  
       return Regex.Replace(SourceStr, RegExp, ReplacementStr);  
     }  
   }  
 }

Build the solution and locate the generated DLL file
The DLL file name should be "SQLServerCLR.dll"
Copy "SQLServerCLR.dll" to a folder on the SQL server, for the sake of this tutorial, copy it to "C:\" so that the file "C:\SQLServerCLR.dll" is found on the SQL server
Open "Microsoft SQL Server Management Studio" on the SQL server
Run the script below after updating the database name on the first line

 USE [TestDB]  
 GO  
   
 --Turning 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  
 EXEC sp_configure 'clr enabled', 1  
 GO  
 RECONFIGURE  
 GO  
   
 --Checking if the SQL assesmbly we want to create already exists  
 --If exists, drop it to recreate  
 IF EXISTS  
 (  
         SELECT *  
         FROM sys.assemblies  
         WHERE name = 'SQLServerCLRAssembly'  
 )  
 BEGIN  
         -- To drop the SQL assembly, we must first drop all functions  
         -- which reference this SQL assembly  
         IF EXISTS  
         (  
     SELECT *  
     FROM INFORMATION_SCHEMA.ROUTINES  
     WHERE ROUTINE_NAME = 'HelloWorld'  
     AND ROUTINE_SCHEMA = 'dbo'  
     AND ROUTINE_TYPE = 'FUNCTION'  
         )  
         DROP FUNCTION [dbo].HelloWorld  
   
         IF EXISTS  
         (  
     SELECT *  
     FROM INFORMATION_SCHEMA.ROUTINES  
     WHERE ROUTINE_NAME = 'ReplaceWithRegExp'  
     AND ROUTINE_SCHEMA = 'dbo'  
     AND ROUTINE_TYPE = 'FUNCTION'  
         )  
         DROP FUNCTION [dbo].ReplaceWithRegExp  
   
         -- Dropping the SQL assmbly  
         DROP ASSEMBLY SQLServerCLRAssembly  
 END  
   
 -- Creating the SQL assembly and linking it to the C# library DLL we created  
 CREATE ASSEMBLY SQLServerCLRAssembly FROM 'C:\SQLServerCLR.dll'  
 GO  
   
 -- Creating SQL wraping functions to use the C# methods we created in the C# library  
 CREATE FUNCTION dbo.HelloWorld  
 (  
         @name as nvarchar(MAX)  
 )  
 RETURNS nvarchar(MAX)  
 AS EXTERNAL NAME SQLServerCLRAssembly.[SQLServerClrNS.CLRFunctions].HelloWorld  
 --SQLAssemblyName.[C#NameSpace".C#ClassName].C#MethodName  
 GO  
   
 CREATE FUNCTION dbo.ReplaceWithRegExp  
 (  
         @SourceStr as nvarchar(MAX)  
         , @RegExp as nvarchar(MAX)  
         , @ReplacementStr as nvarchar(MAX)  
 )  
 RETURNS nvarchar(MAX)  
 AS EXTERNAL NAME SQLServerCLRAssembly.[SQLServerClrNS.CLRFunctions].ReplaceWithRegExp  
 --SQLAssemblyName.[C#NameSpace".C#ClassName].C#MethodName  
 GO

Now, you are ready to run a test, so run the 2 lines below

 SELECT dbo.HelloWorld('Ahmed Tarek')  
 SELECT dbo.ReplaceWithRegExp('Ahmed Tarek', 'Tarek', 'Tarek Hasan')

When you run the 2 lines above, you will get the results
```
 Hello Ahmed Tarek  
 Ahmed Tarek Hasan  
```
This means that you have just ran your first C# method on SQL :)

Notes:

You should read the SQL script and you will find it well documented
You first create/define a SQL assembly which refers to the C# DLL you created
Then create SQL function to wrap a C# function in the DLL through the SQL assembly
Now, you are ready to call this SQL function wherever you need and SQL supports calling functions
I wrote the SQL script in a way that you don't need to make big changes in it when just updating the C# DLL file. In other words, I wrote some code to drop the SQL assembly and its depending functions if they already exist so that you can run the whole script more than once without worrying about SQL errors due to creating an already existing SQL assembly and functions
You can delete the DLL file from the path on the SQL server NTFS (in our example, "C:\SQLServerCLR.dll") after running the SQL script. It is not needed anymore