Common Method for Executing Crud application using stored procedure
Hi,here iam going to explain a common method for executing stored procedure
for insert,update and delete.
a single method will do all these 3 operations
the use of this function is we can add,edit and delete record using a single function.Normally if we want to add,update and delete operation using stored procedure, we should write 3 different method for executing stored procedure.
here we can do these 3 operation in single method.
First create a table with 3 fields
id,fname,lname.
next create a stored procedure for inserting data like this
Create procedure Sp-Add
@id varchar(10),
@fname varchar(50),
@lname varchar(50)
as
insert into emp1 values(@id,@fname,@lname)
//stored procedure for deleting a record
create procedure Sp_delete
@id nvarchar(20)
as delete from emp1 where id=@id
//stored procedure for updating records
create procedure Sp_Update
@id varchar(50),
@fname varchar(50),
@lname varchar(50)
as
update emp1 set fname=@fname,lname=@lname where id=@id
*******************************
next design a form with 3 text boxes as txtid,txtfname,txtlname and 3 buttons for insert,update,delete and name as btninsert,btnupdate,btndelete
finally add a label as label1 for display message
create a function like this
here we pass parameters as arraylist and procedurename
//create connection
SqlConnection con = new SqlConnection("Data Source=BAIJU-PC;Initial Catalog=baiju;Integrated Security=True");
public int ExecuteStoredProcedureForCrud(ArrayList alist, string spname)
{
//following command is used to retrieve the parameters from stored //procedure spname is your stored procedure name
SqlCommand cmdparam = new SqlCommand("SELECT name FROM sys.parameters WHERE object_id = OBJECT_ID('" + spname + "')", con);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmdparam;
DataSet ds = new DataSet();
da.Fill(ds);
SqlCommand cmd = new SqlCommand(spname, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < alist.Count; i++)
{
cmd.Parameters.AddWithValue(ds.Tables[0].Rows[i]["name"].ToString(), alist[i].ToString());
}
try
{
con.Open();
int j = cmd.ExecuteNonQuery();
if (j > 0)
{
return 1;
}
else
{
return 0;
}
}
catch (Exception ex)
{
return 0;
}
finally
{
con.Close();
}
}
******************************************
on the click event of btninsert add following code
//create an arraylist adding data
ArrayList list = new ArrayList();
list.Add(txtid.Text);
list.Add(txtfname.Text);
list.Add(txtlname.Text);
int i = ExecuteStoredProcedureForCrud(list, "Sp_Add1");
if (i > 0)
{
Label1.Text = "Added Successfully";
Label1.ForeColor = System.Drawing.Color.Blue;
}
*********************************************************************
on the click event of btn update
add following code
ArrayList list = new ArrayList();
list.Add(txtid.Text);
list.Add(txtfname.Text);
list.Add(txtlname.Text);
int i = ExecuteStoredProcedureForCrud(list, "Sp_Update");
if (i > 0)
{
Label1.Text = "Updated Successfully";
Label1.ForeColor = System.Drawing.Color.Blue;
}
*********************************************
on the click event of btn delete add following code
ArrayList list=new ArrayList();
list.Add(txtid.Text);
int i = ExecuteStoredProcedureForCrud(list, "Sp_delete");
if (i >0)
{
Label1.Text = "Deleted Successfully";
Label1.ForeColor = System.Drawing.Color.Blue;
}
Design only Id mentioned u create by own on Visual Studio
GridView ID="GridView1"
asp:Label ID="Label1" runat="server" Text="First name"
TextBox ID="TextBox1" runat="server" Width="180px"
Label ID="Label4" runat="server" Text="enter Fname to retrive
details" Visible="False"
Label ID="Label2" runat="server" Text="Last name
TextBox ID="TextBox2" runat="server" Width="180px">
Label ID="Label3" runat="server" Text="Salery"
TextBox ID="TextBox3" runat="server" Width="180px"
Button ID="Button1" runat="server" OnClick="Button1_Click"
Text="Insert" Width="120px"
Button ID="Button2" runat="server" Text="Updata"
OnClick="Button2_Click" Width="120px"
Button ID="Button4" runat="server" Text="Delete"
OnClick="Button4_Click" Width="120px"
Button ID="Button3" runat="server" Text="Get particular emp details" Width="135px"OnClick="Button3_Click"
Button ID="Button5" runat="server" Text="clear" Width="120px" OnClick="Button5_Click1"
aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=KOTI-PC;Initial Catalog=empdb;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
fillgrid();
}
}
public void fillgrid()
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("display",conn);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
conn.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
conn.Open();
SqlCommand cmd = new SqlCommand("insertdata", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Fname", TextBox1.Text);
cmd.Parameters.AddWithValue("@Lname", TextBox2.Text);
cmd.Parameters.AddWithValue("@Salery", Convert.ToInt32(TextBox3.Text));
cmd.ExecuteNonQuery();
conn.Close();
Response.Write("Insertion Success");
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
fillgrid();
}
protected void Button2_Click(object sender, EventArgs e)
{
conn.Open();
SqlCommand cmd = new SqlCommand("updatedata", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Fname", TextBox1.Text);
cmd.Parameters.AddWithValue("@Lname", TextBox2.Text);
cmd.Parameters.AddWithValue("@Salery", Convert.ToInt32(TextBox3.Text));
cmd.ExecuteNonQuery();
conn.Close();
Response.Write("Update Success");
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
fillgrid();
}
protected void Button4_Click(object sender, EventArgs e)
{
conn.Open();
SqlCommand cmd = new SqlCommand("deletedata", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Fname", TextBox1.Text);
cmd.ExecuteNonQuery();
conn.Close();
Response.Write("Delete Success");
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
fillgrid();
}
protected void Button3_Click(object sender, EventArgs e)
{
conn.Open();
Label4.Visible = true;
SqlCommand cmd = new SqlCommand("getdata", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Fname", TextBox1.Text);
//dont use cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
TextBox1.Text = dr[0].ToString();
TextBox2.Text = dr[1].ToString();
TextBox3.Text = dr[2].ToString();
}
else
{
dr.Close();
conn.Close();
}
conn.Close();
fillgrid();
}
protected void Button5_Click1(object sender, EventArgs e)
{
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
}
}
Stored Procedures
Create PROCEDURE [dbo].[getdata]
@Fname varchar(50)
AS
BEGIN
SELECT *from emp where Fname=@Fname
END
Create PROCEDURE [dbo].[display]
-- Add the parameters for the stored procedure here
AS
BEGIN
SELECT *from emp
END
Create PROCEDURE [dbo].[insertdata]
@Fname varchar(50),@Lname varchar(50),@Salery int
AS
BEGIN
insert into emp values(@Fname,@Lname,@Salery)
END
Create PROCEDURE [dbo].[updatedata]
@Fname varchar(50),@Lname varchar(50),@Salery int
as
BEGIN
update emp set Lname=@Lname,Salery=@Salery where Fname=@Fname
END
Create PROCEDURE [dbo].[deletedata]
@Fname varchar(50)
as
begin
delete from emp where Fname=@Fname
end
*** first create Table emp in empdb ***
Fname varchar(50)
Lname varchar(50)
Salery int