Method of realizing paging + sorting function with ASP. Net MVC + data table

Time:2021-8-24

This paper describes the method of realizing paging + sorting function with ASP. Net MVC + data table. Share with you for your reference, as follows:

Implementation idea:

Use the built-in paging of datatable to sort
Use attribute + reflection to control the fields to be sorted and displayed and the order
Separating sorting and display logic
To add search logic, you only need to pass the search field to the back end (remove “searching”: false during JS initialization).

View :


@using BCMS.BusinessLogic
@using BCMS.BusinessLogic.Models
@model List<BusCaptainObj>
<table>
  <thead>
    <tr>
      <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 information about asp.net, readers who are interested can see the special topics on this site:《Summary of asp.net optimization skills》、《Summary of asp.net string operation skills》、《Summary of asp.net operating XML skills》、《Summary of asp.net file operation skills》、《Asp.net Ajax skills summary topic》And《Summary of asp.net cache operation skills》。

I hope this article will help you in ASP. Net programming.