LogoASPAlliance: Articles, reviews, and samples for .NET Developers
CLR Integration in SQL Server 2005
by Soyuj Kumar Sahoo
Average Rating: 
Views (Total / Last 10 Days): 49006/ 110


We all know how great T-SQL is for database code, but writing procedural code in T-SQL has always been difficult.  It seems impossible when we need a stored procedure or two, requiring some array-operations, text-parsing or complex math operations.  However, it is quite easy if our Server allows us to deploy C# or VB.NET code that is used within the Server process. In other words, if we need complex procedural code, we can write it as managed code.

With the Common Language Runtime (CLR) hosted in Microsoft SQL Server 2005(called CLR integration), we can write stored procedures, triggers, user- defined functions, user-defined types, and user-defined aggregates in managed code.  Because managed code compiles to native code prior to execution, we can achieve significant performance increases in some scenarios.  Thus SQL Server essentially acts as the operating system for the CLR when it is hosted inside SQL Server.

Let us go through an example for details understanding.  The most important factor is that we should have SQL Server 2005 with .NET Framework version 2.0 installed in our Server with Visual Studio 2005.

Steps for building a CLR stored procedure in SQL Server 2005

·         Enabling CLR integration in SQL Server 2005

·         Creating a CLR stored procedure Assembly

·         Deploying the Assembly in SQL Server 2005

·         Creating & executing the CLR stored procedure in SQL Server 2005

Enabling CLR integration in SQL Server 2005

Generally, the Common Language Runtime (CLR) integration feature is off by default in Microsoft SQL Server and must be enabled in order to use objects that are implemented using CLR integration.  To enable CLR integration we use the clr enabled option of the sp_configure stored procedure and set the value as "1" as shown below.

Execute the following code against SQL Server with choosing the proper database in SQL Server 2005 Management Studio.

Listing 1

EXEC sp_configure @configname = 'clr enabled', @configvalue = 1

Creating a CLR stored procedure Assembly

1.    Open the text editor then copy and paste the following code in that.  Save it as a Class file to MyCLRAssembly.vb.

Listing 2

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Public Class MyFirstCLRProcs
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub CLRProcs(ByVal iActionID As Integer)
        SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString())
        Using oConnection As New SqlConnection("context connection=true")
       Dim oCommand As New SqlCommand( _ 
"select top 10 EventID ,StatusKey, ProcessTime, UserType, ActionID" & _ 
 " from  EventTable where ActionID = " & _
iActionID, oConnection)
                Dim oReader As SqlDataReader
                oReader = oCommand.ExecuteReader()
            Catch ex As Exception
            End Try
        End Using
    End Sub
End Class                                                                     

2.    Required Namespaces and Classes:

·         Here Microsoft.SqlServer.Server is the main namespace responsible for creating CLR integrated stored procedures, functions, triggers, etc.

·         SqlContext and SqlPipe are two classes responsible for creating objects to output the results and text messages.  Here in the result part we send the SqlDataReader to clientside and print a message as “HelloWorld !” with the Server time.

·         Here we use Context Connection, which allows us to execute Transact-SQL statements in the same context that our code was invoked in the first place, i.e. connect using a connection string that points to local server.

3.    Compile the class file  "MyCLRAssembly.vb" using the following command in SDK command prompt:

·         Go to : start>>All programs>>Microsoft .NET Framework SDK v2.o>>SDK Command Prompt

·         Write the following codes there and press enter.

Listing 3

vbc  /t:library  c:\SQLAssembly\MyCLRAssembly.vb

Here “t” stands for target and “c:\SQLAssembly\” is the physical path of that Class file.  Then we get the MyCLRAssembly.dll assembly file at that folder.

Deploying the Assembly in SQL Server 2005

Execute the following T-SQL code against the SQL Server in Query analyzer of SQL Server 2005 Management Studio.

Listing 4

CREATE ASSEMBLY MyCLRAssembly from ‘c:\SQLAssembly\MyCLRAssembly.dll'  

Here we have to give the exact path of our assembly file (.dll file) for creating assembly.  Again the important factor here is the PERMISSION_SET for accessing security permissions.  There are three modes: SAFE, UNSAFE, EXTERNAL_ACCESS; through which SQL Server allows users to deploy code in database.  SAFE mode is for all users and UNSAFE is for more reliable user like Database Administrator.

So every user can create Assemblies using SAFE mode. However, there are some restrictions and limited number of Namespaces available to use under this mode.

Creating & executing the CLR stored procedure in SQL Server 2005

·         Creating CLR stored procedure:

Execute the following code in query analyzer of SQL Server 2005 Management Studio against the database.

Listing 5

