how to stored procedure sql server 2008 and c#

A stored Procedure is a Group of SQL Statements. It basically contains parameters and your logic in SQL Query format.

You can create a Stored Procedure to do various things like "Insertion", "Deletion", "Updation" which are the 3 Basic funda's of SQL.

Now lets assume a situation, where you want to insert some data regarding a User in your database. So for that case I'll write a Stored Procedure to Insert the data as shown below.

INSERTING DATA USING STORED PROCEDURE

Stored Procedure to Insert Users Information -

Create Procedure Insert_Users

(

@sUsr_Firstname varchar(50), // Parameter to accept First Name

@sUsr_Middlename varchar(50), // Parameter to accept Middle Name

@sUsr_Lastname varchar(50), // Parameter to accept Last Name

@sUsr_Password varchar(50), // Parameter to accept Password

)

BEGIN

INSERT INTO TABLENAME(FirstName, MiddleName, LastName, Password)

VALUES@sUsr_Firstname, @sUsr_Middlename, @sUsr_Lastname, @sUsr_Password)

END

C# Code to Interact with the Stored Procedure

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace InsertUsers

{

Class Cls_InsertUserInfo

{

try

{

SqlConnection con = new SqlConnection("Connection String"); // Create Connection Object.

SqlCommand cmd = new SqlCommand("Insert_Users", con);// Create Command Object and pass St Proc Name and connection Object

con.Open();// Open the Connection

cmd.CommandType = CommandType.StoredProcedure; // Set the Command Type

cmd.Parameters.Add("@sUsr_Firstname", SqlDbType.VarChar, 50).Value = txtFName.Text; // Accept Parameter

cmd.Parameters.Add("@sUsr_Middlename", SqlDbType.VarChar, 50).Value = txtMName.Text;

cmd.Parameters.Add("@sUsr_Lastname", SqlDbType.VarChar, 50).Value = txtLName.Text;

cmd.Parameters.Add("@sUsr_Password", SqlDbType.VarChar, 50).Value = txtPassword.Text;

cmd.ExecuteNonQuery(); // Execute the St Proc Query

con.Close(); // Close the Connection.

}

catch (Exception ex)

{

throw;

}

SELECTING / GETTING DATA USING STORED PROCEDURE FROM DATABASE

Create Procedure Select_Users

(

@sUsr_Firstname varchar(50), // Parameter to accept First Name

)

BEGIN

Select from TableName where FirstName = '@sUsr_Firstname'

END

C# Code to Interact with the Stored Procedure

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace InsertUsers

{

Class Cls_GetUserInfo

{

try

{

SqlConnection con = new SqlConnection("Connection String"); // Create Connection Object.

SqlCommand cmd = new SqlCommand("Select_Users", con); // Create Command Object and pass St Proc Name and connection Object

SqlDataReader dr = null; // Create Datareader to read data.

con.Open();// Open the Connection

cmd.CommandType = CommandType.StoredProcedure;// Set the Command Type

cmd.Parameters.Add("@sUsr_Firstname", SqlDbType.VarChar, 50).Value = txtFName.Text; // Accept Parameter

dr = cmd.ExecuteReader(); // Execute the St Proc Query

if (dr.HasRows) // Check if Dr has got any data.

{

while (dr.Read()) //Read data from Reader Obj if it has any data.

{

Response.Write(dr[0].ToString());

}

con.Close();// Close the Connection.

}

catch (Exception ex)

{

throw;

}

These were some of the examples, which will help you to understand how to use STored Procedures in C# Website or an Application. Please feel free to ask questions if you have any Doubt.

PLEASE MARK THIS ANSWER AS CORRECT IF IT HELPED YOU!!!

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shivanand Arur14y ago
Accepted answer
A stored Procedure is a Group of SQL Statements. It basically contains parameters and your logic in SQL Query format.

You can create a Stored Procedure to do various things like "Insertion", "Deletion", "Updation" which are the 3 Basic funda's of SQL.

Now lets assume a situation, where you want to insert some data regarding a User in your database. So for that case I'll write a Stored Procedure to Insert the data as shown below.

INSERTING DATA USING STORED PROCEDURE

Stored Procedure to Insert Users Information -
Create Procedure Insert_Users
(
@sUsr_Firstname varchar(50), // Parameter to accept First Name
@sUsr_Middlename varchar(50), // Parameter to accept Middle Name
@sUsr_Lastname varchar(50), // Parameter to accept Last Name
@sUsr_Password varchar(50), // Parameter to accept Password
)
AS
BEGIN
INSERT INTO TABLENAME(FirstName, MiddleName, LastName, Password)
VALUES@sUsr_Firstname, @sUsr_Middlename, @sUsr_Lastname, @sUsr_Password)
END

C# Code to Interact with the Stored Procedure
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace InsertUsers
{
Class Cls_InsertUserInfo
{
try
{
SqlConnection con = new SqlConnection("Connection String"); // Create Connection Object.
SqlCommand cmd = new SqlCommand("Insert_Users", con);// Create Command Object and pass St Proc Name and connection Object
con.Open();// Open the Connection
cmd.CommandType = CommandType.StoredProcedure; // Set the Command Type
cmd.Parameters.Add("@sUsr_Firstname", SqlDbType.VarChar, 50).Value = txtFName.Text; // Accept Parameter
cmd.Parameters.Add("@sUsr_Middlename", SqlDbType.VarChar, 50).Value = txtMName.Text;
cmd.Parameters.Add("@sUsr_Lastname", SqlDbType.VarChar, 50).Value = txtLName.Text;
cmd.Parameters.Add("@sUsr_Password", SqlDbType.VarChar, 50).Value = txtPassword.Text;
cmd.ExecuteNonQuery(); // Execute the St Proc Query
con.Close(); // Close the Connection.
}
catch (Exception ex)
{
throw;
}
}
}

SELECTING / GETTING DATA USING STORED PROCEDURE FROM DATABASE
Create Procedure Select_Users
(
@sUsr_Firstname varchar(50), // Parameter to accept First Name
)
AS
BEGIN
Select from TableName where FirstName = '@sUsr_Firstname'
END

C# Code to Interact with the Stored Procedure
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace InsertUsers
{
Class Cls_GetUserInfo
{
try
{
SqlConnection con = new SqlConnection("Connection String"); // Create Connection Object.
SqlCommand cmd = new SqlCommand("Select_Users", con); // Create Command Object and pass St Proc Name and connection Object
SqlDataReader dr = null; // Create Datareader to read data.
con.Open();// Open the Connection
cmd.CommandType = CommandType.StoredProcedure;// Set the Command Type
cmd.Parameters.Add("@sUsr_Firstname", SqlDbType.VarChar, 50).Value = txtFName.Text; // Accept Parameter
dr = cmd.ExecuteReader(); // Execute the St Proc Query

if (dr.HasRows) // Check if Dr has got any data.
{
while (dr.Read()) //Read data from Reader Obj if it has any data.
{
Response.Write(dr[0].ToString());
}
}
con.Close();// Close the Connection.
}
catch (Exception ex)
{
throw;
}
}
}

These were some of the examples, which will help you to understand how to use STored Procedures in C# Website or an Application. Please feel free to ask questions if you have any Doubt.

PLEASE MARK THIS ANSWER AS CORRECT IF IT HELPED YOU!!!
Kunal Vaishya14y ago
Accepted answer
Dear

          Create the StoreProcedures in Sql Server and Create SqlDataCommand and assign that Procedures into that after you can Execute the Command or Fill the Command

In Sql
--------------------------
Create Procedures PrcEmployeeInsert

As

Insert Statement

-------------------------------
In C#

            System.Data.SqlClient.SqlCommand ObjCmd = new System.Data.SqlClient.SqlCommand();
            ObjCmd.CommandType = System.Data.CommandType.StoredProcedure;
            ObjCmd.CommandText = "PrcEmployeeInsert";
            ObjCmd.Connection.ConnectionString = "ConnectionString";
            ObjCmd.ExecuteNonQuery();
amirh karimi14y ago
tanx
amirh karimi14y ago
tanx