Execute .net code under SQL Server 2005

• Download ManagedCodeAndSQLServer.zip - 13.9 KB

Introduction

First of all I would like to congratulate Microsoft Development Team for their superb and innovative technology using which we can use managed code written in any of the .net supported language into Ms SQL Server 2005 Stored Procedure. If I make it simple then "Now you can call any function written in .net class library in your SQL Server stored procedure". This technology will allow us to use the features of .net language with the extendibility of SQL Server.

Description

I got a article of using MSMQ from SQL Server in codeproject written by a gentlemen regarding MSMQ, and Messaging was used from SQL SP. I tried to show the constraints and limitation of using such assemblies and the reguired configuration in SQL Server 2005, so that the developers can start with. And I am sharing the practical problems which I faced in the actual implementation of this.

Before starting you have to enable the managed code execution feature of the SQL Server which is disabled by default. To enable it execute the following code as a query in your SQL Server query editor.

sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

Now your server is ready to run managed code under its runtime. But let me tell you it's not the magic but it's the same technology being used by the .net framework. The only important part is that now SQL Server also is able to execute code on CLR. Isn't it great? Before writing this article I did a lot of Research on this, I got a lot of articles explaining the same. But I faced a lot of problems because things were scattered. Now I am trying to simulate the whole procedure in this article. I did this study because of my specific requirements. I was using Message Queuing Service of Microsoft in C# .net. But after some time I got a requirement in which I need to send a message from Stored Procedure to MSMQ Queue. And till that time for me it was possible by using System. Messaging namespace of .net runtime only. I know most of the developers are wondering about MSMQ. Don't worry I won't go into the details of that right now. We will start with a simple application which will use a simple string returning function of C# .net class library. Let's start with the class library.

? Start .net 2005 studio.
? Open a new class library.
? Select C# as the language.
? Name the project as ManagedCodeAndSQLServer.
? By default you will find a class named Class1.cs created for you.
? Rename it with BaseFunctionClass.cs.

Create a simple function in the class as following:

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace ManagedCodeAndSQLServer
{
    public class BaseFunctionClass
    {
        #region "Default Constructor"
        public BaseFunctionClass()
        { 
                   
        }
        #endregion

        #region "Welcome Function"
        /// <summary>
        /// This function will be called from the SQL Stored Procedure.
        /// </summary>
        /// Name
        /// <returns>Welcome Message</returns>
        [SqlProcedure]
        public static void GetMessage(SqlString strName, out SqlString  
        strMessge)
        {
          strMessge = "Welcome," + strName + ", " + "your code is getting
          executed under CLR !";
        }

        #endregion
    }
}

Important Points

I hope you are able to notice some new things in the above code. First of all the [SqlProcedure] attribute on the GetMessage function, this intimates the CLR that the function is callable from SQL Server Stored procedure. The things that I am telling you are my own experiences; you may not found these things on articles published on Internet. When using a CLR function you must remember that the SQL server nvarchar is equivalent to System.String. But the most important part is that "CLR enable procedure can only return Int32, Int and void data types". That's why I have use output parameter with the GetMessage function. Also I have used SqlString instead of System.String. Please keep all the above things in mind.

Now build the project and leave it because your work from the .net class library point of view is over. Now come to your SQL Server again. I hope you still remember that we enabled CLR integration in our server at the start of the article. If not, then don't wait for me to come and enable it.
We have to register ManagedCodeAndSQLServer.dll as an assembly in our Database. To register the assembly you should have owner right in you database or you should be the local system admin or Server Admin. The assebmlies that we are going to register should be registerd in UnSafe mode. Otherwise they would not be able to accesss resources external to SQL Server 2005. And to register an unsafe assembly you sholud have 'unsafe' right enable in your username or role. All the above things are very important so take care of it, otherwise you won't be able to register your assembly. So create a database 'TestingCLR'. Before registering the .net assembly in the Database you have to set the trust worthy option of the database on using the following code:

ALTER DATABASE TestingCLR SET TRUSTWORTHY ON
GO

Now Register ManagedCodeAndSQLServer.dll under that using the following code:

CREATE ASSEMBLY ManagedCodeAndSQLServer
AUTHORIZATION dbo
FROM 'E:\Important\SQL\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\
Debug\ManagedCodeAndSQLServer.dll'
WITH PERMISSION_SET = UNSAFE
GO

I am again saying if the dbo is not having 'unsafe assembly' right it won't work. Similarly if I am using specific class of .net in my dll I have to register it prior to dll registration in the same way. For example I am using System.Messaging in my assemly, then I have to register it using the following code:

CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

To use the normal functionality we need not to registered dll from .net runtime as in our case Now in your server explorer, go to your TestingCLR database, select Programmability and in Assemblies you will find an assembly registered with the name given by you i.e., "HelloDotNet". Now you are almost done. You should know the full path (namespace convention) of the function that you are going to use from that assembly. In our case it is: ManagedCodeAndSQLServer.BaseFunctionClass and GetMessage is our function to be used. Now I am creating a simple Stored Procedure to use this assebmly as following:

CREATE PROCEDURE usp_UseHelloDotNetAssembly
@name nvarchar(200),
@msg nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.
BaseFunctionClass].GetMessage
GO

To execute the Procedure:

DECLARE @msg varchar(MAX)
EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output
PRINT @msg

You will get the following output: Welcome,Kittu And Tannu, your code is getting executed under CLR !

Try it and enjoy…….. TC……