Introduction

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

Parent-child relationship can be easily implemented using a JQuery DataTables plugin. Although parent-child relationship is not added as standard JQuery DataTables functionality it can be implemented using the API provided by the DataTables plugin.

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

Background  

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

ParentChildDataTables.png

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

Using the code  

For illustrative purposes, we’ll use simple ASP.NET MVC web application to list 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 HTML code that is sent to the browser window

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

   1. JQuery library v1.4.4. containing standard classes used by DataTables plugin
   2. JQuery DataTables plugin v1.7.5. including optional DataTables CSS style-sheets used for applying 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. 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. 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; }
} 

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

View

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 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> 

Layout page has two sections that can be populated on the page: 

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

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> 

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

Head section holds JavaScript code that initializes and connects these two tables. 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> 

First two statements are optional. The first statement initializes companies datatable with JQuery DataTables plugin in order to add pagination, filtering, and sorting functionality (this is not required for the parent-child relationship between tables because parent table can be a plain table). The second statement adds 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 id of the currently selected company. Fourth statement initializes child employees table. Most of the settings are optional and do not affect parent-child configuration because only relevant statements in the initialization are:

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

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

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

Controller 

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

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

First controller method is fairly simple. This method just returns 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 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);
        }
}
		   

Name of the method must match the sAjaxSource parameter set in the child data table (employees table in the listing above). This method accepts one object that encapsulates parameters sent from the DataTables plugin (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, one additional parameter called CompanyID is added in the method signature. Name of this parameter must match the name of the parameter that is added in the fnServerData function in the child table. Other code in the body of the method just filters employee data and returns it in JSON format as it is expected by the JQury DataTables plugin. 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 relationship between two tables in ASP.NET MVC using the JQuery DataTables plugin. Minimal code is required on the client-side and on the server-side we need standard processing functions. This plugin allows you to create effective, ajaxified, Web 2.0 interface with a minimal effort and straightforward implementation guidelines. You can download example project implemented in ASP.NET MVC here - Download JQueryParentChildDataTables.zip - 195.32 KB .

You might be also be interested in the other articles in this series showing:
  1. How to implement server-side processing in ASP.NET MVC with JQuery DataTables plugin
  2. How to implement a fully editable tables in ASP.NET MVC with JQuery DataTables and  several JQuery plugins that enable complete data management functionality. 
 I hope that these articles would help you while implementing ASP.NET MVC applications. 
推荐.NET配套的通用数据层ORM框架:CYQ.Data 通用数据层框架
新浪微博粉丝精灵,刷粉丝、刷评论、刷转发、企业商家微博营销必备工具"