February 15th, 2012

Insert, Update, Delete in ASP.NET Gridview With Entity Framework

This article explains how to perform insert, update and delete operations in ASP.NET GridView with Entity Framework. Let us take an example to perform CRUD operations using Entity Framework Database First approach. Suppose we’ve to work on Customer table having CategoryID as foreign key.

Adding Entity Data Model:

db structure
In your project in solution explorer, right click > Add New Item > ADO.NET Entity Data Model
Give Name “ModelSample.edmx” > Add
If it asks to add in APP_code folder, click ok
Select “Generate From Database” > Next
Select your database and Set “Save Entity connection string in Web.config as” option true giving name SampleEntities.
Select Both Category and Customer tables
Give Model Namespace and click on Finish.

GridView Structure:

insert update delete gridview

We’ll use ASP.NET Gridview Footer row for inserting record, inbuilt way to edit and delete records.

<asp:Label ID="lblMessage" runat="server" Text="" ForeColor="Red"></asp:Label>
    <asp:GridView ID="gridSample" runat="server" AutoGenerateColumns="False" ShowFooter="True"
                        CssClass="grid" OnRowCommand="gridSample_RowCommand" 
        DataKeyNames="CustomerID" CellPadding="4" ForeColor="#333333"
                        GridLines="None" OnRowCancelingEdit="gridSample_RowCancelingEdit" 
                        OnRowEditing="gridSample_RowEditing" 
        OnRowUpdating="gridSample_RowUpdating" 
        onrowdatabound="gridSample_RowDataBound" 
        onrowdeleting="gridSample_RowDeleting">
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:TemplateField HeaderText="">
                                <ItemTemplate>
                                    <asp:LinkButton ID="lnkEdit" runat="server" Text="" CommandName="Edit" ToolTip="Edit"  
                                        CommandArgument=''><img src="../Images/show.png" /></asp:LinkButton>
                                    <asp:LinkButton ID="lnkDelete" runat="server" Text="Delete" CommandName="Delete" 
                                        ToolTip="Delete" OnClientClick='return confirm("Are you sure you want to delete this entry?");'
                                        CommandArgument=''><img src="../Images/icon_delete.png" /></asp:LinkButton>
                                </ItemTemplate>
                                <EditItemTemplate>
                                    <asp:LinkButton ID="lnkInsert" runat="server" Text="" ValidationGroup="editGrp" CommandName="Update" ToolTip="Save"
                                        CommandArgument=''><img src="../Images/icon_save.png" /></asp:LinkButton>
                                    <asp:LinkButton ID="lnkCancel" runat="server" Text="" CommandName="Cancel" ToolTip="Cancel"
                                        CommandArgument=''><img src="../Images/refresh.png" /></asp:LinkButton>
                                </EditItemTemplate>
                                <FooterTemplate>
                                    <asp:LinkButton ID="lnkInsert" runat="server" Text=""  ValidationGroup="newGrp" CommandName="InsertNew" ToolTip="Add New Entry"
                                        CommandArgument=''><img src="../Images/icon_new.png" /></asp:LinkButton>
                                    <asp:LinkButton ID="lnkCancel" runat="server" Text="" CommandName="CancelNew" ToolTip="Cancel"
                                        CommandArgument=''><img src="../Images/refresh.png" /></asp:LinkButton>
                                </FooterTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="First Name">
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtFirstName" runat="server" Text='<%# Bind("FirstName") %>' CssClass="" MaxLength="30"></asp:TextBox>
                                    <asp:RequiredFieldValidator ID="valFirstName" runat="server" ControlToValidate="txtFirstName"
                                    Display="Dynamic" ErrorMessage="First Name is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="editGrp">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                   <asp:TextBox ID="txtFirstNameNew" runat="server" CssClass=""  MaxLength="30"></asp:TextBox>
                                   <asp:RequiredFieldValidator ID="valFirstNameNew" runat="server" ControlToValidate="txtFirstNameNew"
                                    Display="Dynamic" ErrorMessage="First Name is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="newGrp">*</asp:RequiredFieldValidator>
                                </FooterTemplate>
                            </asp:TemplateField>       
                            <asp:TemplateField HeaderText="Last Name">
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtLastName" runat="server" Text='<%# Bind("LastName") %>' CssClass="" MaxLength="30"></asp:TextBox>
                                    <asp:RequiredFieldValidator ID="valLastName" runat="server" ControlToValidate="txtLastName"
                                    Display="Dynamic" ErrorMessage="Last Name is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="editGrp">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblLastName" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                   <asp:TextBox ID="txtLastNameNew" runat="server" CssClass=""   MaxLength="30"></asp:TextBox>
                                   <asp:RequiredFieldValidator ID="valLastNameNew" runat="server" ControlToValidate="txtLastNameNew"
                                    Display="Dynamic" ErrorMessage="Last Name is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="newGrp">*</asp:RequiredFieldValidator>
                                </FooterTemplate>
                            </asp:TemplateField>   
                            <asp:TemplateField HeaderText="Email">
                                <EditItemTemplate>
                                    <asp:TextBox ID="txtEmail" runat="server" Text='<%# Bind("Email") %>' CssClass="" MaxLength="30"></asp:TextBox>
                                    <asp:RequiredFieldValidator ID="valEmail" runat="server" ControlToValidate="txtEmail"
                                    Display="Dynamic" ErrorMessage="Email is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="editGrp">*</asp:RequiredFieldValidator>
                                    <asp:RegularExpressionValidator ID="valRegEmail" runat="server" ErrorMessage="Invalid Email"   ValidationGroup="editGrp"
                                    SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtEmail" ForeColor="Red"
                                    ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">*</asp:RegularExpressionValidator>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblEmail" runat="server" Text='<%# Bind("Email") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                   <asp:TextBox ID="txtEmailNew" runat="server" CssClass=""  MaxLength="30"></asp:TextBox>
                                   <asp:RequiredFieldValidator ID="valEmailNew" runat="server" ControlToValidate="txtEmailNew"
                                    Display="Dynamic" ErrorMessage="Email is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="newGrp">*</asp:RequiredFieldValidator>
                                   <asp:RegularExpressionValidator ID="valRegEmailNew" runat="server" ErrorMessage="Invalid Email"   ValidationGroup="newGrp"
                                    SetFocusOnError="true" Display="Dynamic" ControlToValidate="txtEmailNew" ForeColor="Red"
                                    ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">*</asp:RegularExpressionValidator>
                                </FooterTemplate>
                            </asp:TemplateField> 

                             <asp:TemplateField HeaderText="Category">
                                <EditItemTemplate>
                                    <asp:DropDownList ID="ddlCategory" runat="server">
                                    </asp:DropDownList>
                                    <asp:RequiredFieldValidator ID="valCategory" runat="server" ControlToValidate="ddlCategory"
                                    Display="Dynamic" ErrorMessage="Category is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="editGrp">*</asp:RequiredFieldValidator>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="lblCategory" runat="server" Text='<%# Bind("Category.Name") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterTemplate>
                                  <asp:DropDownList ID="ddlCategoryNew" runat="server">
                                  </asp:DropDownList>
                                   <asp:RequiredFieldValidator ID="valCategoryNew" runat="server" ControlToValidate="ddlCategoryNew"
                                    Display="Dynamic" ErrorMessage="Category is required." ForeColor="Red" SetFocusOnError="True"
                                   ValidationGroup="newGrp">*</asp:RequiredFieldValidator>
                                </FooterTemplate>
                            </asp:TemplateField>                            
                        </Columns>
                        <EditRowStyle BackColor="#2461BF" />
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <RowStyle BackColor="#EFF3FB" />
                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                        <SortedAscendingCellStyle BackColor="#F5F7FB" />
                        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
                        <SortedDescendingCellStyle BackColor="#E9EBEF" />
                        <SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>

