Using CLR Integration in SQL 05 for Writing Stored Procedures
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
If you are familiar with stored procedures and why they are used, you know about Microsoft’s T-SQL language for writing them with SQL Server.
But the problem with T-SQL is that programmers already have enough to learn, much less having to master another language for storing and executing code in databases. T-SQL is a crude version of some higher-level languages such as Visual Basic, but gets the job done with fundamentals.
Now, with SQL 2005, you can harness the power of a higher-level language to not only do more with stored procedures but also save time by already knowing a popular .NET language. It’s just another part of Microsoft’s quest to make things easier and more familiar to administrators and programmers when developing and managing projects with SQL 2005.
The most important reason to use CLR (Common Language Runtime) stored procedures is security. Your stored procedures written with .NET managed code are not only type safe but also more protected from unwanted code manipulation. Microsoft also ascertains that CLR stored procedures written in a .NET-based language are as good performance-wise as T-SQL stored procedures are.
This article shows you how you can use CLR integration creating stored procedures.
Creating Stored Procedures
Before using CLR, it has to be enabled in SQL 2005. To do this, execute the sp_performance system stored procedure by executing the following code in the New Query pane of Management Studio:
EXEC sp_configure @configname = ’clr enabled’, @configvalue = 1 RECONFIGURE WITH OVERRIDE GO
It would be nice to create and execute CLR stored procedures as easily as doing T-SQL stored procedures in SQL 2005, but there are a number of extra steps to do. To get the .NET Framework and SQL 2005 talking to each other first requires the use of an assembly, which is a compiled code source that is more secure because it is stored to a DLL (Dynamic Link Library). It also provides a way for SQL 2005 to implement this code without having the .NET Framework built directly into the SQL Server.
To demonstrate the process of creating procedures with CLR, let’s begin with a simple Hello World example. First, create the file that will hold the code to be executed against SQL Server. In this example, the code could be what follows in Listing 1.1.
Listing 1.1 Hello World CLR stored procedure code using C#
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace HelloWorld.SqlServer
{
public static class SProc
{
public static int PrintMessage(String Message)
{
int i = 0;
try
{
SqlContext.Pipe.Send(Message);
}
catch (Exception err)
{
i = 1;
SqlContext.Pipe.Send("An error occurred:
" + err.Message);
}
return i;
}
The code in Listing 1.1 is a simple Namespace with one class having a constructor that fires off the message when executed. The SqlContext.Pipe property, which belongs to the Microsoft.SqlServer.Server Namespace, is used to pipe the message back to the client from SQL Server. It is also often used to send back result sets from tables in a database when using SQL queries. Copy and paste the code in Listing 1.1 to a file called HelloWorldCLR.cs, which is the file that we’ll compile to an assembly. Use the following code from the command line to compile to an assembly:
csc /target:library HelloWorldCLR.cs /reference:"C:\Program FilesMicrosoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"
The csc command is used to compile C# code. You may have to provide the path to the HelloWorldCLR.cs file if it does not reside in the same directory as the csc executable. Along with compiling our file to an assembly, we’re also referencing the .NET provider library for SQL Server called sqlaccess.dll. Next, we need to add the assembly to SQL Server by using the Create Assembly statement with T-SQL:
CREATE ASSEMBLY HelloWorldCLR FROM ’C:\WINNT\Microsoft.NET\Framework\v2.0.50727\HelloWorldCLR.dll’ WITH PERMISSION_SET = SAFE GO
The path to HelloWorldCLR.dll may vary. Now that the assembly has been registered with SQL Server, we need a way to execute the CLR procedure on demand from the server. As odd as it sounds, the most common way to execute CLR stored procedures is by using an SQL Server stored procedure with T-SQL because SQL Server cannot handle the CLR code natively. A SQL Server stored procedure must first be created with T-SQL that will reference the CLR counterpart. To do this, execute the following code against the server:
CREATE PROC HelloWorldCLRMsg @Message NVARCHAR(255) AS EXTERNAL NAME HelloWorldCLR.[HelloWorld.SqlServer.SProc].PrintMessage GO
The preceding code creates a stored procedure called HelloWorldCLRMsg that, when called, in turn executes the associated CLR procedure code. The statement External Name indexes into our assembly (HelloWorldCLR) and finds the SProc class firing off the class constructor (PrintMessage). To see the CLR stored procedure get set in motion, execute the following code to test it out:
EXEC HelloWorldCLRMsg "Hello World!"
The message should appear in the results pane. This stored procedure can now be called from your web applications in the same way any other SQL Server stored procedures are called. The only difference is that you are actually executing a CLR stored procedure via a SQL Server stored procedure. A slick yet somewhat tedious way to get SQL Server using .NET code for stored procedures.
When doing other CLR stored procedures for the same database, you can add them to the same assembly. Each stored procedure essentially becomes a code module for that assembly.