C# ASP.NET WebForm中DataTables服务端排序与搜索失效求助

原因分析 #

开启serverSide: true后，DataTables的排序、搜索、分页逻辑全部由后端处理，前端仅负责传递参数、渲染结果。你当前的后端LoadData方法没有接收和处理排序、搜索参数，所以不管前端怎么操作，后端都返回相同的分页数据，导致排序、搜索失效。

具体解决步骤 #

1. 后端WebMethod接收并处理DataTables参数 #

首先定义对应DataTables请求结构的实体类，以及返回结果类：

// 接收前端请求的参数类
public class DataTablesRequest
{
    public int draw { get; set; }
    public int start { get; set; }
    public int length { get; set; }
    public Search search { get; set; }
    public List<Order> order { get; set; }
    public List<Column> columns { get; set; }
}

public class Search
{
    public string value { get; set; }
    public bool regex { get; set; }
}

public class Order
{
    public int column { get; set; }
    public string dir { 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 DataTablesResponse<T>
{
    public int draw { get; set; }
    public int recordsTotal { get; set; }
    public int recordsFiltered { get; set; }
    public List<T> data { get; set; }
}

// 你的业务数据实体
public class CDLData
{
    public string CODCDL { get; set; }
    public string DESCDL { get; set; }
}

然后修改LoadData方法，处理排序、搜索、分页逻辑：

[WebMethod]
public static DataTablesResponse<CDLData> LoadData(DataTablesRequest request)
{
    var response = new DataTablesResponse<CDLData>();
    response.draw = request.draw;
    string tableName = "你的数据表名";
    string connStr = "你的数据库连接字符串";

    // 1. 获取总记录数
    using (var conn = new SqlConnection(connStr))
    {
        conn.Open();
        response.recordsTotal = (int)new SqlCommand($"SELECT COUNT(*) FROM {tableName}", conn).ExecuteScalar();
    }

    // 2. 构造搜索条件（注意用参数化防止SQL注入）
    string whereClause = "";
    List<SqlParameter> parameters = new List<SqlParameter>();
    if (!string.IsNullOrEmpty(request.search.value))
    {
        string searchVal = $"%{request.search.value.Trim()}%";
        whereClause = "WHERE CODCDL LIKE @SearchVal OR DESCDL LIKE @SearchVal";
        parameters.Add(new SqlParameter("@SearchVal", searchVal));
    }

    // 3. 构造排序逻辑
    string sortCol = "CODCDL";
    string sortDir = "ASC";
    if (request.order != null && request.order.Count > 0)
    {
        int colIndex = request.order[0].column;
        sortCol = request.columns[colIndex].name;
        sortDir = request.order[0].dir;
    }

    // 4. 构造分页查询SQL
    string sql = $@"
        SELECT CODCDL, DESCDL 
        FROM {tableName}
        {whereClause}
        ORDER BY {sortCol} {sortDir}
        OFFSET {request.start} ROWS FETCH NEXT {request.length} ROWS ONLY";

    // 5. 获取分页数据
    List<CDLData> dataList = new List<CDLData>();
    using (var conn = new SqlConnection(connStr))
    {
        conn.Open();
        var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddRange(parameters.ToArray());
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                dataList.Add(new CDLData
                {
                    CODCDL = reader["CODCDL"].ToString(),
                    DESCDL = reader["DESCDL"].ToString()
                });
            }
        }
    }

    // 6. 获取过滤后的记录数
    if (!string.IsNullOrEmpty(whereClause))
    {
        using (var conn = new SqlConnection(connStr))
        {
            conn.Open();
            var cmd = new SqlCommand($"SELECT COUNT(*) FROM {tableName} {whereClause}", conn);
            cmd.Parameters.AddRange(parameters.ToArray());
            response.recordsFiltered = (int)cmd.ExecuteScalar();
        }
    }
    else
    {
        response.recordsFiltered = response.recordsTotal;
    }

    response.data = dataList;
    return response;
}

2. 前端代码优化 #

移除冗余配置，简化响应结构处理：

