Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » Samples » Revisions
 

Creating parent-child relationships between tables in ASP.NET MVC (jQuery DataTables and ASP.NET MVC integration - Part III)

, 4 May 2011
Rate this:
Please Sign up or sign in to vote.
This article shows how a parent-child relationship between two tables can be implemented in ASP.NET MVC using the jQuery DataTables plug-in.
This is an old version of the currently published article.

Introduction

The purpose of this article is to show how two data tables on a web page can be connected in a parent-child manner. Creating parent-child relations is a common requirement in many applications; e.g., when you click on a sales order, you might want to display the list of items; when you click on a company, you need to show the list of employees, etc. This example shows how a table containing a list of companies is connected to a table which lists the employees of the selected company.

Parent-child relationships can be easily implemented using the jQuery DataTables plug-in. Although a parent-child relationship is not added as standard jQuery DataTables functionality, it can be implemented using the API provided by the DataTables plug-in.

This is a third article in the series explaining how the jQuery DataTables plugin can be integrated into the ASP.NET MVC web application. If you are not familiar with the integration of the DataTables plug-in with ASP.NET MVC server-side code, you might want to read the first article in this series before you proceed.

Background

The goal of this article is to show how two tables containing information about companies and their employees can be connected in a parent-child relationship (illustrated in the figure below).

ParentChildDataTables.png

When the user clicks on a company in the parent table (companies), the employees for the selected company will be listed in the child table. The following sections explain how this parent-child relationship can be implemented in ASP.NET MVC using the jQuery DataTables plug-in.

Using the code

For illustrative purposes, we'll use a simple ASP.NET MVC web application to list the companies and their employees. The first thing you need to do is to create a standard ASP.NET Model-View-Controller structure. There are three steps required for this setup:

  1. Creating the model classes that represent a data structure to be shown.
  2. Creating the controller class that will react on the user events.
  3. Creating the view that will render data and create the HTML code that is sent to the browser window.

In the beginning, we'll just display the company information in a table. Then, we will initialize the second table containing the list of employees, loading only the employees for the selected company. The following JavaScript components need to be downloaded:

  1. jQuery library v1.4.4., containing the standard classes used by the DataTables plug-in.
  2. jQuery DataTables plug-in v1.7.5., including the optional DataTables CSS style-sheets used for applying the default styles on the page.

These files should be stored in the local file system and included in the HTML page that is rendered on the client. An example of usage of these files is explained below.

Model

Two classes that contain information about companies and employees need to be added in the example. The classes are shown in the following listing:

public class Company
{
        public int ID { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string Town { get; set; }
}  
public class Employee
{
        public int EmployeeID { get; set; }
        public string Name { get; set; }
        public string Position { get; set; }
        public int CompanyID { get; set; }
}

The employees are connected to the companies via the CompanyID property. These classes will be used to show information on the page.

View

The view is used to render data on the server-side and to send HTML code to the browser. There's one layout page that is used to include all the necessary CSS and JavaScript files that are used on the page. This layout page is shown below:

<!DOCTYPE html>
<html>
    <head>
        <title>Implementation of Master-Details tables 
                     using a JQuery DataTables plugin</title>
        <link href="@Url.Content("~/Content/dataTables/demo_page.css")" 
           rel="stylesheet" type="text/css" />
        <link href="@Url.Content("~/Content/dataTables/demo_table.css")" 
           rel="stylesheet" type="text/css" />
        <link href="@Url.Content("~/Content/dataTables/demo_table_jui.css")" 
           rel="stylesheet" type="text/css" />
        <link href="@Url.Content("~/Content/themes/base/jquery-ui.css")" 
           rel="stylesheet" type="text/css" media="all" />
       
 <link 
     href="@Url.Content("~/Content/themes/smoothness/
                jquery-ui-1.7.2.custom.css")"rel="stylesheet"
     type="text/css" media="all" />
        <script src="@Url.Content("~/Scripts/jquery-1.4.4.min.js")" 
          type="text/javascript"></script>
        <script src="@Url.Content("~/Scripts/jquery.dataTables.min.js")" 
          type="text/javascript"></script>
        @RenderSection("head", required: false)
    </head>
    <body id="dt_example">
        <div id="container">
            <a href="http://www.codeproject.com/Home/Index">Master/Details Table</a>
            @RenderBody()
        </div>
    </body>
</html>

The layout page has two sections that can be populated on the page:

