Getting Started with the CLR

As mentioned in Chapter 5, the CLR is disabled on SQL Server 2005 for security reasons. You will need to explicitly enable it using the sp_configure stored procedure as follows:

 sp_configure 'show advanced options', 1 GO RECONFIGURE GO Sp_configure 'clr enabled', 1 GO RECONFIGURE GO

You are now ready to create some .NET components to run on the SQL Server CLR.

Creating CLR Database Objects

So now that you know how the CLR works and that SQL Server has its own in-process version of it for code execution, what exactly can you do? The following list describes the database objects that run on the SQL Server CLR:

• Stored Procedures

• User-Defined Functions (UDF)

• Triggers

• Aggregates

• User-Defined Type

As mentioned earlier, the functionality to create and deploy these objects is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. When you create the class for any of the above listed objects you will need to reference the system.data.dll (version 2.0 of the Framework) assembly, which contains the following namespaces:

• System.Data

• System.Data.Sql

• Microsoft.SqlServer.Server

• System.Data.SqlTypes

The following code demonstrates a simple trigger on a table, that notifies an administrator that the table has been modified by someone:

 using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server;  using System.Data.SqlClient; using System.Data.SqlTypes; using System.Xml; using System.Text.RegularExpressions; public class CLRTriggers {    public static void ChangedTableTrigger()    {        SqlTriggerContext triggContext=SqlContext.TriggerContext;        switch(triggContext.TriggerAction)        {            case TriggerAction. ChangedTable:                SqlContext. Pipe.Send("Table changed:");                SqlContext.Pipe.Send(triggContext.EventData.Value);                break;            default:                SqlContext. Pipe.Send("The table was changed:");                SqlContext.Pipe.Send(triggContext.EventData.Value);                break;        }    } }

Once you have created a component to run on the CLR you can either deploy the project using Visual Studio 2005, or manually copy the assembly to the target server and then load it using T-SQL code in Management Studio as follows:

 CREATE ASSEMBLY CLRTriggers FROM '\\SOMEWHERE\MYCODE\CLRTriggers.dll'

The following code installs the assembly for the trigger

 CREATE TRIGGER ChangedTableTrigger ON CRM. Logins FOR ALTER_TABLE AS EXTERNAL NAME NormalData.CLRTriggers.ChangedTableTrigger

If you need to nix the assembly you can either run the following T-SQL code

 DROP ASSEMBLY CLRTriggers

or drill down to the \\MYSQLSERVE\\Databases\MYDB\Programmability\Assemblies right-click the assembly and select Delete.

That’s all there is to creating a CLR database object for SQL Server 2005. The complexity will be in your code, not in the act of installing the assembly and registering the object.