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();
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.
int ProdId = Convert.ToInt32(drpProd.SelectedValue);
Customer c = new Customer();
/Set Customer Name property
c.CustName = txtCus.Text.Trim();
Product p = manyToMany.Products.SingleOrDefault(x => x.ProductId == ProdId);
c.Products.Add(p);
manyToMany.Customers.AddObject(c);
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)
{
this.BindDropdownDetails();
this.BindGridDetails();
}
}
protected void btnCust_Click(object sender, EventArgs e)
{
lblMessage.Text = string.Empty;
try
{
int ProdId = Convert.ToInt32(drpProd.SelectedValue);
Customer c = new Customer();
c.CustName = txtCus.Text.Trim();
Product p = manyToMany.Products.SingleOrDefault(x => x.ProductId == ProdId);
c.Products.Add(p);
manyToMany.Customers.AddObject(c);
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)
{
lblMessage.Text = string.Empty;
try
{
Product p = new Product();
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()
{
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.