Bind GridView:

First, import model namespace and bind customer entities to gridview. One thing, Footer template will not be displayed when no data is present. But, we need to display footer for inserting records. we’ll add a dummy row and clear it by coding to display footer row.

	/// <summary>
    /// Bind Customer data to grid
    /// </summary>
    void BindGrid()
    {
        using (SampleEntities context = new SampleEntities())
        {
            if (context.Customers.Count() > 0)
            {
                gridSample.DataSource = context.Customers;
                gridSample.DataBind();
            }
            else
            {
                var obj = new List<Customer>();
                obj.Add(new Customer());
                // Bind the DataTable which contain a blank row to the GridView
                gridSample.DataSource = obj;
                gridSample.DataBind();
                int columnsCount = gridSample.Columns.Count;
                gridSample.Rows[0].Cells.Clear();// clear all the cells in the row
                gridSample.Rows[0].Cells.Add(new TableCell()); //add a new blank cell
                gridSample.Rows[0].Cells[0].ColumnSpan = columnsCount; //set the column span to the new added cell

                //You can set the styles here
                gridSample.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
                gridSample.Rows[0].Cells[0].ForeColor = System.Drawing.Color.Red;
                gridSample.Rows[0].Cells[0].Font.Bold = true;
                //set No Results found to the new added cell
                gridSample.Rows[0].Cells[0].Text = "NO RESULT FOUND!";
            }
        }
    }

 protected void gridSample_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        DropDownList ddl = null;
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            ddl = e.Row.FindControl("ddlCategoryNew") as DropDownList;
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            ddl = e.Row.FindControl("ddlCategory") as DropDownList;
        }
        if (ddl != null)
        {
            using (SampleEntities context = new SampleEntities())
            {
                ddl.DataSource = context.Categories;
                ddl.DataTextField = "Name";
                ddl.DataValueField = "CategoryID";
                ddl.DataBind();
                ddl.Items.Insert(0, new ListItem(""));
            }
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                ddl.SelectedValue = ((Customer)(e.Row.DataItem)).CategoryID.ToString();
            }
        }
    }	

