Click here to Skip to main content
15,861,168 members
Articles / Database Development / SQL Server / SQL Server 2008

CLR Stored Procedure and Creating It Step by Step

Rate me:
Please Sign up or sign in to vote.
4.07/5 (19 votes)
22 Aug 2009CPOL6 min read 250.2K   5.8K   99   32
CLR Stored procedures are very simple and can be used in most complex scenarios when dealing with database.

Table of Contents 

Introduction

We usually face a problem in Stored Procedures and other database objects when we need to implement some complicated logic within it. We found inefficient performance when we tried to implement complex logic & business rules in database objects. In many cases, we found C# or VB classes more powerful to implement such things. Microsoft has added a new feature to address such issues with SQL server 2005 called "CLR Stored Procedure". 

What is CLR Stored Procedure? 

Now, let us understand CLR stored procedure. CLR, as most .NET programmers know, is Common Language Runtime and Stored Procedures are routine stored procedures of database. Thus, CLR Stored Procedures are a combination of both. As we all know, Common Language Runtime is a core .NET component. The Common Language Runtime is runtime execution environment which supplies managed code with various services like cross language integration, code access security, lifetime management of object, resources management, threading, debugging & type safety, etc. So now, CLR Stored Procedures are .NET objects which run in the memory of database.

The very first usage of CLR Stored Procedures can be said is accessing system resources. Accessing system resources could also be done using Extended Stored Procedures which are again database objects like Stored Procedures, Functions, etc. Extended Stored Procedures can do most of the things which a standard executable program can do. Then, why have CLR Stored Procedures? The very first advantage of CLR Stored Procedures is that it is a managed object unlike Extended Stored Procedures, which are unmanaged objects. The common thing between them is that both run under database memory. In this way, CLR Stored Procedures give all the benefits of managed objects. The following screen explains memory allocation while executing a CLR Stored Procedure.

When Should We Use CLR Stored Procedure? 

Extended stored procedures run in the same process space as the database engine, memory leaks, bugs, etc. can affect the performance of database engine. CLR stored procedures resolve these issues as they are managed objects and run as per specifications of Common Language Runtime. CLR Stored Procedures can replace a standard stored procedure that contains complex logic and business rules. CLR Stored Procedures take benefit of .NET classes and thus make it easy to implement complex logic, calculation, intense string operations, complex iterations, data encryption, etc. that are difficult to obtain in standard stored procedures. Standard stored procedures are still best for data oriented tasks. CLR Stored Procedures not only include stored procedures but also include Functions, Triggers, etc. CLR Stored Procedures are compiled one so they give better performance.

Benefits of CLR Stored Procedures

  1. Gives better results while executing complex logic, intense string operation or string manipulations, cryptography, accessing system resources and file management, etc.
  2. CLR Stored Procedures are managed codes so ensures type safety, memory management, etc.
  3. Better code management and provides object oriented programming capability thus enables encapsulation, polymorphism & inheritance.
  4. Convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that the .NET Framework supports.
  5. CLR Stored Procedures can also be used with Oracle 10g Release 2 or later versions.

Drawbacks of CLR Stored Procedures

  1. Not convenient in all contexts, for e.g. they should not be used to execute simple queries. In that case, standard stored procedures give better results.
  2. Deployment may be difficult in some scenarios.

Standard Stored Procedures vs. CLR Stored Procedures

You are the best judge when to use regular Stored Procedures and when to use CLR Stored Procedures. CLR Stored Procedures can be used in the following scenarios:

  1. When the program requires complex logic or business rules.
  2. When the flow is CPU intensive. CLR Stored Procedures give better results as they are in complied form and managed one.
  3. The tasks which are not possible in TSQL, accessing system resources, cryptography, accessing web services, etc.
  4. In option of Extended Stored Procedures. One should always consider CLR Stored Procedures before going for Extended Stored Procedures.
  5. An operation requires higher data safety.

Creating CLR Stored Procedure Step by Step

Let us create one simple CLR Stored Procedure which fetches all the rows from one table of the database. I have listed all SQL statements used for creating database, creating table, inserting dummy records in the table, etc. under "SQL statements used in the demo" section.

Application Development Specification

  • IDE: Visual Studio 2008
  • Framework: 3.5 with SP 1
  • Language: C# 3.0
  • Database: Microsoft SQL Server 2005 Express edition

