Many to Many relation with Entity Framework in ASP.NET GridView in a simplified way

• Download Sample - 360.7 KB

Introduction

When I started searching articles on ASP.NET with Entity Framework(EF) in web I found very few, most of the articles on ASP.NET MVC instead of ASP.NET WebForms.

I struggled a lot to find an article in ASP.NET with Entity Framework to perform CRUD (Create, Read, Update & Delete) operations using Gridview (Many-to-Many relations) . I didn't find any suitable article on web.

Finally It enables me to write this article which covers CRUD operations using Entity Framework with ASP.NET WebForms support.

Brief about Entity Framework

The basic question is what is the Entity Framework ? What are the Advantages of using ADO.NET Entitiy Framework instead of using ADO.NET?

In Simple EF to reduce your effort to perform CRUD (Create,Read, Update & Delete). Without writing any Queries you can perform CRUD operations by taking support of EF & LINQ. To take advantage of EF you have to create .edmx file. Which you can import required tables & Procedures. It creates auto generated code based on realtions.(Tables created as Classes & Coulums as Properties which you can Rename also).

The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

Getting Started

In this article I mainly concentrated on Many-To-Many Relationship to perform CRUD operations using EF & Linq. I have taken three tables Customer, Product, and CustomerProduct. I am performing Insert, Update, and Delete operations using EF and LINQ.

Database Diagram:

