How to Create & Deploy Stored Procedure from C# [CLR 3.5]

It was really tough to go back and forth between Visual Studio and SQL management Studio to create and reference back stored procedures. And, you have to have the SQL skill set to create SQL scripts at the Back End. But, it has become simpler now a days. Microsoft came up with a new project type to create SQL database projects right inside Visual Studio 2008. Follow the same .NET syntaxes while creating a Data Connection/Data Commands/Data Readers etc.

I will briefly walk through the process:

1. Create a SQL Server Project in C#



2. Select the Database Reference



3. Add a Stored Procedure Class



4. Write the Stored Procedure Like This:

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 MySP1(string _UNID)
{
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"SELECT * FROM OI_COPIED where UNID=@UNID";
conn.Open();
SqlParameter param = new SqlParameter();
param.ParameterName = "@UNID";
param.Value = _UNID;
cmd.Parameters.Add(param);
SqlDataReader rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
rdr.Close();
conn.Close();
}
catch (SqlException ex)
{
SqlContext.Pipe.Send(ex.Message);
}
}
};

5. Build and Deploy the project to SQL Server



6. Goto SQL Server Management Studio to Find and Test the Deployed Stored Procedure



7. Execute the stored procedure



8. Enter teh Parameter Value