在使用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")';});//加載 datatablesLoadDatatables(); $(".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 searchvar 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 criteriallet 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 DOMreturn 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 / formatsdate = 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 傳過來的 需要進行過濾的字段svar 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 mappingpublic 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 usepublic SearchPracticeViewModel(IUtilityService utilityService,IQueryable<SearchResultModel> recs){
_utilityService = utilityService; Criteria = new SearchCriteriaModel(); InitData(recs);}#region Propertiespublic SearchCriteriaModel Criteria {
get; set; } //查詢條件public IQueryable<SearchModel> VMs {
get; set; } #endregionpublic 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...} }
}