Master Detail CRUD Operations using ASP.net MVC 3 and EF

http://hasibulhaque.com/index.php/2011/master-detail-crud-operations-ef-asp-net-mvc-3/

Master Detail CRUD Operations using ASP.net MVC 3 and EF

July 17, 2011 .Net Framework, ASP.net, Entity Framework, MVC 3 12 comments

Download Project

Introduction

In this post I will discuss about how we can perform Master-Detail CRUD operation using Entity Framework (Code First) and ASP.net MVC 3. Here I have used JSON (json2.js) for data passing, Ajax for posting and DataTables (datatables.js) for manipulating detail Record.

Creating Master Detail CRUD Application

Create Sample Solution

>> Open VS 2010

>> Create ASP.net MVC 3 Project named “MasterDetail”

Here I have used

JSON for passing data view to controller

Data Tables for manipulating details record.

>> let add JSON and DataTables js file on our project using following way.

• Select Add Library Package Reference by right clicking Reference.

• Add Library Package Manager Window will appear; from the window search json2 & DataTables and install them.

• After installing them, you will find json2.js and datatables.js on script folder.

Now our solution is ready for further work.

Creating Model
Here we have considered we have two entities SalesMain and SalesSub(One to many relation). One salesMain has multiple sales sub records.

01	public class SalesMain
02	{

03
04	[Key]

05	public int SalesId { get; set; }
06	public string ReferenceNo { get; set; }

07	public DateTime SalesDate { get; set; }
08	public string SalesPerson { get; set; }

09
10	public virtual ICollection SalesSubs { get; set; }

11	}
12	public class SalesSub

13	{
14	[Key, Column(Order = 0)]

15	public int SalesId { get; set; }
16

17	[Key, Column(Order = 1)]
18	public string ItemName { get; set; }

19
20	public int Qty { get; set; }

21	public decimal UnitPrice { get; set; }
22

23	public virtual SalesMain SalesMain { get; set; }
24	}

>> Now build your project/ Press f5.

Creating Controller, Context and Views

>> Right Click on controller Folder and Select Add >> Controller

>> Name it SalesController

>> Select “SalesMain (MasterDetail.Models)” as a Model Class

>> Select and give its name “MasterDetail.Models.MasterDetailContext”

>> Then automatically it will create Views, Controller and Context Class.

Now we have to modify Our Sales Controller Class and Views.

Modify Sales Controller

Modify existing Create Method by following:

01	[HttpPost]
02	public JsonResult Create(SalesMain salesmain)

03	{
04	try

05	{
06	if (ModelState.IsValid)

07	{
08

09	// If sales main has SalesID then we can understand we have existing sales Information
10	// So we need to Perform Update Operation

11
12	// Perform Update

13	if (salesmain.SalesId > 0)
14	{

15
16	var CurrentsalesSUb = db.SalesSubs.Where(p => p.SalesId == salesmain.SalesId);

17
18	foreach (SalesSub ss in CurrentsalesSUb)

19	db.SalesSubs.Remove(ss);
20

21	foreach (SalesSub ss in salesmain.SalesSubs)
22	db.SalesSubs.Add(ss);

23
24	db.Entry(salesmain).State = EntityState.Modified;

25	}
26	//Perform Save

27	else
28	{

29	db.SalesMains.Add(salesmain);
30	}

31
32	db.SaveChanges();

33
34	// If Sucess== 1 then Save/Update Successfull else there it has Exception

35	return Json(new { Success = 1, SalesID = salesmain.SalesId, ex="" });
36	}

37	}
38	catch (Exception ex)

39	{
40	// If Sucess== 0 then Unable to perform Save/Update Operation and send Exception to View as JSON

41	return Json(new { Success = 0, ex = ex.Message.ToString() });
42	}

43
44	return Json(new { Success = 0, ex = new Exception("Unable to save").Message.ToString() });

45

}

Modify Edit Method in following way

1	public ActionResult Edit(int id)
2	{

3	ViewBag.Title = "Edit";
4	SalesMain salesmain = db.SalesMains.Find(id);

5
6	//Call Create View

7	return View("Create", salesmain);
8	}

Delete “Edit method” with Http post because we will use Create method for performing Save and Update operation.
Finally the sales controller looks like following.