Steps to Create CLR Stored Procedure

  1. Open Microsoft Visual Studio >> Click on New Project >> Select Database Projects >> SQL Server Project.

  2. You can choose reference of existing database connection or click on Add New Reference.

  3. If you select from existing references, skip step 3 else add new database reference as shown in the following image and click on Test Connection to test the connection.

  4. On clicking the OK button, Visual Studio will ask you to enable SQL/CLR debugging on the selected connection. You can select "Yes" to enable debugging or "No" to disable the same.

  5. Once the database reference and debugging option is selected, the project will be displayed in Solution Explorer. Select the project and right click on Solution Explorer >> Click on Add >> Stored Procedure.

  6. Add a new procedure from the installed templates as shown in the following screen. Give a proper name to it.

  7. Once you select the template, it will create a .cs file with the content shown in the following image:

  8. Add the following code in the method already created. Pass "context connection=true" as connection string in the constructor while creating new SqlConnection. This CLR stored procedure is going to be the part of the database, so it will be the internal part of database and there is no need to connect database externally. So, no need to provide connection string that we usually provide in applications. Then Click on Build menu >> Click on Build Solution. Also click on Build menu >> Deploy solution. This will deploy the assembly to the database for which we have made connection initially.

  9. Now, select the database >> Programmability. Right click on Stored Procedures >> Click on Refresh. The list of Stored Procedures should show one newly added stored procedure. Also right click on Assemblies >> click on Refresh. This should show newly added Assembly. Also, enable CLR Stored Procedure by the following query.
    SQL
    sp_configure 'clr enabled', 1 

    Run the following query to take effect or the above query.

    SQL
    RECONFIGURE

    Now, execute the stored procedure. It should give similar results shown in the following screen:

Transact-SQL Statements

Following are the queries to create database, create table, insert records in the table, etc. --Create a new database for demo

SQL
CREATE DATABASE DbForClrDemo --Use database USE DbFo

Following are the queries to create database, create table, insert records in the table, etc.

SQL
–Create a new database for demo

CREATE

–Use database

USE DbForClrDemo

–Create table for CustomerSalesInformation

CREATE
TABLE [dbo].[CustomerSalesInformation](

[Id] [int] IDENTITY(1,1)
NOT
NULL,

[Name] [varchar](50)
NOT
NULL,

[Sales] [decimal](18, 2)
NOT
NULL
DEFAULT
((0)),


CONSTRAINT [PK_CustomerSalesInformation] PRIMARY
KEY
CLUSTERED

(

[Id] ASC

)WITH
(PAD_INDEX =
OFF, IGNORE_DUP_KEY =
OFF)
ON [PRIMARY]

)
ON [PRIMARY]

–Insert dummy data to CustomerSalesInformation table

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Virat Kothari’, 50000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Dhruval Shah’, 5000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Urvish Sheth’, 15000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Rakesh Bajania’, 25000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Dhaval Shah’, 150000)

–Enable CLR Stored Procedure in database

sp_configure ‘clr enabled’, 1

–Run following statement to take effect of above statement

RECONFIGURE

–Now execute our CLR Stored Procedure. Remember "ClrDemo" 
-is name of our Stored Procedure
EXEC [dbo].ClrDemo

rClrDemo  

Conclusion

CLR Stored procedures are very simple and can be used in most complex scenarios.

Bibliography

References

History 

  • 30th June 2009: Initial post
  • 22th August 2009: Article formation

License

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


Written By
Product Manager
Denmark Denmark
A passionate techno-functional IT Executive with a total of 18+ years of experience in Delivery Head, Project/Product Management, Consulting, Development, R&D, and Delivery of enterprise global IT & Software solutions across multiple domains.

Spent 6+ years as a Program Director and 11+ years in core leadership, strategic planning & implementation, creating high-performance teams.

As a Co-founder and CTO at Xporium.com - one of the pioneer 3D virtual exhibition and digital connection platforms.

Directs and optimizes processes for evaluating alternate technologies while supervising and monitoring various IT and technology departments across the various spectrum for SDLC using various management models including waterfall, SCRUM, Agile, and Extreme Programming.

Heads large and complex projects at PMO and taking care of activities including but not limited to budgeting, funding, planning, personnel & resource allocation, risk and conflicts management, stakeholder management, and closures.

Currently working in cutting edge technologies including Quantum Computing, Artificial Intelligence and Machine Learning where I specifically love Computer Vision. I am a 2X Kaggle Expert.

He believes in "Perfection matters". He enjoys teaching people. When not working, Virat likes to swim and sleep.

Comments and Discussions

 
QuestionDeploy fails Pin
ANKIT_JHA13-Sep-13 1:49
professionalANKIT_JHA13-Sep-13 1:49 
AnswerRe: Deploy fails Pin
nikhil.d20-Jan-14 17:40
nikhil.d20-Jan-14 17:40 
GeneralMy vote of 3 Pin
Jaiby Varghese28-Aug-12 1:30
Jaiby Varghese28-Aug-12 1:30 
GeneralThank you for the excellent article. Pin
Top.Program14-Aug-12 3:20
Top.Program14-Aug-12 3:20 
QuestionUsing SSRS Web Service Pin
Eric Polson26-Feb-12 10:53
Eric Polson26-Feb-12 10:53 
Questionerror :( Pin
Sahil_Nagpal20-Jan-12 1:50
Sahil_Nagpal20-Jan-12 1:50 
GeneralExcellent article - inspired my article "FTP directly in SQL (Using SQL CLR)" Pin
Justin Porteous7-May-11 1:17
Justin Porteous7-May-11 1:17 
QuestionCalling from functions Pin
SUMYA25-May-10 0:32
SUMYA25-May-10 0:32 
GeneralExecuting SP throws following Exception [ConfigurationPermission] Pin
Rav77727-Aug-09 10:18
Rav77727-Aug-09 10:18 
GeneralRe: Executing SP throws following Exception [ConfigurationPermission] Pin
Virat Kothari5-Sep-09 11:41
Virat Kothari5-Sep-09 11:41 
GeneralRe: Executing SP throws following Exception [ConfigurationPermission] Pin
Rav7775-Sep-09 12:24
Rav7775-Sep-09 12:24 
GeneralRe: Executing SP throws following Exception [ConfigurationPermission] Pin
Virat Kothari6-Sep-09 1:45
Virat Kothari6-Sep-09 1:45 
QuestionHow can I query the CLR SPs using LINQ? Pin
rghubert24-Aug-09 21:52
professionalrghubert24-Aug-09 21:52 
AnswerRe: How can I query the CLR SPs using LINQ? Pin
Virat Kothari26-Aug-09 6:26
Virat Kothari26-Aug-09 6:26 
GeneralRe: How can I query the CLR SPs using LINQ? [modified] Pin
Virat Kothari8-Sep-09 20:29
Virat Kothari8-Sep-09 20:29 
GeneralRe formation Pin
Md. Marufuzzaman21-Aug-09 9:21
professionalMd. Marufuzzaman21-Aug-09 9:21 
GeneralRe: Re formation Pin
Virat Kothari22-Aug-09 3:32
Virat Kothari22-Aug-09 3:32 
GeneralRe: Re formation Pin
Md. Marufuzzaman22-Aug-09 3:42
professionalMd. Marufuzzaman22-Aug-09 3:42 
GeneralRe: Re formation Pin
Virat Kothari22-Aug-09 3:50
Virat Kothari22-Aug-09 3:50 
GeneralRe: Re formation Pin
Md. Marufuzzaman22-Aug-09 3:56
professionalMd. Marufuzzaman22-Aug-09 3:56 
GeneralRe: Re formation Pin
Md. Marufuzzaman22-Aug-09 4:31
professionalMd. Marufuzzaman22-Aug-09 4:31 
GeneralRe: Re formation Pin
Virat Kothari23-Aug-09 1:34
Virat Kothari23-Aug-09 1:34 
GeneralRe: Re formation Pin
Md. Marufuzzaman23-Aug-09 3:01
professionalMd. Marufuzzaman23-Aug-09 3:01 
GeneralRe: Re formation Pin
Virat Kothari30-Aug-09 2:39
Virat Kothari30-Aug-09 2:39 
GeneralRe: Re formation Pin
Md. Marufuzzaman30-Aug-09 7:32
professionalMd. Marufuzzaman30-Aug-09 7:32 

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.