CLR Integration Changes in SQL Server 2008

Technorati Tags: SQL Server 2008,CLR Integration,Nullable Types,Database

CLR Integration was first introduced in SQL Server 2005. Though there was a lot of excitement when the feature was  introduced, it has since fizzled off. Personally, I think CLR Integration is one of the most underused features of SQL Server. For some reason, we have not been able to move away from extended procedures or UDFs to writing CLR Integrated code.

In SQL Server 2008, there are two noticeable introductions to CLR Introduction. One is the support for LINQ and the other is the support for Nullable types.

To enable CLR support in SQL Server, you first have to switch it on. This is done by running the sp_configure with clr enabled = 1.  One thing to keep in mind is when you are using nullable types, you cannot use the automatic deployment option within Visual Studio 2008. You will have to register your assemblies manually. I will briefly show you how it is done.

Once you have turned the support for CLR on, the next step is to create your assembly. For this, I have a stored procedure written in Visual Studio. The code for the same is as below

 

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6:  

   7:  

   8: public partial class StoredProcedures

   9: {

  10:     [Microsoft.SqlServer.Server.SqlProcedure]

  11:     public static void SearchEmployee(Int64? iEmployeeId, out Int32? iVacationhours)

  12:     {

  13:         iVacationhours = null;

  14:         if (iEmployeeId !=null)

  15:         {

  16:             //open the sql connection with the current context

  17:             using (SqlConnection connection = new SqlConnection("context connection=true"))

  18:             {

  19:                 //open the connection

  20:                 connection.Open();

  21:                 //build the query and execute it               

  22:                 string query = "Select VacationHours from HumanResources.Employee where EmployeeId = " + iEmployeeId.ToString();

  23:                 SqlCommand sCommand = new SqlCommand(query, connection);

  24:                 SqlDataReader vacationReader = sCommand.ExecuteReader();

  25:                 using (vacationReader)

  26:                 {

  27:                     vacationReader.Read();

  28:                     iVacationhours = vacationReader.GetInt32(0);

  29:                 }

  30:             }            

  31:         }

  32:         

  33:  

  34:     }

  35: };

The code just returns the vacation hours for a given employee. The point worth noting is that both input and output parameters are nullable types. The next step is to add the assembly to the SQL Server database.

The syntax to do that is

   1: create assembly NullableTypes from

   2:  'C:\Users\Administrator\Documents\Visual Studio 2008\Projects\NullableTypesExample\NullableTypesExample\bin\Debug\NullableTypesExample.dll'

   3:  Go

Once the assembly is registered, you can now proceed to create your stored procedure pointing it to the managed code by executing the statement below

   1:  Create Proc dbo.SearchEmployee(@a bigint, @b smallint output)

   2:  as

   3:  EXTERNAL NAME NullableTypes.[StoredProcedures].SearchEmployee

   4:  GO

That is it!!! You are now all set to execute the stored procedure and check the results. For your reference, I am also giving below the statements to execute the procedure

 

   1:  declare @outvalue smallint

   2:  set @outvalue = 0

   3:  exec dbo.SearchEmployee 1,@outvalue output

   4:  select @outvalue

If I change the input value to null, null will be returned as output exhibiting support for Nullable Types.