Writing Managed Stored Procedures using C#

Introduction

With the integration of the CLR with SQL Server 2005, we can create database objects using modern object-oriented languages like VB.NET and C#. In fact, for pure non-data access code like computation, string parsing logic, etc., we should use .NET for writing the SQL server objects. It is also advisable to use managed code for writing the store procedure. Also, for accessing web services, exploring OOP's programming for better reusability, and reading external files, it is good to use managed store procedures.

This article is trying to explain the simple and required steps that are required starting the creation of the Manage Stored Procedure using C# and using them.

We will create a Visual Studio 2005 database project for the managed stored procedure.

Creating the Database project

Open Microsoft Visual Studio 2005 and create a SQL Server Project.

File->New->Project->Database.

Adding a database reference

Now it will ask for a database reference. Add one.

Adding a Stored Procedure

Write, click on the Project, and add a stored procedure.

 

The file SPOne.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SPOne()
    {
        SqlPipe p;
        SqlCommand sCmd = new SqlCommand();
        sCmd.CommandText = "SELECT * FROM Person";
        p = SqlContext.Pipe;
        p.ExecuteAndSend(sCmd);
    }
}

Deploy the Stored Procedure

Build the project and then deploy it.

Run the Stored Procedure

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

sp_configure 'clr enabled', 1;
GO
RECONFIGURE; GO

Now execute the Stored Procedure and you will get an output of select statement.

Make your Life follows Procedures and Stored them safely! If possible, manage them.