Click here to Skip to main content
Click here to Skip to main content
Go to top

Working with CLR Objects in SQL Server 2005 or Above Part 2

, 2 Aug 2009
Rate this:
Please Sign up or sign in to vote.
Gives an introduction of how to create SQL CLR managed objects in SQL server.

This Article contains 2 parts. This is the second part of the Article. If you want to see the first part click here

Table of Contents

Introduction

After writing the First article on Basic introduction of SQL server CLR Objects lets put forward this topic in the application area. In this article I will discuss about some of the basic Coding structure that you have to follow to build your first CLR Object.

Those who have missed the first part of the article please find it here [^]. Hope you would like it.

Background

CLR is a new topic to SQL Server 2005 to provide the .NET developers to use SQL server in a better way. CLR object uses the classes associated with .NET framework and can fully utilise all the benefits that comes with .NET. It can call Managed Classes if the Assembly is Created in EXTERNAL_ACCESS as discussed in the first part of the article. It can also access Registry, File System, Registry etc. Even Can Access Memory Locations using Pointers when assemblies are created with PERMISSION SET=UNSAFE.

Let us delve into the actual coding details on to build successful CLR objects.

Data Types

There are a number of Data Type Wrappers available in the Managed Environment which might be used to process data output back to the server properly. Few examples are
  1. SqlString : Used to send String Data to SQL Server
  2. SqlDouble : Maps the the Double Data Type
  3. SqlDateTime : Represents DateTime type of the SQL server
  4. SqlError : Represents Sql Errror Types in the Sql Server. Used to Raise Error from managed Environement
You can create object of them and send date to the calling environment directly. You can also assign normail managed variables directly to these objects like
SqlInt32 sint = 10;
We can get the value of sint from sint.Value

Types of CLR Objects

There are 5 types of objects you can build using CLR. They are :
  • Stored Procedures
  • User Defined Functions
  • User Defined Aggregate Functions
  • Triggers
  • User Defined Data Types

CLR3.JPG

Let us discuss each of them one by One.

Stored Procedure

Stored procedures are used to run a sequence of logic around data objects. We Generally place our logic inside our Business Classes, just to take advantage of flexibility of Managed classes. CLR introduces this flexibility within the database itself, so that your stored procedure can be stored within the database itself and any client can use directly.
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void xsp_ExportData()
    {
        SqlPipe pipe = SqlContext.Pipe;
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * FROM USERS", con))
            {
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                XmlDocument document = new XmlDocument();
                XmlDeclaration xmlDeclaration = 
document.CreateXmlDeclaration("1.0", "utf-8", null);
                XmlElement rootNode = document.CreateElement("USERS");
                document.AppendChild(rootNode);
                while (reader.Read())
                {
                    XmlElement parentNode = 
document.CreateElement("USER");
                    parentNode.SetAttribute("ID", reader["userid"].ToString());
                    parentNode.InnerText = reader["FNAME"].ToString();
                    rootNode.AppendChild(parentNode);

                }
                pipe.Send(document.InnerXml); 
                // this will print the XML output
                reader.Close();
                pipe.ExecuteAndSend(cmd); 
                // This will show the result
                con.Close();
            }

        }
    }
       
};

The above code block will create a Stored Procedure in the SQL server. The Class StoredProcedure has a static function called xsp_ExportData which represents the actual Stored Procedure.

The Attribute Microsoft.SqlServer.Server.SqlProcedure is very essential as it indicates the function to the Database Environment as Stored Procedure.

First we created SqlPipe object. This object is used to send back to the client when the stored procedure is created. SqlContext.Pipe holds the current Pipe object which can send results from Managed Environment. The object is Thread safe.

We create an object of SqlConnection with Connectionstring "Context-connection=true". This indicates the SQL server to create connection using the existing context. Thus it will not relogin to the server again rather use the exisiting implicit connection.

We use Pipe.send() to Print data in the console and ExecuteAndSend to Return any result to the client. The ExecuteAndSend executes a command object and returns the result to the calling environment.

You can also make use of SendResultStart,SendResultRow and SendResultEnd functions to create dynamic SqlDataRecord objects like the way mentioned below.

SqlDataRecord rec = new SqlDataRecord("1","2","3");
pipe.SendResultsStart(rec);
pipe.SendResultsRow(rec);
pipe.SendResultsEnd(rec);
To Run the code, first you need to deploy the application right clicking on the Project and Select deploy. Run the stored procedure to get the result.

