jQuery Datatables Bindings
Namespace
WebExtras.JQDataTables
Dependancies
- Appropriate third party libraries
- jquery.dataTables.bootstrap3.js
- jquery.dataTables.bootstrap3.css
- webextras.bootstrap3.css
Steps involved in creating a Datatable
- Creating the column definitions
- Creating the table settings
- Creating the table records
- Creating the table object
- And finally the output.....
Sorting support can be added by slightly modifying the column definitions and using the Sort extension method.
Updating the column definitions to enable sorting
Column definitions are created by instantiating the
WebExtras.JQDataTables.AOColumn class. In order to enable sorting
on a column, you MUST set the Sortable property to
true. By default this will be set to
false by
the constructor.
AOColumn dtColumn = new AOColumn
{
sTitle = "First Column",
bSortable = true,
sClass = "",
sWidth = "10%",
bVisible = true
};
Creating table settings
Table settings are created by instantiating the
WebExtras.JQDataTables.DatatableSettings class
AOColumn[] dtColumns = new AOColumn[] { dtColumn };
DatatableSettings dtSettings = new DatatableSettings
(
5,
dtColumns,
new AASort(0, SortType.Ascending),
"~/getsorteddata",
"paged and sorted records",
"150px"
);
Creating a data handler
We make use of the C# LinqToSQL feature in order to simulate the sorting behavior. WebExtras provides an extension method for the
IEnumerable<IEnumerable<string>> interface which takes care of the sorting for us.
public DatatableRecords GetSortedRecords(DatatableFilters filters)
{
List<string[]> dtData = new List<string[]>();
for (int i = 0; i < 15; i++)
{
dtData.Add(new string[] {
string.Format("first column paged row {0}", i + 1),
string.Format("second column paged row {0}", i + 1)
});
}
DatatableRecords dtRecords = new DatatableRecords
{
sEcho = filters.sEcho,
iTotalRecords = dtData.Length,
iTotalDisplayRecords = dtData.Length,
aaData = dtData.Sort(filters.iSortCol_0, filters.SortDirection)
};
return dtRecords;
}
Make the first page of the table
We need to create the first page of the table in order to have the paging behavior kick in. This should be done when you display
the table the first time.
DatatableRecords dtRecords = GetSortedRecords(new DatatableFilters { iDisplayStart = 0, iDisplayLength = 5 });
Datatable dTable = new Datatable("sorted-table", dtSettings, dtRecords);
A slightly modified AJAX callback handler
We will now make use of the method that we have already created before to do our grunt work and get the data. The fact that
we have used DatatableFilters as one of the parameters means that we can simply forward the filtering parameters we got
from the HTTP GET request from the client side.
public JsonResult GetSortedData(DatatableFilters filters)
{
DatatableRecords dtRecords = GetSortedRecords(filters);
return Json(dtRecords, JsonRequestBehavior.AllowGet);
}
And our sorting enabled output is
HTML field column |
String Column |
DateTime Column |
Numeric Column |
Currency Column |
Some details about the sorter extension
The sorter extension can currently handle columns with the following .NET base types and some special formats as listed below:
- string
- DateTime
- int, float, double, decimal
- currency i.e. data in the format: €5.00, $6.00, £7.00, ¥8.00. The sorter will strip the currencies and sort on the decimal number.
It isn't intelligent enough to look up the current exchange rate and sort them, so beware!!
Euro, Dollar, Pound and Yen are the currently supported
currencies.
- HTML fields i.e data in format: <a href='/somelink.html'>some text</a>. The sorter will strip the HTML tags and sort on the inner text of the tag.
In this case the sorter will sort on some text and not the HTML A tag.
But my data doesn't fit any of the default formats :(
All defaults sorters have a fixed way in which they try to parse data and then apply the sorting logic on it.
However, you are not limited to only the default sorters.
If you have a custom data format you are using, you can provide custom parsers to use in order to perform sorts.
Func<string, object> secondCharParser = (string str) =>
{
char[] arr = str.ToCharArray();
if(arr.Length > 1)
return arr[1];
return null;
}
Dictionary<int, Func<string, object>> parsers = new Dictionary<int, Func<string, object>>();
parsers.Add(1, secondCharParser);
DatatableRecords dtRecords = new DatatableRecords
{
sEcho = filters.sEcho,
iTotalRecords = dtData.Length,
iTotalDisplayRecords = dtData.Length,
aaData = dtData.Sort(filters.iSortCol_0, filters.SortDirection, parsers)
};