JQuery Datatable - Column Sorting is not working in the

Question

I am working on an ASP.NET Core MVC app and using JQuery Datatable in my app. My datatable is not sorting when clicking the sort button in the column header. My datatable is as shown below:

<table class="table display table-bordered" id="DATATABLE">
</table>

Inside the <script> tag:

$("#DATATABLE").DataTable({
    serverSide: true,
    filter: true,
    searchDelay: 1000,
    scrollY: StaticData.TABLE_HEIGHT + 'px',
    lengthMenu: StaticData.TABLE_PAGE_SIZE,
    language: { searchPlaceholder: "Name, Teacher" },
    scrollCollapse: true,
    ajax: {
        url: '/STUD_MANAGEMENT/LoadStud',
        type: 'GET',
        datatype: 'json',
        headers: { 'RequestVerificationToken': 'your json token' },
        data: (d) => {                    
            return { draw: d.draw, start: d.start, length: d.length, search: d.search.value, FilterByColumn: d.columns[d.order[0].column].data, ASC_DSEC: d.order[0].dir }
        },
        beforeSend: () => { ShowLoader(); },
        complete: () => { HideLoader(); },
        dataSrc: (json) => {
            json = json.data;                    
            return json;
        }
    },
    columnDefs: [{ className: "dt-center", targets: [5, 6, 7, 8, 11], width: '2%', }],
    columns: [
        { data: 'STUD_ID', title: 'STUD ID', autoWidth: false, visible: false },
        { data: 'CLASS_ID', title: 'CLASS ID', autoWidth: false, visible: false },
        { data: 'NAME', title: 'Name', autoWidth: true, searchable: true },
        { data: 'AGE', title: 'Age', autoWidth: true },
        { data: 'TEACHER', title: 'Teacher', autoWidth: true },               
    ]            
});

And the LoadStud method in the controller is shown below:

public IActionResult LoadStud(int draw = 1, int start = 0, int length = 10, string search = "", string FilterByColumn = "", string ASC_DSEC = "")
{
    List<STUD_MANAGEMENT> ListData = new List<STUD_MANAGEMENT>();

    int recordsTotal = 0;            

    STUD_MANAGEMENT dm = new STUD_MANAGEMENT();
    dm.STUD_ID = 1;
    dm.CLASS_ID = 1;
    dm.NAME = "James";
    dm.TEACHER = "SEPHORA";
    dm.AGE = "12";

    STUD_MANAGEMENT dm1 = new STUD_MANAGEMENT();
    dm1.STUD_ID = 2;
    dm1.CLASS_ID = 2;
    dm1.NAME = "Naneem";
    dm1.TEACHER = "Chithra";
    dm1.AGE = "15";

    STUD_MANAGEMENT dm11 = new STUD_MANAGEMENT();
    dm11.STUD_ID = 3;
    dm11.CLASS_ID = 3;
    dm11.NAME = "Sony";
    dm11.TEACHER = "Mano USA";
    dm11.AGE = "3";            

    ListData.Add(dm);
    ListData.Add(dm1);
    ListData.Add(dm11);

    recordsTotal = ListData.Count();                    

    var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = ListData };
    return Ok(jsonData);
}

The STUD_MANAGEMENT class is shown below:

public class STUD_MANAGEMENT
{             
   public int STUD_ID { get; set; }
   public int CLASS_ID { get; set; }      
   public string NAME { get; set; }
   public int AGE { get; set; }   
   public string TEACHER { get; set; }
}

Answer 1

From Server-side processing:

With server-side processing enabled, all paging, searching, ordering actions that DataTables performs are handed off to a server where an SQL engine (or similar) can perform these actions on the large data set

It mentions that it is the responsibility of the back-end to perform those operations.

Your current implementation didn't cover the ordering.

A simple way is that you need to check based on the FilterByColumn and ASC_DSEC to order the property value by sorting order accordingly.

if (!String.IsNullOrEmpty(FilterByColumn))
{
    ASC_DSEC = ASC_DSEC.ToUpper();
    ASC_DSEC = ASC_DSEC == "ASC" || ASC_DSEC == "DESC"
        ? ASC_DSEC
        : "ASC";

    switch (FilterByColumn.ToUpper())
    {
        case "STUD_ID":
            ListData = ASC_DSEC == "ASC"
                ? ListData.OrderBy(x => x.STUD_ID).ToList()
                : ListData.OrderByDescending(x => x.STUD_ID).ToList();
            break;

        case "CLASS_ID":
            ListData = ASC_DSEC == "ASC"
                ? ListData.OrderBy(x => x.CLASS_ID).ToList()
                : ListData.OrderByDescending(x => x.CLASS_ID).ToList();
            break;

        case "NAME":
            ListData = ASC_DSEC == "ASC"
                ? ListData.OrderBy(x => x.NAME).ToList()
                : ListData.OrderByDescending(x => x.NAME).ToList();
            break;

        case "AGE":
            ListData = ASC_DSEC == "ASC"
                ? ListData.OrderBy(x => x.AGE).ToList()
                : ListData.OrderByDescending(x => x.AGE).ToList();
            break;

        case "TEACHER":
            ListData = ASC_DSEC == "ASC"
                ? ListData.OrderBy(x => x.TEACHER).ToList()
                : ListData.OrderByDescending(x => x.TEACHER).ToList();
            break;                  
    }
}

var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = ListData };

However, this is not a good way, considering the extensibility and maintainability, for example in the future you may add a new property, change the property name, or remove the property.

If you are looking for an advanced way, work with System.Reflection.

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;

if (!String.IsNullOrEmpty(FilterByColumn))
{
    PropertyInfo propInfo = typeof(STUD_MANAGEMENT).GetProperty(FilterByColumn, BindingFlags.Public | BindingFlags.Instance);
    if (propInfo != null)
    {
        switch (ASC_DSEC.ToUpper())
        {
            case "ASC":
                ListData = ListData.OrderBy(x => propInfo.GetValue(x, null)).ToList();
                break;
                        
            case "DESC":
                ListData = ListData.OrderByDescending(x => propInfo.GetValue(x, null)).ToList();
                break;
        }
    }
}

var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = ListData };