Common Language Runtime (CLR) Integration Programming in .NET

When Microsoft introduced common language runtime (CLR) integration support with SQL Server an new window was opened for .NET application developers and SQL server users. They can write stored procedures, triggers, user-defined types, user defined function or user defined aggregate functions using any .NET language of their choice. Previous versions of Visual Studio.NET did not support CLR integration programing because .NET 2003 cannot use the .NET Framework 2.0 assemblies. .NET programming languages provide a richer programming environment for developers than Transact-SQL. All the managed code runs into the common language runtime environment which provides more security to the code besides the normal database engine’s stored procedures available in earlier versions of SQL Server. You should use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework. The following libraries/namespaces are supported by CLR integration in SQL Server:

• CustomMarshalers

• Microsoft.VisualBasic

• Microsoft.VisualC

• mscorlib

• System

• System.Configuration

• System.Data

• System.Data.OracleClient

• System.Data.SqlXml

• System.Deployment

• System.Security

• System.Transactions

• System.Web.Services

• System.Xml

• System.Core.dll

• System.Xml.Linq.dll

CLR Stored Procedures

Stored procedures cannot be used in scalar expressions, unlike scalar expressions they are able to return tabular format data, and can invoke data definition language (DDL) and data manipulation language (DML) statements and return Output parameters. In CLR, stored procedures are created as public static methods in the .NET framework assembly. This static method can be of void or integer type.  I it returns an integer value, it is treated as return code of procedure as-

EXECUTE @return_status = your_procedure 

The @return_status variable will contain the value returned by the method. If the method is declared void, the return code will be 0. The following code snippet shows a stored procedure returning information through an OUTPUT parameter:

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class StoredProcedures 
{
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void RateSum(out SqlInt32 value)
   {
      using(SqlConnection con = new SqlConnection("context connection=true")) 
      {
         value = 0;
         con.Open();
         SqlCommand cmd = new SqlCommand("SELECT Rate FROM Products", con);
         SqlDataReader reader = cmd.ExecuteReader();
         
         using (reader)
         {
            while( reader.Read() )
            {
               value += reader.GetSqlInt32(0);
            }
         }         
      }
   }
}

CREATE PROCEDURE RateSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.RateSum

To create a dynamic result set, populate it and send it to the client, you can create records from the current connection and send them using SqlPipe.Send.  

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 
using System.Data.SqlTypes;

public class StoredProcedures 
{
   /// <summary>
   /// Create a result set on the fly and send it to the client.
   /// </summary>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void SendTransientResultSet()
   {
      // Create a record object that represents an individual row, including its metadata.
      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));
      
      // Populate the record.
      record.SetSqlString(0, "Hello World!");
      
      // Send the record to the client.
      SqlContext.Pipe.Send(record);
   }
}

CLR Triggers

A trigger can be regarded as a special type of stored procedure which executes itself when a language event executes. There are two general types of triggers: data manipulation language (DML) triggers and data definition language (DDL) triggers. DML triggers can be used when INSERT, UPDATE, or DELETE statements while DDL triggers can be used for administrative tasks, such as auditing and regulating database operations. CLR triggers can access the inserted and deleted tables through the CLR in-process provider. This is done by obtaining a SqlCommand object from the SqlContext object. For example:

SqlConnection con = new SqlConnection ("context connection = true");
con.Open();
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * from " + "inserted";

You can determine the number of columns that were modified by an UPDATE operation by using the ColumnCount property of the SqlTriggerContext object. You can use the IsUpdatedColumn method, which takes the column ordinal as an input parameter, to determine whether the column was updated.

reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
   pipe.Send("Updated column "
      + reader.GetName(columnNumber) + "? "
   + triggContext.IsUpdatedColumn(columnNumber).ToString());
 }

 reader.Close(); 

The following DDL trigger returns the raw EventData property.

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 DropTableTrigger()
   {
       SqlTriggerContext triggContext = SqlContext.TriggerContext;           

       switch(triggContext.TriggerAction)
       {
           case TriggerAction.DropTable:
           SqlContext.Pipe.Send("Table dropped! Here's the EventData:");
           SqlContext.Pipe.Send(triggContext.EventData.Value);
           break;
                
           default:
           SqlContext.Pipe.Send("Something happened! Here's the EventData:");
           SqlContext.Pipe.Send(triggContext.EventData.Value);
           break;
       }
   }
}

In the following example, consider the scenario in which you let the user choose any ID they want, but you want to know the users that specifically entered an e-mail address as an ID. The following trigger would detect that information and log it to an audit table.

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
{
   [SqlTrigger(Name = @"EmailAudit", Target = "[dbo].[Users]", Event = "FOR INSERT, UPDATE, DELETE")]
   public static void EmailAudit()
   {
      string userName;
      string realName;
      SqlCommand command;
      SqlTriggerContext triggContext = SqlContext.TriggerContext;
      SqlPipe pipe = SqlContext.Pipe;
      SqlDataReader reader;

      switch (triggContext.TriggerAction)
      {
         case TriggerAction.Insert:
         // Retrieve the connection that the trigger is using
         using (SqlConnection connection
            = new SqlConnection(@"context connection=true"))
         {
            connection.Open();
            command = new SqlCommand(@"SELECT * FROM INSERTED;",
               connection);
            reader = command.ExecuteReader();
            reader.Read();
            userName = (string)reader[0];
            realName = (string)reader[1];
            reader.Close();

            if (IsValidEMailAddress(userName))
            {
               command = new SqlCommand(
                  @"INSERT [dbo].[UserNameAudit] VALUES ('"
                  + userName + @"', '" + realName + @"');",
                  connection);
               pipe.Send(command.CommandText);
               command.ExecuteNonQuery();
               pipe.Send("You inserted: " + userName);
            }
         }

         break;

         case TriggerAction.Update:
         // Retrieve the connection that the trigger is using
         using (SqlConnection connection
            = new SqlConnection(@"context connection=true"))
         {
            connection.Open();
            command = new SqlCommand(@"SELECT * FROM INSERTED;",
               connection);
            reader = command.ExecuteReader();
            reader.Read();

            userName = (string)reader[0];
            realName = (string)reader[1];

            pipe.Send(@"You updated: '" + userName + @"' - '"
               + realName + @"'");

            for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
            {
               pipe.Send("Updated column "
                  + reader.GetName(columnNumber) + "? "
                  + triggContext.IsUpdatedColumn(columnNumber).ToString());
            }

            reader.Close();
         }

         break;

         case TriggerAction.Delete:
            using (SqlConnection connection
               = new SqlConnection(@"context connection=true"))
               {
                  connection.Open();
                  command = new SqlCommand(@"SELECT * FROM DELETED;",
                     connection);
                  reader = command.ExecuteReader();

                  if (reader.HasRows)
                  {
                     pipe.Send(@"You deleted the following rows:");
                     while (reader.Read())
                     {
                        pipe.Send(@"'" + reader.GetString(0)
                        + @"', '" + reader.GetString(1) + @"'");
                     }

                     reader.Close();

                     //alternately, to just send a tabular resultset back:
                     //pipe.ExecuteAndSend(command);
                  }
                  else
                  {
                     pipe.Send("No rows affected.");
                  }
               }

               break;
            }
        }

     public static bool IsValidEMailAddress(string email)
     {
         return Regex.IsMatch(email, @"^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$");
     }
}