Click here to Skip to main content
Click here to Skip to main content

Writing CLR Stored Procedures

, 6 Mar 2007
Rate this:
Please Sign up or sign in to vote.
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

    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

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)

About the Author

Parwej Ahamad
Student Self
India India
Learning............................
 
ahamad.parwej[at]gmail[dot]com
Skype: pahamad
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralDeployment PinmemberArkonXX8-Mar-07 21:23 
General[Message Deleted] PinmemberParwej Ahamad9-Mar-07 6:06 
GeneralRe: Deployment PinmemberIvan (Zaragoza)3-Oct-07 8:07 
As previously said, you don't need to copy the DLL to the SQL Server bin folder, you can deploy it directly against the server..

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 6 Mar 2007
Article Copyright 2007 by Parwej Ahamad
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid