ASP.Net MVC + Data Table 实现分页+排序

来源：互联网发布：兰黛丽莎香水知乎编辑：程序博客网时间：2024/06/06 02:49

实现思路：
使用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 ;            }        }...

2 0