<form id="form1" runat="server">
    <div class="container-fluid">
        <div class="table-responsive">
            <table id="dataTable" class="table table-striped" style="width:100%">
                <thead>
                    <tr><th>CODCDL</th><th>DESCDL</th></tr>
                </thead>
                <tfoot>
                    <tr><th>CODCDL</th><th>DESCDL</th></tr>
                </tfoot>
            </table>
        </div>
    </div>
</form>
<script>
    $(document).ready(function () {
        $('#dataTable').DataTable({
            processing: true,
            serverSide: true,
            pageLength: 10,
            ajax: {
                url: "WebForm1.aspx/LoadData", 
                type: "POST",
                contentType: "application/json",
                dataType : "json",
                data: function (d) {
                    return JSON.stringify(d);
                },
                dataSrc: function (data) {
                    // 直接映射后端返回的结构
                    return {
                        draw: data.d.draw,
                        recordsTotal: data.d.recordsTotal,
                        recordsFiltered: data.d.recordsFiltered,
                        data: data.d.data
                    };
                }
            },
            columns: [
                { data: "CODCDL", name: "CODCDL" },
                { data: "DESCDL", name: "DESCDL" }
            ]
        });
    });
</script>

注：全局的searchable、sortable配置无效，列级默认开启，若某列需禁用，可在columns中添加searchable: false或orderable: false。

1. 动态列实现 #

思路 #

后端提供接口返回列配置（字段名、标题、可搜索/排序属性），前端先请求配置再初始化DataTables。

后端新增列配置接口

[WebMethod]
public static List<dynamic> GetColumnConfig()
{
    // 可从数据库配置表读取，此处为硬编码示例
    return new List<dynamic>
    {
        new { data = "CODCDL", name = "CODCDL", title = "编码", searchable = true, orderable = true },
        new { data = "DESCDL", name = "DESCDL", title = "描述", searchable = true, orderable = true }
    };
}

前端动态初始化表格

$(document).ready(function () {
    $.ajax({
        url: "WebForm1.aspx/GetColumnConfig",
        type: "POST",
        contentType: "application/json",
        dataType: "json",
        success: function (res) {
            var columns = res.d;
            $('#dataTable').DataTable({
                processing: true,
                serverSide: true,
                pageLength: 10,
                ajax: {
                    url: "WebForm1.aspx/LoadData", 
                    type: "POST",
                    contentType: "application/json",
                    dataType : "json",
                    data: function (d) {
                        return JSON.stringify(d);
                    },
                    dataSrc: function (data) {
                        return {
                            draw: data.d.draw,
                            recordsTotal: data.d.recordsTotal,
                            recordsFiltered: data.d.recordsFiltered,
                            data: data.d.data
                        };
                    }
                },
                columns: columns
            });
        }
    });
});

2. 参数查询实现 #

思路 #

页面添加查询控件，前端将控件值作为自定义参数传给后端，后端追加到SQL条件中。

前端添加参数传递

// 在ajax.data中添加自定义参数
data: function (d) {
    d.customParams = {
        startDate: $("#startDate").val(),
        endDate: $("#endDate").val()
    };
    return JSON.stringify(d);
}

后端接收并处理自定义参数

修改DataTablesRequest类：

public class DataTablesRequest
{
    // 原有字段...
    public CustomParams customParams { get; set; }
}

public class CustomParams
{
    public string startDate { get; set; }
    public string endDate { get; set; }
}

在LoadData方法中追加查询条件：

// 处理自定义日期参数
if (request.customParams != null)
{
    if (!string.IsNullOrEmpty(request.customParams.startDate))
    {
        whereClause += string.IsNullOrEmpty(whereClause) ? "WHERE" : " AND";
        whereClause += $" CREATE_DATE >= @StartDate";
        parameters.Add(new SqlParameter("@StartDate", DateTime.Parse(request.customParams.startDate)));
    }
    if (!string.IsNullOrEmpty(request.customParams.endDate))
    {
        whereClause += string.IsNullOrEmpty(whereClause) ? "WHERE" : " AND";
        whereClause += $" CREATE_DATE <= @EndDate";
        parameters.Add(new SqlParameter("@EndDate", DateTime.Parse(request.customParams.endDate)));
    }
}

内容的提问来源于stack exchange，提问作者Daniele Mura