CLR Stored Procedure and Creating It Step by Step

• Download source - 27.48 KB
• Download script - 861 B

Table of Contents 

• Introduction
• What is CLR Stored Procedure?
• When Should We Use CLR Stored Procedure? 
• Benefits of CLR Stored Procedures
• Drawbacks of CLR Stored Procedures
• Standard Stored Procedures vs. CLR Stored Procedures 
• Creating CLR Stored Procedure Step by Step
• Transact-SQL Statements 
• Conclusion 
• Bibliography 
• References 
• History

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.

   sp_configure 'clr enabled', 1 

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

   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

CREATE DATABASE DbForClrDemo --Use database USE DbFo

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

–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

• Code Project
• MSDN
• www.searchsqlserver.techtarget.com

References

• http://blog.digi-corp.com/2009/05/seminar-on-net-framework-and-silverlight-basics/
• http://dev.digi-corp.com/2009/06/clr-stored-procedures-and-creating-it-step-by-step/

History 

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