001	using System;
002	using System.Collections.Generic;

003	using System.Data;
004	using System.Data.Entity;

005	using System.Linq;
006	using System.Web;

007	using System.Web.Mvc;
008	using MasterDetail.Models;

009	using System.Web.Helpers;
010	using System.Data.Objects;

011
012	namespace MasterDetail.Controllers

013	{
014	public class SalesController : Controller

015	{
016	private MasterDetailContext db = new MasterDetailContext();

017
018	//

019	// GET: /Sales/
020	public ViewResult Index()

021	{
022	return View(db.SalesMains.ToList());

023	}
024

025	//
026	// GET: /Sales/Details/5

027
028	public ViewResult Details(int id)

029	{
030	SalesMain salesmain = db.SalesMains.Find(id);

031	return View(salesmain);
032	}

033
034	//

035	// GET: /Sales/Create
036

037	public ActionResult Create()
038	{

039	ViewBag.Title = "Create";
040	return View();

041	}
042

043	// POST: /Sales/Create
044	///

045	/// This method is used for Creating and Updating Sales Information
046	/// (Sales Contains: 1.SalesMain and *SalesSub )

047	///
048	///

049
050	///

051	///
052	/// Returns Json data Containing Success Status, New Sales ID and Exeception

053	///
054	[HttpPost]

055	public JsonResult Create(SalesMain salesmain)
056	{

057	try
058	{

059	if (ModelState.IsValid)
060	{

061
062	// If sales main has SalesID then we can understand we have existing sales Information

063	// So we need to Perform Update Operation
064

065	// Perform Update
066	if (salesmain.SalesId > 0)

067	{
068

069	var CurrentsalesSUb = db.SalesSubs.Where(p => p.SalesId == salesmain.SalesId);
070

071	foreach (SalesSub ss in CurrentsalesSUb)
072	db.SalesSubs.Remove(ss);

073
074	foreach (SalesSub ss in salesmain.SalesSubs)

075	db.SalesSubs.Add(ss);
076

077	db.Entry(salesmain).State = EntityState.Modified;
078	}

079	//Perform Save
080	else

081	{
082	db.SalesMains.Add(salesmain);

083	}
084

085	db.SaveChanges();
086

087	// If Sucess== 1 then Save/Update Successfull else there it has Exception
088	return Json(new { Success = 1, SalesID = salesmain.SalesId, ex="" });

089	}
090	}

091	catch (Exception ex)
092	{

093	// If Sucess== 0 then Unable to perform Save/Update Operation and send Exception to View as JSON
094	return Json(new { Success = 0, ex = ex.Message.ToString() });

095	}
096

097	return Json(new { Success = 0, ex = new Exception("Unable to save").Message.ToString() });
098	}

099
100	//

101	// GET: /Sales/Edit/5
102	public ActionResult Edit(int id)

103	{
104	ViewBag.Title = "Edit";

105	SalesMain salesmain = db.SalesMains.Find(id);
106

107	//Call Create View
108	return View("Create", salesmain);

109	}
110

111	// GET: /Sales/Delete/5
112	public ActionResult Delete(int id)

113	{
114	SalesMain salesmain = db.SalesMains.Find(id);

115	return View(salesmain);
116	}

117
118	// POST: /Sales/Delete/5

119	[HttpPost, ActionName("Delete")]
120	public ActionResult DeleteConfirmed(int id)

121	{
122	SalesMain salesmain = db.SalesMains.Find(id);

123	db.SalesMains.Remove(salesmain);
124	db.SaveChanges();

125	return RedirectToAction("Index");
126	}

127
128	protected override void Dispose(bool disposing)

129	{
130	db.Dispose();

131	base.Dispose(disposing);
132	}

133	}
134	}

Modifying Create View

Add following *.js and *.css file.

01	@*This is for jquery*@
02

03	@*This is for jquery UI, for Calender control*@
04

05
06	@*This is for JSON*@

07
08	@*These are for DataTables*@

09
10

11
12	@*These are for styling Control*@

13	rel="stylesheet" type="text/css" />
14	rel="stylesheet" type="text/css" />

15	rel="stylesheet" type="text/css" />

Add html table for manipulating list of data.

