Creating Managed Objects in SQL Server 2005

Introduction

SQL Server 2005 has a lot of outstanding features that distinguishes it from other versions of SQL Server. These include the Service Broker that allows you to create SOA loosely coupled applications in a database, Notification Services that sends notification to the subscribers when a specific event occurs, Security enhancements which gives finer control access to the object and avoids some of the common security breaches. Among them one of the most interesting features is the integration of CLR with the database engine. This article gives a small demo on how to use CLR in SQL Server 2005 and how to create managed objects in SQL Server 2005.

CLR language vs T-SQL

Common Language Runtime (CLR) is the runtime environment that every .NET application shares and it provides services like garbage collection, threading support, memory allocations, code access security etc. to the .NET application. It is the heart of the .NET Framework. Now CLR is integrated with SQL Server 2005(commonly known as Yukon). This feature gives a way to create database objects like triggers, user defined functions, and aggregates through the .NET language and VS.NET 2005. Now, you can D3 (develop, debug and deploy) the database objects.

Integration of CLR with SQL Server 2005 forces the developer to think as to when he should use a .NET compatible language like C# and when to use T-SQL. T-SQL has a lot of features and has been used as a uniform standard for accessing and manipulating databases since long by Microsoft right from earlier versions of SQL Server in 1988. Is there anything wrong in that? Well, if you look at it, you will find that you generally develop the UI and the programming logic using a language other than T-SQL. Secondly, T-SQL has a different programming model than the languages like C#. For e.g. how can we use arrays, collections, classes and OOPS concept in T-SQL? Though there is a way to work on it in T-SQL, you need to be aware of the T-SQL programming statements and a lot of code is required to implement the task that can be easily done through C#.

CLR has a lot of advantages like security, type safety, enhanced programming model etc. If we analyze deeply, we will find that T-SQL is good for the set based operations and might not be as efficient as C# or VB.NET. .NET language leverages the unleashed power of the Framework class libraries which makes it easy to do operations in C# rather than in T-SQL. For e.g. how can we split the string in T-SQL. Here in C#, we can easily do that with the split function of System.String class. So the code that takes many lines in T-SQL can be easily done with a few lines of code in C#. One of the main points that needs to be considered is, as to where the code is to be kept, near the server or near the client. Now with SQL Server 2005, you can keep the C# code in the client as well as in the server but T-SQL can only be kept at the server.

SQLAccess

Do you now think that any .NET code can be hosted by SQL Server 2005? The fact is you cannot. You need to compile your code to DLLs which can be hosted by SQL Server 2005. Secondly, SQL Server 2005 can't work with multi-file assemblies so you need to create a single file assembly. Before we delve deep into the code, I would like to mention that if we are developing database objects, we will refer to SQLAccess.dll, which is used to access other database related objects in a bit optimized manner. This contains a namespace called System.Data.SQLSever. It contains a few classes like SQLContext through which you can get the information about the current database, connections, trigger context etc. SQLConnection, SQLCommand, SQLCommand, SQLDataReader do the same task as they do in ADO.NET 2.0.

.NET code in SQL Server 2005

Following are the steps needed to use .NET code in SQL Server 205:

1. Create a static function/method in C# in some class.
2. Compile the code in the form of DLLs.
3. Import the assembly in SQL Server 2005 and supply a name to it.
4. Create a stored procedure, triggers, UDFs and call the method from the class by giving it the name you specified in step 3 with the namespaces and classes.

Methods in .NET

You can create any static method and use the classes residing in the SQLAccess.dll to use the database object and use the .NET Framework classes to access BCL. Let's say you are interested in creating a stored procedure:

using String;
public static class Split
{
   public static void GetContacts() 
   { 
      SQLCommand com = SQLContext.GetCommand();
      
      com.CommandText = " Select * from Customers";
      com.CommandType = CommandType.Text;
      
      SQLPipe p = new SQLPipe();
      p.Send(com.ExecuteReader());
   }
}

There are a few differences between programming ADO.NET, inside or outside the SQL Server. The basic difference is how to get hold of the object, so in the example mentioned above, rather than instantiating the command object directly, we are using the helper class called SqlContext to create the command. Along with that, when you get a DataSet or DataReader, there is a need for sending it back to the caller who is actually executing the stored procedure. For this, we have a class called SqlPipe which can be used to send results back to the caller. You can also create parameterized stored procedure, which is demonstrated in the next example:

public static class Split
{
   public static void GetContacts(SQLString Customerid ) 
   { 
      SQLCommand com = SQLContext.GetCommand();
      
      com.CommandText = "Select * from Customers where Customerid = @id";
      com.CommandType = CommandType.Text;
      com.Parameters.Add("@id",SQLDbType.Char, 100).value= Customerid; 
      
      SQLPipe p = new SQLPipe();
      p.Send(com.ExecuteReader());
   }
}

In this code, we are just getting the data from the database. The second step will be to compile the code. You can compile your code using Visual Studio 2005 or using the specific compiler through the command prompt. As I had mentioned earlier, SQL Server 2005 can only host DLLs and not EXEs. So you can't host Windows application in SQL Server 2005. Compile the class to get the DLL say splitter.dll residing in c:\nishith folder.

Importing the assembly

Third step is to import the assembly created above in your SQL Server. For importing, we have two ways:

1. Using SQL Server Management Studio.
2. Using the New T-SQL Statement.

If you want to import the assembly through SQL Server Management Studio, right click the Assemblies folder in the Programmability folder for the database in the Object Explorer and then click New Assembly. Another way of importing the assembly is through the Create Assembly statement.

Create assembly splitter

From 'c:\nishith\splitter.dll'
With Permission_set = SAFE

In this statement, we are specifying the name of the assembly through which it would be recognized in the SQL Server object. This name is specified in the stored procedures, triggers, etc. Along with specifying the assembly name, you have to specify the location of the assembly which is present at c:\nishith\splitter.dll. You can specify the Permission_Set which tells what your assembly can do. Can it access other .NET resources or even unmanaged resource? By default, its Permission_Set value is SAFE. Other options are EXTERNAL ACCESS or UNSAFE. Once you execute the above statement, SQL Server checks the dependencies in the same folder in which your assembly resides, and if any dependencies are missing, Create Assembly fails. If you want to list out the assemblies inside the SQL Server, you can use:

Select * from sys.Assemblies

Now if your Create Assembly successfully executes, you can now use it in any of the stored procedures, triggers, or even user defined functions. The definition of stored procedure will change after the AS clause:

Create Procedure Employee.GetData 
as
EXTERNAL NAME Splitter.Split.GetContacts

The only thing you need to specify while calling the managed code is the keyword External Name which tells the SQL Server engine that the method is external to SQL Server. If there is a namespace specified while creating the class then you need to include the namespace name as a fully qualified name. After you create the stored procedure, you can use it like any other stored procedure.

SQL Server project type

Visual Studio 2005 ships with SQL Server project type which can develop, debug and deploy within the IDE. SQL Server Project allows you to create managed objects like triggers, stored procedures using the predefined template and it uses attribute based programming to do that. If you look at the references, you will find the reference of SqlAccess.Dll. Visual studio 2005 adds a file named Test.SQL to a testScript folder. What you need to do additionally is decorate the class with the appropriate attribute residing in the System.Data.SQL folder. For e.g. if you are creating the stored procedure then your method should be decorated with SQLProcedure. You can include any number of stored procedures within the class:

public static class Split
{
   [SQLProcedure]
   public static void GetContacts() 
   { 
      SQLCommand com = SQLContext.GetCommand();
      
      com.CommandText = " Select * from Customers";
      com.CommandType = CommandType.Text;
      
      SQLPipe p = new SQLPipe();
      p.Send(com.ExecuteReader());
   }
}

Once defined, click on Deploy Projects in the Build Menu to compile your assembly and register it with SQL Server 2005. Visual studio 2005 actually runs Tests.SQL residing in the Test Script folder to register the object. Internally it calls, the Create Assembly method to register the managed objects in SQL Server 2005.

Happy coding!