User Defined Functions

User defined functions are code blocks similar to Stored procedure but the only difference is that it has a return parameter which will implicitely returns an object back to the calling environment. Just see the code below:
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDouble xudf_GetSquare(SqlDouble number)
    {
        // Put your code here
        return Math.Pow(number.Value, 2);
    }
};

The above function takes Double argument and returns the Square of that number back to the server. We have used SqlDouble Class to send double Data from managed environment.
To Run the Code:

SELECT dbo.xudf_GetSquare(5)

User Defined Aggregate Functions

Aggregate functions are those which works on a number of records that represents a group. We can process a group of rows to get a result using Aggregate functions. Just see the code below :
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct xudaf_AsciiSum
{
    private SqlInt32 asciiCount;

    /// <span class="code-SummaryComment"><summary></span>
    /// Initialize will be called once.
    /// <span class="code-SummaryComment"></summary></span>
    public void Init()
    {
        asciiCount = 0;
    }

    /// <span class="code-SummaryComment"><summary></span>
    /// Write your main logic within this.
    /// <span class="code-SummaryComment"></summary></span>
    /// <span class="code-SummaryComment"><param name="Value">Should be the value coming from the Sql console</param></span>
    public void Accumulate(SqlString Value)
    {
        Char[] chr = Value.ToString().ToCharArray();
        for (int i = 0; i < chr.Length; i++)
        {
            asciiCount += Convert.ToInt32(chr[i]);
        }

    }

    /// <span class="code-SummaryComment"><summary></span>
    /// Should call Accumulate. 
    /// <span class="code-SummaryComment"></summary></span>
    /// <span class="code-SummaryComment"><param name="Group"></param></span>
    public void Merge(xudaf_AsciiSum Group)
    {
        Accumulate(Group.Terminate());
    }
    /// <span class="code-SummaryComment"><summary></span>
    /// Represents the Termination condition of the Aggregate function
    /// <span class="code-SummaryComment"></summary></span>
    /// <span class="code-SummaryComment"><returns>Return tyoe if the function</returns></span>
    public SqlString Terminate()
    {
        return asciiCount.ToString();
    }
}
In the above code we have created an Aggregate function which works on a number of records passed. It uses four interfaces. Init, Accumulate, Merge, Terminate. These functions runs multi-threaded.

  1. Here in the code, when the aggregate function is executed, Init initialises all the variables that you want in the function. I have used SqlInt32 to count the characters ascii of each record.
  2. Accumulate is the main block where you need to write the aggregation logic. Here we are passing SqlString object which holds the actual object. Accumulate is called for each rows on a group.
  3. Merge represents the code block which is called once for each groups. It is used to group call Accumulate for processing.
  4. Terminate is used to return the result back to the calling environment.
To Run the Above aggregate function :
select dbo.xudaf_AsciiSum(upass) from users group by userid where Users is a table.

Triggers

Database triggers are used to automatically run a code block when some database operation is executed. We can create managed database objects using CLR. Take a look at the code below :
public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger (
     Name="xut_UpdateDateTime", 
     Target="Users", 
     Event="FOR UPDATE")]
    public static void xut_UpdateDateTime()
    {
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        SqlParameter userID = new SqlParameter("@userid", System.Data.SqlDbType.NVarChar);

        if (triggContext.TriggerAction == TriggerAction.Update) 
            // Represents when to take what action. 
   //This is essential when you create triggers for more than one operation
        {
            using (SqlConnection con = new SqlConnection("context connection=true"))
            {
                con.Open();
                using (SqlCommand sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = con;
                    SqlPipe sqlP = SqlContext.Pipe; 
// Represents the Pipe object to send data to the Sql server
                    sqlComm.CommandText = "SELECT UserId from INSERTED";
                    userID.Value = sqlComm.ExecuteScalar().ToString();
                    sqlComm.CommandText = "INSERT UpdateTrackBack(UpdateUId, UpdateTime) " _
    "VALUES(@userid, GetDate())";
                    sqlComm.Parameters.Add(userID);
                    sqlP.ExecuteAndSend(sqlComm); 
// Executes the Insert Statement and also writes {0} rows affected.
                    sqlP.Send("Trigger executed!");
                }

            }
        }
    }
}

In the above code will add one trigger with the table "Users" so that whenever update statement is executed in the Users table the code block will be executed automatically.

