Building managed code using Common Language Runtime (CLR) Integration in SQL Server 2005

Introduction

In this article, I am going to talk about a cool new feature of SQL Server 2005 called "CLR Integration", its advantages and how to program database objects using managed code with a simple project in Visual Studio 2005 beta 2.

What is CLR integration?

The Common Language Runtime which is the core .NET Framework component is now integrated with SQL Server 2005 code named Yukon and is called CLR integration. So what it means to developers is that the database objects like the user defined types (UDT), user defined functions (UDF), tables, stored procedures and triggers can be built by using C#, VB.NET or any .NET supported languages. Developers can leverage the rich features of the managed code such as cross language integration, object life time management, code access security, etc., for programming database objects, and also the object oriented capabilities of the .NET supported languages. TSQL is good at data access and management but it’s not a full fledged programming language.

SQL Server essentially acts as the operating system for the CLR when it is hosted inside SQL Server. The CLR calls low-level routines implemented by SQL Server for threading, scheduling, synchronization, and memory management. These are the same primitives that the rest of the SQL Server engine uses.

Advantages of CLR integration

There are numerous advantages provided by CLR integration in SQL Server 2005:

1. We can use the .NET Framework Base Class Libraries (BCL) while creating the stored procedures and triggers for complex execution logic, also for string manipulation, cryptography and file management.
2. We can write better code using the object oriented capabilities such as encapsulation, polymorphism and inheritance provided by C#, VB.NET. We can make the code more organized and manageable.
3. Managed code ensures type safety. Before the code gets executed, CLR verifies that the code is safe.
4. Provides better memory management. The CLR calls SQL Server primitives for allocating and de-allocating its memory. Because the memory used by the CLR is accounted for in the total memory usage of the system, SQL Server can stay within its configured memory limits and ensure the CLR and SQL Server are not competing with each other for memory.
5. In general managed code gives better performance but with CLR integration there are some performance considerations. The performance varies depending on the context and usage of the managed code. For example, all memory intensive functions which do not access data can be written in managed code for optimal performance. However, the TSQL functions perform data access more efficiently than CLR integration.

TSQL vs. CLR integration

Depending on the context you have to take decision whether to use TSQL or managed code. I have stated the situations to make your decision simple.

Use CLR integration in the following situations:

1. When the program requires complex logic which can be achieved by using object orientation, exception handling and complex conditional constructs.
2. When the program requires the usage of .NET Base Class Library (BCL) for cryptography, handling file system, calling web services or any other tasks that are not impossible with TSQL.
3. When the program is CPU intensive. Since managed code is always compiled, it runs more efficiently.
4. Before using any extended stored procedures, check if the same functionality can be achieved by using managed code. If so then go for managed code for type safety.

You should use TSQL for creating and managing the database objects with its procedural language features, as it’s highly optimized for it. Don’t use managed code when it just needs to access data and nothing else.

Building a stored procedure using CLR integration

We have gained fair amount of information about this cool new feature and now I will demonstrate a simple example. All the code below is built in Visual Studio 2005 Beta 2. So there can be slight changes in the final product.

1. Open Visual Studio 2005 IDE and create a New Project.

   File >> New Project >> Visual C# >> SQL Server Project >> Name the project. I have named it as EMP_SqlServerProject for this example.

2. The next step is to create the database reference. Click on the "Add New Reference" button and specify the server name, credentials and the database name. You can verify this information by clicking on the "Test Connection" button.
3. Now we have to choose the database object we want to create. To create a new stored procedure, go to Project >> Add Stored Procedure. Specify the stored procedure as EMP_GetEmployee
4. By default all the required namespaces are added to the project. They are System; System.Data; System.Data.Sql; System.Data.SqlTypes; Microsoft.SqlServer.Server;
5. Here is the C# code which creates a stored procedure "EMP_SqlServerProject" which gets the records from the EMPLOYEE table in the database.

   using System;
   using System.Data;
   using System.Data.Sql;
   using System.Data.SqlTypes;
   using System.Data.SqlClient;
   using Microsoft.SqlServer.Server;
   
   public partial class StoredProcedures
   {
       [Microsoft.SqlServer.Server.SqlProcedure]
       public static void sp_GetEmployeeInfo()
       {
           SqlConnection connection = 
                 new SqlConnection("context connection=true");
           try
           {
               connection.Open();
               SqlCommand sqlCommand = 
                   new SqlCommand("SELECT EMPID,EMAIL FROM EMPLOYEE",
                                                           connection);
               SqlDataReader sqlReader = sqlCommand.ExecuteReader();
               SqlContext.Pipe.Send(sqlReader);
           }
           catch(Exception ex)
           {
               throw ex;
           }
           finally
           {
               if (connection != null)
               connection.Close();
           }
           
       }
   };

The new class used in this code is SqlContext which is part of the Microsoft.SqlServer.Server namespace. To return the result sets and the messages from this stored procedure I have used another object called SqlPipe which is exposed as the Pipe property of SqlContext class. The Send method of this object is used to output messages or result sets to the client. So in this example the code creates a SqlDataReader with the data from the table EMPLOYEE and sends the result set back to the client.

You can also pass parameters to the stored procedure in the same as way we do in TSQL. All the CLR data types which are equivalent to SQL Server data types are present in System.Data.SqlTypes namespace. For example, SqlChars is the CLR equivalent of the NVARCHAR data type of SQL.

Build – Build >> Build EMP_SqlServerProject

Deploy – To deploy this assembly on the target SQL Server - Go to Build >> Deploy EMP_SqlServerProject. To verify this deployment open the SQL Server Management Studio. Navigate to Server Name >> Databases >> DB Name >> Programmability >> Assemblies >> you will the see the assembly "EMP_SqlServerProject" deployed there.

Run – EXEC sp_GetEmployeeInfo

Conclusion

In this example, I have explained how to create managed code for CLR integration but you can do a lot of stuffs other than just executing a simple query. Refer to MSDN to learn more about creating UDF, UDT and other database objects.