ASP.NET MVC 使用 Datatables (2)

在伺服器端實現分頁，排序，獲取當前頁面資料

在上篇的基礎上進行改造(datatables的客戶端實現)

1、修改View頁面程式碼如下：

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-primary" id="list-panel">
            <div class="panel-heading">
                <h1 class="panel-title">Assets</h1>
            </div>
            <div class="panel-body">
                <table id="assets-data-table" class="table table-striped table-bordered" style="width:100%">

                </table>
            </div>
        </div>
    </div>
</div>
@section Scripts
{
    <script type="text/javascript">
        var assetListVM;
        $(document).ready(function () {
            assetListVM = {
                dt:null,
                init: function () {
                    dt = $("#assets-data-table").DataTable({
                        "serverSide": true,
                        "proccessing": true,
                        "ajax": {
                            "url":"@Url.Action("Get","Asset")"
                        },
                        "columns": [
                            { "title": "Bar Code", "data": "Barcode", "searchable": true },
                            { "title": "Manufacturer", "data": "Manufacturer", "searchable": true },
                            { "title": "Model", "data": "ModelNumber", "searchable": true },
                            { "title": "Building", "data": "Building", "searchable": true },
                            { "title": "Room No", "data": "RoomNo" },
                            { "title": "Quantity", "data": "Quantity" }
                        ],
                        "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
                        "language": {
                            "processing": "處理中...",
                            "lengthMenu": "顯示 _MENU_ 項結果",
                            "zeroRecords": "沒有匹配結果",
                            "info": "顯示第 _START_ 至 _END_ 項結果，共 _TOTAL_ 項",
                            "infoEmpty": "顯示第 0 至 0 項結果，共 0 項",
                            "infoFiltered": "(由 _MAX_ 項結果過濾)",
                            "infoPostFix": "",
                            "search": "搜尋:",
                            "searchPlaceholder": "搜尋...",
                            "url": "",
                            "emptyTable": "表中資料為空",
                            "loadingRecords": "載入中...",
                            "infoThousands": ",",
                            "paginate": {
                                "first": "首頁",
                                "previous": "上頁",
                                "next": "下頁",
                                "last": "末頁"
                            },
                            "aria": {
                                paginate: {
                                    first: '首頁',
                                    previous: '上頁',
                                    next: '下頁',
                                    last: '末頁'
                                },
                                "sortAscending": ": 以升序排列此列",
                                "sortDescending": ": 以降序排列此列"
                            },
                            "decimal": "-",
                            "thousands": ","
                        }
                    });
                }
            };
            assetListVM.init();
        });
    </script>
}

2、新增服務端必須的元件：

　　A:Install-Package datatables.mvc5

　　B:Install-Package System.Linq.Dynamic　　

3、新增伺服器端方法：

public ActionResult Get([ModelBinder(typeof(DataTablesBinder))] IDataTablesRequest requestModel)
        {
            IQueryable<Asset> query = dbContext.Assets;
            var totalcount = query.Count();

            #region Filtering
            if (requestModel.Search.Value!=string.Empty)
            {
                var value = requestModel.Search.Value.Trim();
                query = query.Where(p => p.Barcode.Contains(value) ||
                                         p.Manufacturer.Contains(value) ||
                                         p.ModelNumber.Contains(value) ||
                                         p.Building.Contains(value)
                                    );
            }

            var filteredCount = query.Count();
            #endregion

            #region Sorting
            var sortedColumns = requestModel.Columns.GetSortedColumns();
            var orderByString = string.Empty;
            foreach (var column in sortedColumns)
            {
                orderByString += orderByString != string.Empty ? "," : "";
                orderByString += (column.Data) + (column.SortDirection == Column.OrderDirection.Ascendant?" asc":" desc");
            }

            query = query.OrderBy(orderByString == string.Empty ? " Barcode asc" : orderByString);


            #endregion
            //Paging
            query = query.Skip(requestModel.Start).Take(requestModel.Length);

            var data = query.Select(asset=>new
            {
                 AssetID=asset.AssetID,
                Barcode=asset.Barcode,
                Manufacturer=asset.Manufacturer,
                ModelNumber=asset.ModelNumber,
                Building=asset.Building,
                RoomNo=asset.RoomNo,
                Quantity=asset.Quantity
            }).ToList();

            return Json(new DataTablesResponse(requestModel.Draw, data, filteredCount, totalcount), JsonRequestBehavior.AllowGet);
        }

4、執行程式，檢視結果