Entity Framework ER diagram (Which shows the relation between Customer & Product (in code Model1.edmx):

The following screen shows the UI part. (Add Product, Add Customer and Update & Delete Customer details in GridView)

Using the code

Create an object to the Edmx class globally:

ManyToManyEntities manyToMany = new ManyToManyEntities();

To Add Product: (In AddProduct Button Click)

Create an object to Product class and Set the ProductName property. Finally add the Product object to EDMX object (manyToMany).

Product p = new Product();
//Set the ProductName property from textbox.
p.ProductName = txtProd.Text.Trim();
manyToMany.Products.AddObject(p);
manyToMany.SaveChanges();

To Add Customer: (In AddCustomer Button Click)

Create an object to Customer class and Get single Product object based on ProdId dropdown from Product collection. Add Product to Customer object. Finally Add Customer object to manyToMany object.

In simple add all the collection details to Customer object and finally add Customer object to Edmx object (manyToMany) and call the SaveChanges() method.

Note: No need to write code for inserting records into CustomerProduct table. Entity Framework will automatically handles the code to insert the relation record.

//Get ProductId from Dropdown selection.
int ProdId = Convert.ToInt32(drpProd.SelectedValue);               
Customer c = new Customer();
/Set Customer Name property
c.CustName = txtCus.Text.Trim();
//Get single product object from collection.  
Product p = manyToMany.Products.SingleOrDefault(x => x.ProductId == ProdId);
//Add Product details to customer
c.Products.Add(p);
//Add customer details to EDMX class object
manyToMany.Customers.AddObject(c);
//Call SaveChanges method. Saving the records in DB will occur only when you call the SaveChanges Method.
manyToMany.SaveChanges();               
lblMessage.Text = "Customer Added successfully";

To Retrieve Customer Details including Related Products details and display in Gridview.

Note: No need to join the two tables on based on some condition. This is main thing I want to share in this article. This is little bit tricky & It is little bit different from regular join.

var _custProList =(from c in manyToMany.Customers.Where(prod => prod.Products.Any())
                      from p in manyToMany.Products.Where(cust => cust.Customers.Contains(c))
                       select new { c.CustomerId, c.CustName, p.ProductId, p.ProductName });
 
grdDetails.DataSource = _custProList;
grdDetails.DataBind();

Sample code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ManyToMany
{

    public partial class _Default : System.Web.UI.Page
    {
        ManyToManyEntities manyToMany = new ManyToManyEntities();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //Bind Dropdown details.
                this.BindDropdownDetails();
                this.BindGridDetails();
            }
        }
        protected void btnCust_Click(object sender, EventArgs e)
        {
            //set Default value as empty for error message.
            lblMessage.Text = string.Empty;

            try
            {
                //Get ProductId from Dropdown selection.
                int ProdId = Convert.ToInt32(drpProd.SelectedValue);               
                Customer c = new Customer();
                //Set Customer Name property
                c.CustName = txtCus.Text.Trim();
                //Get Prodct 
                Product p = manyToMany.Products.SingleOrDefault(x => x.ProductId == ProdId);
                //Add Product details to customer
                c.Products.Add(p);
                //Add customer details to EDMX class object
                manyToMany.Customers.AddObject(c);
                //Call SaveChanges method. Saving the records in DB will occur only when you call the SaveChanges Method.
                manyToMany.SaveChanges();
                this.BindDropdownDetails();
                lblMessage.Text = "Customer Added successfully";
                lblMessage.ForeColor = System.Drawing.Color.Green;
                lblMessage.Font.Bold = true;
                this.BindGridDetails();
                txtCus.Text = string.Empty;
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
        }
        protected void btnProd_Click(object sender, EventArgs e)
        {
            //set Default value as empty for error message.
            lblMessage.Text = string.Empty;
            try
            {
                Product p = new Product();
                //Set the ProductName property from textbox.
                p.ProductName = txtProd.Text.Trim();
                manyToMany.Products.AddObject(p);
                manyToMany.SaveChanges();
                this.BindDropdownDetails();
                lblMessage.Text = "Product Added successfully";
                lblMessage.ForeColor = System.Drawing.Color.Green;
                lblMessage.Font.Bold = true;
                txtProd.Text = string.Empty;
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
        }
        protected void grdDetails_RowEditing(object sender, GridViewEditEventArgs e)
        {
            grdDetails.EditIndex = e.NewEditIndex;
            this.BindGridDetails();
        }
        protected void grdDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int CustId = Convert.ToInt32(((Label)grdDetails.Rows[e.RowIndex].FindControl("lblCusId")).Text);
            int ProdId = Convert.ToInt32(((Label)grdDetails.Rows[e.RowIndex].FindControl("lblPId")).Text);
            
            Customer cus = (from c in manyToMany.Customers where c.CustomerId == CustId select c).FirstOrDefault();
            cus.CustName = e.NewValues["CustName"].ToString();
            Product prod = (from p in manyToMany.Products where p.ProductId == ProdId select p).FirstOrDefault();
            prod.ProductName = ((DropDownList)grdDetails.Rows[e.RowIndex].FindControl("drpProd1")).SelectedItem.Text;
            prod.ProductId = Convert.ToInt32( ((DropDownList)grdDetails.Rows[e.RowIndex].FindControl("drpProd1")).SelectedItem.Value);
            manyToMany.Customers.ApplyCurrentValues(cus);            
            manyToMany.Products.ApplyCurrentValues(prod);
            manyToMany.SaveChanges();

            grdDetails.EditIndex = -1;
            this.BindDropdownDetails();
            this.BindGridDetails();
        }
        protected void grdDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int CustId = Convert.ToInt32(((Label)grdDetails.Rows[e.RowIndex].FindControl("lblCusId")).Text);
            int ProdId = Convert.ToInt32(((Label)grdDetails.Rows[e.RowIndex].FindControl("lblPId")).Text);

            Customer cus = (from c in manyToMany.Customers where c.CustomerId == CustId select c).First();
            Product prod = (from p in manyToMany.Products where p.ProductId == ProdId select p).First();
            cus.Products.Attach(prod);
            manyToMany.Customers.DeleteObject(cus);
            manyToMany.SaveChanges();
            this.BindGridDetails();
        }
        protected void grdDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            grdDetails.EditIndex = -1;
            this.BindGridDetails();
        }

        #region Binding Dropdowns & GridView

        private void BindGridDetails()
        {
            var _custProList = (from c in manyToMany.Customers.Where(f => f.Products.Any())
                                from p in manyToMany.Products.Where(g => g.Customers.Contains(c))
                                select new { c.CustomerId, c.CustName, p.ProductId, p.ProductName });

            grdDetails.DataSource = _custProList;
            grdDetails.DataBind();
        }
        private void BindDropdownDetails()
        {
            //set Default value as empty for error message.
            lblMessage.Text = string.Empty;

            try
            {
                drpProd.Items.Clear();
                drpProd.Items.Add(new ListItem("--Select--", ""));
                drpProd.DataTextField = "ProductName";
                drpProd.DataValueField = "ProductId";
                drpProd.DataSource = manyToMany.Products;
                drpProd.DataBind();
            }
            catch (Exception ex)
            {
                lblMessage.Text = ex.Message;
            }
        }

        #endregion

        protected void grdDetails_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {

                LinkButton lnk1 = (LinkButton)e.Row.Cells[e.Row.Cells.Count - 1].Controls[2];
                if(lnk1.Text=="Delete")
                lnk1.Attributes.Add("onclick", "if(window.confirm('Are you sure,You want to delete?'))return true;else return false;");   

                DropDownList drpProd = (DropDownList)e.Row.FindControl("drpProd1");
                Label lblPId = (Label)e.Row.FindControl("lblPId");
                int Pid = Convert.ToInt32(lblPId.Text);

                drpProd.Items.Clear();
                drpProd.Items.Add(new ListItem("--Select--", ""));
                drpProd.DataTextField = "ProductName";
                drpProd.DataValueField = "ProductId";
                drpProd.DataSource = manyToMany.Products;
                drpProd.DataBind();
                drpProd.SelectedValue = Pid.ToString();


            }
        }
    }
}

Summary

This article is very useful how to perform CRUD operations by using ASP.NET Entity Framework. I need your opinion on this article. Don't forgot to rate it.