ASP.Net MVC+Data Table实现分页+排序功能的方法

Z时代
2024-01-10
分类：综合

本文实例讲述了ASP.Net MVC+Data Table实现分页+排序功能的方法。分享给大家供大家参考，具体如下：

实现思路：

使用datatable内置的分页，排序

使用attribute+反射来控制需要排序和显示的字段以及顺序

分离排序和显示逻辑

若要添加搜索逻辑只需要传递搜索的字段到后端即可(js初始化时把"searching": false拿掉)。

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