ASPX页面AJAX调用C#方法实现DataTable数据加载问题咨询

我想创建一个包含AJAX调用的ASPX页面，调用C#后台方法查询数据库，返回的数据用来实例化jQuery的DataTable对象。目前已经编写了部分代码，但不知道如何继续，查阅资料也无法理解具体操作。我需要明确两个核心问题：

1. AJAX调用需要返回什么格式的对象/变量，才能让DataTable正常实例化？
2. 为什么当前代码中AJAX无法调用GetData函数？

现有代码 #

JavaScript代码 #

var pageUrl = '<%=ResolveUrl("~/Sph_table_00.aspx/GetData")%>';
$(function () {
new DataTable('#example', {
    ajax: {
        "type": 'POST',
        "url": pageUrl,
        "contentType": 'application/json; charset=utf-8',
        "dataType": 'json',
        "success": function (msg) {
            alert("Success: " + msg.d);
            
            //EXAMPLE
            let out = [];
            for (var i = 0; i < 5; i++) {
                out.push([i + '-1', i + '-2']);
            }

            setTimeout(() => {
                callback({
                    draw: data.draw,
                    data: out,
                    recordsTotal: 5000000,
                    recordsFiltered: 5000000
                });
            }, 150);
        }
    },
    processing: true,
    ordering: false,
    scroller: true,
    scrollY: 200,
    searching: false,
    serverSide: true
});
}

C#代码 #

public partial class Sph_table_00 : System.Web.UI.Page
{
    [WebMethod]
    public void GetData()
    {
    if (Common.openConnOLEADODB(OLEConnectionSPHERA) != null)
{
    OLEConnectionSPHERA = Common.openConnOLEADODB(OLEConnectionSPHERA);

    string tabella = "";
    string titolo = "";
    string reparto = "";
    string p1 = "";
    string p2 = "";
    string p3 = "";
    string p4 = "";
    string p5 = "";

    Uri uri = new Uri(HttpContext.Current.Request.Url.AbsoluteUri);

    if (HttpUtility.ParseQueryString(uri.Query).Get("TABELLA") != null)
        tabella = HttpUtility.ParseQueryString(uri.Query).Get("TABELLA");

    if (HttpUtility.ParseQueryString(uri.Query).Get("TITOLO") != null)
        titolo = HttpUtility.ParseQueryString(uri.Query).Get("TITOLO");

    if (HttpUtility.ParseQueryString(uri.Query).Get("REPARTO") != null)
        reparto = HttpUtility.ParseQueryString(uri.Query).Get("REPARTO");

    if (HttpUtility.ParseQueryString(uri.Query).Get("P1") != null)
        p1 = HttpUtility.ParseQueryString(uri.Query).Get("P1");

    if (HttpUtility.ParseQueryString(uri.Query).Get("P2") != null)
        p2 = HttpUtility.ParseQueryString(uri.Query).Get("P2");

    if (HttpUtility.ParseQueryString(uri.Query).Get("P3") != null)
        p3 = HttpUtility.ParseQueryString(uri.Query).Get("P3");

    if (HttpUtility.ParseQueryString(uri.Query).Get("P4") != null)
        p4 = HttpUtility.ParseQueryString(uri.Query).Get("P4");

    if (HttpUtility.ParseQueryString(uri.Query).Get("P5") != null)
        p5 = HttpUtility.ParseQueryString(uri.Query).Get("P5");

    if (tabella == "" && titolo == "")
    {
        Response.Write("Nessun parametro");
        return;
    }

    if (reparto != "")
        Session["REPARTO"] = reparto;
    else if (reparto == "Azienda")
        reparto = "";
    else
        reparto = Session["REPARTO"].ToString();

    string queryMaster = $"select * from WEB_QUERY where tablename = '{tabella}'";

    if (Common.executeQuery(queryMaster, OLEConnectionSPHERA) != null)
        oleDbDataReader_QUERY = Common.executeQuery(queryMaster, OLEConnectionSPHERA);

    while (oleDbDataReader_QUERY.Read())
    {
        string query = oleDbDataReader_QUERY.GetValue(oleDbDataReader_QUERY.GetOrdinal("QUERYSELECT")).ToString().Replace("@PREP", reparto);
        //string query = "SELECT * from s_odl";

        if (Common.executeQuery(query, OLEConnectionSPHERA) != null)
            oleDbDataReader_CDL = Common.executeQuery(query, OLEConnectionSPHERA);

        for (int i = 0; i < oleDbDataReader_CDL.FieldCount; i++)
            ListaNomiColonne.Add(oleDbDataReader_CDL.GetName(i).ToString());

        while (oleDbDataReader_CDL.Read())
        {
            foreach (string field in ListaNomiColonne)
            {
                ListaDati.Add(oleDbDataReader_CDL.GetValue(oleDbDataReader_CDL.GetOrdinal(field)).ToString());
            }
        }
        oleDbDataReader_CDL.Dispose();
        oleDbDataReader_CDL.Close();
    }

    oleDbDataReader_QUERY.Dispose();
    oleDbDataReader_QUERY.Close();

    if (Common.closeConnOLEADODB(OLEConnectionSPHERA) != null)
        OLEConnectionSPHERA = Common.closeConnOLEADODB(OLEConnectionSPHERA);
}
    }
}

HTML代码 #

<table id="example" style="width:100%" class="table table-striped display nowrap">
<thead>
    <tr>
        <th>ID</th>
        <th>First name</th>
    </tr>
</thead>
</table>

问题分析与解决方案 #

1. 为什么AJAX无法调用GetData函数？ #

• WebMethod定义错误：ASP.NET WebMethod默认要求为static方法，否则无法被AJAX直接调用；同时当前方法返回类型为void，无法返回数据给前端。
• Session访问未授权：代码中读取了Session，但未给WebMethod添加EnableSession = true特性，导致无法访问Session。
• 响应格式破坏：使用Response.Write输出内容，会破坏WebMethod默认的JSON响应格式，导致前端无法解析。
• 参数不匹配：开启serverSide: true后，DataTable会自动发送分页、draw等参数，但当前GetData方法未接收这些参数，无法处理服务器端分页逻辑。

2. DataTable所需的返回格式 #

当启用serverSide: true时，DataTable要求后台返回固定结构的JSON对象，必填字段包括：

• draw：对应前端发送的draw参数，用于匹配请求与响应
• recordsTotal：未过滤的总记录数
• recordsFiltered：过滤后的记录数（无过滤逻辑时与recordsTotal一致）
• data：当前页的数据，格式为二维数组（每个子数组对应一行数据）或对象数组（需配合columns的data属性配置）

修改后的完整代码 #

修改后的C#代码 #

public partial class Sph_table_00 : System.Web.UI.Page
{
    // 启用Session访问，标记为静态方法
    [WebMethod(EnableSession = true)]
    public static object GetData(int draw, int start, int length)
    {
        OleDbConnection OLEConnectionSPHERA = null;
        OleDbDataReader oleDbDataReader_QUERY = null;
        OleDbDataReader oleDbDataReader_CDL = null;
        List<string> ListaNomiColonne = new List<string>();
        List<List<string>> ListaDati = new List<List<string>>(); // 二维列表存储行数据

        try
        {
            OLEConnectionSPHERA = Common.openConnOLEADODB(OLEConnectionSPHERA);
            if (OLEConnectionSPHERA == null)
            {
                return new { draw = draw, recordsTotal = 0, recordsFiltered = 0, data = new List<List<string>>() };
            }

            // 获取查询参数
            var queryString = HttpContext.Current.Request.QueryString;
            string tabella = queryString.Get("TABELLA") ?? "";
            string titolo = queryString.Get("TITOLO") ?? "";
            string reparto = queryString.Get("REPARTO") ?? "";

            if (string.IsNullOrEmpty(tabella) && string.IsNullOrEmpty(titolo))
            {
                return new { draw = draw, recordsTotal = 0, recordsFiltered = 0, data = new List<List<string>>(), error = "Nessun parametro" };
            }

            // 处理Session中的REPARTO
            if (!string.IsNullOrEmpty(reparto))
            {
                HttpContext.Current.Session["REPARTO"] = reparto;
            }
            else if (reparto == "Azienda")
            {
                reparto = "";
            }
            else
            {
                reparto = HttpContext.Current.Session["REPARTO"]?.ToString() ?? "";
            }

            // 注意：此处存在SQL注入风险，建议改用参数化查询
            string queryMaster = $"select * from WEB_QUERY where tablename = '{tabella}'";
            oleDbDataReader_QUERY = Common.executeQuery(queryMaster, OLEConnectionSPHERA);
            if (oleDbDataReader_QUERY == null)
            {
                return new { draw = draw, recordsTotal = 0, recordsFiltered = 0, data = new List<List<string>>() };
            }

            while (oleDbDataReader_QUERY.Read())
            {
                string query = oleDbDataReader_QUERY["QUERYSELECT"].ToString().Replace("@PREP", reparto);
                oleDbDataReader_CDL = Common.executeQuery(query, OLEConnectionSPHERA);
                if (oleDbDataReader_CDL == null) continue;

                // 仅获取一次列名
                if (ListaNomiColonne.Count == 0)
                {
                    for (int i = 0; i < oleDbDataReader_CDL.FieldCount; i++)
                    {
                        ListaNomiColonne.Add(oleDbDataReader_CDL.GetName(i));
                    }
                }

                // 读取行数据
                while (oleDbDataReader_CDL.Read())
                {
                    List<string> row = new List<string>();
                    foreach (string field in ListaNomiColonne)
                    {
                        row.Add(oleDbDataReader_CDL[field]?.ToString() ?? "");
                    }
                    ListaDati.Add(row);
                }

                oleDbDataReader_CDL.Close();
                oleDbDataReader_CDL.Dispose();
            }

            oleDbDataReader_QUERY.Close();
            oleDbDataReader_QUERY.Dispose();

            // 处理分页逻辑
            var filteredData = ListaDati.Skip(start).Take(length).ToList();

            return new
            {
                draw = draw,
                recordsTotal = ListaDati.Count,
                recordsFiltered = ListaDati.Count,
                data = filteredData
            };
        }
        catch (Exception ex)
        {
            return new { draw = draw, recordsTotal = 0, recordsFiltered = 0, data = new List<List<string>>(), error = ex.Message };
        }
        finally
        {
            // 确保数据库连接关闭
            if (OLEConnectionSPHERA != null && OLEConnectionSPHERA.State == ConnectionState.Open)
            {
                OLEConnectionSPHERA.Close();
                OLEConnectionSPHERA.Dispose();
            }
        }
    }
}

修改后的JavaScript代码 #

var pageUrl = '<%=ResolveUrl("~/Sph_table_00.aspx/GetData")%>';
$(function () {
    new DataTable('#example', {
        ajax: {
            type: 'POST',
            url: pageUrl,
            contentType: 'application/json; charset=utf-8',
            dataType: 'json',
            // 将DataTable请求参数序列化为JSON
            data: function (d) {
                return JSON.stringify(d);
            },
            success: function (msg) {
                // 处理错误信息
                if (msg.d.error) {
                    alert("Errore: " + msg.d.error);
                    return;
                }
                // 传递数据给DataTable
                this.success(msg.d);
            }
        },
        processing: true,
        ordering: false,
        scroller: true,
        scrollY: 200,
        searching: false,
        serverSide: true,
        // 配置列与数据的对应关系
        columns: [
            { data: 0 },
            { data: 1 }
        ]
    });
});

关键注意事项 #

• WebMethod必须为静态：ASP.NET WebMethod默认要求静态方法，避免实例化页面的开销。
• Session访问需启用：添加[WebMethod(EnableSession = true)]才能在方法中操作Session。
• SQL注入风险：当前代码直接拼接SQL参数，存在注入风险，建议改用参数化查询。
• 分页逻辑：启用服务器端分页后，必须根据start和length参数返回对应页的数据，否则会返回全部数据，失去分页意义。

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