Click here to Skip to main content
Click here to Skip to main content

CLR Magics in Yukon

, 21 Dec 2003
Rate this:
Please Sign up or sign in to vote.
This article describes Yukon (SQL Server 9.0) using CLR

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Selvan
Software Developer (Senior)
United States United States
Tamilselvan Subramanian is a Lead consultant working on Microsoft Technologies for the past 6 years, Currently living in Newyork, US. Technical experience most specifically Biztalk 2004/2006, Webservices, C# and .NET framework,VB.NET, XML, XSLT, Flat file,Java.
 
He was awarded 'Community Star' by Microsoft for resolving .NET community people questions.
http://www.microsoft.com/india/communitystar/CurrentSelections.aspx
 
He blogs @ http://biztek.blogspot.com here.
You can reach him at tamilselvan +2 gmail.com.

Comments and Discussions

 
GeneralIntegrating with ASP.NET PinmemberSingh Saab27-Aug-07 6:02 
GeneralSystem.Data.Sqlserver Pinsussnatashaaaa2-Mar-05 23:02 
GeneralRe: System.Data.Sqlserver PinsussSelvan3-Mar-05 0:55 
GeneralRe: System.Data.Sqlserver PinsussAnonymous22-Aug-05 6:01 
GeneralRe: System.Data.Sqlserver PinmemberSingh Saab27-Aug-07 6:20 
GeneralRe: System.Data.Sqlserver PinmemberSingh Saab28-Aug-07 4:20 
GeneralDoes not work Pinmembersqlguy19-Jul-04 14:47 
GeneralGreat! PinmemberWillemM29-Dec-03 23:03 
GeneralError in Article PineditorHeath Stewart23-Dec-03 5:08 
QuestionPerformance??? PinmemberBrian Shifrin22-Dec-03 4:50 
AnswerRe: Performance??? Pinmemberjdunlap22-Dec-03 9:05 
GeneralRe: Performance??? PinmemberWcohen23-Dec-03 8:43 
GeneralRe: Performance??? PinsussJoseph Ward29-Dec-03 8:54 
GeneralRe: Performance??? PinmemberJochen Kalmbach29-Dec-03 21:29 
GeneralRe: Performance??? PinmemberWcohen30-Dec-03 3:33 
GeneralRe: Performance??? PinsussLeonardo Constantino30-Dec-03 8:07 
GeneralRe: Performance??? PinmemberWcohen30-Dec-03 21:44 
GeneralRe: Performance??? PinsussAnonymous6-Feb-04 11:05 
GeneralRe: Performance??? Pinmembermrhassell19-Feb-07 22:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 22 Dec 2003
Article Copyright 2003 by Selvan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid