SQLCLR

In SQL Server there are certain things that are good to implement using T-SQL, but certain requirements of real world can be better implemented using CLR (Common Language Runtime). CLR is better over T-SQL when we want to do high mathematical calculations, or implement a code that involves nested loops or recursions. Database objects like stored procedure, triggers, functions etc…can be written in CLR. We can write the code using C# or VB. The example that I will be demonstrating here is implemented in C#.

So let’s get started with the implementation

1.     First thing required is “Open your Visual Studio”. If you do not have, express edition can be downloaded from

   http://www.microsoft.com/express/Downloads/#2010-Visual-CS

 

2.     Then we create our usual table Breverage  and populate some data in it using SQL Server Management Studio

Create table Breverage

(ID  int Identity(1,1) NOT NULL,

BreverageName varchar(60),

ListPrice money,

DatePurchased date)

 

Populate data

 

Insert Breverage values('Lemonade', 20, '20100101')

Insert Breverage values('Soda', 10, '20100201')

Insert Breverage values('Wine', 25, '20100301')

Insert Breverage values('Chai', 20, '20100401')

Insert Breverage values('Espresso', 15, '20100501')

Insert Breverage values('Milkshake', 8, '20100601')

Insert Breverage values('Pepsi', 12, '20100601')

Insert Breverage values('Sweet Tea', 6, '20100701')

Insert Breverage values('Margarita', 18, '20100701')

Insert Breverage values('Coke', 21, '20100901')

Insert Breverage values('Latte', 17, '20100901')

Insert Breverage values('Water', 5, '20101001')

Insert Breverage values('Syrups', 9, '20101101')

Insert Breverage values('Juice', 30, '20101201') 

 

3.     Start with a new Project. Go to New and Select Project as displayed below:

 

4. Choose Class Library and just for the purposes of this demonstration I am using the default name ClassLibrary1, if you want you can give it more meaningful name based on your project. Click on OK 

5.     Once this is done we will start with our code

using System;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

 

namespace ClassLibrary1

{

    class Class2

    {

    }

    public class BreverageList

    {

        public static void getBreverage()

        {

            using (SqlConnection conn = new SqlConnection("context connection=true"))

            {

                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"Select * from Breverage";

                conn.Open();

                SqlContext.Pipe.ExecuteAndSend(cmd);

            }

        }

    }

} 

Before we proceed further few note points about above code:

It is important that the class created is public

public class BreverageList 

Since I am running this code locally I am using ("context connection=true") which will connect to the SQL Server instance within which I am already executing, if the session has created any locks this program would not be affected by it.

using (SqlConnection conn = new SqlConnection("context connection=true"))

SqlContext and SqlPipe are contained in Microsoft.SqlServer.Server namespace and these classes are imperative for this complete implementation.

6.     Above program needs to be saved and for the sake of simplicity I am saving it in my C:\ drive

7.     Next step is to create .dll file for the code developed above. This can be done by executing the C# compiler(csc.exe) which is located in

 

C:\Windows\Microsoft.NET\Framework\v3.5

 

It is important before we compile using csc.exe we need to make sure the environment variables are set.

 

In order to get this accomplished we need to compile in command prompt

 

Csc.exe /target:library Class2.cs 

Class2.dll is created in the same location as Class2.cs. In our above demonstration in C:\ drive

8.     We now need to create the assembly in SQL Server using Management Studio

create assembly assm_Breverage

from 'C:\Class2.dll'

This assembly can be viewed either by running

select * from sys.assemblies

or under Programmability->Assemblies- assm_Breverage(assembly name) 

9.     Now we need to tie this assembly to a procedure

Create Procedure GetBrevarage

As external Name assm_Breverage."ClassLibrary1.BreverageList".getBreverage

  

10.     Next we would enable CLR in DB

 

Exec sp_configure ‘clr enabled’,1

Reconfigure with override

 

Exec GetBrevarage

Result:

 

 

Tags: , , ,

SQL Server