How to use DataTables server side column ordering?

Question

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.

EDIT: It's initialise not initialize. I'm not American.

Answer 1

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

Answer 2

For server side datatable, every ajax call have so many paramater one of them is : iSortCol_0 : 0. Here 0 means the index on which the indexing is maintained on page load.

When you click on some other column than it will change like : iSortCol_0 : 1. So in your server side script get this index and maintain the indexing accordingly.