Working with CLR Objects in SQL Server 2005 or Above Part 1

Introduction

Sql Server 2005 introduces a new way of writing database object for .NET developers. In addition to Extended stored procedures we may now use CLR stored procedures to fetch or store data, write Triggers, User Defined Functions etc and get the full functionality of powerful .NET framework through SQL server.

For example, Suppose you want to create an xml parser which parses data and gives output. Let us think what are the options you will be having if you want to do this in database.

1. You can write a Normal Stored Procedure to handle this complex Logic of creating an XML output.
2. Use OpenXML, to read the xml, open cursor to read only the data within the output table from Open XML.
3. Use Manual String parsing technique.
4. Use XML Data Type introduced in Sql Server 2005.

From the above few techniques available the best way is to use XML Data Type. If you have used XML datatype ever, you might have already got the flavour of CLR types. XML is serialisable data type which you can use in Sql Server 2005. You can call the functions available to its objects.

In this article my intension is to make you understand how we can build our own data types, objects etc in SQL server.

Background

I think Database is the ideal place to store Business logic. We can create stored procedures, functions etc to create business logic so that we can only use these interfaces from our application and get data stored, not by creating insert/update statements. We can even check user previledges, if session, auth tokens and everything is stored inside the database, just before running one query.

Thus if we can write complex database logic using .NET classes, our task will be the easiest and also we could get all the benifits available to .NET classes within our stored procedures.

Advantages

Extended stored procedures are already there with earlier versions of DataBases. These objects can do anything in the system like normal executables. The main advantage of CLR over extended stored procedure is:

• CLR stored procedures are intended to work within Managed Environment. So all the benefit of Managed Environment (like Garbage Collection) are there with those objects.
• We can use the advantage of huge classes available with .NET library.
• Both of them uses DataBase Memory, so no new process will be created to run your code.

When we call these objects it first goes to the Assembly registered to the database, and find the class associated with the calling object. Then it calls that object with the context database connection.

From the above Diagram, you can think the main assembly hold all the object within itself. When an External world calls (which is this case is Application Object) those Objects, SQL server gets the defination of the object in the database. Then it reads the assembly associated with the current object and calls the method automatically.

Each object in the SQL database including the .NET CLR objects share the memory of SQL Server DataBase Engine. So if we call a CLR object directly, it will not relogin to the database, rather it will go on using the existing login connection. We will discuss about this later on.

Types of Objects Supported

CLR of SQL Server 2005 supports 5 types of objects

• Stored Procedures
• User Defined Functions
• User Defined Aggregate Functions
• Triggers
• User Defined Data Types

We can use each of them when required and import them to the Database Engine. Before we discuss each of them first let us create a sample application in Visual Studio.

Creating your First SQL Server Project

Start your Visual Studio, Go to New Project. You will be provided with the New Project Dialog Box. Choose DataBase From the Left hand side Tree, and Select SQL Server Project. Choose your desired location and Click OK.

A series of Message boxes will appear. First Appears:

Here you can choose the database connection. You can also choose Add new Reference to add a new database connection. This connection will be used by the Visual Studio to Deploy your application. After that 2 warning message boxes appears:

Just be affermative to these messageboxes if you are using Test database connection.

After you finish these steps a new project will be created with one folder named Test and an SQL file within it. We will come to this later on.

In the Solution explore, you will find one Stored procedure is file is already created. You can delete the file and select a new object based on your requirement.

Let us choose Stored Procedure First.

You will be prompted with a new dialog box to name the stored procedure. Name it whatever you like. In my sample I have created a class with name MyFirstCLRSP. Lets Write the code below:

See full detail: http://www.codeproject.com/KB/database/ClrStoredProcedure.aspx