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:
  1. Carry out complex string manipulations
  2. Sending mails
  3. Dealing with NTFS (Note: requires some permissions and policies)
  4. 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:
  1. Open visual studio
  2. Create a class library
  3. Change the name of the class to "CLRFunctions"
  4. Change the namespace to "SQLServerClrNS" (Note: make sure the name is updated in the project properties not just in the cs file)
  5. Add "using System.Text.RegularExpressions;" to the using block
  6. Now you should write the two methods so that the code will look like that

  7.  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);  
         }  
       }  
     }  
    
  8. Build the solution and locate the generated DLL file
  9. The DLL file name should be "SQLServerCLR.dll"
  10. 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
  11. Open "Microsoft SQL Server Management Studio" on the SQL server
  12. Run the script below after updating the database name on the first line

  13.  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
    
  14. 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')  
    
  15. When you run the 2 lines above, you will get the results
     Hello Ahmed Tarek  
     Ahmed Tarek Hasan  
    
  16.  This means that you have just ran your first C# method on SQL :)

Notes:
  1. You should read the SQL script and you will find it well documented
  2. You first create/define a SQL assembly which refers to the C# DLL you created
  3. Then create SQL function to wrap a C# function in the DLL through the SQL assembly
  4. Now, you are ready to call this SQL function wherever you need and SQL supports calling functions
  5. 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
  6. 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

You can download the code from this link


I hope this approach will help you someday to carry out complex operations :)



Categories: , , ,