Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server
Article

Writing CLR Stored Procedures

Rate me:
Please Sign up or sign in to vote.
2.85/5 (12 votes)
6 Mar 2007CPOL2 min read 41.1K   27   5
CLR hosted in Microsoft SQL Server 2005

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:

    Screenshot - createproject.jpg
  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:

    Screenshot - solutionexplorer.jpg

    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.........

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

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

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

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

SQL
CREATE procedure Helloworld 
AS EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorld

Go

Then you can execute ......

SQL
exec Helloworld 

References

History

  • 6th March, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Student Self
India India
Learning............................

ahamad.parwej[at]gmail[dot]com
Skype: pahamad

Comments and Discussions

 
GeneralDeployment Pin
Jeffrey Deflers8-Mar-07 21:23
Jeffrey Deflers8-Mar-07 21:23 
General[Message Deleted] Pin
Parwej Ahamad9-Mar-07 6:06
professionalParwej Ahamad9-Mar-07 6:06 
[Message Deleted]
GeneralRe: Deployment Pin
Ivan (Zaragoza)3-Oct-07 8:07
Ivan (Zaragoza)3-Oct-07 8:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.