本文详细介绍了如何使用Datatables进行分页加载数据，并展示了服务器端处理分页请求的示例代码。通过应用Pipeline缓存减少Ajax请求频率，优化大数据量时的页面加载效率。同时，文章还涵盖了高级搜索、过滤、导出等功能的实现，以及在ASP.NET MVC框架下控制器的处理逻辑。
在使用datatables加載數據時，我們通常使用一次加載數據再渲染的方式。如果記錄不多，一般幾百，小幾千的頁面加載速度問題不大，但超過就會導致頁面加載時間過長。下面分享記錄一個Datatables ajax server side 分頁獲取數據的例子,：
第一部分：VIEW HTML&JS
<div id="searchServices" data-content="List" style="width:98%">
<div class="row">
<div class="col-12 col-md-12">
<div class="card card-solid">
<div class="card-body">
<table id="servicelist" class="table table-bordered table-striped table-hover" width="100%">
<thead>
<tr role="row">
<th>單位</th>
<th>編號</th>
<th>名稱</th>
<th>活動</th>
<th>日期</th>
<th>狀態</th>
<th>地點</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
<!-- Search Modals -->
<div class="modal fade" id="advSearchModal" tabindex="-1" role="dialog" aria-labelledby="進階搜尋">
<form method="post" asp-action="SearchPractice" id="advSearchForm">
<div class="modal-dialog modal-lg" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
@*<h4 class="modal-title">搜尋</h4>*@
</div>
<div class="modal-body">
<div class="form-group">
<div class="col-sm-12">
<div class="row">
<label for="advsCaseNo" class="col-sm-6">編號</label>
<div class="col-sm-6">
<input asp-for="@Model.Criteria.CaseNo" type="text" class="form-control" id="advsCaseNo">
</div>
</div>
</div>
</div>
<div class="form-group">
<div class="col-sm-12">
<div class="row">
<label for="advsOwnerName" class="col-sm-6">姓名</label>
<div class="col-sm-6">
<input asp-for="@Model.Criteria.CaseName" type="text" class="form-control" id="advsOwnerName">
</div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal" aria-label="Close">關閉</button>
<button type="button" class="btn btn-default" id="btnSetfilter">搜尋</button>
</div>
</div>
</div>
</form>
</div>
</div>
<!--引入的JS-->
<!-- DataTables --各位需要的自行到網上下載>
<script src="~/jslib/datatables/jquery.dataTables.min.js"></script>
<script src="~/jslib/datatables/dataTables.bootstrap4.min.js"></script>
<script src="~/jslib/datatables/extensions/Select/js/dataTables.select.min.js"></script>
<script src="~/jslib/datatables/extensions/Select/js/select.bootstrap4.min.js"></script>
<script src="~/jslib/datatables/extensions/Buttons/js/dataTables.buttons.min.js"></script>
<script src="~/jslib/datatables/extensions/Buttons/js/buttons.bootstrap4.min.js"></script>
<script src="~/jslib/jszip/jszip.min.js"></script>
<script src="~/jslib/pdfmake/pdfmake.min.js"></script>
<script src="~/jslib/pdfmake/vfs_fonts.js"></script>
<script src="~/jslib/datatables/extensions/Buttons/js/buttons.html5.min.js"></script>
<script src="~/jslib/datatables/extensions/Buttons/js/buttons.print.min.js"></script>
<script src="~/jslib/datatables/extensions/Buttons/js/buttons.colVis.min.js"></script>
<script src="~/jslib/datatables/extensions/PipeliningData/PipeliningData.js"></script>
<!-- Datetimepicker -->
<script src="~/jslib/moment/min/moment.min.js"></script>
<script src="~/jslib/moment/min/moment-with-locales.min.js"></script>
<script src="~/jslib/tempusdominus-bootstrap-4/js/tempusdominus-bootstrap-4.min.js"></script>
<script src="~/bower/select2/js/select2.min.js"></script>
<!--JS-->
<script>
$().ready(function () {
$("#btnAddPractice").on("click", function () {
document.location.href = '@Url.Action("action1", "controller1")';
});
$("#btnNewAddPractice").on("click", function () {
document.location.href = '@Url.Action("action2", "controller1")';
});
//加載 datatables
LoadDatatables();
$(".addButton>span").attr("class", "fas fa-plus");
$(".filterButton>span").attr("class", "fas fa-filter");
$(".clearFilterButton>span").attr("class", "fas fa-times");
$('#btnSetfilter').on('click', function () {
$("#advSearchModal").fadeOut('slow').modal('hide');//.modal('hide');
LoadDatatables(); //按條件查詢
});
$('#btnResetFilter').on('click', function () {
document.getElementById("advSearchForm").reset();
LoadDatatables();//重置條件查詢
})
});
function LoadDatatables() {
// datatables --begin------
// Apply the search
var serviceTable = $("#servicelist").DataTable({
"stateSave": false,//true 表示記錄了狀態，例如datatables當前page number 是第2頁，然後離開.假如再回到此頁時, 會默認到該頁。還有每頁{X}項結果等也會記錄
"deferRender": true,//当处理大数据时，延迟渲染数据，有效提高Datatables处理能力
'searching': true,
"scrollX": false,
"bDestroy": true,//**允許重復生成datatable
"order": [[0, "desc"]],
"ordering": true,
"pagination": true,
"lengthChange": true,
"lengthMenu": [[5, 10, 20, 50, 100], [5, 10, 20, 50, 100]],
"pageLength": 10,
"processing": true,
"search": {
return: true
},
"serverSide": true,
"ajax": $.fn.dataTable.pipeline({//**應用管道緩存，減少分頁操作去后臺獲取數據的頻率（注：正常是一頁AJAX去拿一次；應用之后，就是下面參數{pages}頁去拿一次）
"url": "@Url.Action("GetData", "Controller1")",
"pages": 30, // number of pages to cache(即有多少頁的recores 會被緩存，超過后就會去后臺AJAX獲取)
"type": "POST",
"data": function (d) {
//add the advance search form criterial
let formData = new FormData(document.getElementById('advSearchForm'));
var jsonData = {};
formData.forEach((value, key) => jsonData[key] = value);
return $.extend({}, d, jsonData);
}
}),
"columns":
[
{
data: 'CtrCode',
render: function (data, type, row, meta) {
return row.ctrCode;
},
searchable: false
},
{
data: 'CaseNo',
render: function (data, type, row, meta) {
return row.caseNo;
},
searchable: true
},
{
data: 'CaseName',
render: function (data, type, row, meta) {
return row.caseName;
},
searchable: true
},
{
data: 'TrainingTypeDesc',
render: function (data, type, row, meta) {
return row.trainingTypeDesc;
},
searchable: true
},
{
data: 'SessionStartDt',
render: function (data, type, row, meta) {
return formatDate(row.sessionStartDt);
},
searchable: false
},
{
data: 'AttendStatusDesc',
render: function (data, type, row, meta) {
return row.attendStatusDesc;
},
searchable: false
},
{
data: 'TrainingPlaceDesc',
render: function (data, type, row, meta) {
return row.trainingPlaceDesc;
},
searchable: true
}],
"columnDefs": [
{ "visible": false, "targets": 0 }//,
//{ "visible": false, "targets": 4 },
],
"dom": "<'row'<'col-md-9 text-danger msgBox'B><'col-md-3'f>>" + //B :button; f:filter
"<'row'<'col-md-6'><'col-md-6'>>" +
"<'row'<'col-md-12'tr>>" + //t指table,r 處理中消息
"<'row'<'col-md-4'i><'col-md-3 text-align:right'l><'col-md-5'p>> ", //i : info; l: 每頁記錄數下拉框；p :分頁
"buttons": [
{
text: ' 新增/更改',
className: 'btn btn-success addButton mr-2',
action: function () {
$('#btnNewAddPractice').click();
}
},
{
className: 'btn btn-default',
extend: 'collection',
text: '匯出',
buttons: [
{
extend: 'excelHtml5',
action: exportAllRow,
exportOptions: {
columns: [0, 1, 2, 3, 4, 5, 6]
}
},
{
extend: 'print',
action: exportAllRow,
exportOptions: {
columns: [0, 1, 2, 3, 4, 5, 6]
}
}
]
},
{
extend: 'colvis',
className: 'btn btn-default mr-2'
},
{
text: ' 篩選',
className: 'btn btn-default filterButton mr-2',
action: function () {
$('#btnadvSearch').click();
}
},
{
text: ' 清除篩選項',
className: 'btn btn-default clearFilterButton mr-2',
action: function () {
$('#btnResetFilter').click();
}
}],
"language": {
"processing":'<span class="fa fa-spinner fa-spin fa-3x text-info"></span><span class="text-info" style="margin-left:5px;font-size:larger;font-weight:bold">處理中...</span>',// "處理中...",
"loadingRecords": "載入中...",
"lengthMenu": "每頁 _MENU_ 項結果",
"emptyTable": "沒有記錄",
"zeroRecords": "沒有符合的結果",
"info": "顯示第 _START_ 至 _END_ 項結果，共 _TOTAL_ 項",
"infoEmpty": "顯示第 0 至 0 項結果，共 0 項",
"infoFiltered": "(從 _MAX_ 項結果中過濾)",
"infoPostFix": "",
"search": "搜尋:",
"paginate": {
"first": "第一頁",
"previous": "上一頁",
"next": "下一頁",
"last": "最後一頁"
},
"aria": {
"sortAscending": ": 升冪排列",
"sortDescending": ": 降冪排列"
},
"buttons": {
"csv": "純文字",
"print": "列印",
"colvis": "欄位顯示"
}
}
});
//如果當前頁超過 總頁數，則回第1頁
serviceTable.on('draw', function () {
if (serviceTable.page.info().start > serviceTable.page.info().recordsTotal) {
serviceTable.page(0).draw(false);
}
});
// datatables --end------
}
function exportAllRow(e, dt, button, config) {
// Call the original action function
let rowsAmt = dt.page.info().recordsDisplay;
if (rowsAmt > 10000) {//所有数据的总量
if (!confirm(`匯出或列印的記錄數量較多( ${rowsAmt} 條記錄)
您確定繼續嗎？`)) {
return;
}
}
var self = this;
var oldStart = dt.settings()[0]._iDisplayStart;
dt.one('preXhr', function (e, s, data) {
// Just this once, load all data from the server...
data.start = 0;
data.length = 5000000;//note : int type, 最大值約21億。 該數會乘以上面 "ajax": $.fn.dataTable.pipeline --> "pages"，所以要注意不要大于 int max number，否則超過會被重置為0.
dt.one('preDraw', function (e, settings) {
if (button[0].className.indexOf('buttons-copy') >= 0) {
$.fn.dataTable.ext.buttons.copyHtml5.action.call(self, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-excel') >= 0) {
$.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ?
$.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config) :
$.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-csv') >= 0) {
$.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ?
$.fn.dataTable.ext.buttons.csvHtml5.action.call(self, e, dt, button, config) :
$.fn.dataTable.ext.buttons.csvFlash.action.call(self, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-pdf') >= 0) {
$.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ?
$.fn.dataTable.ext.buttons.pdfHtml5.action.call(self, e, dt, button, config) :
$.fn.dataTable.ext.buttons.pdfFlash.action.call(self, e, dt, button, config);
} else if (button[0].className.indexOf('buttons-print') >= 0) {
setTimeout(function () { $.fn.dataTable.ext.buttons.print.action(e, dt, button, config); }, 0);
}
dt.one('preXhr', function (e, s, data) {
// DataTables thinks the first item displayed is index 0, but we're not drawing that.
// Set the property to what it was before exporting.
settings._iDisplayStart = oldStart;
data.start = oldStart;
});
// Reload the grid with the original page. Otherwise, API functions like table.cell(this) don't work properly.
setTimeout(dt.ajax.reload, 0);
// Prevent rendering of the full data to the DOM
return false;
});
});
// Requery the server with the new one-time export settings
dt.ajax.reload();
};
function formatDate(sdate) {
var initDate = sdate;
try {
if (typeof (sdate) == "string")
sdate = sdate.replace(/\-/g, "/");//trans to / format
sdate = sdate.replace(/T/g, " ");
var date = new Date(sdate);
var myyear = date.getFullYear();
var mymonth = date.getMonth() + 1;
var myweekday = date.getDate();
var myhour = date.getHours();
var mymin = date.getMinutes();
var mysec = date.getSeconds();
if (mymonth < 10) {
mymonth = "0" + mymonth;
}
if (myweekday < 10) {
myweekday = "0" + myweekday;
}
if (myhour < 10) {
myhour = "0" + myhour;
}
if (mymin < 10) {
mymin = "0" + mymin;
}
if (mysec < 10) {
mysec = "0" + mysec;
}
return (myyear + "-" + mymonth + "-" + myweekday + " " + myhour + ":" + mymin);
}
catch (e) {
return initDate;
}
}
</script>
}
第二部分：Server Side : Controller action & Class
[HttpPost]
public IActionResult GetData(SearchViewModel searchVM)
{
try
{
var orderColumn = searchVM.columns[searchVM.order[0].column].data;//datables傳過來排序的字段
if (ModelState.IsValid)
{
//step1:以下是獲取按條件查詢的數據,各自不一樣，僅作參考、記錄^-^,begin
SearchViewModel _searchVM = new SearchViewModel();
var related = _Service.SearchPractices(searchVM.Criteria);
_searchVM = new SearchViewModel( _utilityService, related);
//end
//如果datatables 有過濾框內容。再對上面step1的結果進行過濾。當然，也可以跟step1合在一起進行數據獲取
if (!string.IsNullOrWhiteSpace(searchVM.search?.value)) //has search filter in datatables
{
//以下searchVM.columns.Where(x=>x.searchable==true).Select(x => x.data).ToList() ,就是datatables 傳過來的 需要進行過濾的字段s
var expression = GetDynamicExpression_Or<SearchViewModel.SearchModel>(searchVM.columns.Where(x=>x.searchable==true).Select(x => x.data).ToList(), false, searchVM.search?.value);
_searchVM.VMs = _searchVM.VMs.Where(expression);
}
return Json(new
{
draw = searchVM.draw,//datatables 需要的
recordsTotal = _searchVM.VMs.Count(),//datatables 需要的，記錄總數
recordsFiltered = _searchVM.VMs.Count(),//datatables 需要的
data = _searchVM.VMs.OrderBy(orderColumn, searchVM.order[0].dir).Skip(searchVM.start).Take(searchVM.length //***返回分頁的數據
});
}
else
{
return Json(new
{
draw = searchVM.draw,
recordsTotal = 0,
recordsFiltered = 0,
data = new List<SearchViewModel>()
});
}
}
catch (Exception ex)
{
return View("viewName");
}
}
// 動態生成 linq or 表達式。就不用hard code 按字段過濾
private Expression<Func<T,bool>> GetDynamicExpression_Or<T>(List<string> columns,bool defalutVale,string searchVal)
{
Expression<Func<T, bool>> expressionFinal = x => defalutVale;
InvocationExpression invocation;
foreach (var column in columns)
{
Expression<Func<T, bool>> newExpression = x => Convert.ToString(x.getPropertyValueByPropertyName(column, false)).Contains(searchVal);
invocation = Expression.Invoke(expressionFinal, newExpression.Parameters.Cast<Expression>());
BinaryExpression binary = Expression.Or(newExpression.Body, invocation);
expressionFinal = Expression.Lambda<Func<T, bool>>(binary, newExpression.Parameters);
}
return expressionFinal;
}
//以下是datatables ajax post 類：這個很重要，是datatables 傳過來的參數，意思從字面上很容易理解
public class DataTableAjaxPostModel
{
// properties are not capital due to json mapping
public int draw { get; set; }
public int start { get; set; }
public int length { get; set; }
public List<Column> columns { get; set; }
public Search search { get; set; }
public List<Order> order { get; set; }
}
public class Column
{
public string data { get; set; }
public string name { get; set; }
public bool searchable { get; set; }
public bool orderable { get; set; }
public Search search { get; set; }
}
public class Search
{
public string value { get; set; }
public string regex { get; set; }
}
public class Order
{
public int column { get; set; }
public string dir { get; set; }
}
//*****下面是 獲取數據的類，我們不一樣^-^，供作參考記錄
public class SearchViewModel: DataTableAjaxPostModel
{
private IUtilityService _utilityService;
// General use
public SearchPracticeViewModel(IUtilityService utilityService,IQueryable<SearchResultModel> recs)
{
_utilityService = utilityService;
Criteria = new SearchCriteriaModel();
InitData(recs);
}
#region Properties
public SearchCriteriaModel Criteria { get; set; } //查詢條件
public IQueryable<SearchModel> VMs { get; set; }
#endregion
public class SearchModel //返回的結果model
{
public int PracticeId { get; set; }
public string TrainingPlace { get; set; }
public string PracticeNo { get; set; }
public DateTime? PracticeDt { get; set; }
public string CaseNo { get; set; }
public string CaseName { get; set; }
public string School { get; set; }
public string ProgramNo { get; set; }
public string ProgramName { get; set; }
public string TrainingType { get; set; }
public string AttendStatus { get; set; }
public string TrainingTypeDesc { get; set; }
public string AttendStatusDesc { get; set; }
public DateTime SessionStartDt { get; set; }
public DateTime SessionEndDt { get; set; }
public string StaffBy { get; set; }
public string CtrCode { get; set; }
public string TrainingPlaceDesc { get; set; }
}
private void InitData(IQueryable<SearchResultModel> recs)
{
...獲取數據 code...
}
}
}
139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
