Asp.net Gridview basic example for insert update and delete the records

Here I am going to give an example about asp.net gridvew which covers the following points.
  • Insert the data in database 
  • Binding the data to gridview from database 
  • Grid view row editing
  • Updating the records in database
  • Grid view row deleting and taking confirmation before deleting the record
Output screen shots:








SQL query to create table tblCustomers:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblCustomers]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](50) NULL, [PhoneNumber] [varchar](10) NULL, [Address] [varchar](200) NULL, [IsActive] [bit] NULL, CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED ( [CustomerID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO .


GridviewSample.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewSample.aspx.cs" Inherits="GridviewSample" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Gridview Basics example.</title> <style type="text/css"> .gv { font-family: Arial; margin-top: 30px; font-size: 14px; } .gv th { background-color: #5D7B9D; font-weight: bold; color: #fff; padding: 2px 10px; } .gv td { padding: 2px 10px; } input[type="submit"] { margin: 2px 10px; padding: 2px 20px; background-color: #5D7B9D; border-radius: 10px; border: solid 1px #000; cursor: pointer; color: #fff; } input[type="submit"]:hover { background-color: orange; } </style> </head> <body> <form id="form1" runat="server"> <div> <table align="center" style="position: relative; top: 20px;"> <tr> <td> <table align="center"> <tr> <td> Customer Name : </td> <td> <asp:TextBox ID="txtCustomerName" runat="server" MaxLength="50" Width="250px"></asp:TextBox> </td> </tr> <tr> <td> Phone Number : </td> <td> <asp:TextBox ID="txtPhoneNumber" runat="server" MaxLength="10" Width="250px"></asp:TextBox> </td> </tr> <tr> <td> Address : </td> <td> <asp:TextBox ID="txtAddress" runat="server" MaxLength="200" Width="250px"></asp:TextBox> </td> </tr> <tr> <td colspan="2" align="center"> <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /> <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" Visible="false" /> <asp:Button ID="btnClear" runat="server" Text="Clear" OnClick="btnClear_Click" /> </td> </tr> </table> </td> </tr> <tr> <td align="center"> <br /> <asp:Label ID="lblMessage" runat="server" EnableViewState="false" ForeColor="Blue"></asp:Label> </td> </tr> <tr> <td> <asp:GridView ID="gvDepartments" runat="server" AutoGenerateColumns="False" ShowHeaderWhenEmpty="True" EmptyDataText="No Records Found" GridLines="both" CssClass="gv" EmptyDataRowStyle-ForeColor="Red"> <Columns> <asp:TemplateField HeaderText="Customer Name"> <ItemTemplate> <asp:Label ID="lblCustomerName" runat="server" Text='<%#Eval("CustomerName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Phone Number"> <ItemTemplate> <asp:Label ID="lblPhoneNumber" runat="server" Text='<%#Eval("PhoneNumber") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Address"> <ItemTemplate> <asp:Label ID="lblAddress" runat="server" Text='<%#Eval("Address") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Action"> <ItemTemplate> <asp:Button ID="btnEdit" runat="server" Text="Edit" OnClick="btnEdit_Click" /> <asp:Button ID="btnDelete" runat="server" Text="Delete" OnClientClick="return confirm('Are you sure? want to delete the department.');" OnClick="btnDelete_Click" /> <asp:Label ID="lblCustomerID" runat="server" Text='<%#Eval("CustomerID") %>' Visible="false"></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> </td> </tr> </table> <input type="hidden" runat="server" id="hidCustomerID" /> </div> </form> </body> </html>


GridviewSample.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 GridviewSample : System.Web.UI.Page { SqlConnection con = new SqlConnection(@"Data Source=localhost\sqlexpress;persist security info=True; Integrated Security=SSPI; Database=Sample;"); protected void Page_Load(object sender, EventArgs e) { try { txtCustomerName.Focus(); if (!IsPostBack) { FillGrid(); } } catch { } } void FillGrid() { try { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "Select CustomerID,CustomerName,PhoneNumber,Address from tblCustomers where IsActive=1"; cmd.Connection = con; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); gvDepartments.DataSource = ds; gvDepartments.DataBind(); } catch { } } void ClearControls() { try { txtCustomerName.Text = ""; txtPhoneNumber.Text = ""; txtAddress.Text = ""; hidCustomerID.Value = ""; btnSave.Visible = true; btnUpdate.Visible = false; } catch { throw; } } protected void btnSave_Click(object sender, EventArgs e) { try { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "insert into tblCustomers (CustomerName,PhoneNumber,Address,IsActive) values (@CustomerName,@PhoneNumber,@Address,1)"; cmd.Parameters.AddWithValue("@CustomerName", txtCustomerName.Text); cmd.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text); cmd.Parameters.AddWithValue("@Address", txtAddress.Text); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); FillGrid(); ClearControls(); lblMessage.Text = "Saved Successfully."; } catch { } finally { if (con.State == ConnectionState.Open) con.Close(); } } protected void btnClear_Click(object sender, EventArgs e) { try { ClearControls(); } catch { } } protected void btnEdit_Click(object sender, EventArgs e) { try { ClearControls(); Button btn = sender as Button; GridViewRow grow = btn.NamingContainer as GridViewRow; hidCustomerID.Value = (grow.FindControl("lblCustomerID") as Label).Text; txtCustomerName.Text = (grow.FindControl("lblCustomerName") as Label).Text; txtPhoneNumber.Text = (grow.FindControl("lblPhoneNumber") as Label).Text; txtAddress.Text = (grow.FindControl("lblAddress") as Label).Text; btnSave.Visible = false; btnUpdate.Visible = true; } catch { } } protected void btnUpdate_Click(object sender, EventArgs e) { try { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "update tblCustomers set CustomerName=@CustomerName,PhoneNumber=@PhoneNumber,Address=@Address where CustomerID=@CustomerID"; cmd.Parameters.AddWithValue("@CustomerName", txtCustomerName.Text); cmd.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text); cmd.Parameters.AddWithValue("@Address", txtAddress.Text); cmd.Parameters.AddWithValue("@CustomerID", hidCustomerID.Value); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); FillGrid(); ClearControls(); lblMessage.Text = "Updated Successfully."; } catch { } finally { if (con.State == ConnectionState.Open) con.Close(); } } protected void btnDelete_Click(object sender, EventArgs e) { try { ClearControls(); Button btn = sender as Button; GridViewRow grow = btn.NamingContainer as GridViewRow; SqlCommand cmd = new SqlCommand(); cmd.CommandText = "update tblCustomers set IsActive=0 where CustomerID=@CustomerID"; cmd.Parameters.AddWithValue("@CustomerID", (grow.FindControl("lblCustomerID") as Label).Text); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); FillGrid(); lblMessage.Text = "Deleted Successfully."; } catch { } finally { if (con.State == ConnectionState.Open) con.Close(); } } }