jQuery DataTable 1.10.24对接服务端数据实现客户端分页搜索方案咨询

首先明确两个核心前提：

1. 如果你开启了serverSide: true，所有分页、排序、搜索逻辑都必须由服务端实现，DataTable本身不会做客户端处理，这是官方定义的规则
2. 如果你想一次性拿到所有服务端数据后由DataTable客户端处理分页、排序、搜索，不需要开serverSide参数，直接把所有数据赋值给data配置项即可

你当前代码的核心问题 #

• 搜索逻辑是精确匹配，官方默认是模糊匹配：你代码里判断m.DomainName == searchValue，和官网输入部分字符就匹配的效果不符
• 控制器Action路径不匹配：JS里ajax url填的是/Domain/GetDomainData，但你控制器方法名是GetData，请求会404
• recordsTotal和recordsFiltered赋值错误：recordsTotal是未经过任何过滤的全量数据总数，recordsFiltered是搜索过滤后符合条件的数据总数，你现在两个都赋值为过滤后的数量，会导致分页总数显示异常
• 仓储层直接查询全量数据返回，数据量大的时候性能会非常差，建议把排序、搜索、分页逻辑下推到数据库查询层，不要在内存中处理

修改后的代码 #

1. 如果你要保留服务端处理模式（推荐数据量大的场景） #

JS代码调整

$(document).ready(function () {
    $("#DomainDatatable").DataTable({
        "processing": true,
        "serverSide": true,
        "orderMulti": false,
        "pageLength": 10,
        "deferRender": true,
        "scrollY": 500,
        "scrollCollapse": true,
        "scroller": true,
        // 路径和控制器方法对齐
        "ajax": {
            "url": "/Domain/GetData",
            "type": "POST",
            "datatype": "json"
        },
        "columnDefs": [{
            "targets": [0],
            "visible": false,
            "searchable": false
        },
        {
            "targets": [1],
            "searchable": true,
        },
        {
            "targets": [2],
            "orderable": true
        },
        {
            "targets": [3],
            "searchable": true,
            "orderable": false
        },
        {
            "targets": [4],
            "searchable": true,
            "orderable": false
        },
        ],
        "columns": [
            { "data": "id", "name": "Id", "autoWidth": true },
            { "data": "domainName", "name": "DomainName", "autoWidth": true, "searchable": true },
            {
                data: "createdOn", "name": "CreatedOn", "autoWidth": true,
                "render": function (value) {
                    if (value === null) return "";
                    return moment(value).format('D-MMM-YYYY hh:mm');
                }
            },
            { "data": "isActive", "name": "IsActive", "autoWidth": true },
            { "data": "createdByName", "name": "CreatedByName", "autoWidth": true },
            {
                data: "updatedOn", "name": "UpdatedOn", "autoWidth": true,
                "render": function (value) {
                    if (value === null) return "";
                    return moment(value).format('D-MMM-YYYY hh:mm');
                }
            },
            { "data": "updatedByName", "name": "UpdatedByName", "autoWidth": true },
            {
                "render": function (data, type, full, meta) {
                    return (
                        "<button class='btn btn-default btn-xs' style='display:flex;flex-flow:row nowrap;justify-content:center;' onClick='EditForm(" + full.id + ",\"" + full.domainName + "\",\"" + full.isActive + "\" );'>Edit</button>"
                    );
                }
            },
        ]
    });
});

控制器代码调整

public async Task<JsonResult> GetData()
{
    try
    {
        var draw = Request.Form["draw"].FirstOrDefault();
        var start = Request.Form["start"].FirstOrDefault();
        var length = Request.Form["length"].FirstOrDefault();
        var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
        var sortColumnDir = Request.Form["order[0][dir]"].FirstOrDefault();
        var searchValue = Request.Form["search[value]"].FirstOrDefault();
        int pageSize = length != null ? Convert.ToInt32(length) : 10;
        int skip = start != null ? Convert.ToInt32(start) : 0;
        int recordsTotal = 0;
        int recordsFiltered = 0;
        
        // 先查全量数据总条数
        recordsTotal = await _iUnitOfWork.DomainRepository.GetTotalCount();
        // 查过滤后的数据
        var domain_data = await _iUnitOfWork.DomainRepository.GetFilteredData(sortColumn, sortColumnDir, searchValue);
        recordsFiltered = domain_data.Count();
        // 分页
        var data = domain_data.Skip(skip).Take(pageSize).ToList();
        
        return Json(new { draw = draw, recordsFiltered = recordsFiltered, recordsTotal = recordsTotal, data = data });
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

仓储层代码调整

这里把模糊匹配、排序逻辑放到数据库查询，避免全量加载到内存

// 新增全量计数方法
public async Task<int> GetTotalCount()
{
    return await DataContext.domains.CountAsync();
}

// 新增过滤查询方法
public async Task<IEnumerable<M.DomainModel>> GetFilteredData(string sortColumn, string sortDir, string searchValue)
{
    var query = DataContext.domains.Include(a=>a.FKCreatedBy).Include(a=>a.FkUpdatedBy).AsQueryable();
    // 搜索逻辑改为模糊匹配
    if (!string.IsNullOrEmpty(searchValue))
    {
        query = query.Where(m => m.Name.Contains(searchValue)
        || m.FKCreatedBy.UserName.Contains(searchValue) 
        || (m.IsActive == true ? "Yes" : "No").Contains(searchValue));
    }
    // 排序
    if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortDir))
    {
        query = query.OrderBy($"{sortColumn} {sortDir}");
    }
    // 映射返回
    return await query.Select(x => new M.DomainModel()
    {
        Id = x.Id,
        DomainName = x.Name,
        IsActive = x.IsActive == true ? "Yes" : "No",
        CreatedOn = x.CreatedOn,
        FkCreatedById = x.FKCreatedById,
        CreatedByName = x.FKCreatedBy.UserName,
        UpdatedOn = x.UpdatedOn,
        FKUpdatedById = x.FKUpdatedById,
        UpdatedByName = x.FkUpdatedBy == null ? "" : x.FkUpdatedBy.UserName,
    }).ToListAsync();
}

2. 如果你要使用客户端处理模式（适合数据量小的场景，1000条以内） #

直接关闭serverSide，一次性加载所有数据，分页排序搜索都由DataTable自动处理，不需要改服务端原有逻辑

$(document).ready(function () {
    // 先请求所有数据
    $.post("/Domain/GetAllDomainData", function(allData){
        $("#DomainDatatable").DataTable({
            "processing": true,
            // 关闭服务端模式，所有逻辑客户端处理
            "serverSide": false,
            "orderMulti": false,
            "pageLength": 10,
            "deferRender": true,
            "scrollY": 500,
            "scrollCollapse": true,
            "scroller": true,
            // 直接赋值全量数据
            "data": allData,
            "columnDefs": [{
                "targets": [0],
                "visible": false,
                "searchable": false
            },
            {
                "targets": [1],
                "searchable": true,
            },
            {
                "targets": [2],
                "orderable": true
            },
            {
                "targets": [3],
                "searchable": true,
                "orderable": false
            },
            {
                "targets": [4],
                "searchable": true,
                "orderable": false
            },
            ],
            "columns": [
                { "data": "id", "name": "Id", "autoWidth": true },
                { "data": "domainName", "name": "DomainName", "autoWidth": true, "searchable": true },
                {
                    data: "createdOn", "name": "CreatedOn", "autoWidth": true,
                    "render": function (value) {
                        if (value === null) return "";
                        return moment(value).format('D-MMM-YYYY hh:mm');
                    }
                },
                { "data": "isActive", "name": "IsActive", "autoWidth": true },
                { "data": "createdByName", "name": "CreatedByName", "autoWidth": true },
                {
                    data: "updatedOn", "name": "UpdatedOn", "autoWidth": true,
                    "render": function (value) {
                        if (value === null) return "";
                        return moment(value).format('D-MMM-YYYY hh:mm');
                    }
                },
                { "data": "updatedByName", "name": "UpdatedByName", "autoWidth": true },
                {
                    "render": function (data, type, full, meta) {
                        return (
                            "<button class='btn btn-default btn-xs' style='display:flex;flex-flow:row nowrap;justify-content:center;' onClick='EditForm(" + full.id + ",\"" + full.domainName + "\",\"" + full.isActive + "\" );'>Edit</button>"
                        );
                    }
                },
            ]
        });
    });
});

控制器新增返回全量数据的方法即可：

public async Task<JsonResult> GetAllDomainData()
{
    var data = await _iUnitOfWork.DomainRepository.GetData();
    return Json(data);
}

内容的提问来源于stack exchange，提问作者Ubaid Ur Rehman