CREATE STORED PROCEDURES USING .NET IDE

CREATE STORED PROCEDURES USING .NET IDE

We can create stored procedures using modern IDE such as Visual Studio .Net and using some languets such as C#, VB and etc. As well as, we can easily manage stored procedures through that one. By following below simple step it will be very easy to create stored procedure without using Microsoft SQL management studio. In here I am going to explain how to create a SQL Server Project using .net C#.

1st step:
Create the Data base project. Follow step.
File->New->Project->Database

By providing necessary details can create Database project. Figure 1 how to create database project.














Figure 1 show that how to create a SQL Server Project.

2nd step:
Adding a database reference

Now it will ask for a database reference. In here you can add new data base reference or can use existing data base references. Figure 2 explain that how to add data base reference for SQL Server Project.



















Figure 2 show: can use existing Db reference or Add New Reference

Figure 3 show how to add new Database Reference to SQL Database Project.



















Figure 3 show how to create new database reference by adding necessary details.

3rd step:
Adding a Stored Procedure to SQL Database project

Write click on the Project and add a stored procedure. By adding stored procedure you can create whatever stored procedure you want. Normally there are few templates available from that template select stored procedure template. Figure 4 introduce how to add new stored procedure file to project.













Figure 4 show that adding new stored procedure to SQL Database project.

Now I have already created stored procedure on my SQL Database project. Open stored procedure file. It will be simply 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 TestStoredProcedure()
{
// Put your code here
}
};

4th step
Now in the method we have to add suitable SQL query and able to create simple Stored procedure. By adding following codes to that methods. Make SqlPipe object and SqlCommand object.

Important: The function from stored procedure want to perform should enter as SQL query. Stored procedure works according to the SQL query we are placing here. Therefore we must identify SQL query is working correctly otherwise Stored Procedure will not be working correctly according to our requirements.

public static void TestStoredProcedure()
{
// Put your code here
SqlPipe sqlPipe;
SqlCommand sCmd = new SqlCommand();
sCmd.CommandText = "SELECT Name FROM NameOfStudent";
sqlPipe = SqlContext.Pipe;
sqlPipe.ExecuteAndSend(sCmd);
}

5th step:
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

Your Comments are highly appreciated; it will be improving my knowledge also.