In this article, you will learn how to amalgamate features such as performing specific and multiple column searches, sorting, dynamic page sizing in a single, easy to code and easy to manage solution without degrading the performance aspect.
Introduction
There was a need plausible data grid, capable of performing specific and multiple column searches, sorting, dynamic page sizing, using MongoDB, it handles very large data (over a million records), had elegant code structure, least coding effort and most of all performance. The intention is to amalgamate these features in a single, easy to code and easy to manage solution without degrading the performance aspect.
Pre-Requisites
In order to follow this article, you need to have some understanding of MVC Framework. If you think you have sufficient expertise, then you are good to read further through this article.
Abstraction
The solution is a blend of DataTable
s and MongoDB driver, DataTable
s is used on client side, it handles display grid data and provider searching and ordering UI feature and sends user search query to the server. At server end, MongoDB driver is used to build up flexible and high performance query. For further explanation, the code is broken up into two, client side and server side code.
Client Side Code
At client side, DataTables
is king, here we are using its server-side processing feature. Before going further, DataTables
is a plug-in for the jQuery JavaScript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table. It handles and performs our demanded functionality from our client side. Most importantly, it invokes Ajax request to the server for each draw of the information on the grid or page (i.e., when paging, ordering, searching, etc.). It sends a number of variables to the server (its DataTable
s build in feature) to allow server side to process this Ajax request and return's JSON data. You can download DataTables
plugin from here or just add up the coming line to your page.
<link href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css"
rel="stylesheet" type="text/css" />
<script type="text/javascript"
src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>
DataTables Table Structure
Here is an example of table structure for DataTables
plugin to use, you can see that we have define two header for the table, first header contains the column name while the next header is a place holder for our search controls.
<table id="grid" class="table table-striped small stripe hover cell-border
compact order-column row-border" cellspacing="0" style="font-size: small;">
<thead>
<tr>
<th width="20%">Company</th>
<th width="15%">Contact Person</th>
<th width="7%">Phone</th>
<th width="7%">Fax</th>
<th width="10%">City</th>
<th width="10%">Region</th>
<th width="5%">Status</th>
<th width="14%">Date</th>
<th width="12%">Balance</th>
</tr>
<tr id="filterrow" class="hidden-xs hidden-sm">
<th width="20%" style="padding:0px 0px;border:0px;"></th>
<th width="15%" style="padding: 0px 0px; border: 0px;"></th>
<th width="7%" style="padding: 0px 0px; border: 0px;"></th>
<th width="7%" style="padding: 0px 0px; border: 0px;"></th>
<th width="10%" style="padding: 0px 0px; border: 0px;"></th>
<th width="10%" style="padding: 0px 0px; border: 0px;"></th>
<th width="5%" style="padding: 0px 0px; border: 0px;"></th>
<th width="14%" style="padding: 0px 0px; border: 0px;"></th>
<th width="14%" style="padding: 0px 0px; border: 0px;
text-align:center"></th>
</tr>
</thead>
</table>
DataTables Table's Initialization
Before going into the initialization step, let me explain the variable _columnFilterMannifest
, it holds manifest and data for all the column search controls which will reside on the second header row as discussed previously. This variable is set during our default action method call. On page ready function, call setGridFilter('grid',_columnFilterManifest)
function is invoked, this function is present in our Grid.js to encapsulates functionalities, to make it short, the first parameter is the id of our structure table and the second is the manifest which we had discussed earlier, not long ago. Now our created table structure is initialize from DataTables
plugin, you can go through its manual here.
At the end, initGridFilter()
method is called which is again in Grid.js, and encapsulated. Its core purpose is to assign trigger search feature to all the grid's columns search controls.
var _columnFilterManifest = JSON.parse( @Html.Raw(Json.Encode
( ViewData["columnFilterManifest"].ToString() ) ) );
$(document).ready(function () {
setGridFilters('grid',_columnFilterManifest);
var table = $('#grid').DataTable({
"order": [[1, "asc"]],
"orderCellsTop": true,
"pagingType": "full_numbers",
"scrollCollapse": true,
rowReorder: {
selector: 'td:nth-child(2)'
},
responsive: true,
"processing": true,
"serverSide": true,
"lengthMenu": [[10,15,25, 50], [10,15,25, 50]],
"ajax": "/Customer/GetPage",
"columns": [
{ "data": "company" },
{ "data": "contactPerson" },
{ "data": "phone" },
{ "data": "fax" },
{ "data": "city" },
{ "data": "region" },
{ "data": "status" },
{ "data": "createdDate" },
{ "data": "balance" },
],
"columnDefs": [
{
"render": function (data, type, row) {
return accounting.formatMoney(data);
},
"targets": 8
},
{
"render": function (data, type, row) {
return moment(data).format("MM/DD/YYYY");
},
"targets": 7
},
{ "className": "dt-right", "targets": 8 },
],
"autoWidth": false,
});
initGridFilter(table, 'grid');
});
Server Side
On server side, we have MongoDB driver to make our aspiration possible. One of its controller action method set _columnFilterManifest
variable for the client side which is used to initiate and populate the column search controls which we had discussed earlier in our client side code discussion.
public ActionResult Index()
{
BusinessLogic.Entity.Customer objCust = new BusinessLogic.Entity.Customer();
ViewData["columnFilterManifest"] =
new JavaScriptSerializer().Serialize(objCust.GetColumnFilterManifest());
return View("list");
}
DataTables
shall call GetPage()
controller Action method to get paginated data here. You can see it calling Business Logic layer entity to get entity array result which sends back to the client in JSON format. Its return JSON object contains properties such as data (which contains entity array), recordsTotal
(entity array size), recordsFiltered
(entity array size) and draw (its used by DataTable
s to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTable
).
public JsonResult GetPage()
{
int draw = Convert.ToInt32(Request["draw"]);
BusinessLogic.Entity.Customer objCust = new BusinessLogic.Entity.Customer();
CustomerGrid[] customers = objCust.Search(this);
return Json(new
{
data = customers,
recordsTotal = base.GetGridTotalRows(),
recordsFiltered = base.GetGridTotalRows(),
draw = draw
},
"application/json", Encoding.UTF8, JsonRequestBehavior.AllowGet);
}
Now moving on to the entity business logic layer which got invoked from controller action method, which we have just discussed that is Search(IBaseController iBaseController)
, it performs all of our pagination activities in this Business Logic layer entity method that filter by
clause and order by
clause shall be added to the query dynamically according to DataTables
query.
#region Document Search
public virtual CustomerGrid[] Search(IBaseController iBaseController)
{
GridParameterCollector gridParamCollet =
iBaseController.GenerateGridParameterCollector();
IMongoCollection<CustomerGrid> collection =
database.GetCollection<CustomerGrid>("Customer");
List<FilterDefinition<CustomerGrid>> filterDef =
new List<FilterDefinition<CustomerGrid>>(); ;
genericSearchText = gridParamCollet.searchText.Trim();
foreach (ColumnParameterCollector column in gridParamCollet.columnCollector)
{
searchColumnText = column.columnSearchText.Trim();
switch (column.dataName)
{
case "company":
string companyColumnText = searchColumnText;
string companyGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.company,
new BsonRegularExpression(companyColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.company,
new BsonRegularExpression(companyGenericText + "*", "i")),
x => x.company
);
break;
case "contactPerson":
string contactPersonColumnText = searchColumnText;
string contactPersonGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonGenericText + "*", "i")),
x => x.contactPerson
);
break;
case "phone":
string phoneColumnText = searchColumnText;
string phoneGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.phone,
new BsonRegularExpression(phoneColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.phone,
new BsonRegularExpression(phoneGenericText + "*", "i")),
x => x.phone
);
break;
case "fax":
string faxColumnText = searchColumnText;
string faxGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.fax,
new BsonRegularExpression(faxColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.fax,
new BsonRegularExpression(faxGenericText + "*", "i")),
x => x.fax
);
break;
case "city":
string cityColumnText = searchColumnText;
string cityGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.city,
new BsonRegularExpression(cityColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.city,
new BsonRegularExpression(cityGenericText + "*", "i")),
x => x.city
);
break;
case "region":
string regionColumnText = searchColumnText;
string regionGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.region,
new BsonRegularExpression(regionColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.region,
new BsonRegularExpression(regionGenericText + "*", "i")),
x => x.region
);
break;
case "status":
string statusColumnText = searchColumnText;
string statusGenericText = genericSearchText;
System.Linq.Expressions.Expression<Func<CustomerGrid, bool>>
jobStatusFilterExpression = null;
if (searchColumnText.Length != 0 && searchColumnText != "-1")
{
jobStatusFilterExpression = x => x.status == statusColumnText;
}
EvaluateFilterMongoDB(column,
jobStatusFilterExpression,
x => x.status.Equals(statusGenericText),
x => x.status
);
break;
case "createdDate":
if (searchColumnText.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.createdDate
);
}
continue;
}
string[] firstSplit = searchColumnText.Split(',');
if (firstSplit[0] == "false")
{
continue;
}
string creationDateRangeColumnText = firstSplit[1];
string[] creationDateRangeSplit =
creationDateRangeColumnText.Split('-');
DateTime startDate = DateTime.Parse(creationDateRangeSplit[0]);
DateTime endDate = DateTime.Parse(creationDateRangeSplit[1]);
EvaluateFilterMongoDB(column,
x => x.createdDate >= startDate && x.createdDate <= endDate,
x => x.createdDate >= startDate && x.createdDate <= endDate,
x => x.createdDate
);
break;
case "balance":
string balanceColumnText = searchColumnText;
if (searchColumnText.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
string[] str = searchColumnText.Split(',');
string strAmount = str[1].Trim();
if (strAmount.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
double amount = double.Parse(strAmount);
EvaluateNumericComparisonFilterMongoDB(column,
balanceColumnText,
"balance",
x => x.balance,
Builders<CustomerGrid>.Filter.Lte(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Eq(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Gte(c => c.balance, amount)
);
break;
}
}
FilterDefinition<CustomerGrid> filter = null;
if (isAndPredicateAdded)
{
filter = Builders<CustomerGrid>.Filter.And(andPredicateMongoDB);
}
if (isOrPredicateAdded)
{
filter = Builders<CustomerGrid>.Filter.Or(orPredicateMongoDB);
}
if (iBaseController != null)
{
int count = 0;
if (filter == null)
{
count = Convert.ToInt32(collection.Count(x => x.company != ""));
}
else
{
count = Convert.ToInt32(collection.Find(filter).Count());
}
iBaseController.SetGridTotalRows(count);
}
ProjectionDefinition<CustomerGrid> proj = Builders<CustomerGrid>.Projection
.Include("Id")
.Include("company")
.Include("contactPerson")
.Include("createdDate")
.Include("phone")
.Include("CreatedBy")
.Include("fax")
.Include("email")
.Include("city")
.Include("region")
.Include("balance")
.Include("status")
;
if (gridParamCollet != null)
{
if (filter == null)
{
customers = collection.Find(x => x.company != "")
.Sort(sortDefMongoDB)
.Project<CustomerGrid>(proj)
.Skip(gridParamCollet.start).Limit(gridParamCollet.length).ToList();
}
else
{
customers = collection.Find(filter).Sort
(sortDefMongoDB).Project<CustomerGrid>(proj).Skip
(gridParamCollet.start).Limit(gridParamCollet.length).ToList();
}
}
return customers.ToArray();
}
In the first part of the above entity search method, GenerateGridParameterCollector()
method is called which makes DataTable
s Ajax call querystring
into desire object, in this case, it's turned into GridParameterCollector
object definition of which is as given below. I belief that only comments shall do the job here.
public class GridParameterCollector
{
public int start{ get; set; }
public int length{ get; set; }
public string searchText { get; set; }
public bool isSearchRegex { get; set; }
public List<ColumnParameterCollector> columnCollector { get; set; }
}
public class ColumnParameterCollector
{
public int columnIndex { get; set; }
public string orderDirection { get; set; }
public string dataName { get; set; }
public string columnName { get; set; }
public bool isColumnSearchable { get; set; }
public bool isColumnOrderable { get; set; }
public string columnSearchText { get; set; }
public bool isColumnSearchRegex { get; set; }
}
The next step is to build filter definition for final query. In subsequent step, gridParamCollect
objects iteratable property columnCollector
is iterated to append where
clause to the query. Now in the next step, we shall discuss the search column filter evaluation method that would build up the predicate, functionality of which is encapsulated in business logic layer entity base class.
case "contactPerson":
string contactPersonColumnText = searchColumnText;
string contactPersonGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonGenericText + "*", "i")),
x => x.contactPerson
);
break;
When dealing with column text search or select option (sometime), then EvaluteFilter
method is called which has its definition in base class. It takes four parameters, first parameter is current iteration ColumnParameterCollector
object which provides info regarding current iterated column, second one is lamda expression for column search only, for instance lamda expression x => x.email.StartWtih("codeproject")
shall generate where clause equivalent to '%codeproject
'. The third parameter is for generic search, it consumes value from the upper right search textbox
of DataTables
grid. The fourth parameter is lamda expression as well but with different purpose, it defines order by
clause for the query. e.g., x => x.email
, then query will be embedded with order by email clause.
case "status":
string statusColumnText = searchColumnText;
string statusGenericText = genericSearchText;
System.Linq.Expressions.Expression<Func<CustomerGrid, bool>>
jobStatusFilterExpression = null;
if (searchColumnText.Length != 0 && searchColumnText != "-1")
{
jobStatusFilterExpression = x => x.status == statusColumnText;
}
EvaluateFilterMongoDB(column,
jobStatusFilterExpression,
x => x.status.Equals(statusGenericText),
x => x.status
);
break;
For select
option, column search approach is slightly different, only from second parameter in which lamda expression is dynamic defined in if
condition. Rest is exactly the same as the last explanation.
case "balance":
string balanceColumnText = searchColumnText;
if (searchColumnText.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
string[] str = searchColumnText.Split(',');
string strAmount = str[1].Trim();
if (strAmount.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
double amount = double.Parse(strAmount);
EvaluateNumericComparisonFilterMongoDB(column,
balanceColumnText,
"balance",
x => x.balance,
Builders<CustomerGrid>.Filter.Lte(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Eq(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Gte(c => c.balance, amount)
);
break;
Numerical comparison is different from the previous one as dynamic binary tree expression is involved here, this functionality is performed by EvaluateNumericComparisionFilter()
method, its second parameter is column search value, third column is concerned with entity object field name, and last one is lambda expression for order by
clause.
History
- 4th February, 2018: Initial version