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 webservices, exploring OOP's programming for better reusability and read external files, it is good to used managed store procedure.

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

The Project

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:

Past following lines to the file SPOne.cs. Make sure that the table Person exist on your database or give the name of your table instead of Person.

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 the CLR 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!!!!