CREATE PROC sp_MyCLRProcs(@iActionID as int)

Here we have to give [Procedure Name] and must specify the exact path of assembly method as [Assembly Name].[Class Name].[CLR function Name].  As our CLR method, which having the prefix as <Microsoft.SqlServer.Server.SqlProcedure()>, has an input parameter so we have to define the parameter at creation of the stored procedure with the identical datatypes exact variable name. (Here it is "Integer" which is equivalent ot ' int'  in T-SQL)

·         Executing the CLR stored procedure:

As any other stored procedure the CLR stored procedure is executed using EXEC  T-SQL command as follows :

Listing 6

EXEC sp_MyCLRProcs 216268    

The following result/message we get from our stored procedure for the particular ActionID (216268) in the SQL Server 2005 Management Studio:

Figure 1: (O/P Result & Message)

Using of CLR stored procedure in business logic layer or in .NET

As we execute any general T-SQL stored procedure in that way we can execute the CLR stored procedure in business-logic layer or in any .NET application, but here the result part is available i.e. the database Objects like tables, readers, data records, etc. The following codes are helpful for us.

Listing 6

Dim oCommand As SqlCommand
Dim oReader As SqlDataReader
oCommand = New System.Data.SqlClient.SqlCommand("sp_MyCLRProcs", oConnection)
oCommand.CommandTimeout = 300
oCommand.CommandType = CommandType.StoredProcedure
oCommand.Parameters.Add("@iActionID", SqlDbType.Int)
oCommand.Parameters("@iActionID").Value = iActionID
oCommand.Parameters("@iActionID").Direction = ParameterDirection.Input
oReader = oCommand.ExecuteReader()

Then we get that DataReader which we send from our CLR stored procedure through SQLPipe using the "SqlContext.Pipe.Send( )" method.

Advantages of CLR procedure over T-SQL procedure

·         Releases the tension to become Master of T-SQL for doing complex database operations.

·         Through this we can move our Business layer into database, getting closer to Data for increasing performances by avoiding unnecessary server trips.

·         Here .NET Framework Base Class Libraries (BCL) are available to use, through which we can solve our complex logic operations like string manipulations, threading operations, file management, cryptography, etc.

·         It is known as Managed Procedure that means it deals with automatic garbage collection, memory management, exception handling, etc.

·         As a developer in C#.NET or VB.NET we can use our OOP's concepts for more structured operations.

·         This feature provides the ability to leverage the features of .NET Code Access Security (CAS) to prevent assemblies from performing certain operations.

Security Permissions

Security Permissions are mainly due to the code check performed by the SQL Server 2005.  There are certain code checks performed by SQL Server 2005 when we build a managed stored procedure (or CLR integrated stored procs/functions/or triggers).  SQL Server performs checks on the managed code assembly during two steps.

·         It checks at the Creation time (i.e. CREATE ASSEMBLY) when the managed code is first registered in the database.

·         It also checks at runtime.

Create Assembly Checks

The following checks are performed when the CREATE ASSEMBLY statement is run for each security level.  If any check fails, CREATE ASSEMBLY will fail with an error message.  Global Checking for any mode:

·         The assembly should already register in the database.

·         The assembly is one of the supported assemblies.

·         All the referenced assemblies and their dependencies are available in the given location <location>, which is given in the create statement i.e. CREATE ASSEMBLY FROM <location>.

Default supported .NET Framework Libraries

These are the list of supported .NET Framework Libraries, which have been tested to ensure that they meet reliability and security standards for interaction with SQL Server 2005 in any mode of assembly creation.  Supported libraries do not need to be explicitly registered on the server, but unsupported libraries must first be registered in SQL Server database using CREATE ASSEMBLY before calling them in managed codes.

·         CustomMarshalers

·         Microsoft.VisualBasic

·         Microsoft.VisualC

·         mscorlib

·         System

·         System.Configuration

·         System.Data

·         System.Data.OracleClient

·         System.Data.SqlXml

·         System.Deployment

·         System.Security

·         System.Transactions

·         System.Web.Services

·         System.Xml


Moving Business layer into database make sense, but moving all in code requires a lot of CPU or Memory to process, which decreases performances as the single server is asked to take on too much of a load.  It depends upon developers to decide and determine how much business logic to put in a database versus in the middle layer.  In SAFE mode limited numbers of Namespaces and Classes are available to use, so getting full advantage of CLR integration should require more reliable user with high priority in security permissions.  Thus, that makes sense for Microsoft's .NET, “Don’t waste time in coding, but use it in building logic.”

©Copyright 1998-2021  |  Page Processed at 2021-10-27 3:06:24 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search