  1. head section where the JavaScript calls from the page will be injected,
  2. body section that enables the page that uses this layout page to inject code to the page that will be shown on the page.

The body of the page is shown in the following listing:

<div id="demo">
    <table id="companies" class="display">
        <thead>
            <tr>
                <th>Company name</th>
                <th>Address</th>
                <th>Town</th>
            </tr>
        </thead>
        <tbody>
            <tr id="0" class="masterlink">
                <td>Emkay Entertainments</td>
                <td>Nobel House, Regent Centre</td>
                <td>Lothian</td>
            </tr>
            <tr id="1" class="masterlink">
                <td>The Empire</td>
                <td>Milton Keynes Leisure Plaza</td>
                <td>Buckinghamshire</td>
            </tr>
            <tr id="2" class="masterlink">
                <td>Asadul Ltd</td>
                <td>Hophouse</td>
                <td>Essex</td>
            </tr>
            <tr id="3" class="masterlink">
                <td>Ashley Mark Publishing Company</td>
                <td>1-2 Vance Court</td>
                <td>Tyne &amp; Wear</td>
            </tr>
            <tr id="4" class="masterlink">
                <td>MuchMoreMusic Studios</td>
                <td>Unit 29</td>
                <td>London</td>
            </tr>
            <tr id="5" class="masterlink">
                <td>Audio Records Studios</td>
                <td>Oxford Street</td>
                <td>London</td>
            </tr>
        </tbody>
    </table>

    <table id="employees" class="display">
        <thead>
            <tr>
                <th>ID</th>
                <th>Employee</th>
                <th>Position</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
</div>

The first table with "companies" ID contains a list of five companies, and the employees table is empty. The employees table will be populated with AJAX JavaScript calls. Each row in the companies table contains the ID of the company - this information will be used to load the employees for the selected company.

The head section holds the JavaScript code that initializes and connects these two tables. The JavaScript initialization code is shown in the following listing:

<script language="javascript" type="text/javascript">
    $(document).ready(function () {

        /* Initialize master table - optionally */
        var oCompaniesTable = $('#companies').dataTable({ "bJQueryUI": true });
        /* Highlight selected row - optionally */
        $("#companies tbody").click(function (event) {
            $(oCompaniesTable.fnSettings().aoData).each(function () {
                $(this.nTr).removeClass('row_selected');
            });
            $(event.target.parentNode).addClass('row_selected');
        });

        var MasterRecordID = null;

        var oEmployeesTable = $('#employees').dataTable({
            "sScrollY": "100px",
            "bJQueryUI": true,
            "bServerSide": true,
            "sAjaxSource": "MasterDetailsAjaxHandler",
            "bProcessing": true,
            "fnServerData": function (sSource, aoData, fnCallback) {
                aoData.push({ "name": "CompanyID", "value": MasterRecordID });
                $.getJSON(sSource, aoData, function (json) {
                    fnCallback(json)
                });
            }
        });

        $(".masterlink").click(function (e) {
            MasterRecordID = $(this).attr("id");
            oEmployeesTable.fnDraw();
        });
    });
</script>

The first two statements are optional. The first statement initializes the companies datatable with the jQuery DataTables plug-in in order to add pagination, filtering, and sorting functionality (this is not required for the parent-child relationship between the tables because the parent table can be a plain table). The second statement adds the row_selected class on the selected row in the parent table. This is also not required, but it's useful to highlight a company whose employees are shown in the child table.

A local variable MasterRecordID is used to hold the ID of the currently selected company. The fourth statement initializes the child employees table. Most of the settings are optional and do not affect the parent-child configuration because the only relevant statements in the initialization are:

  1. Server-side processing configuration implemented using the bServerSide and sAjaxSource parameters,
  2. fnServerData method used to inject the ID of the selected company into the AJAX call sent to the server-side. This method is used to add the additional parameter called CompanyID with the value of the MasterRecordID variable to the AJAX call sent to the server-side.

The last statement attaches the event handler which populates the ID of the selected row and forces the redraw of the child table on each click on a row in the parent table. Redrawing of the table sends an AJAX request to the server-side and updates the table with the employee records that belong to the selected company.

The last required part of the example is a controller that will handle the requests.

Controller

The controller handles the request sent from the browser and provides view/data that will be shown in the browser. Here, the controller has two methods that handle a request:

  1. Load method that returns the view page when the page is loaded,
  2. Employees AJAX handler that returns the employees for the provider company ID.

The first controller method is fairly simple. This method just returns the view that will be shown in the browser, as shown in the following listing:

public class HomeController : Controller
{
    public ActionResult Index()
        {
            return View();
        }
}

The second controller method is crucial - it returns the employees for the child employees table. This method is shown in the following listing:

public class HomeController : Controller
{
    public ActionResult MasterDetailsAjaxHandler(
             JQueryDataTableParamModel param, int? CompanyID)
    {

        var employees = DataRepository.GetEmployees();

        //"Business logic" method that filters employees by the employer id
        var companyEmployees = (from e in employees
                                where (CompanyID == null || e.CompanyID == CompanyID)
                                select e).ToList();

        //UI processing logic that filter company employees by name and paginates them
        var filteredEmployees = (from e in companyEmployees
                                 where (param.sSearch == null || 
                                 e.Name.ToLower().Contains(param.sSearch.ToLower()))
                                 select e).ToList();
        var result = from emp in filteredEmployees.Skip(
                     param.iDisplayStart).Take(param.iDisplayLength)
                     select new[] { Convert.ToString(emp.EmployeeID), 
                     emp.Name, emp.Position };

        return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = companyEmployees.Count,
            iTotalDisplayRecords = filteredEmployees.Count,
            aaData = result
        },
        JsonRequestBehavior.AllowGet);
    }
}

