SQL - CLR Integration in Visual Studio .NET

The SQL – CLR integration feature has been into existence since VS 2005. The most amazing feature of this is, frankly speaking , you can create a dll using VS.Net and then you can access that dll’s logic in SQL Server 2005.. Wanna know more about this , then lets get it started…

The first and foremost point is to "ENABLE" the CLR functionality in Sql Server 2005 ,( 'coz by default it's DISABLED) which you can do it in this way ... Write the below given commands in the Sql Server Management Studio IDE in the database where you want to perform this.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Step1: Create a SQL Server Database Project in Visual Studio .

I named it as "My Project", you can name it as you like.

Now it asks for a Database connection instance ,like the one shown below.





Select your desired database conection or create a new one by clicking " Add New Reference".

Step2: Right Click on the Project name and add a new item " User-Defined Function" ( I have used function here, you can create a Stored Procedure or trigger as well ... depending on your requirement ).

By default , the Function Class contains a function returning "Hello " . So we'll use that one as our function.

Step 3 : Now , BUILD the project and then the dll is created for that project in its root folder.

Now , right click on the project and click "Deploy" . This will deploy your project onto the instance of Sql Server and also creates an ASSEMBLY with the same name as per the name of your project's dll. for eg; here it's name is MyProject .

To check out whether the assembly has been created or not , execute this in Sql Server Management Studio IDE ,

Select * from sys.Assemblies

This will display the registered assemblies in that database .

Step 4: As of now the assembly is created, so now we need to create a FUNCTION that references that assembly .

CREATE function MyFunction ()
returns nvarchar(15)
AS EXTERNAL NAME MyProject.[UserDefinedFunctions].Function1

Note : MyFunction () is the name of the function you want to create.

MyProject is the name of the Assembly ,which got created when we click the DEPLOY option .

UserDefinedFunctions is the name of the Parent Class present in the file Function1.cs

Function1 is the name of the function present in the file Function1.cs

Execute the above code in Sql Server Management Studio IDE and now your Function is ready to be used . Check it out by executing the below command,

Select dbo.MyFunction ()

Now this returns the output as "Hello" ..

From this article , we have learnt as to how one can use the SQL - CLR Integration feature of Visual Studio .Net.

Hope you liked the article . Till then keep rocking and enjoy programming .