Similar to normal Triggers, we can find the table temporary tables INSERTED(for Insert and Update) or DELETED(for Delete) record.

We can also use SqlPipe object to manipulate Output of the calling environment in the similar way you do in Stored Procedured.

The SqlTrigger attribute represents the configuration section where the trigger will be applied. There are three Parameters that you can use :

  • Name : Represents the name of the Trigger
  • Target : Represents the Table object where the Trigger to be applied.
  • Event : Comma separated string which represents the events for which the trigger block is to be executed.
    Example : Event = "For Insert, Update, Delete"
In managed Environment you can also create triggers on DDL statements like Create, Alter or Drop statements.

User Defined DataTypes

We can also create User Defined Data Types in the sql Databases. These datatypes can be used in TSQL to store data. Similar to XML data Types and all the newly introduced Data types in SQL Server 2008, you can build your own data type that represents the business logic data within the SQL server.

Lets see the code below:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct xut_GeoPoint : INullable
{
    private int _lat, _lon;
    private bool _isnull;
    public override string ToString()
    {
        return _lat.ToString() + "," + _lon.ToString();
    }

    public SqlInt32 Latitude
    {
        get { return this._lat; }
        set { this._lat = value.Value; }
    }
    public SqlInt32 Longitude
    {
        get { return this._lon; }
        set { this._lon = value.Value; }
    }
    public bool IsNull
    {
        get
        {
            if (this._lat == default(int) && this._lon == default(int))
            {
                this._isnull = true;
                return true;
            }
            else
            {
                this._isnull = false;
                return false;
            }
        }
    }

    public static xut_GeoPoint Null
    {
        get
        {
            xut_GeoPoint h = new xut_GeoPoint();
            h._isnull = true;
            return h;
        }
    }

    public static xut_GeoPoint Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        xut_GeoPoint u = new xut_GeoPoint();
        string[] result = s.ToString().Split(',');
        u._lat = Convert.ToInt32(result[0]);
        u._lon = Convert.ToInt32(result[0]);
        return u;
    }

    // This is a place-holder method
    public SqlString GetLatLonData()
    {
        //Insert method code here
        return this._lat.ToString() + "," + this._lon.ToString();
    }

    // This is a place-holder static method
    public static xut_GeoPoint GetEmptyObject()
    {
        xut_GeoPoint point = new xut_GeoPoint();
        return point;
    }
}

In the above code we have actually made a serialisable class, which has few functions like GetEmptyObject, GetLatLonData etc. and few Properties are defined like Latitude, Longitude, IsNull etc. Each of these fields can be accessed from the TSQL environment.
DECLARE @x xut_GeoPoint
SET @x = '20,5'
set @x.Latitude = 5;
set @x.Longitude = 10;

print @x.ToString()
We override ToString function to get the result. We can call a normal function using :: operator.

Download Sample Application

References

http://msdn.microsoft.com/en-us/library/5czye81z(VS.80).aspx
http://msdn.microsoft.com/en-us/library/938d9dz2(VS.80).aspx
http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx
http://msdn.microsoft.com/en-us/library/w2kae45k(VS.80).aspx
http://msdn.microsoft.com/en-us/library/a8s4s5dz(VS.80).aspx

Conclusion

So, CLR stored procedure is the most flexible way of writing Database objects nowadays, and we can easily use it to create custom classes.
As I am very new to this CLR stuffs, please feel free to comment and also hoping that you like my work.

You can See the First part of the Article from Here

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abhishek Sur
Architect
India India
Did you like his post?
 
Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.
 
Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook
 
Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.
 
Presently he is working in WPF, a new foundation to UI development, but mostly he likes to work on architecture and business classes. ASP.NET is one of his strength as well.
Have any problem? Write to him in his Forum.
 
You can also mail him directly to abhi2434@yahoo.com
 
Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com
 
Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 6:22 
GeneralNice overview PinmemberGio Bejarasco7-Aug-09 6:28 
GeneralRe: Nice overview PinmemberAbhishek Sur8-Aug-09 10:20 
GeneralRe: Nice overview PinmemberGio Bejarasco9-Aug-09 11:51 
GeneralRe: Nice overview PinmemberAbhishek Sur10-Aug-09 5:46 

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
Web01 | 2.8.140905.1 | Last Updated 2 Aug 2009
Article Copyright 2009 by Abhishek Sur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid