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 :
- User-Defined Procedures
- User-Defined Triggers
- 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
