ASP. Net MVC + Data Table Paging + Sorting Function

  • 2021-09-24 21:59:23
  • OfStack

In this paper, an example of ASP. Net MVC + Data Table to achieve the function of paging + sorting. Share it for your reference, as follows:

Implementation ideas:

Use datatable built-in paging, sorting
Use attribute + reflection to control the fields to be sorted and displayed and the order
Separate sorting and display logic
To add search logic, just pass the searched fields to the back end (js initializes "searching": false is removed).

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 ;
      }
    }
...

For more readers interested in asp. net related contents, please check the topics of this site: "Summary of asp Optimization Skills", "Summary of asp String Operation Skills", "Summary of asp. net Operation Skills of XML", "Summary of asp.net File Operation Skills", "Summary of asp. net ajax Skills" and "Summary of asp. net Cache Operation Skills".

I hope this paper is helpful to everyone's asp. net programming.


Related articles: