Gridview - Add, Edit and delete
In this article we create a Gridview from database and add some additional operations such a insert, edit and delete and bind data in the GridView control. In the previous article , we learned how to create a simple GridView at runtime .
First of all we create following table in SQL DATABASE:-
Create table tblRecord ( Id int primary key, Name varchar(200),Branch varchar(50) )
Now we can add following code in our project or design as following code:-
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style> .body { background-color:#0094ff; color:white; } </style> </head> <body class="body"> <form id="form1" runat="server"> <center> <div> <h1>Insert, Update,Delete and Bind in Grid view by santosh-asp.com</h1> <asp:GridView ID="gvDetail" runat="server" AutoGenerateColumns="false" ShowFooter="true" DataKeyNames="Id" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" OnRowCancelingEdit="gvDetail_RowCancelingEdit" OnRowCommand="gvDetail_RowCommand" OnRowDataBound="gvDetail_RowDataBound" OnRowEditing="gvDetail_RowEditing" OnRowUpdating="gvDetail_RowUpdating" OnRowDeleting="gvDetail_RowDeleting"> <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" /> <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" /> <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" /> <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" /> <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" /> <SortedAscendingCellStyle BackColor="#FFF1D4" /> <SortedAscendingHeaderStyle BackColor="#B95C30" /> <SortedDescendingCellStyle BackColor="#F1E5CE" /> <SortedDescendingHeaderStyle BackColor="#93451F" /> <Columns> <asp:TemplateField HeaderText="Id"> <ItemTemplate> <asp:Label ID="txtId" runat="server" Text='<%#Eval("Id") %>'/> </ItemTemplate> <EditItemTemplate> <asp:Label ID="lblId" runat="server" width="40px" Text='<%#Eval("Id") %>'/> </EditItemTemplate> <FooterTemplate> <asp:TextBox ID="insId" width="40px" runat="server"/> <asp:RequiredFieldValidator ID="rfvinsId" runat="server" ControlToValidate="insId" Text="?" ValidationGroup="validaiton"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Name"> <ItemTemplate> <asp:Label ID="lblName" runat="server" Text='<%#Eval("Name") %>'/> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtName" width="70px" runat="server" Text='<%#Eval("Name") %>'/> </EditItemTemplate> <FooterTemplate> <asp:TextBox ID="insName" width="120px" runat="server"/> <asp:RequiredFieldValidator ID="vname" runat="server" ControlToValidate="insName" Text="?" ValidationGroup="validaiton"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Branch"> <ItemTemplate> <asp:Label ID="lblBranch" runat="server" Text='<%#Eval("Branch") %>'/> </ItemTemplate> <EditItemTemplate> <asp:TextBox ID="txtBranch" width="70px" runat="server" Text='<%#Eval("Branch") %>'/> </EditItemTemplate> <FooterTemplate> <asp:TextBox ID="insBranch" width="120px" runat="server"/> <asp:RequiredFieldValidator ID="rfvBranch" runat="server" ControlToValidate="insBranch" Text="?" ValidationGroup="validaiton"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField> <EditItemTemplate> <asp:Button ID="ButtonUpdate" runat="server" CommandName="Update" Text="Update" Width="100px" Height="25px"/> <asp:Button ID="ButtonCancel" runat="server" CommandName="Cancel" Text="Cancel" Width="100px" Height="25px"/> </EditItemTemplate> <ItemTemplate> <asp:Button ID="ButtonEdit" runat="server" CommandName="Edit" Text="Edit" Width="100px" /> <asp:Button ID="ButtonDelete" runat="server" CommandName="Delete" Text="Delete" Width="100px" /> </ItemTemplate> <FooterTemplate> <asp:Button ID="ButtonAdd" runat="server" CommandName="AddNew" Text="Add New Row" ValidationGroup="validaiton" Width="200px" Height="25px"/> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Label ID="lblMessage" runat="server"></asp:Label> </div> </center> </form> </body> </html>
After design complete we add following code
Default.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.Drawing; using System.Data; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { SqlConnection con = new SqlConnection("Data source=PS-PC\\SANTOSH; Database=dbSantoshTest; Integrated Security=true"); //connection is created..... protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { loadRecord(); } } protected void loadRecord() //bind gridview..... { con.Open(); SqlCommand cmd = new SqlCommand("Select * from tblRecord", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); int count = ds.Tables[0].Rows.Count; con.Close(); if (ds.Tables[0].Rows.Count > 0) { gvDetail.DataSource = ds; gvDetail.DataBind(); } else { ds.Tables[0].Rows.Add(ds.Tables[0].NewRow()); gvDetail.DataSource = ds; gvDetail.DataBind(); int columncount = gvDetail.Rows[0].Cells.Count; lblMessage.Text = " No data found !!!"; } } //ask confirm for delete record..... protected void gvDetail_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { string id = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Id")); Button lnkbtnresult = (Button)e.Row.FindControl("ButtonDelete"); if (lnkbtnresult != null) { lnkbtnresult.Attributes.Add("onclick", "javascript:return deleteConfirm('" +id + "')"); } } } //go for edit..... protected void gvDetail_RowEditing(object sender, GridViewEditEventArgs e) { gvDetail.EditIndex = e.NewEditIndex; loadRecord(); } //canceling edit code...... protected void gvDetail_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) { gvDetail.EditIndex = -1; loadRecord(); } //insert record..... protected void gvDetail_RowCommand(object sender, GridViewCommandEventArgs e) { try { if (e.CommandName.Equals("AddNew")) { TextBox id = (TextBox)gvDetail.FooterRow.FindControl("insId"); TextBox inName = (TextBox)gvDetail.FooterRow.FindControl("insName"); TextBox inBranch = (TextBox)gvDetail.FooterRow.FindControl("insBranch"); con.Open(); SqlCommand cmd = new SqlCommand("insert into tblRecord values(@Id,@Name,@Branch)", con); cmd.Parameters.AddWithValue("@Id", id.Text); cmd.Parameters.AddWithValue("@Name", inName.Text); cmd.Parameters.AddWithValue("@Branch", inBranch.Text); int result = cmd.ExecuteNonQuery(); con.Close(); if (result == 1) { loadRecord(); lblMessage.BackColor = Color.Green; lblMessage.ForeColor = Color.White; lblMessage.Text = id.Text + " Added successfully...... "; } else { loadRecord(); lblMessage.BackColor = Color.Red; lblMessage.ForeColor = Color.White; lblMessage.Text = id.Text + " Error while adding row....."; } } } catch (Exception ex) { lblMessage.Text = "Invalid input data...Try Again!"; } } //update code protected void gvDetail_RowUpdating(object sender, GridViewUpdateEventArgs e) { try { TextBox txtName = (TextBox)gvDetail.Rows[e.RowIndex].FindControl("txtName"); TextBox txtBranch = (TextBox)gvDetail.Rows[e.RowIndex].FindControl("txtBranch"); string id = gvDetail.DataKeys[e.RowIndex].Values["Id"].ToString(); con.Open(); SqlCommand cmd = new SqlCommand("update tblRecord set Name=@Name,Branch=@Branch where Id=@Id", con); cmd.Parameters.AddWithValue("@Name", txtName.Text); cmd.Parameters.AddWithValue("@Branch", txtBranch.Text); cmd.Parameters.AddWithValue("@Id", id); cmd.ExecuteNonQuery(); con.Close(); lblMessage.BackColor = Color.Blue; lblMessage.ForeColor = Color.White; lblMessage.Text = id + " Updated successfully........ "; gvDetail.EditIndex = -1; loadRecord(); } catch (Exception ex) { lblMessage.Text = "Invalid input data...Try Again!"; } } //delete record..... protected void gvDetail_RowDeleting(object sender, GridViewDeleteEventArgs e) { string id = gvDetail.DataKeys[e.RowIndex].Values["Id"].ToString(); con.Open(); SqlCommand cmd = new SqlCommand("delete from tblRecord where Id=" + id, con); int result = cmd.ExecuteNonQuery(); con.Close(); if (result == 1) { loadRecord(); lblMessage.BackColor = Color.Red; lblMessage.ForeColor = Color.White; lblMessage.Text = id + " Deleted successfully....... "; } } }