How to create a CLR Stored Procedure using C# and Visual Studio

Below are the points I will be covering in this article

What exactly do I mean by CLR Stored Procedure?

Why do we use CLR Stored Procedure?

Can we write a CLR Stored Procedure using C# and Visual Studio?

Step by Step guide to creating your first CLR Stored Procedure

What exactly do I mean by CLR Stored Procedure?

CLR stands for Common Language Runtime. Stored Procedures, Functions and triggers can be coded in CLR.

Why do we use CLR Stored Procedure?

We use CLR stored procedure for a number of reasons, few of which are as listed below…

1. CLR is faster than T-SQL in many cases.

2. CLR is used to accomplish task that are not possible by T-SQL because of their complexity.

3. Convenient for programmers, as CLR stored procedures can be written in C# or VB or in any other language that .NET framework supports.

Can we write a CLR Stored Procedure using C# and Visual Studio?

Yes, we can. In this article I will show you how to.

Step by Step guide to creating your first CLR Stored Procedure

Now that we have a basic understanding on what CLR stored procedures are and when to use them, lets now get started with how to implement them.

Step 1 : Open Visual Studio (I have used visual studio 2010 and SQL server 2005 for this example). Create new project and select the project type as “Visual C# SQL CLR Database Project” set the .NET framework to 3.5 (why ?)

Step 2 : Once the project is loaded. You should get a prompt like the one below,

Select an existing database connection or you can create a new one.

Step 3 : Once the project loads, right click the project and Add > Stored Procedure.

Give a name to the Stored Procedure and select ‘ok’.

Once the stored procedure is added, you will see a file like the one below

Step 4: Code

Now below is a small code set to get you started. I am preparing a simple sql command SELECT * FROM Products and sending the data using the SqlContext.Pipe.Send()

public partial class StoredProcedures  
public static void GetAllProducts()
SqlConnection connection = new SqlConnection("context connection=true");
SqlCommand command = new SqlCommand("SELECT * FROM Products", connection);  
SqlDataReader reader = command.ExecuteReader();
Step 5: Build and Deploy your CLR Stored Procedure
Once your CLR stored procedure is ready the next step is to build and then deploy the stored procedure.(For successful deployment you will require your database login to be on the sysadmin role group)

P.S : Deploying the stored procedure may take some time and may even cause your visual studio to be 'not responding', be prepared for this.
If for whatever reason, the deployment is not successful you can always manually deploy the stored procedure and assembly by referring this article : Manually Deploying a CLR Stored Procedure
Step 6: Deployment Complete !
Once the deployment is successful, open the Sql Server Management studio and now you will see a stored proc added with the same name as that of the above function/method. Also along with the stored procedure an assembly is also added.

Step 6: Execute and Test your CLR Stored Procedure !
This is the last step of this tutorial. Now after your CLR stored procedure is deployed successfully, now its time to test it.
USE [ProductsDB]  
and on execution of the above CLR stored proc, I should get all the rows of the Products table as seen below.

Phew ! that was long :)
I also plan to write two more related post to this which are
- How to pass parameter/input to a CLR stored procedure ?
- How to manually deploy a CLR stored procedure ?
Cheers !
Further Reading :
SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure
CLR Stored Procedure and Creating It Step by Step
Writing CLR Stored Procedures in C# - Introduction to C# (Part 1)