Stored procedure in asp.net c# with example

Stored procedures are generally an ordered series of Transact-SQL statements bundled into a single logical unit. They allow for variables and parameters, as well as selection and looping constructs. A key point is that sprocs are stored in the database rather than in a separate file.

Advantages of Stored procedure in asp.net

  1. It provides query encapsulation and offer simple sp function. So it means sp provide better security to your data queries.
  2. Stored procedures are special objects available in database server and they are very fast when compared to ordinary t-sql statements and thus helps to improve performance both sql and application
  3. Stored procedures are faster as compared to normal T-SQL statements
  4. In a single SP execution plan we can execute a bunch of SQL statements
  5. Stored procedures are easy to maintain. If any changes occurs just we need to update single stored procedure located at database server, single update will reflect in all pages
  6. We can reuse the SP code again and again means we can call SP function again and again without compromising the performance and memory.

Types of Parameters in Stored Procedures

Input Parameter :  In SP function We can pass any number of input parameters

Output Parameter : In SP function We can output any number of output parameters

Return Parameter : In SP function we can return only one/single return parameter

Now See Stored procedure in asp.net c# with example

Step 1: Create database

CREATE TABLE [dbo].[stud] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[name] NVARCHAR (50) NOT NULL,
[address] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

Step 2 : Create store procedure

CREATE PROCEDURE [dbo].[sp_demo]
@Id int,
@name nvarchar(50),
@address nvarchar(50),
@query nvarchar(50)
AS
if(@query=’insert‘)
Begin
insert into stud values(@name,@address);
End
else if(@query=’delete‘)
Begin
delete from stud where (name)=(@name);
End
else if(@query=’update‘)
Begin
update stud set address=@address where (name)=(@name);
End
RETURN 0

Step 3:  Create data Access Class And Give name DAL.CS

public class DAL
{
public int Id { get; set; }
public string name { get; set; }
public string address { get; set; }
}

Step 4:  Create Busseness Access Layer BAL.CS

public class BAL
{
SqlConnection con=new SqlConnection(@”Data Source=(LocalDB)App_Data\mydb.mdf;Integrated Security=True“);

public void Insert_Data(DAL Da)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand(“sp_demo ‘”+DBNull.Value+”‘,'”+Da.name+”‘,'”+Da.address+”‘,’insert'”,con);
cmd.ExecuteNonQuery();
con.Close();
}

public void Delete_Data(DAL Da)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand(“sp_demo ‘”+DBNull.Value+”‘,'”+Da.name+”‘,
‘”+DBNull.Value+”‘,’delete'”,con);
cmd.ExecuteNonQuery();
con.Close();
}

public void Update_Data(DAL Da)
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = new SqlCommand(“sp_demo ‘”+DBNull.Value+”‘,'”+Da.name+”‘,
‘”+Da.address+”‘,’update'”, con);
cmd.ExecuteNonQuery();
con.Close();
}

Step 5 : Code in Code behind file

public partial class _default : System.Web.UI.Page
{
DAL da = new DAL();
BAL ba = new BAL();

protected void btn_submit_Click(object sender, EventArgs e)
{
da.name = txt_name.Text;
da.address = txt_address.Text;
ba.Insert_Data(da);
Response.Write(“data inserted….“);
Grid();
txt_name.Text = “”;
txt_address.Text = “”;
}

protected void btn_delete_Click(object sender, EventArgs e)
{
da.name = txt_name.Text;
ba.Delete_Data(da);
Grid();
txt_name.Text = “”;
}

protected void btn_update_Click(object sender, EventArgs e)
{
da.name = txt_name.Text;
da.address = txt_address.Text;
ba.Update_Data(da);
Response.Write(“Data Updated….“);
Grid();
txt_name.Text = “”;
txt_address.Text = “”;
}
}

Now Run Project And See O/P

SEE MORE

 

5 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *