How to write Stored Procedure for basic operations?
In this article I have explained about how to create stored procedure and how to call stored procedure in server page. This example application is created with simple select record statement and multiple record select statement, Insert and update stored procedure operations.
I have create table with three fields eno, empname, sal
How to create Insert procedure and how to call insert procedure?
Create Procedure like this
CREATE Procedure emp_insert
@eno int,
@empname varchar(20),
@sal bigint
AS
BEGIN
insert into emp values(@eno,@empname,@sal)
END
Call in server side
protected void Button1_Click(object sender, EventArgs e)
{
sqlcon.Open();
sqlcmd = new SqlCommand("emp_insert", sqlcon); //Stored Procedure Name
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = TextBox2.Text;
sqlcmd.Parameters.Add("@sal", SqlDbType.VarChar).Value = Convert.ToInt64(TextBox3.Text);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
RefreshGrid();
}
How to create Update procedure and how to call Update procedure?
Create Procedure like this
CREATE procedure emp_update
@eno int,
@empname varchar(50),
@sal bigint
as
Begin
update emp set empname=@empname,sal=@sal where eno=@eno
End
Call procedure from server side like this
protected void Button1_Click(object sender, EventArgs e)
{
sqlcon.Open();
sqlcmd = new SqlCommand("emp_update", sqlcon); //Stored Procedure Name
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
sqlcmd.Parameters.Add("@empname", SqlDbType.VarChar).Value = TextBox2.Text;
sqlcmd.Parameters.Add("@sal", SqlDbType.VarChar).Value = Convert.ToInt64(TextBox3.Text);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
RefreshGrid();
}
How to create Delete procedure and how to call Delete procedure?
Create Procedure like this
CREATE procedure emp_delete
@eno int
as
Begin
delete from emp where eno=@eno
end
Call procedure from server side like this
protected void Button1_Click(object sender, EventArgs e)
{
sqlcon.Open();
sqlcmd = new SqlCommand("emp_delete", sqlcon); //Stored Procedure Name
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.ExecuteNonQuery();
sqlcon.Close();
TextBox1.Text = "";
RefreshGrid();
}
How to create Select procedure and how to call Select procedure?
Create Procedure like this
CREATE procedure empSingleselect
@eno int
as
Begin
select * from emp where eno=@eno
End
Call procedure from server side like this
protected void Button1_Click(object sender, EventArgs e)
{
if (TextBox1.Text == "")
{
Label1.Text="Enter Employee Number";
return;
}
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("empSingleselect", sqlcon); //empSingleselect as a stored procedure name
sqlcmd.Parameters.Add("@eno", SqlDbType.Int).Value = TextBox1.Text;
sqlcmd.CommandType = CommandType.StoredProcedure;
da = new SqlDataAdapter(sqlcmd);
dt.Clear();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
GridView1.DataBind();
Label1.Text = "No Records Found";
}
sqlcon.Close();
}
Source Code Detail:
Here with I have attached source code with Database and stored procedure. Download it and try to learn Stored Procedure basic concepts.
Front End : ASP.NET
Code Behind : C#
Note: In this application I created four separate pages for understand stored procedure basic operation
Conclusion:
I hope this Article is help to beginners for understand of stored procedure Concept.