前端代码
<table class="ui celled padded table" id="job-list">
<thead>
<tr>
<th>Id</th>
<th>JobName</th>
<th>JobGroup</th>
<th>JobType</th>
<th>Result</th>
<th>Message</th>
<th style="min-width: 160px">ScheduledFireTime</th>
<th style="min-width: 160px">ActualFireTime</th>
<th style="min-width: 160px">FinishedTime</th>
<th style="min-width: 160px">JobRunTime</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<link rel="stylesheet" href="Content/Lib/Datatables/dataTables.semanticui.min.css" />
<script src="Content/Lib/Datatables/jquery.dataTables.min.js"></script>
<script src="Content/Lib/Datatables/dataTables.semanticui.min.js"></script>
<script>
$(document).ready(function () {
$('#job-list').DataTable({
pageLength: 10,//每页显示10条数据
searchDelay: 450,
searching: true,
serverSide: true, //启用服务器端分页,要进行后端分页必须的环节
ajax: function (data, callback, settings) {
//封装相应的请求参数,这里获取页大小和当前页码
var pagesize = data.length;//页面显示记录条数,在页面显示每页显示多少项的时候,页大小
var start = data.start;//开始的记录序号
var page = (data.start) / data.length + 1;//当前页码
var pagination = {
KeyWords: data.search.value,
page: page,
pagesize: pagesize,//这里只传了当前页和页大小,如果有其他参数,可继续封装
}
console.log(data);
//var json = {
// pagination: JSON.stringify(data)
//}
$.ajax({
type: "POST",
url: "/test/SFAresults/GetData",
cache: false, //禁用缓存
data: pagination, //传入已封装的参数
dataType: "json",//返回数据格式为json
success: function (data) {
var arr = "";
if ('object' == typeof data) {
arr = data;
} else {
arr = $.parseJSON(data);//将json字符串转化为了一个Object对象
}
console.log("============数据==========")
console.log(arr)
var returnData = {};
//returnData.draw = arr.data.pagination.pageCount;//这里直接自行返回了draw计数器,应该由后台返回,没什么卵用!
returnData.recordsTotal = arr.totalCount;//totalCount指的是总记录数
returnData.recordsFiltered = arr.totalCount;//后台不实现过滤功能,全部的记录数都需输出到前端,记录数为总数
returnData.data = arr.DataList;//返回汽车列表
console.log("======returnData.data=======")
console.log(returnData.recordsTotal)
console.log(returnData.recordsFiltered)
callback(returnData);//这个别忘了!!!
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
console.log(textStatus,"查询失败", XMLHttpRequest);
}
});
},
columns: [
{ "data": "Id" },
{ "data": "JobName" },
{ "data": "JobGroup" },
{ "data": "JobType" },
{ "data": "Result" },
{ "data": "Message" },
{ "data": "ScheduledFireTime" },
{ "data": "ActualFireTime" },
{ "data": "FinishedTime" },
{ "data": "JobRunTime" },
]
});
});
</script>
后端代码
[HttpGet, HttpPost]
public async Task<IActionResult> GetData(pagination p)
{
using (IDbConnection conn = new MySqlConnection(DBHelper.connStr))
{
int offset = (p.page - 1) * p.pagesize;
var param = new { @limit = p.pagesize, @offset = offset, @KeyWords = "%" + p.KeyWords + "%" };
string condition = "where 1=1 ";
if (!string.IsNullOrEmpty(p.KeyWords))
{
offset = 0;
condition += " and (JobName like @KeyWords or JobGroup like @KeyWords or JobType like @KeyWords or Result like @KeyWords or Message like @KeyWords)";
}
string query = $"select * from my_sfaresults {condition} limit @limit offset @offset";
string queryCount = $"select count(Id) from my_sfaresults {condition}";
List<dynamic> queryList = conn.Query<dynamic>(query, param).ToList();
p.DataList = queryList;
p.totalCount = conn.ExecuteScalar<int>(queryCount, param);
}
return Json(p);
}