Jitesh Byahut 3/10/2014 12072

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();

    }

}

View output :

demo


Please give your feedback for improving this page