CLR Magics in Yukon

Introduction

The Microsoft .NET framework CLR has been integrated with the newer version of SQL Server. T-SQL is an existing data access manipulation language ,its suited only for procedural oriented languages. It was designed more than 10 years ago, So we need a modern approach to create data objects, the integration of SQLServer and Common Language Runtime enables the developer to create a routines by using object oriented languages. When we call the Managed routine in SQLServer it hosts the CLR in-process mode. The article begins by introducing you to those new .NET CLR features and showing you some samples (in VB.NET/C#) of how they are used.

The .NET Managed code provides four types of user defined SQL Server Routines :

1. User-Defined Procedures
2. User-Defined Triggers
3. User-Defined Functions

All the .NET Routines can be called anywhere in the SQL Languages.

User- Defined Procedures

Normally Stored procedures return tabular values and messages to the client. A .NET User-Defined Procedure Routine can do that Using SQLPipe.Send command. SQLPipe class is used to send the result to client ,like a Response.Write command in ASP.NET. We will see it how in the below Example.

[VB.NET]

Imports System.Data.Sqlserver
Imports System.Data.Sql
  Public Class clsBookStoreProcedures
  <SqlProcedure>
     Public Shared Sub GetAuthorName()
       Dim pipeSql As SqlPipe = Sqlcontext.GetPipe()
       Dim cmdSql As Sqlcommand = Sqlcontext.GetCommand()
       cmdSql.CommandText= _
          "Select AuthorName from Books where BookId   = @prmBookId"
       Dim prmBookId As SqlParameter =
       cmdSql.Parameters.Add("@prmBookId",SqlDBType.String)
       prmBookId.Direction = ParameterDirection.Input
       prmBookId.Value="BID001"
       pipesql.Send(cmdSql.ExecuteScalar())
     End Function
 End Class

[C#]

using System.Data.Sqlserver;
using System.Data.Sql;
 public Class clsBookStoreProcedure
  {
   [SqlProcedure]
  public static void GetAuthorName() {
     SqlPipe  pipeSql = Sqlcontext.GetPipe();
     Sqlcommand  cmdSql = Sqlcontext.GetCommand();
     cmdSql.CommandText=
       "Select AuthorName from Books where BookId   = @prmBookId";
     SqlParameter prmBookId  =
     cmdSql.Parameters.Add("@prmBookId",SqlDBType.String);
     prmBookId.Direction = ParameterDirection.Input;
     prmBookId.Value="BID001";
     pipeSql.Send(cmdSql.ExecuteScalar());
   }
 }

How to Register

CREATE ASSEMBLY ProcBookstore FROM file://Server/bin/ BookStoreProcedure.dll 
CREATE PROCEDURE GetAuthorByID(@prmID as Varchar) 
    AS EXTERNAL NAME BookStoreProcedure: clsBookStoreProcedure: GetAuthorName 

How to Run

EXEC ProcBookstore()

Output

Tamil selvan Subramanian

User- Defined Triggers

Creating .NET based user-defined Triggers is another new feature that's enabled by the upcoming CLR integrated SQLServer Yukon . In the Below shown example we will get the "INSERTED" Table Values from Books Table

[VB.NET]

Imports System.Data.Sqlserver
Imports System.Data.Sql
  Public Class clsBookStoreTriggers
  Public Shared Sub GetInsertedAuthor()
    Dim cntSql As SqlContext = SqlContext.GetTriggerContext()
    Dim pipeSql As SqlPipe = Sqlcontext.GetPipe()
    Dim cmdSql As Sqlcommand = Sqlcontext.GetCommand()
      If cntSql.TriggerAction = System.Data.Sql.TriggerAction.Insert Then
         cmdSql.CommandText="Select * FROM INSERTED"
         pipesql.Execute(cmdSql)
      End If
  End Function
 End Class

[C#]

using System.Data.Sqlserver;
 using System.Data.Sql;
        public Class clsBookStoreTriggers
       {
         public static void GetInsertedAuthor()
           {
            SqlContext cntSql = SqlContext.GetTriggerContext();
            SqlPipe pipeSql = Sqlcontext.GetPipe();
            Sqlcommand cmdSql = Sqlcontext.GetCommand();
             if (cntSql.TriggerAction ==
                 System.Data.Sql.TriggerAction.Insert)
               {
                cmdSql.CommandText="Select * FROM INSERTED";
                pipesql.Execute(cmdSql);
               }
       }
     }

How to Register

CREATE ASSEMBLY funBooks FROM "file://Server/bin/funBooks.dll"   
CREATE TRIGGER bookTrigger ON BOOKS  
FOR INSERT AS  
EXTERNAL NAME BookStore:clsBookStoreTriggers:GetInsertedAuthor 

User-Defined Functions

This function returns a single SQLType value such as integer, string, bit. For example if the below shown function returns a Book name against the Book ID. SQLContext is a class , it provides the Database connection and Transaction of the Current Execution Environment. Yukon Supports Two type of User defined Functions

• Scalar valued User Defined Function - Returns only one value
• Table valued User Defined Function - Returns Multiple values

.NET CLR Enables the user to create an aggregate function. Lets see how to create a scalar valued user defined function in .NET

[VB.NET]

Imports System.Data.Sqlserver
 Imports System.Data.Sql
   Public Class BookStore
    <SqlFunction(DataAccess=DataAccessKind.Read)>
    Public Shared Function GetAuthorName() as String
    Dim cmdSql As Sqlcommand = Sqlcontext.GetCommand()
    cmdSql.CommandText= _
        "Select AuthorName from Books where BookId = @prmBookId"
    Dim prmBookId As SqlParameter =
                   cmdSql.Parameters.Add("@prmBookId",SqlDBType.String)
    prmBookId.Direction = ParameterDirection.Input
    prmBookId.Value="BID001"
    Return Ctype(cmdSql.ExecuteScalar(),String)
  End Function
 End Class

[C#]

using System.Data.Sqlserver; 
    using System.Data.Sql; 
       public Class BookStore 
       { 
      [SqlFunction(DataAccess=DataAccessKind.Read)] 
      public static string GetAuthorName() {
        Sqlcommand  cmdSql = Sqlcontext.GetCommand(); 
        cmdSql.CommandText=
           "Select AuthorName from Books where BookId   = @prmBookId"; 
        SqlParameter prmBookId  =     
                      cmdSql.Parameters.Add("@prmBookId",SqlDBType.String); 
        prmBookId.Direction = ParameterDirection.Input; 
        prmBookId.Value="BID001"; 
        return (string)cmdSql.ExecuteScalar(); 
      } 
    } 

In the above code the SqlFunction custom attribute is send the information to the managed code that this function is used whether to Read, Insert, Update or Delete. In our sample we simply read the AuthorName from the database. So the only DataAccessKind.Read parameter has included. This attribute is found in System.Data.Sql namespace. GetCommand Function returns a Command type according to the current context From the SQLContext class. The last 4 lines of code should look familiar to all the .NET programmers

How to Register

CREATE ASSEMBLY funBooks FROM "file://Server/bin/funBooks.dll"
CREATE FUNCTION GetAuthName() RETURNS String
AS EXTERNAL NAME funBooks:BookStore:: GetAuthorName

How to Run

Select dbo.GetAuthName()

Output

Tamilselvan Subramanian

Points of Interest

All the Assembly Registration process is taken care by Whidbey IDE by using Build Menu of an SQL Project.

Conclusion

We've seen the major Routines those are supported by a .NET CLR in this article. It allows creating a User Defined Data types and Security Mechanism to protect the user from unauthorized access. I'm not able to discuss all the features in a this article. so the forthcoming my articles will definitely help you to go through the reaming Topics.
Please post your comments