ASP. Net MVC + Data Table to implement paging + sorting, asp. netmvc

ASP. Net MVC + Data Table to implement paging + sorting, asp. netmvc

This article describes how to implement paging + sorting by ASP. Net MVC + Data Table. We will share this with you for your reference. The details are as follows:

Implementation ideas:

Use datatable built-in paging and sorting
Use attribute + reflection to control the fields to be sorted and displayed and their order
Separate sorting and display logic
To add the search logic, you only need to pass the search field to the backend ("searching": false is removed during js initialization ).

View:

@using BCMS.BusinessLogic@using BCMS.BusinessLogic.Models@model List<BusCaptainObj><table id="tblData" class="table table-striped">  <thead>    <tr class="data-list">      <th style="width:10%;">@Html.DisplayNameFor(model => model.First().PersNo)</th>      <th style="width:30%;">@Html.DisplayNameFor(model => model.First().Personnel_Name)</th>      <th style="width:20%;">@Html.DisplayNameFor(model => model.First().Position)</th>      <th style="width:20%;">@Html.DisplayNameFor(model => model.First().Interchange)</th>      <th style="width:20%;">Action</th>    </tr>  </thead></table>@section scripts {  <script type="text/javascript">     @{       var columns = DataTableHelper.DisplayColumns<BusCaptainObj>();     }    $(document).ready(function () {      $('#tblData').dataTable({        "processing": true,        "serverSide": true,        "searching": false,        "stateSave": true,        "oLanguage": { "sInfoFiltered": "" },        "ajax": {          "url": @Url.Action("GetJsonData"),          "type": "GET"        },        "columns": [          { "data": "@columns[0]" },          { "data": "@columns[1]" },          { "data": "@columns[2]" },          { "data": "@columns[3]" },          {            "data": "@columns[0]",            "orderable": false,            "searchable": false,            "render": function (data, type, full, meta) {              if (type === 'display') {                return GetDetailButton("/BusCaptain/Detail?bcId=", data) + GetInfoButton("/Telematics?bcId=", data, "Performance");              } else { return data; }            }          }        ],        "order": [[0, "asc"]]      });    });  </script>}

Controller:

public ActionResult GetJsonData(int draw, int start, int length){  string search = Request.QueryString[DataTableQueryString.Searching];  string sortColumn = "";  string sortDirection = "asc";  if (Request.QueryString[DataTableQueryString.OrderingColumn] != null)  {    sortColumn = GetSortColumn(Request.QueryString[DataTableQueryString.OrderingColumn]);  }  if (Request.QueryString[DataTableQueryString.OrderingDir] != null)  {    sortDirection = Request.QueryString[DataTableQueryString.OrderingDir];  }  DataTableData dataTableData = new DataTableData();  dataTableData.draw = draw;  int recordsFiltered = 0;  dataTableData.data = BusCaptainService.Instance.SearchMyBuscaptains(User.Identity.Name, out recordsFiltered, start, length, sortColumn, sortDirection, search).Data;  dataTableData.recordsFiltered = recordsFiltered;  return Json(dataTableData, JsonRequestBehavior.AllowGet);}public string GetSortColumn(string sortColumnNo){  var name = DataTableHelper.SoringColumnName<BusCaptainObj>(sortColumnNo);  return name;}public class DataTableData{  public int draw { get; set; }  public int recordsFiltered { get; set; }  public List<BusCaptainObj> data { get; set; }}

Model:

class XXX{...  [DisplayColumn(0)]    [SortingColumn(0)]    public int? A { get; set; }    [DisplayColumn(1)]    [SortingColumn(1)]    public string B { get; set; }...}

Helper class:

public class SortingColumnAttribute : Attribute{    public int Index { get; }    public SortingColumnAttribute(int index)    {      Index = index;    }}public class DisplayColumnAttribute : Attribute{    public int Index { get; }    public DisplayColumnAttribute(int index)    {      Index = index;    }}public static class DataTableQueryString{    public static string OrderingColumn = "order[0][column]";    public static string OrderingDir = "order[0][dir]";    public static string Searching = "search[value]";}public static class DataTableHelper{    public static IList<string> DisplayColumns<T>()    {      var result = new Dictionary<int, string>();      var props = typeof(T).GetProperties();      foreach (var propertyInfo in props)      {        var propAttr =          propertyInfo            .GetCustomAttributes(false)            .OfType<DisplayColumnAttribute>()            .FirstOrDefault();        if (propAttr != null)        {          result.Add(propAttr.Index,propertyInfo.Name);        }      }      return result.OrderBy(x => x.Key).Select(x => x.Value).ToList();    }    public static string SoringColumnName<T>(string columnIndex)    {      int index;      if (!int.TryParse(columnIndex, out index))      {        throw new ArgumentOutOfRangeException();      }      return SoringColumnName<T>(index);    }    public static string SoringColumnName<T>(int index)    {      var props = typeof(T).GetProperties();      foreach (var propertyInfo in props)      {        var propAttr =          propertyInfo            .GetCustomAttributes(false)            .OfType<SortingColumnAttribute>()            .FirstOrDefault();        if (propAttr != null && propAttr.Index == index)        {          return propertyInfo.Name;        }      }      return "";    }}

Query:

...var query = context.BusCaptains            .Where(x => ...)            .OrderByEx(sortDirection, sortField)            .Skip(start)            .Take(pageSize);...

LINQ Helper:

...public static IQueryable<T> OrderByEx<T>(this IQueryable<T> q, string direction, string fieldName)    {      try      {        var customProperty = typeof(T).GetCustomAttributes(false).OfType<ColumnAttribute>().FirstOrDefault();        if (customProperty != null)        {          fieldName = customProperty.Name;        }        var param = Expression.Parameter(typeof(T), "p");        var prop = Expression.Property(param, fieldName);        var exp = Expression.Lambda(prop, param);        string method = direction.ToLower() == "asc" ? "OrderBy" : "OrderByDescending";        Type[] types = new Type[] {q.ElementType, exp.Body.Type};        var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);        return q.Provider.CreateQuery<T>(mce);      }      catch (Exception ex)      {        _log.ErrorFormat("error form OrderByEx.");        _log.Error(ex);        throw ;      }    }...