如何在服务端分页的DataTables中传递自定义参数获取数据

我用DataTables从控制器获取数据，需要传递额外参数从数据库查询记录。发现使用sAjaxSource时，请求会自动携带分页大小、排序等默认参数；但使用显式Ajax配置仅传入accountID时，仅发送该自定义参数，默认参数不会被携带。

最初可正常运行的前端代码 #

$(function () {
    var tableTemplates = $('#Voicemailtable').DataTable({
        sAjaxSource: "@Url.Action(\"VoicemailList\", \"Voicemail\")",
        serverSide: true,
        bServerSide: true,
        bProcessing: true,
        processing: true,
        columns: [
            {
                data: null, // <-- 索引列
                sortable: false,
                autoWidth: false
            },
            {
                data: "context",
                sortable: false,
                autoWidth: false
            },
            {
                data: "created",
                render: function (data) {
                    return moment(data).format('MM/DD/YYYY hh:mm:ss');
                }
            },
            {
                data: "guid",
                sortable: false,
                autoWidth: false
            },
            {
                data: "mailbox",
                sortable: false,
                autoWidth: false
            },
            {
                data: "password",
                sortable: false,
                autoWidth: false
            },
            {
                data: "uniqueid",
                sortable: false,
                autoWidth: false
            }
        ]
    });
    tableTemplates.on('draw.dt', function () {
        var PageInfo = $('#Voicemailtable').DataTable().page.info();
        tableTemplates.column(0, { page: 'current' }).nodes().each(function (cell, i) {
            cell.innerHTML = i + 1 + PageInfo.start;
        });
    });
});

对应的后端代码 #

public ActionResult VoicemailList(JqueryDatatableRequestModel model, string accountId)
{
    var totalRecords = 0;
    var voicemails = voicemailService.GetVoicemailsListByAccountId(accountId, name: model.sSearch, totalCount: out totalRecords, recordStart: model.iDisplayStart, pageSize: model.iDisplayLength);
    var finalResults = Mapper.Map<List<VoicemailModel>>(voicemails);
    return Json(new
    {
        model.sEcho,
        iTotalRecords = totalRecords,
        iTotalDisplayRecords = totalRecords,
        aaData = finalResults
    }, JsonRequestBehavior.AllowGet);
}

尝试添加自定义参数后无法运行的前端代码 #

$(function () {
    var table = $('#Voicemailtable').DataTable();
    var sEcho = table.settings()[0].oAjaxData.sEcho;
    var sSearch = table.search();
    var iDisplayLength = table.page.len();
    var iDisplayStart = table.page.info().start;
    var iSortCol_0 = table.order()[0][0];
    var sSortDir_0 = table.order()[0][1];

    var tableTemplates = $('#Voicemailtable').DataTable({
        sAjaxSource: "@Url.Action(\"VoicemailList\", \"Voicemail\")",
        serverSide: true,
        bServerSide: true,
        bProcessing: true,
        processing: true,
        ajax: {
            url: '@Url.Action(\"VoicemailList\", \"Voicemail\")',
            type: "Get",
            data: {
                accountId: "45454-df454-f4d65f4d54f"
                sEcho: sEcho,
                sSearch: sSearch,
                iDisplayLength: iDisplayLength,
                iDisplayStart: iDisplayStart,
                iSortCol_0: iSortCol_0,
                sSortDir_0: sSortDir_0
            },
        },
        columns: [
            // 列配置与之前一致
            {
                data: null,
                sortable: false,
                autoWidth: false
            },
            {
                data: "context",
                sortable: false,
                autoWidth: false
            },
            {
                data: "created",
                render: function (data) {
                    return moment(data).format('MM/DD/YYYY hh:mm:ss');
                }
            },
            {
                data: "guid",
                sortable: false,
                autoWidth: false
            },
            {
                data: "mailbox",
                sortable: false,
                autoWidth: false
            },
            {
                data: "password",
                sortable: false,
                autoWidth: false
            },
            {
                data: "uniqueid",
                sortable: false,
                autoWidth: false
            }
        ]
    });
    tableTemplates.on('draw.dt', function () {
        var PageInfo = $('#Voicemailtable').DataTable().page.info();
        tableTemplates.column(0, { page: 'current' }).nodes().each(function (cell, i) {
            cell.innerHTML = i + 1 + PageInfo.start;
        });
    });
});

解决方案 #

问题根源在于手动获取参数的方式错误，且同时配置sAjaxSource和ajax选项会产生冲突。正确做法是利用ajax.data的函数形式，在默认参数基础上扩展自定义参数：

修改后的前端代码 #

$(function () {
    var tableTemplates = $('#Voicemailtable').DataTable({
        serverSide: true,
        processing: true,
        ajax: {
            url: '@Url.Action("VoicemailList", "Voicemail")',
            type: "Get",
            // 用函数扩展默认参数，自动保留分页、排序、搜索等参数
            data: function (d) {
                // 添加自定义参数
                d.accountId = "45454-df454-f4d65f4d54f";
            }
        },
        columns: [
            {
                data: null,
                sortable: false,
                autoWidth: false
            },
            {
                data: "context",
                sortable: false,
                autoWidth: false
            },
            {
                data: "created",
                render: function (data) {
                    return moment(data).format('MM/DD/YYYY hh:mm:ss');
                }
            },
            {
                data: "guid",
                sortable: false,
                autoWidth: false
            },
            {
                data: "mailbox",
                sortable: false,
                autoWidth: false
            },
            {
                data: "password",
                sortable: false,
                autoWidth: false
            },
            {
                data: "uniqueid",
                sortable: false,
                autoWidth: false
            }
        ]
    });

    tableTemplates.on('draw.dt', function () {
        var PageInfo = $('#Voicemailtable').DataTable().page.info();
        tableTemplates.column(0, { page: 'current' }).nodes().each(function (cell, i) {
            cell.innerHTML = i + 1 + PageInfo.start;
        });
    });
});

关键说明 #

1. 移除冲突的sAjaxSource配置，以及重复的旧版参数（bServerSide、bProcessing），保留新版的serverSide和processing即可。
2. ajax.data使用函数形式时，DataTables会自动将所有默认服务器端参数（分页、排序、搜索等）传入参数d，只需在该对象上添加自定义参数即可。
3. 无需提前手动获取表格状态，函数每次请求都会拿到最新参数，确保分页、排序、搜索变化时参数同步更新。

内容的提问来源于stack exchange，提问作者Shahreyar Butt