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

Master Detail CRUD Operations using ASP.net MVC 3 and EF. Master detail in asp.net mvc

Introduction

In this post I will discuss about how we can perform Master Detail CRUD Operations using ASP.net MVC 3 and Entity Framework (Code First).  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.

public class SalesMain
    {

        [Key]
        public int SalesId { get; set; }
        public string ReferenceNo { get; set; }
        public DateTime SalesDate { get; set; }
        public string SalesPerson { get; set; }

        public virtual ICollection SalesSubs { get; set; }
    }
public class SalesSub
    {
        [Key, Column(Order = 0)]
        public int SalesId { get; set; }

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

        public int Qty { get; set; }
        public decimal UnitPrice { get; set; }

        public virtual SalesMain SalesMain { get; set; }
    }

>> 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 <new data Context> 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:

[HttpPost]
        public JsonResult Create(SalesMain salesmain)
        {
            try
            {
                if (ModelState.IsValid)
                {

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

                    // Perform Update
                    if (salesmain.SalesId > 0)
                    {

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

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

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

                        db.Entry(salesmain).State = EntityState.Modified;
                    }
                    //Perform Save
                    else
                    {
                        db.SalesMains.Add(salesmain);
                    }

                    db.SaveChanges();

                    // If Sucess== 1 then Save/Update Successfull else there it has Exception
                    return Json(new { Success = 1, SalesID = salesmain.SalesId, ex="" });
                }
            }
            catch (Exception ex)
            {
                // If Sucess== 0 then Unable to perform Save/Update Operation and send Exception to View as JSON
                return Json(new { Success = 0, ex = ex.Message.ToString() });
            }

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

Modify Edit Method in following way

public ActionResult Edit(int id)
        {
            ViewBag.Title = "Edit";
            SalesMain salesmain = db.SalesMains.Find(id);

            //Call Create View
            return View("Create", salesmain);
        }

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.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MasterDetail.Models;
using System.Web.Helpers;
using System.Data.Objects;

namespace MasterDetail.Controllers
{
    public class SalesController : Controller
    {
        private MasterDetailContext db = new MasterDetailContext();

        //
        // GET: /Sales/
        public ViewResult Index()
        {
            return View(db.SalesMains.ToList());
        }

        //
        // GET: /Sales/Details/5

        public ViewResult Details(int id)
        {
            SalesMain salesmain = db.SalesMains.Find(id);
            return View(salesmain);
        }

        //
        // GET: /Sales/Create

        public ActionResult Create()
        {
            ViewBag.Title = "Create";
            return View();
        } 

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

        ///
        ///
        /// Returns Json data Containing Success Status, New Sales ID and Exeception
        ///
        [HttpPost]
        public JsonResult Create(SalesMain salesmain)
        {
            try
            {
                if (ModelState.IsValid)
                {

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

                    // Perform Update
                    if (salesmain.SalesId > 0)
                    {

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

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

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

                        db.Entry(salesmain).State = EntityState.Modified;
                    }
                    //Perform Save
                    else
                    {
                        db.SalesMains.Add(salesmain);
                    }

                    db.SaveChanges();

                    // If Sucess== 1 then Save/Update Successfull else there it has Exception
                    return Json(new { Success = 1, SalesID = salesmain.SalesId, ex="" });
                }
            }
            catch (Exception ex)
            {
                // If Sucess== 0 then Unable to perform Save/Update Operation and send Exception to View as JSON
                return Json(new { Success = 0, ex = ex.Message.ToString() });
            }

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

        //
        // GET: /Sales/Edit/5
        public ActionResult Edit(int id)
        {
            ViewBag.Title = "Edit";
            SalesMain salesmain = db.SalesMains.Find(id);

            //Call Create View
            return View("Create", salesmain);
        }

        // GET: /Sales/Delete/5
        public ActionResult Delete(int id)
        {
            SalesMain salesmain = db.SalesMains.Find(id);
            return View(salesmain);
        }

        // POST: /Sales/Delete/5
        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            SalesMain salesmain = db.SalesMains.Find(id);
            db.SalesMains.Remove(salesmain);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }
    }
}

Modifying Create View

Add following *.js and *.css file.

@*This is for jquery*@
<script src="../../Scripts/jquery-1.5.1.js" type="text/javascript"></script>
@*This is for jquery UI, for Calender control*@
<script src="../../Scripts/jquery-ui-1.8.11.js" type="text/javascript"></script>

@*This is for JSON*@
<script src="../../Scripts/json2.js" type="text/javascript"></script>
@*These are for DataTables*@
<script src="../../Scripts/DataTables-1.8.1/media/js/jquery.dataTables.js" type="text/javascript"></script>
<script src="../../Scripts/DataTables-1.8.1/extras/TableTools/media/js/TableTools.js" type="text/javascript"></script>
<script src="../../Scripts/DataTables-1.8.1/extras/TableTools/media/js/ZeroClipboard.js" type="text/javascript"></script>
@*These are for styling Control*@
<link href="../../Content/DataTables-1.8.1/extras/TableTools/media/css/TableTools.css" rel="stylesheet" type="text/css" />
<link href="../../Content/DataTables-1.8.1/extras/TableTools/media/css/TableTools_JUI.css" rel="stylesheet" type="text/css" />
<link href="../../Content/themes/base/jquery.ui.all.css" 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.

<table id="tbl">
<thead>
<tr>
<th>ItemName</th> <th>Quantity</th><th>Unit Price</th>
</tr>
</thead>
<tbody>
@if (Model != null)
{
foreach (var item in Model.SalesSubs)
{
<tr>
<td>
@Html.DisplayTextFor(i => item.ItemName)
</td>
<td>
@Html.DisplayTextFor(i => item.Qty)
</td>
<td>
@Html.DisplayTextFor(i => item.UnitPrice)
</td>
</tr>
}
}
</tbody>
</table>

Adding new row to Table

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

function Add() {
        // Adding item to table
        $('.tbl').dataTable().fnAddData([$('#ItemName').val(), $('#Qty').val(), $('#UnitPrice').val()]);

        // Making Editable text empty
        $('#ItemName').val("")
        $('#Qty').val("")
        $('#UnitPrice').val("")

     }

Delete selected row from Table

Following code shows how to remove selected item from datatable.

// This function is used fro
    // delete selected row from Detail Table
    // set deleted item to Edit text Boxes
    function DeleteRow() {

        // Here I have used DataTables.TableTools plugin for getting selected row items
        var oTT = TableTools.fnGetInstance('tbl'); // Get Table instance
        var sRow = oTT.fnGetSelected(); // Get Selected Item From Table

        // Set deleted row item to editable text boxes
        $('#ItemName').val($.trim(sRow[0].cells[0].innerHTML.toString()));
        $('#Qty').val(jQuery.trim(sRow[0].cells[1].innerHTML.toString()));
        $('#UnitPrice').val($.trim(sRow[0].cells[2].innerHTML.toString()));

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

    }

Save/Posting Data to sales Controller

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

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

        // Creating SalesSub Json Object
        var salessub = {"SalesId":"", "ItemName":"","Qty":"","UnitPrice":""};

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

        // Set Sales Main Value
        salesmain.SalesId = $("#SalesId").val();
        salesmain.ReferenceNo = $("#ReferenceNo").val();
        salesmain.SalesDate = $("#SalesDate").val();
        salesmain.SalesPerson = $("#SalesPerson").val();

        // Getting Table Data from where we will fetch Sales Sub Record
        var oTable = $('.tbl').dataTable().fnGetData();

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

            // IF This view is for edit then it will read SalesId from Hidden field
            if ($('h2').text() == "Edit")
            {
                salessub.SalesId = $('#SalesId').val();
            }
            else
            {
                salessub.SalesId = 0;
            }

            // Set SalesSub individual Value
            salessub.ItemName = oTable[i][0];
            salessub.Qty = oTable[i][1];
            salessub.UnitPrice = oTable[i][2];
            // adding to SalesMain.SalesSub List Item
            salesmain.SalesSubs.push(salessub);
            salessub = { "ItemName": "", "Qty": "", "UnitPrice": "" };

        }
        // Step 1: Ends Here

        // Set 2: Ajax Post
        // Here i have used ajax post for saving/updating information
        $.ajax({
            url: '/Sales/Create',
            data: JSON.stringify(salesmain),
            type: 'POST',
            contentType: 'application/json;',
            dataType: 'json',
            success: function (result) {

                if (result.Success == "1") {
                    window.location.href = "/Sales/index";
                }
                else {
                    alert(result.ex);
                }
            }
        });

    }

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 Create view for performing both create and Edit operation.

