CLR Stored Procedure

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.

 Pass "context connection=true" as connection string in the constructor while creating new SqlConnection.

Run the Stored Procedure:

Make sure the CLR is enabled with your SQL Server by running the following SQL.

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;GO