Insert Edit Update in Gridview Using Single Stored Procedure

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit">

<ItemTemplate>

<asp:LinkButton ID="btnEdit" Text="Edit" runat="server" CommandName="Edit" />

<br />

<span onclick="return confirm('Are you sure you want to delete this record?')">

<asp:LinkButton ID="btnDelete" Text="Delete" runat="server" CommandName="Delete"/>

</span>

</ItemTemplate>

<EditItemTemplate>

<asp:LinkButton ID="btnUpdate" Text="Update" runat="server" CommandName="Update" />

<br />

<asp:LinkButton ID="btnCancel" Text="Cancel" runat="server" CommandName="Cancel" />

</EditItemTemplate>

<FooterTemplate>

<asp:Button ID="btnAddRecord" runat="server" Text="Add" CommandName="Add"></asp:Button>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</div>

</form>

</body>

</html>

StoredProcedure.aspx.cs

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Insert_update_delete_Stored_Pro

{

public partial class StoredProcedure : System.Web.UI.Page

{

string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlCommand com;

SqlDataAdapter sqlda;

DataSet ds;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

}

protected void BindGrid()

{

SqlConnection con = new SqlConnection(strConnString);

com = new SqlCommand();

con.Open();

com.Connection = con;

com.CommandText = "employee_pro";

com.CommandType = CommandType.StoredProcedure;

com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));

com.Parameters["@status"].Value = "Display";

sqlda = new SqlDataAdapter(com);

ds = new DataSet();

sqlda.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

con.Close();

}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridView1.PageIndex = e.NewPageIndex;

BindGrid();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1;

BindGrid();

}

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName.Equals("Add"))

{

TextBox txtname = (TextBox)GridView1.FooterRow.FindControl("txtAddname");

TextBox txtaddress = (TextBox)GridView1.FooterRow.FindControl("txtAddaddress");

TextBox txtdesignation = (TextBox)GridView1.FooterRow.FindControl("txtAdddesignation");

string name, address, designation;

name = txtname.Text;

address = txtaddress.Text;

designation = txtdesignation.Text;

Addemployee(name, address, designation);

GridView1.EditIndex = -1;

BindGrid();

}

}

protected void Addemployee(string name, string address, string designation)

{

SqlConnection con = new SqlConnection(strConnString);

con.Open();

com = new SqlCommand();

com.CommandText = "employee_pro";

com.CommandType = CommandType.StoredProcedure;

com.Connection = con;

com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@designation", SqlDbType.VarChar, 50));

com.Parameters["@status"].Value = "Add";

com.Parameters["@name"].Value = name;

com.Parameters["@address"].Value = address;

com.Parameters["@designation"].Value = designation;

sqlda = new SqlDataAdapter(com);

ds = new DataSet();

sqlda.Fill(ds);

con.Close();

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

Label empid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblempid");

string eid = empid.Text;

Deleteemployee(eid);

GridView1.EditIndex = -1;

BindGrid();

}

protected void Deleteemployee(string empid)

{

SqlConnection con = new SqlConnection(strConnString);

com = new SqlCommand();

com.CommandText = "employee_pro";

com.CommandType = CommandType.StoredProcedure;

com.Connection = con;

com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int));

com.Parameters["@status"].Value = "Delete";

com.Parameters["@empid"].Value = empid;

sqlda = new SqlDataAdapter(com);

ds = new DataSet();

sqlda.Fill(ds);

con.Close();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

BindGrid();

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

Label empid = (Label)GridView1.Rows[e.RowIndex].FindControl("lblempid");

TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname");

TextBox address = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtaddress");

TextBox designation = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtdesignation");

string eid = empid.Text;

string ename = name.Text;

string eaddress = address.Text;

string edesignation = designation.Text;

Updateemployee(eid, ename, eaddress, edesignation);

GridView1.EditIndex = -1;

BindGrid();

}

protected void Updateemployee(string empid, string name, string address, string designation)

{

SqlConnection con = new SqlConnection(strConnString);

com = new SqlCommand();

con.Open();

com.Connection = con;

com.CommandText = "employee_pro";

com.CommandType = CommandType.StoredProcedure;

com.Parameters.Add(new SqlParameter("@empid", SqlDbType.Int));

com.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));

com.Parameters.Add(new SqlParameter("@designation", SqlDbType.VarChar, 50));

com.Parameters["@empid"].Value = Convert.ToInt32(empid.ToString());

com.Parameters["@status"].Value = "Update";

com.Parameters["@name"].Value = name;

com.Parameters["@address"].Value = address;

com.Parameters["@designation"].Value = designation;

sqlda = new SqlDataAdapter(com);

ds = new DataSet();

sqlda.Fill(ds);

con.Close();

}

}

}

Output



Conclusion

So here we learned how to insert, edit, update and cancel the records in a grid view using a single stored procedure.