Feb 15, 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 !!!