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

Yasser Shaikh
Nov 17, 2012 · 4 min read

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  
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetAllProducts()
{
SqlConnection connection = new SqlConnection("context connection=true");
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM Products", connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
};
Step 5: Build and Deploy your CLR Stored ProcedureOnce 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 ProcedureStep 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]  
GO
EXEC[dbo].[GetAllProducts]
GO
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 ProcedureCLR Stored Procedure and Creating It Step by StepWriting CLR Stored Procedures in C# - Introduction to C# (Part 1)

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Yasser Shaikh

Written by

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade