Creating parent-child relationships between tables in ASP.NET MVC (JQuery DataTables and ASP.NET MVC integration - Part III)
- Download source - 195.32 KB
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).
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:
- head section where JavaScript calls from the page will be injected,
- 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 & 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:
- Server-side processing configuration implemented using a
bServerSide
andsAjaxSource
parameters, 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 theMasterRecordID
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:
- Load method that returns view page when page is loaded,
- 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:- How to implement server-side processing in ASP.NET MVC with JQuery DataTables plugin
- How to implement a fully editable tables in ASP.NET MVC with JQuery DataTables and several JQuery plugins that enable complete data management functionality.
发表评论
tAGkzi Mighty helpful mindset, appreciate your sharing with us.. So happy to get discovered this submit.. So pleased to possess identified this article.. certainly, investigation is having to pay off.
LJSqsB Muchos Gracias for your article post. Much obliged.
Ul2w1r This very blog is really educating as well as amusing. I have picked up many helpful tips out of this source. I ad love to return again soon. Thanks a bunch!
OdCleG It as not that I want to duplicate your web site, but I really like the style and design. Could you tell me which style are you using? Or was it custom made?
MWwv7c You ought to really control the comments on this site
ifA8dK Wow! Thank you! I always needed to write on my website something like that. Can I implement a part of your post to my site?
RlNQrF Your style is unique compared to other folks I have read stuff from. Thank you for posting when you ave got the opportunity, Guess I all just book mark this blog.
Hello there, My name is Aly and I would like to know if you would have any interest to have your website here at cyqdata.com promoted as a resource on our blog alychidesign.com ?
We are updating our do-follow broken link resources to include current and up to date resources for our readers. If you may be interested in being included as a resource on our blog, please let me know.
Thanks, AlyYour style is unique compared to other folks I have read stuff from. I appreciate you for posting when you have the opportunity, Guess I will just book mark this site.
Wow, fantastic blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your website is magnificent, let alone the content!
You have brought up a very wonderful points , appreciate it for the post.
Incredible mastewq! This blog looks just like my old one! It as on a entirely different subject but it has pretty much the same page layout and design. Outstanding choice of colors!
This web site is known as a stroll-through for all of the info you wanted about this and didn?t know who to ask. Glimpse right here, and also you?ll definitely uncover it.
Usually I do not read article on blogs, however I would like to say that this write-up very pressured me to try and do so! Your writing taste has been amazed me. Thanks, quite great post.
This can be a set of phrases, not an essay. you are incompetent
we came across a cool web-site that you just might appreciate. Take a search if you want
It as nearly impossible to find educated people on this topic, however, you seem like you know what you are talking about! Thanks
Wow, incredible blog structure! How lengthy have you been running a blog for? you made blogging glance easy. The full glance of your site is great, let alone the content!
This is really fascinating, You are a very professional blogger. I ave joined your rss feed and sit up for searching for more of your great post. Also, I have shared your site in my social networks!
Its hard to find good help I am forever proclaiming that its difficult to procure good help, but here is
wow, awesome blog post.Really thank you! Keep writing.
You have made some good points there. I looked on the internet to learn more about the issue and found most individuals will go along with your views on this site.
Very informative blog article.Much thanks again. Awesome.
the net. Disgrace on Google for not positioning this submit upper!
There is visibly a bundle to identify about this. I feel you made some good points in features also.
This website was how do I say it? Relevant!! Finally I ave found something that helped me. Thanks!
Your mode of telling the whole thing in this article is in fact good, all be capable of without difficulty understand it, Thanks a lot.
Thankyou for this post, I am a big big fan of this internet site would like to proceed updated.
Right now it appears like BlogEngine is the top blogging platform out there right now. (from what I ave read) Is that what you are using on your blog?
Thanks-a-mundo for the article.Really thank you!
Your style is unique in comparison to other people I ave read stuff from. Thank you for posting when you have the opportunity, Guess I will just bookmark this site.
Very exciting information! Perfect just what I was trying to find!
we came across a cool web-site which you may possibly appreciate. Take a look when you want
Thank you, I ave recently been searching for information about this topic for ages and yours is the best I have discovered till now. But, what about the bottom line? Are you sure about the source?
Looking forward to reading more. Great blog post.Really looking forward to read more. Keep writing.
Wonderful story Here are a couple of unrelated information, nonetheless actually really worth taking a your time to visit this website
Wow, incredible weblog structure! How long have you been running a blog for? you made running a blog look easy. The overall look of your web site is wonderful, let alone the content material!
Right now it appears like Movable Type is the top blogging platform out there right now. (from what I ave read) Is that what you are using on your blog?
times will often affect your placement in google and could damage your quality score if
The Silent Shard This can in all probability be very practical for many of one as job opportunities I want to really don at only with my web site but
Thanks for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your further post thank you once again.
magnificent points altogether, you simply gained a new reader. What might you recommend about your post that you just made a few days in the past? Any certain?
Im thankful for the blog.Thanks Again. Fantastic.
Looking forward to reading more. Great blog post. Great.
Utterly written articles , appreciate it for selective information.
Where can I contact your company if I need some help?
There is definately a lot to learn about this issue. I like all of the points you have made.
There is certainly a lot to find out about this topic. I like all the points you made.
You have made some really good points there. I looked on the internet to learn more about the issue and found most individuals will go along with your views on this site.
Thankyou for this post, I am a big big fan of this internet site would like to proceed updated.