If you want to know more about datatable and JSON then you can check following links.
http://www.datatables.net/
http://json.org/
Download Project

  • Shripati

    Nice blog . Please add more more projects on Razor functionality .

    • http://e-rains.com Hasibul

      Thanks Shripati

    • Nirzhor Raz

      This is very helpful.Keep it.

  • http://www.digix-pro.com Anass

    Very nice Hasibul, this is one of the most helpful blogs on MVC 3, thank you for publishing it

  • http://www.digix-pro.com Anass

    When clicking in the ItemName textBox, Is there a way to add it from a list, in stead of typing the whole Name?

  • Gustavo

    Hi, very nice, but when I tried to save a sale it says: Unable to save. (just ran the downloadble project)
    Am I missing something ?
    tks anyway, very helpfull!

  • http://HIagain Gustavo

    I figure out the issue.
    Due to date format, here at my pc I use: dd/mm/yyyy, but json datepicker use month at front. Just forced day at front and it saved!

    • http://e-rains.com Hasibul

      Thanks for visiting my post..

  • http://HIagain Gustavo

    Hi again,
    I am receiving “object reference not set…” when I try to save the sales/ sales sub. (i am using my sales / sales sub classes the most close to your’s).
    I already debug with vs2010 the jquery but with no luck. Any tips ?
    Tks

  • Sridhar

    Thanks; was looking for an example that uses EF4 and datatables and yours came in handy.

  • Eaktadiur

    very good work

  • http://www.trgtechnologies.com/ Rakesh

    great!!!

  • adi

    hi …thank you so much for this great article…
    then can you please give us more article or link to the article on MVC 3 and entity framework

    thanks again

  • Jean

    Hi im new to MVC im having this error: Uncaught TypeError: Cannot read property ‘aoData’ of null

  • sue

    Great article, only suggestion would be on the update of existing items, deleting and re-adding normally don’t work with ref integrity, i used update method.

  • Brian

    I got your example working and then modified it by adding an edit method to the sales controller and edit view. I then modified the SalesSub table to show the Edit link. It works great! Now when I click add I get a warning message that says Datatables warning (table id = ‘tbl’): Requested unknown parameter ’3′ from the data source for row …

    Also, I noticed that the price is rounded on input – so if I put in 79. 95 It saves as 80. Can these issues be fixed?

  • Adrian Garcia

    Hi.

    This method is great from my debug (using VSS2010 on W7 64bits) the data is saved good twice (detail and master info), but when this is publish to WS2008 Enterprise on IIS; save only partial data (no detail info) only master info.
    :(
    Any have Idea why this problem. I have all dll references.

    Thanks in advance.

    • hasibul2363

      Have u deployed with dll files in bin directory? Is Entityframework.dll available on your published folder on IIS?

  • http://www.facebook.com/oshabani Osama Alshabani

    Hello , it was nice , i have a question ,what if i want to add DropDownlist into the SubSales which i want to bring data from database , how to do that. thank you

    • hasibul2363

      Thanks for reading my post. Yes you can add dropdown menu to SalesSub for your Item. In that case you have to pass dropdown data means selectList from controller through ViewBag and in view you have to bind that data to dropdown list. like following

      @Html.DropDownList(“cmbItem”,new SelectList((List)ViewBag.Item, “ItemId”, “ItemName”))

      • http://www.facebook.com/oshabani Osama Alshabani

        thank you , i did and its work great , i have another question , i am trying to add so i can upload multiple files with same post , in the controle action Create i am trying access the file upload object using Request.Files and i got nothing the value of Request.Files.Count = 0 ,!! ,also i am trying to pass information from input text like to the action controller and value is null,
        thank you

        • hasibul2363

          I think you want to upload file to server right?
          If you are interested then you can use telerik MVC extension which is free
          Please have a look for free uploading control for ASP.net MVC

          http://demos.telerik.com/aspnet-mvc/upload?theme=metro

          • http://www.facebook.com/oshabani Osama Alshabani

            thanks , but i am using your solution of master details i want to add the files upload to this Master-Details ,is there is anyway to do that ?

          • hasibul2363

            Do you want to upload file to server?
            If yes then; when you want to upload that ?
            > By pressing Save button(which will upload multiple files on same time) Or
            > You want to upload file individually?
            ======================================
            Now consider my solution; i didnt implmented any upload option in my sample.

          • http://www.facebook.com/oshabani Osama Alshabani

            Dear, thank you for your reply , i have 2 master-detail in the same form , first is Job-JobLineItem Second Job-JobDocument , so i want to use your sample for the first and second master-details with ability to upload files to the server when i click the save button ,
            thank you

  • Sandip Shinde

    very Usefull Thank u for post..

  • Shelly

    I tried this and it is working! I’m new to web development and to mvc/entity framework. I tried to change the webconfig file to connect to my local database (I created the db manually here)(sql server 2008 R2)

    , but it keeps dynamically creating and connecting to the databases into mycomputernamesqlexpress instance. How do I change the connecting string? Can you guys guide me please. The example is really working for me I just can’t connect to the location I want. Thanks!

    • hasibul2363

      Hi you can check in following way
      ————————————————————————————————————————————————
      1. Go to Project Reference> Nuget > Installed Packages > Uninstall SQL Compact
      2. Delete App Data and App Start folder
      3. From Reference Delete EntityFramework
      4. Again Go to NuGet; from there Add latest Entity Framework(5.0.0)
      5. Delete following line from Global.asax file
      System.Data.Entity.Database.DbDatabase.SetInitializer(new System.Data.Entity.Database.DropCreateDatabaseIfModelChanges());
      6. Delete following from Global.asax
      public class MasterDetailDBInitializer : System.Data.Entity.Database.DropCreateDatabaseIfModelChanges
      {
      protected override void Seed(MasterDetailContext context)
      {
      base.Seed(context);
      }
      }

      Then replace existing connectionString block with following Following connection string on you web.config file

      In this way i have changed my solution to work with External data sources.
      ————————————————————————————————————————————————

      OR
      you can download my modified solution from following
      https://github.com/hasibul2363/masterDetailWithAsp.netMVC

      Let me know is it working for you or not.

  • vinoth

    Hi,

    when i run the project it show the error. see the image

    Please help me…

    Thanks in advance.

    VInoth R. || vinom1389@yahoo.com