Writing CLR Stored Procedures

Introduction

CLR Integration

Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security. With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author 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, you can achieve significant performance increases in some scenarios.

Advantage of CLR Integration

Transact-SQL is specifically designed for direct data access and manipulation in the database. While Transact-SQL excels at data access and management, it is not a full-fledged programming language. For example, Transact-SQL does not support arrays, collections, for-each loops, bit shifting, or classes. While some of these constructs can be simulated in Transact-SQL, managed code has integrated support for these constructs. Depending on the scenario, these features can provide a compelling reason to implement certain database functionality in managed code. Microsoft Visual Basic .NET and Microsoft Visual C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can now be easily organized into classes and namespaces. When you are working with large amounts of server code, this allows you to more easily organize and maintain your code. Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Library, you have access to thousands of pre-built classes and routines. These can be easily accessed from any stored procedure, trigger or user defined function. The Base Class Library (BCL) includes classes that provide functionality for string manipulation, advanced math operations, file access, cryptography, and more.

Steps to Create a CLR Stored Procedure

1. After launching Visual Studio 2005, choose File - > New Project. In the dialog box under Project Type, choose Visual C# - > Database and then choose SQL Server Project on the right side:

   

2. In the Solution Explorer, right click on the project and choose Add. From the submenu choose Stored Procedure and name the file HelloWorld.cs:

   

   C#

   using System;
   using System.Data;
   using System.Data.Sql;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   
   public class StoredProcedures
   {
     [Microsoft.SqlServer.Server.SqlProcedure]
     public static void HelloWorld()
     {
      SqlContext.Pipe.Send("Hello world! It's now " + 
               System.DateTime.Now.ToString()+"\n");
     }
   }

3. Then build the solution..... right-clicking it in the Solution Explorer and choosing Build.

4. This compiles our source code into a DLL. If you look in the directory for the project, you'll see a directory called bin. Under this directory you'll find a Debug directory. If you'll look in bin/Debug in the project directory, you should see a file called HelloWorld.DLL. That's our compiled DLL.

5. Copy the HelloWorld.DLL into an SQL Server folder like below path- C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Bin.

6. Open SQL Server 2005.

Enable clr Stored procedure by below stored procedure.........

EXEC dbo.sp_configure 'clr enabled',1 RECONFIGURE WITH OVERRIDE

Create assembly ..........

create assembly Helloworld from 'c:\HelloWorld.dll' WITH PERMISSION_SET = SAFE

Create stored procedure...........

CREATE procedure Helloworld 
AS EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld

Go

Then you can execute ......

exec Helloworld 

References

• CLR Stored Procedures
• CLR Stored Procedure for searching files

History

• 6^th March, 2007: Initial post