We access category dropdown (in footer row and during editing) and bind to category data in RowDataBound event.
BindGrid method is called in page_load event.

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
        lblMessage.Text = "";
    }

Inserting Data:

 protected void gridSample_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "InsertNew")
        {
            GridViewRow row = gridSample.FooterRow;
            TextBox txtFirstName = row.FindControl("txtFirstNameNew") as TextBox;
            TextBox txtLastName = row.FindControl("txtLastNameNew") as TextBox;
            TextBox txtEmail = row.FindControl("txtEmailNew") as TextBox;
            DropDownList ddlCategory = row.FindControl("ddlCategoryNew") as DropDownList;
            if (txtFirstName != null && txtLastName != null && txtEmail != null && ddlCategory != null)
            {
                using (SampleEntities context = new SampleEntities())
                {
                    Customer obj = new Customer();
                    obj.FirstName = txtFirstName.Text;
                    obj.LastName = txtLastName.Text;
                    obj.Email = txtEmail.Text;
                    obj.CategoryID = Convert.ToInt32(ddlCategory.SelectedValue);
                    context.Customers.AddObject(obj);
                    context.SaveChanges();
                    lblMessage.Text = "Added successfully.";
                    BindGrid();
                }
            }
        }       
    }

We will get data from footer controls, create new object and use the EntitySet<T>.AddObject method for adding newly created objects. When context.SaveChanges() is called, EF goes ahead and inserts the record into the database.

Updating Data:

	protected void gridSample_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gridSample.EditIndex = e.NewEditIndex;
        BindGrid();
    }
    protected void gridSample_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gridSample.EditIndex = -1;
        BindGrid();
    } 
   protected void gridSample_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow row = gridSample.Rows[e.RowIndex];
        TextBox txtFirstName = row.FindControl("txtFirstName") as TextBox;
        TextBox txtLastName = row.FindControl("txtLastName") as TextBox;
        TextBox txtEmail = row.FindControl("txtEmail") as TextBox;
        DropDownList ddlCategory = row.FindControl("ddlCategory") as DropDownList;
        if (txtFirstName != null && txtLastName != null && txtEmail != null && ddlCategory != null)
        {
            using (SampleEntities context = new SampleEntities())
            {
                int customerID = Convert.ToInt32(gridSample.DataKeys[e.RowIndex].Value);
                Customer obj = context.Customers.First(x => x.CustomerID == customerID);
                obj.FirstName = txtFirstName.Text;
                obj.LastName = txtLastName.Text;
                obj.Email = txtEmail.Text;
                obj.CategoryID = Convert.ToInt32(ddlCategory.SelectedValue);
                context.SaveChanges();
                lblMessage.Text = "Saved successfully.";
                gridSample.EditIndex = -1;
                BindGrid();
            }
        }
    }
	
	

We get customerID from datakey of gridview, get Customer object using customerID, modify data in customer object and call SaveChanges to apply on database.

Deleting Data:

 protected void gridSample_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int customerID = Convert.ToInt32(gridSample.DataKeys[e.RowIndex].Value);
        using (SampleEntities context = new SampleEntities())
        {
            Customer obj = context.Customers.First(x=>x.CustomerID == customerID);
            context.Customers.DeleteObject(obj);
            context.SaveChanges();
            BindGrid();
            lblMessage.Text = "Deleted successfully.";
        }     
    }

We use DeleteObject method to delete object and SaveChanges to reflect it on database.

Hope, It helps !!!

  • Massimiliano Martella

    grazie

  • Massimiliano Martella

    grazie

  • Mehboo Hmc

    So nice….thnx

    Mehboob Ali

  • jake

    great information, thank you

  • Jeff

    You really ought to show separation of concerns (DAL, BBL), but overall nice example

  • Fernando Lusardo

    Please, give me some help here! I’m trying to apply your example and have problems here:
    protected void gridEmpresa_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == “InsertNew”) { GridViewRow row = gridEmpresa.FooterRow; TextBox txtRazao = row.FindControl(“txtRazaoNew”) as TextBox; TextBox txtEmail = row.FindControl(“txtEMailNew”) as TextBox;
    if (txtRazao != null && txtEmail != null) { using (EmpresaEntities context = new EmpresaEntities()) { Empresa obj = new Empresa(); obj.Razao = txtRazao.Text; obj.EMail = txtEmail.Text; context.Empresa.AddObject(obj); context.SaveChanges(); lblMessage.Text = “Empresa inserida com sucesso!”; BindGrid(); } } } }

    when I build the application give me an error in all the fields of the table used to test your GridView, says: Empresa doesnt have a definition to Razao or no extension takes a first parameter of type Empresa (and says about forgotten assembly reference?)

    Please, could you give me some help?

  • Dinesh Arjun

    Hi,

    Im trying to add new row, but it doesnt
    i set my first column as identity
    pls help

  • Hoang Nguyen

    thanks so much.

  • Danny

    I try to explore your code in a similar situation. However, it show error at

    if (context.Customers.Count() > 0) //Error at context

    var obj = new List(); //Error at
    obj.Add(new Customer()); //Error at

    ddl.DataSource = context.Categories; //Error at context

    ddl.SelectedValue = ((Customer)(e.Row.DataItem)).CategoryID.ToString();

    Customer obj = new Customer(); //Error at
    Please help by providing additional code and help.
    Thank

  • Rahul Reddy

    nice article..but am getting issue at the level of editing, the drop downlist is not showing selected value its showing error at the level of
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    ddl.SelectedValue = ((Customer)(e.Row.DataItem)).CategoryID.ToString();
    }

    like Unable to cast object of type .any one suggest me best