After adding html table, we have converted it to DataTable so that we can easily add/delete item, read item more easily.

01	id="tbl">
02

03
04			ItemName	Quantity	Unit Price

05
06

07
08	@if (Model != null)

09	{
10	foreach (var item in Model.SalesSubs)

11	{
12

13
14	@Html.DisplayTextFor(i => item.ItemName)

15
16

17	@Html.DisplayTextFor(i => item.Qty)
18

19
20	@Html.DisplayTextFor(i => item.UnitPrice)

21
22

23	}
24	}

25
26

Adding new row to Table

Following code shows how to read from text boxes and then add it to datatable.

01	function Add() {
02	// Adding item to table

03	$('.tbl').dataTable().fnAddData([$('#ItemName').val(), $('#Qty').val(), $('#UnitPrice').val()]);
04

05	// Making Editable text empty
06	$('#ItemName').val("")

07	$('#Qty').val("")
08	$('#UnitPrice').val("")

09
10	}

Delete selected row from Table

Following code shows how to remove selected item from datatable.

01	// This function is used fro
02	// delete selected row from Detail Table

03	// set deleted item to Edit text Boxes
04	function DeleteRow() {

05
06	// Here I have used DataTables.TableTools plugin for getting selected row items

07	var oTT = TableTools.fnGetInstance('tbl'); // Get Table instance
08	var sRow = oTT.fnGetSelected(); // Get Selected Item From Table

09
10	// Set deleted row item to editable text boxes

11	$('#ItemName').val($.trim(sRow[0].cells[0].innerHTML.toString()));
12	$('#Qty').val(jQuery.trim(sRow[0].cells[1].innerHTML.toString()));

13	$('#UnitPrice').val($.trim(sRow[0].cells[2].innerHTML.toString()));
14

15	$('.tbl').dataTable().fnDeleteRow(sRow[0]);
16

17

}

Save/Posting Data to sales Controller

Here we have two steps
1. Read view data and create JSON object
2. Ajax post

01	function Sales_save() {
02	// Step 1: Read View Data and Create JSON Object

03
04	// Creating SalesSub Json Object

05	var salessub = {"SalesId":"", "ItemName":"","Qty":"","UnitPrice":""};
06

07	// Creating SalesMain Json Object
08	var salesmain = { "SalesId":"","ReferenceNo": "", "SalesDate": "", "SalesPerson": "", "SalesSubs":[] };

09
10	// Set Sales Main Value

11	salesmain.SalesId = $("#SalesId").val();
12	salesmain.ReferenceNo = $("#ReferenceNo").val();

13	salesmain.SalesDate = $("#SalesDate").val();
14	salesmain.SalesPerson = $("#SalesPerson").val();

15
16	// Getting Table Data from where we will fetch Sales Sub Record

17	var oTable = $('.tbl').dataTable().fnGetData();
18

19	for (var i = 0; i < oTable.length; i++)
20	{

21
22	// IF This view is for edit then it will read SalesId from Hidden field

23	if ($('h2').text() == "Edit")
24	{

25	salessub.SalesId = $('#SalesId').val();
26	}

27	else
28	{

29	salessub.SalesId = 0;
30	}

31
32	// Set SalesSub individual Value

33	salessub.ItemName = oTable[i][0];
34	salessub.Qty = oTable[i][1];

35	salessub.UnitPrice = oTable[i][2];
36	// adding to SalesMain.SalesSub List Item

37	salesmain.SalesSubs.push(salessub);
38	salessub = { "ItemName": "", "Qty": "", "UnitPrice": "" };

39
40	}

41	// Step 1: Ends Here
42

43	// Set 2: Ajax Post
44	// Here i have used ajax post for saving/updating information

45	$.ajax({
46	url: '/Sales/Create',

47	data: JSON.stringify(salesmain),
48	type: 'POST',

49	contentType: 'application/json;',
50	dataType: 'json',

51	success: function (result) {
52

53	if (result.Success == "1") {
54	window.location.href = "/Sales/index";

55	}
56	else {

57	alert(result.ex);
58	}

59	}
60	});

61
62	}

Summery

Here I have discussed about Sales Controller and only create view. Index, details and Delete views are skipped because they are as usual. Edit view has deleted because here we have used