The name of the method must match the sAjaxSource parameter set in the child data table (employees table in the listing above). This method accepts an object that encapsulates the parameters sent from the DataTables plug-in (current page, sort direction, number of items that should be displayed per page, etc.) More details about the server side processing parameters can be found in the first article in this series. Besides this parameter, an additional parameter called CompanyID is added in the method signature. The name of this parameter must match the name of the parameter that is added in the fnServerData function in the child table. The other code in the body of the method just filters the employee data and returns it in JSON format as it is expected by the jQuery DataTables plug-in. More details about the server-side configuration can be found in the first article in this series.

Summary

This article shows how you can easily implement parent-child relationships between two tables in ASP.NET MVC using the jQuery DataTables plug-in. Minimal code is required on the client-side, and on the server-side we need standard processing functions. This plug-in allows you to create an effective, AJAXified, Web 2.0 interface with minimal effort and straightforward implementation guidelines. You can download the example project implemented in ASP.NET MVC here.

You might also be interested in the other articles in this series showing:

  1. How to implement server-side processing in ASP.NET MVC with the jQuery DataTables plug-in
  2. How to implement a fully editable table in ASP.NET MVC with jQuery DataTables and several jQuery plug-ins that enable complete data management functionality.

I hope that these articles would help you while implementing ASP.NET MVC applications.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Jovan Popovic
Architect Gowi
Serbia Serbia
Started as a young scientist - winning the highest national awards in mathematics, physics, electrotechnics, and electronics.
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia, as a first in the class, as a Master of Software Sciences.
Currently working in Gowi as a Software engineer, architect, and project manager since 2004 - mostly using Microsoft technologies (ASP.NET, C#). Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions


Discussions posted for the Published version of this article. Posting a message here will take you to the publicly available article in order to continue your conversation in public.
 
GeneralVery good tutorial! PinmemberMember 1039974412-May-14 3:26 
QuestionCase 3 ! PinmemberMember 1017890713-Mar-14 12:35 
Questionplz help PinmemberMember 454743020-Oct-13 7:09 
QuestionCase 3 doesnt't work PinmemberMember 97134734-May-13 0:01 
QuestionKeep pagination/sorting/filtering settings Pinmember_koen_28-Apr-13 20:56 
Questionpassing a array using ajax from view page to cotroller Pinmemberabusalehrajib21-Mar-13 2:51 
GeneralMy vote of 5 PinmemberAbinash Bishoyi27-Feb-13 0:48 
Questionhow to create a new row in mvc Pinmemberraja.mstech31-Jan-13 19:37 
QuestionHTML Helper to create Table with datatables plugin PinmemberRajagcha8-Aug-12 8:43 
Bugajax.cshtml not working Pinmembersamthec11-May-12 4:47 
GeneralMy vote of 5 PinmemberAnurag Gandhi21-Apr-12 9:26 
GeneralMy vote of 5 Pinmemberpothiq26-Feb-12 19:21 
GeneralMy vote of 5 PinmemberMahmud Hasan26-Feb-12 16:43 
QuestionGreat Article Pinmemberzyck26-Feb-12 3:39 
GeneralMy vote of 5 PinmemberGreg Sipes22-Feb-12 10:54 
GeneralMy vote of 5 PinmemberPrasanta_Prince17-Apr-11 7:33 
GeneralOdlicno 5 PinmemberSlobodan12-Apr-11 11:02 
GeneralRe: Odlicno 5 PinmemberJovan Popovic13-Apr-11 21:25 
GeneralMy vote of 5 PinmemberArlen Navasartian8-Apr-11 4:34 
GeneralGood PinmemberSunasara Imdadhusen6-Apr-11 23:37 
GeneralError: Stuck on 'Processing' Pinmembervkuttyp6-Apr-11 4:34 
GeneralRe: Error: Stuck on 'Processing' PinmemberJovan Popovic6-Apr-11 5:02 
GeneralRe: Error: Stuck on 'Processing' Pinmembervkuttyp6-Apr-11 11:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 4 May 2011
Article Copyright 2011 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid