CODEDIGEST
Home » Articles
Search
 

Technologies
 

Sponsored links
 

CodeDigest Navigation
 

Technology News
No News Feeds available at this time.
 

Community News
No News Feeds available at this time.
 
SQL CLR INTEGRATION

By Selvakumar
Posted On July 13,2008
Article Rating:
Average Rating: 3
No of Ratings: 2
No of Comments: 2
Category: Sql Server 2005
Print this article.

SQL CLR INTEGRATION

Introduction

 

SqlServer 2005 introduced a new feature where we can write procedures, triggers, functions etc as a managed code(C#, VB.Net, etc). This feature was introduced with .Netframework 2.0 and Sql server 2005. Moving forward, this article will provide an overview of the namespaces and libraries required to compile database objects using the Microsoft SQL Server integration with the Microsoft .NET Framework common language runtime (CLR). The topic also shows you how to write, compile, and run a simple CLR stored procedure written in Microsoft Visual C#.I have republished this article from my previous posts.

 

CLR integration

The common language runtime (CLR) integration feature is turned off by default in Microsoft SQL Server. It should be enabled in order to use objects that are implemented for CLR integration. To enable CLR integration, use the “clr enabled” option of the sp_configure stored procedure as shown in the below listing.

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

You can disable CLR integration by setting the “clr enabled” option to 0. When you disable CLR integration, SQL Server stops executing all CLR routines and unloads all application domains.

Computers configured with large amounts of memory and a large number of processors may fail to load the CLR integration feature of SQL Server when starting the server. To address this issue, start the server by using the -g memory_to_reserve of SQL Server’s service startup option, and specify a memory value large enough.

 

Required Namespaces

The components required to develop basic CLR database objects are installed with SQL Server 2005. CLR integration functionality is exposed in an assembly called system.data.dll, which is part of the .NET Framework. This assembly can be found in the Global Assembly Cache (GAC) as well as in the .NET Framework directory. A reference to this assembly is typically added automatically by both command line tools and Microsoft Visual Studio, so there is no need to add it manually.

The system.data.dll assembly contains the following namespaces, which are required for compiling CLR database objects:

System.Data

System.Data.Sql

Microsoft.SqlServer.Server

System.Data.SqlTypes

 

Writing A Simple "Hello World" Stored Procedure

To Create a new VS 2005 CLR project,

1. Open VS 2005 IDE.

2. Click on the Create Project.

3. Choose the language you want.

4. Expand the Language you’ve chosen and choose Database.

5. Select the SQL Server Project and click OK.

Copy and paste the following Visual C# code into a text editor, and save it

C#

using System;

using System.Data;

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

public class HelloWorldProc

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HelloWorld()

{

SqlContext.Pipe.Send("Hello world!\n");

}

}

Visual Basic

Imports System

Imports System.Data

Imports Microsoft.SqlServer.Server

Imports System.Data.SqlTypes

Public Class HelloWorldProc

<Microsoft.SqlServer.Server.SqlProcedure> _

Public Shared Sub HelloWorld()

SqlContext.Pipe.Send("Hello world!\n")

End Sub

End Class

 

This method uses two new classes, SqlContext and SqlPipe, for creating managed database objects to output a simple text message. This method can be declared as a stored procedure in SQL Server, and can be executed in the same way as a Transact-SQL stored procedure.

 




DEPLOYING the PROJECT from Visual Studio 2005

1.      Right click on the project and choose DEPLOY.

2.      If you get an ERROR like this,

Error 1 Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.HelloWorldCLR

Then, run this procedure in the SQL Server against the Database to which we’re deploying the Assembly.

sp_dbcmptlevel 'pubs'

If the result returned is less that 90. Then, run the command,

sp_dbcmptlevel 'pubs', 90

1.      Now RE-DEPLOY the assembly.

2.      To view / modify the connection settings of the database, Go to the Project menu of the Visual studio, Click on the Project Properties and choose Database tab. You can find the connection settings there.

3.      After deploying it, Go to the SQL Server, Expand the Database to which you’ve deployed, Expand Programmability, Expand Stored Procedure and you’ll be able to see the Procedure HelloWorld .

4.      Now go to the Assembly and Expand it. You’ll see the Assembly that you’ve created. (The Project name).

 

 

DEPLOYING the PROJECT if you’re using the Command line Utility

Compiling the "Hello World" Stored Procedure

SQL Server installs the .NET Framework redistribution files by default. These files include csc.exe and vbc.exe, the command-line compilers for Visual C# and Visual Basic programs. To compile our sample, modify your path variable to point to the directory containing csc.exe or vbc.exe. The following is the default installation path of the .NET Framework.

C:\Windows\Microsoft.NET\Framework\(version)

Version contains the version number of the installed .NET Framework redistributable. For example:

C:\Windows\Microsoft.NET\Framework\v2.0.31113

Once you have added the .NET Framework directory to your path, you can compile the sample stored procedure into an assembly with the following command.

For Visual C# source files:

csc /target:library helloworld.cs

For Visual Basic source files:

vbc /target:library helloworld.vb

The /target option allows you to compile it into an assembly.

These commands launch the Visual C# or Visual Basic compiler using the /target option to specify building a library DLL.

 

Loading and executing the "Hello World" Stored Procedure in SQL Server 2005

Once the sample procedure has successfully compiled, you can test it against the SQL Server 2005. To do this, open SQL Server Management Studio and create a new query, connecting to a suitable test database (for example, the AdventureWorks sample database).

For this example, we will assume that you have created the helloworld.dll assembly in the C:\ directory. Add the following Transact-SQL statement in your query.

 

CREATE ASSEMBLY helloworld from 'c:\helloworld.dll' WITH PERMISSION_SET = SAFE

 

Execution in SQL Server 2005

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement.

 

CREATE PROCEDURE hello

AS

EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

//AssemblyName.ClassName.ProcedureName

 

Once the procedure has been created, it can be executed just like a normal stored procedure written in Transact-SQL.

EXEC hello

The following message will be outputted in SQL Server Management Studio messages window.

Hello world!

 

Removing the "Hello World" Stored Procedure Sample

When you are finished with the execution of the sample stored procedure, you can remove the procedure and the assembly from your test database.

Ø       First, remove the procedure using the drop procedure command.

drop procedure hello

Ø       Once the procedure has been dropped, you can remove the assembly containing your sample code.

drop assembly helloworld

 

Here is the complete code for your Reference.

--To enable SQL Server as CLR enabled – Only first time

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

--Make sure the DB compatible level is 90, which refers to SQL Server 2005.

sp_dbcmptlevel 'pubs'

if O/P is <= 80 then,

sp_dbcmptlevel 'pubs', 90

60 = SQL Server 6.0

65 = SQL Server 6.5

70 = SQL Server 7.0

80 = SQL Server 2000

90 = SQL Server 2005

//Visual Studio code for Reference.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HelloWorld()

{

SqlContext.Pipe.Send("Hello world!\n");

}

};

-- Create a New Procedure that will refer the CLR Procedure.

use pubs

go

CREATE PROCEDURE hello

AS

EXTERNAL NAME HelloWorldCLR.StoredProcedures.HelloWorld

-- Execute the CLR Procedure using the newly created reference procedure.

EXEC hello

 

Conclusion

This article will help us understanding CLR integration feature of Sql Server 2005. With this feature, it is now possible to write complex logic using managed code in Sql server. Also, writing pocedural codes is difficult in T-SQL and this difficulty is removed with the introduction of CLR integration feature of Sql Server 2005.

 

Similar Articles
You can contribute to CodeDiget.Com:
Donate to CodeDigest.com
Article Feedback
Comments
Very helpful CLR integration example
Well Done,Selva kumar!
This article is very useful, it helps alot to learn the CLR integration. great work! keep it up buddy!

jashchahal[at]gmail[dot]com
FeedBack
Not setting the compatibility level to 90 throws an error on keyword 'EXTERNAL' as this is not a keyword prior to SQL Server 2005.