Andrew Kilburn Andrew Kilburn - 3 months ago 39
jQuery Question

How to use DataTables server side column ordering?

So we have a DataTable which contains data like so:

<table class="display" id="invoiceTable">
<thead>
<tr id="headingRow">
<th>Sys_InvoiceID</th>
<th>Details</th>
<th>Inc.In Turnover</th>
<th>Status</th>
<th>Invoice No.</th>
<th>Invoice Date</th>
<th>Type</th>
<th>Supplier Invoice No.</th>
<th>Account Number</th>
<th>Supplier</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>Details1</td>
<td>Yes</td>
<td>Issue</td>
<td>500</td>
<td>18/08/2016</td>
<td>Type1</td>
<td>111111</td>
<td>56565</td>
<td>SupplierID</td>
</tr>
</tbody>
</table>


And the jquery to initialise the DataTable looks like this:

$(document).ready(function(){
var table = $('#invoiceTable').DataTable({
dom: 'rti',
ajax: {
"url": filterDataLink,
},
serverSide: true,
"order": [[5, 'asc']],
searching: false,
paging: true,
scrollX: true,
scrollY: "75vh", //70% of the window height
processing: false,
ordering: true,
scroller: {
loadingIndicator: true,
displayBuffer: 20
}
});
});


And the c# method looks like this:

public string FilterData(TableFilterItem tableFilterItem) {
try {
if (tableFilterItem.ColumnOrder == null || tableFilterItem.ColumnOrder.First() == null)
tableFilterItem.ColumnOrder = new List<ColumnOrder>();


SessionModel sessionModel = (SessionModel)Session["Filters"];
DataTableReturn output = _homeManager.ReturnDataTableSession(sessionModel, tableFilterItem);
Session["Filters"] = output.SessionModel;

return output.DataTable;
}
catch (Exception ex) {
return "An error occurred, please try later! " + ex.Message;
}
}


TableFilterItem looks like this:

public class TableFilterItem {
public TableFilterItem() {

}
public int Start { get; set; }
public int Length { get; set; }
public int Draw { get; set; }

public int order { get; set; }

}


We also have another ajax call later in the application which calls the C# method to return data. When clicking on a column it calls the method but I don't know what property to have in the method which would be populated by DataTables so I can access the sorting.

Can anyone help me with this?

Answer

I would suggest to use the datatables.mvc5 package which is a datatables model binder to controller implemented by Stefan Nuxoll.

Your action would look like:

public ActionResult Get([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
{

  IQueryable<asset> query = DbContext.Assets;

  // Sorting
  var sortedColumns = requestModel.Columns.GetSortedColumns();
  var orderByString = String.Empty;

  foreach (var column in sortedColumns)
  {
     orderByString += orderByString != String.Empty ? "," : "";
     orderByString += (column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant ? " asc" : " desc");
  }

    query = query.OrderBy(orderByString == string.Empty ? "BarCode asc" : orderByString);

   var data = query.Select(asset => new
   {
      AssetID = asset.AssetID,
      BarCode = asset.Barcode,
      Manufacturer = asset.Manufacturer,
      ModelNumber = asset.ModelNumber,
      Building = asset.Building,
      RoomNo = asset.RoomNo,
      Quantity = asset.Quantity
   }).ToList();

 return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalCount), JsonRequestBehavior.AllowGet);

}

and your datatable jquery code will be like:

$('#assets-data-table').DataTable({
                        "serverSide": true,
                        "processing": true,
                        "ajax": {
                            "url": "@Url.Action("Get","Asset")"
                        },
                        "columns": [
                            { "title": "Bar Code", "data": "BarCode", "searchable": true },
                            { "title": "Manufacturer", "data": "Manufacturer", "searchable": true },
                            { "title": "Model", "data": "ModelNumber", "searchable": true },
                            { "title": "Building", "data": "Building", "searchable": true },
                            { "title": "Room No", "data": "RoomNo" },
                            { "title": "Quantity", "data": "Quantity" }
                        ],
                        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
                    });

You can have a look at this post (Server Side Searching, Paging and Sorting using Jquery DataTables).

Comments