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
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.
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.
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
SqlString : Used to send String Data to SQL ServerSqlDouble : Maps the the Double Data TypeSqlDateTime : Represents DateTime type of the SQL serverSqlError : 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
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
Let us discuss each of them one by One.
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);
reader.Close();
pipe.ExecuteAndSend(cmd);
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 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)
{
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)
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;
public void Init()
{
asciiCount = 0;
}
public void Accumulate(SqlString Value)
{
Char[] chr = Value.ToString().ToCharArray();
for (int i = 0; i < chr.Length; i++)
{
asciiCount += Convert.ToInt32(chr[i]);
}
}
public void Merge(xudaf_AsciiSum Group)
{
Accumulate(Group.Terminate());
}
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.
- 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. 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. -
Merge represents the code block which is called once for each groups. It is used to group call Accumulate for processing. -
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.
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
{
[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)
{
using (SqlConnection con = new SqlConnection("context connection=true"))
{
con.Open();
using (SqlCommand sqlComm = new SqlCommand())
{
sqlComm.Connection = con;
SqlPipe sqlP = SqlContext.Pipe;
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);
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 TriggerTarget : 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.
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;
}
public SqlString GetLatLonData()
{
return this._lat.ToString() + "," + this._lon.ToString();
}
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
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
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
