.Net, Technology

CLR Stored Procedure and Creating It Step-by-Step

CLR Demo

Introduction

We often encounter problems in Stored Procedures and other database objects when implementing complicated logic. We have observed inefficient performance when attempting to implement complex logic and business rules within database objects. In many cases, we find that C# or VB classes are more powerful for implementing such tasks. Microsoft introduced a new feature in SQL Server 2005 to address these issues, called "CLR Stored Procedure."

What is CLR Stored Procedure?

CLR Stored Procedure is a combination of Common Language Runtime (CLR), which is a core .NET component, and routine stored procedures of the database. CLR Stored Procedures are .NET objects that run in the memory of the database. CLR leverages the benefits of managed code, providing various services like cross-language integration, code access security, object lifetime management, resource management, threading, debugging, and type safety.

The first usage of CLR Stored Procedures can be attributed to accessing system resources. While Extended Stored Procedures can achieve similar tasks, CLR Stored Procedures have the advantage of being managed objects, ensuring better performance and resolving issues like memory leaks and bugs.

When Should We Use CLR Stored Procedure?

CLR Stored Procedures are beneficial when accessing system resources, replacing standard stored procedures with complex logic and business rules. They leverage .NET classes for implementing tasks that are challenging to accomplish in standard stored procedures, such as complex logic, calculations, intense string operations, iterations, data encryption, etc. While standard stored procedures remain best for data-oriented tasks, CLR Stored Procedures, including Functions and Triggers, offer better performance due to compilation.

Benefits of CLR Stored Procedures

  1. Better results for executing complex logic, intense string operations, cryptography, accessing system resources, and file management.
  2. Managed codes ensure type safety, memory management, etc.
  3. Better code management, providing object-oriented programming capabilities like encapsulation, polymorphism, and inheritance.
  4. Convenient for programmers, supporting languages such as C# and VB.
  5. Compatible with Oracle 10g Release 2 or later versions.

Drawbacks of CLR Stored Procedures

  1. Not suitable for executing simple queries; standard stored procedures are more efficient.
  2. Deployment may be challenging in some scenarios.

Standard Stored Procedures vs. CLR Stored Procedures

The choice between standard and CLR Stored Procedures depends on the specific requirements. CLR Stored Procedures are suitable when:

  • Complex logic or business rules are needed.
  • The operation is CPU-intensive.
  • Tasks are not possible in TSQL, such as accessing system resources, cryptography, and web services.
  • Considering Extended Stored Procedures, it's advisable to explore CLR Stored Procedures first.
  • Higher data safety is required.

Creating CLR Stored Procedure Step by Step

To illustrate the process, let's create a simple CLR Stored Procedure fetching all rows from a table in the database. SQL statements used for creating the database, table, inserting records, etc., are listed under the "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.
CLR Demo

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

CLR Demo

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

CLR Demo

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.

CLR Demo

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.

CLR Demo

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

CLR Demo

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

CLR Demo

8. Add the following code to the method already created. Pass "context connection=true" as a connection string in the constructor while creating a new SqlConnection. This CLR stored procedure is going to be part of the database, so it will be the internal part of the database, and there is no need to connect the database externally. So, no need to provide the 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 a connection initially.

CLR Demo

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 the 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:

CLR Demo

ransact-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 sim

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

References

References

    Leave a Comment

    Recent Posts

    Recent Comments

      Archives

      Categories

      Meta