As a database developer you create database objects such as procedures, functions and triggers to implement programming logic by using T-SQL. However in some situations it is not possible to implement the required functionality by using T-SQL code.
For e.g.: - you need to store the credit card number in a table in an encrypted format so that it cannot be tampered. For this you need to apply various string manipulations and complex mathematical operations which involves use of arrays and constructs. It is a very complex process to do this using T-SQL code. With CLR integration in SQL Server 2005 you can create a program in any of the .Net supported languages to implement enhanced programming logic that cannot be implemented by using T-SQL. Code written in any of the .Net supported languages and runs in the CLR is called Managed code. You can embed this program in your database so that it can be run in the same environment in which the database exists.
CLR Integration provides the following benefits
Better programming model :- The .Net programming languages provide a set of programming constructs that are not available in T-SQL. In addition, .Net provides a set of classes that can be used to implement a predefined functionality. For eg :- you need to save the data in a file in a compressed format. For this you can use binary reader and binary writer classes provided by the .Net framework base class library.
Common development environment: - Application developers can use the same tool, Visual Studio 2005 to create database objects and scripts as they use to create a client tier.
Ability to define data types: - Database developers can create user defined datatypes to expand the storage capabilities of SQL Server. For eg, you need to store a set of values in a single column or variable in a pre-defined format. For this, you need to use an array. In such a case, you can create a new data type using a .Net programming language.
Identify the need for managed code
You should use managed code objects when you need to:
Implement complicated programming logic for which you can reuse the functionality provided by the .Net base class libraries.
Access external resources, such as calling a web service or accessing the file system
Implement a CPU-intensive functionality that can run more efficiently as compared to the managed code.
Implement Managed Database Objects
To create managed database objects in SQL Server, you need to first create a managed code in any of the .Net programming languages. A managed code contains classes and methods that provides a desired functionality. Next you need to compile the code to create an assembly. An assembly can be a .dll or .exe file that contains compiled managed code.
SQL Server cannot directly execute the assemblies. Therefore before using the assemblies you need to import and configure the assemblies in the database engine.
Syntax :
Create assembly assembly_name
[Authorization owener_name]
From [<client_assembly_specifier>|<assembly_bits>]
With permission_set=[Safe|External_Access|Unsafe]
Permission_Set specifies the permissions that are granted to the assembly when it is accessed by SQL Server. This parameter can accept any of the following values.
SAFE : - Is the most secure permission as the code will not be able to access any external resources, such as files, networks, environment variables or registry. If no values is specified the default value is SAFE.
External_Access :- Enables the .Net code to access some external resources, such as files, network and environmental variables and registry.
Unsafe:- Enables the .Net code to access any resources within or outside SQL Server. Eg:- it allows a .Net code to call unmanaged code.
Creating Managed Database Objects
After importing assemblies in SQL Server, you can create managed database objects that use the managed code provided in the assembly. By Default, SQL Server does not allow the running of the managed code on the server. Therefore, before creating a managed database object in your database you need to enable the CLR integration feature in your database objects. To enable CLR you need to use the following statements.
Sp_configure 'Clr_enable',1
Go
Reconfigure
Go
While developing managed database objects, you will have to use the System.Data.SqlClient, System.Data.SqlTypes and Microsoft.SqlServer.Server namespaces found in the .Net base class libraries. These namespaces contains several key classes that you'll use to create the managed database objects. The main purpose of these classes is to give you faster access to a database. The following classes found in the System.Data.SqlClient namespace are used to access a database from managed code.
SqlContext:- Represents the context under which the assembly is running. It provides several properties, such as pipe, TriggerContext and windowsIdentity which you can use to access the sqlpipe, Sqltriggercontext and windowsIdentity objects respectively.
SqlPipe:- Allows you to send results or a message directly to the client application. For eg:- if you are using SQL Server management studio to display a message in the message tab you can use the following code.
SqlPipe pipe=SqlContext.Pipe;
Pipe.Send("This is a test message").
SqlTriggerContext := Allows you to access information about the event that fired the trigger during a managed trigger operation. Consider the following code.
SqlTriggerContext tr=SqlContext.GetTriggerContext();
SqlPipe pipe=SqlContext.Pipe;
If(tr.TriggerAction==TriggerAction.Insert)
pipe.send("A Record Inserted");
SqlConnection : - Allows you to connect a database before querying any data. You can use the following code to connect to the database.
SqlConnection con=new SqlConnection("context connection=true");
SqlCommand :- Allows you to send T-SQL commands to the database server. The following code displays how to delete a row from a table in the database.
SqlCommand cmd=new SqlCommand();
cmd.CommandText="Delete from HumanResources.Product";
cmd.Connection=con;
int rows=cmd.ExecuteNonQuery();
SqlDataReader dr=Allows you to work with the results of a query. You can use this class to retrieve records, as shown in the following code:
SqlCommand cmd=new SqlCommand("Select LastName From Person.Contact",con);
SqlPipe pipe=SqlContext.Pipe;
SqlDataReader dr=cmd.ExecuteReader();
While(dr.Read())
{
Pipe.send(dr["LastName"]);s
}
Note: Depending upon the requirement, the database developer can create the following types of database objects:
Stored Procedure
Functions
Triggers
User Defined Functions.
Now enough of theory we will move towards practical:
Open Visual Studio 2005 and click on File -> New Project Select Class Library from the template and specify the name as TestManagedCode.
By default your class will have class1 as a class name rename it to TestingCLR.
Copy the following code and paste it...
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace TestManagedCode
{
public class TestingCLR
{
//Sql Trigger specifies that the following method can be called from trigger.Inorder to call the //method from stored procedure Specify [SqlProcedure].
[SqlTrigger]
public static void CheckWhichEventFired()
{
SqlTriggerContext tr = SqlContext.TriggerContext;
SqlPipe pipe = SqlContext.Pipe;
if (tr.TriggerAction == TriggerAction.Insert)
{
pipe.Send("A Record Inserted");
}
else if (tr.TriggerAction == TriggerAction.Update)
{
pipe.Send("A Record updated");
}
else if (tr.TriggerAction == TriggerAction.Delete)
{
pipe.Send("A Record Deleted");
}
else
pipe.Send("Another Command Executed");
}
}
}
Just build your project and one dll will be created.
Now open SQL Server Management Studio.
Now we'll create the assembly in SQL Server 2005.
create assembly TestingTriggerFunctionality
authorization dbo
from 'E:\vishal\TestManagedCode\TestManagedCode\bin\Debug\TestManagedCode.dll'
with Permission_Set=External_Access
Now I've created a table with the name as Test
create table Test
(
TestId int,
Testname varchar(30)
)
insert into Test values(1,'Testing CLR')
//now create the trigger for insert,update and delete on test table.
create trigger trgTest
on Test
for insert,update,delete
as
//Assemblyname.[Namespacename.ClassName].functionname
external name TestingTriggerFunctionality.[TestManagedCode.TestingCLR].CheckWhichEventFired
Now insert 1 record in the table; you'll get the following output.
output
A Record Inserted
(1 row(s) affected)
Try to delete a record from the table.
A Record Deleted
(1 row(s) affected)
And the same thing will happen when you try to update a record in the table.
Hope you have like the example.