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