How To Insert, Update And Delete Rows In GridView using Linq In Asp.Net.
Here we are binding a GridView and insert, update and delete records.
Points Of Remember:
1. Place LinkButton Edit and Delete in ItemTemplate, LinkButton Update and Cancel in EditTamplate and LinkButton Insert in FooterTemplate of Gridview's column.
2. Fire GridView's RowEditing event for LinkButton Edit.
3. Fire GridView's RowUpdating event for LinkButton Update.
4. Fire GridView's SelectedIndexChanging event for LinkButton Insert.
5. Fire GridView's RowDeleting event for LinkButton Delete.
6. Fire GridView's RowCancelingEdit event for LinkButton Cancel.
7. Set CommandName propery of LinkButtons according to their respective event.
8. Set GridView's DataKeyNames Property to record ID (ID=Primary/Unique key of student table)
9. Add Linq to Sql in your project, drag your table and create object of DataContext
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Insert, update and delete in GridView using Linq</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" DataKeyNames="ID"
OnRowCancelingEdit="GridView1_RowCancelingEdit" CellPadding="4" ForeColor="#5798CF"
OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating"
OnRowDeleting="GridView1_RowDeleting" Width="700px" ShowFooter="True"
OnSelectedIndexChanging="GridView1_SelectedIndexChanging">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="LB1" runat="server" CommandName="Edit">Edit</asp:LinkButton>
<asp:LinkButton ID="LB2" runat="server" CommandName="Delete">Delete</asp:LinkButton>
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="LB3" runat="server" CommandName="Update">Update</asp:LinkButton>
<asp:LinkButton ID="LB4" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="LkB1" runat="server" CommandName="Select">Insert</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txt_Name" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txt_Name_insert" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Branch">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Branch") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txt_Branch" runat="server" Text='<%# Eval("Branch") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txt_Branch_insert" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("City") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txt_City" runat="server" Text='<%# Eval("City") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txt_City_insert" runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#5798CF" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<FooterStyle BackColor="#ff9751" />
</asp:GridView>
</div>
</form>
</body>
</html>
C# Codes :
public partial class GridView_test_GridViewLinqFunctions
:
System.Web.UI.
Page
{
// Linq object
DataClassesDataContext db = new DataClassesDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridView();
}
}
// Fill GridView
protected void BindGridView()
{
var data = from i in db.tbl_students
select i;
if (data != null)
{
GridView1.DataSource = data;
GridView1.DataBind();
}
}
// Edit the Gridview's row
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindGridView();
}
// Update the Gridview's row
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
// Find student id for update the row
int id = (int)GridView1.DataKeys[e.RowIndex].Value;
// Find new updated values for TexBox
TextBox name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txt_Name");
TextBox branch = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txt_Branch");
TextBox city = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txt_City");
// Select specific row from database table
tbl_student student = (from i in db.tbl_students
where i.ID == id
select i).First();
student.Name = name.Text;
student.Branch = branch.Text;
student.City = city.Text;
// Update changes in database table
db.SubmitChanges();
GridView1.EditIndex = -1;
BindGridView();
}
// Cancel row edit operation
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
BindGridView();
}
// Delete row from database table
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
// Find student id for edit the row
int id = (int)GridView1.DataKeys[e.RowIndex].Value;
// Select specific row from database table
tbl_student student = (from i in db.tbl_students
where i.ID == id
select i).First();
// Delete row from database table
db.tbl_students.DeleteOnSubmit(student);
db.SubmitChanges();
GridView1.EditIndex = -1;
BindGridView();
}
// Insert new row in database table
protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
TextBox txtName = (TextBox)GridView1.FooterRow.FindControl("txt_Name_insert");
TextBox txtBranch = (TextBox)GridView1.FooterRow.FindControl("txt_Branch_insert");
TextBox txtCity = (TextBox)GridView1.FooterRow.FindControl("txt_City_insert");
tbl_student student = new tbl_student();
student.Name = txtName.Text;
student.Branch = txtBranch.Text;
student.City = txtCity.Text;
// no need to supply autoincreament column
// Insert into database
db.tbl_students.InsertOnSubmit(student);
db.SubmitChanges();
// Refresh Gridview for reflecting new row
BindGridView();
}
}
Output:
