In this article I’m going to explain how to insert
delete edit and update records in ASP.NET GridView using C#.
If we want to insert delete edit and update records in gridview we
should use following gridview events,
1.
onrowcommand
2.
onrowdeleting
3.
onrowupdating
4.
onrowcancelingedit
5. onrowediting
Also we should use following
template field in GridView.
1.ItemTemplate :
ItemTemplate is used
to display records in grid view
2.EditItemTemplate
EditItemTemplate is used to
Edit/Update records in grid view
3.FooterTemplate
FooterTemplate is used
to insert new records in grid view
Table Design:
|
Column Name
|
Data Type
|
|
empid
|
varchar(50)
|
|
name
|
varchar(100)
|
|
designation
|
varchar(50)
|
|
city
|
varchar(50)
|
|
country
|
varchar(50)
|
Create table script:
CREATE TABLE [dbo].[EmployeeDetails](
[empid]
[varchar](50) NULL,
[name]
[varchar](100) NULL,
[designation]
[varchar](100) NULL,
[city]
[varchar](50) NULL,
[country]
[varchar](50) NULL
) ON [PRIMARY]
Designer
Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
<table width="800px" align="center">
<tr>
<td colspan="2" align="center"><b>Employee Details</b></td>
</tr>
<tr>
<td colspan="2">
<asp:GridView ID="gvEmployeeDetails" runat="server"
Width="100%"
AutoGenerateColumns="false" ShowFooter="true"
onrowcommand="gvEmployeeDetails_RowCommand"
onrowdeleting="gvEmployeeDetails_RowDeleting"
onrowupdating="gvEmployeeDetails_RowUpdating"
onrowcancelingedit="gvEmployeeDetails_RowCancelingEdit"
onrowediting="gvEmployeeDetails_RowEditing">
<Columns>
<asp:TemplateField HeaderText="Employee ID">
<ItemTemplate>
<asp:Label ID="lblEmpID"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"empid") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblEditEmpID"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"empid") %>'></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddEmpID"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="lblName"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditName"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddName"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Designation">
<ItemTemplate>
<asp:Label ID="lblDesignation"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"designation") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditDesignation"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"designation") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddDesignation"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="lblCity"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"city") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditCity"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"city") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCity"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:Label ID="lblCountry"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"country") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEditCountry"
runat="server"
Text='<%#DataBinder.Eval(Container.DataItem,
"country") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddCountry"
runat="server"
></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Action">
<ItemTemplate>
<asp:ImageButton ID="imgbtnEdit"
runat="server"
CommandName="Edit"
ImageUrl="~/Images/icon-edit.png"
Height="32px"
Width="32px"/>
<asp:ImageButton ID="imgbtnDelete" runat="server" CommandName="Delete" ImageUrl="~/Images/Delete.png"/>
</ItemTemplate>
<EditItemTemplate>
<asp:ImageButton ID="imgbtnUpdate"
runat="server"
CommandName="Update"
ImageUrl="~/Images/icon-update.png"/>
<asp:ImageButton ID="imgbtnCancel"
runat="server"
CommandName="Cancel"
ImageUrl="~/Images/icon-Cancel.png"/>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lbtnAdd"
runat="server"
CommandName="ADD"
Text="Add"
Width="100px"></asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Code
Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
public partial class _Default :
System.Web.UI.Page
{
SqlConnection
conn = new SqlConnection("Data Source=SPIDER;Initial Catalog=Demo;Integrated
Security=True");
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
BindData();
}
}
protected void BindData()
{
DataSet
ds = new DataSet();
DataTable
FromTable = new DataTable();
conn.Open();
string
cmdstr = "Select * from EmployeeDetails";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
SqlDataAdapter
adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
cmd.ExecuteNonQuery();
FromTable = ds.Tables[0];
if
(FromTable.Rows.Count > 0)
{
gvEmployeeDetails.DataSource =
FromTable;
gvEmployeeDetails.DataBind();
}
else
{
FromTable.Rows.Add(FromTable.NewRow());
gvEmployeeDetails.DataSource =
FromTable;
gvEmployeeDetails.DataBind();
int
TotalColumns = gvEmployeeDetails.Rows[0].Cells.Count;
gvEmployeeDetails.Rows[0].Cells.Clear();
gvEmployeeDetails.Rows[0].Cells.Add(new TableCell());
gvEmployeeDetails.Rows[0].Cells[0].ColumnSpan = TotalColumns;
gvEmployeeDetails.Rows[0].Cells[0].Text = "No
records Found";
}
ds.Dispose();
conn.Close();
}
protected void gvEmployeeDetails_RowDeleting(object sender, GridViewDeleteEventArgs
e)
{
Label
lblEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEmpID");
conn.Open();
string
cmdstr = "delete from EmployeeDetails where
empid=@empid";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
cmd.Parameters.AddWithValue("@empid", lblEmpID.Text);
cmd.ExecuteNonQuery();
conn.Close();
BindData();
}
protected void gvEmployeeDetails_RowCommand(object sender, GridViewCommandEventArgs
e)
{
if
(e.CommandName.Equals("ADD"))
{
TextBox
txtAddEmpID = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddEmpID");
TextBox
txtAddName = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddName");
TextBox
txtAddDesignation = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddDesignation");
TextBox
txtAddCity = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCity");
TextBox
txtAddCountry = (TextBox)gvEmployeeDetails.FooterRow.FindControl("txtAddCountry");
conn.Open();
string
cmdstr = "insert into
EmployeeDetails(empid,name,designation,city,country)
values(@empid,@name,@designation,@city,@country)";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
cmd.Parameters.AddWithValue("@empid", txtAddEmpID.Text);
cmd.Parameters.AddWithValue("@name", txtAddName.Text);
cmd.Parameters.AddWithValue("@designation", txtAddDesignation.Text);
cmd.Parameters.AddWithValue("@city", txtAddCity.Text);
cmd.Parameters.AddWithValue("@country", txtAddCountry.Text);
cmd.ExecuteNonQuery();
conn.Close();
BindData();
}
}
protected void gvEmployeeDetails_RowUpdating(object sender, GridViewUpdateEventArgs
e)
{
Label
lblEditEmpID = (Label)gvEmployeeDetails.Rows[e.RowIndex].FindControl("lblEditEmpID");
TextBox
txtEditName = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditName");
TextBox
txtEditDesignation = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditDesignation");
TextBox
txtEditCity = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCity");
TextBox
txtEditCountry = (TextBox)gvEmployeeDetails.Rows[e.RowIndex].FindControl("txtEditCountry");
conn.Open();
string
cmdstr = "update EmployeeDetails set
name=@name,designation=@designation,city=@city,country=@country where
empid=@empid";
SqlCommand
cmd = new SqlCommand(cmdstr,
conn);
cmd.Parameters.AddWithValue("@empid", lblEditEmpID.Text);
cmd.Parameters.AddWithValue("@name", txtEditName.Text);
cmd.Parameters.AddWithValue("@designation",
txtEditDesignation.Text);
cmd.Parameters.AddWithValue("@city", txtEditCity.Text);
cmd.Parameters.AddWithValue("@country", txtEditCountry.Text);
cmd.ExecuteNonQuery();
conn.Close();
gvEmployeeDetails.EditIndex = -1;
BindData();
}
protected void gvEmployeeDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)
{
gvEmployeeDetails.EditIndex = -1;
BindData();
}
protected void gvEmployeeDetails_RowEditing(object sender, GridViewEditEventArgs
e)
{
gvEmployeeDetails.EditIndex =
e.NewEditIndex;
BindData();
}
}