基于带参数存储过程的jQuery Datatables数据获取方案咨询

Hey there! Awesome to hear jQuery DataTables is working well for you—it’s definitely one of the most reliable tools out there for interactive data tables. Let’s tackle your questions head-on and give you practical, working examples to implement what you need.

Short answer: No, you don’t have to build a dedicated, standalone service layer (like WCF or a separate Web API project). Instead, you can use a lightweight backend endpoint directly within your existing project—think an ASP.NET Web Forms handler, an MVC action method, or even a classic ASP page—to handle DataTables’ AJAX requests. This acts as a simple bridge between your frontend and SQL Server, without the overhead of a full service layer.

Let’s walk through a complete example using ASP.NET Web Forms, since it’s common for SQL Server environments. The idea is:

1. Frontend DataTables sends an AJAX request with its standard parameters (page number, search term, etc.) plus your custom business parameters.
2. A backend handler page calls your parameterized stored procedure, fetches the data, and returns it in the JSON format DataTables expects.

Step 1: Backend Handler (DataHandler.aspx) #

This page will process the AJAX request, interact with SQL Server, and send back JSON.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;

public partial class DataHandler : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Grab DataTables' built-in parameters
        int draw = int.Parse(Request["draw"]);
        int start = int.Parse(Request["start"]);
        int length = int.Parse(Request["length"]);
        string searchTerm = Request["search[value]"] ?? string.Empty;
        
        // Your custom parameter (e.g., a category filter from the frontend)
        string selectedCategory = Request["category"] ?? string.Empty;
        
        // Fetch paginated data and total record count
        DataTable results = FetchPaginatedData(start, length, searchTerm, selectedCategory);
        int totalRecords = GetTotalRecordCount(selectedCategory);
        
        // Format response to match DataTables' required structure
        var response = new
        {
            draw = draw,
            recordsTotal = totalRecords,
            recordsFiltered = totalRecords, // Use filtered count if your proc handles that
            data = ConvertDataTableToObjectArray(results)
        };
        
        // Send JSON response
        Response.ContentType = "application/json";
        Response.Write(new JavaScriptSerializer().Serialize(response));
        Response.End();
    }

    private DataTable FetchPaginatedData(int startRow, int pageSize, string searchTerm, string category)
    {
        DataTable dataTable = new DataTable();
        string connectionString = "Your_SQL_Server_Connection_String_Here";
        
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("GetFilteredProductData", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                
                // Add parameters matching your stored procedure
                cmd.Parameters.Add("@StartRow", SqlDbType.Int).Value = startRow;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                cmd.Parameters.Add("@SearchTerm", SqlDbType.NVarChar, 255).Value = searchTerm;
                cmd.Parameters.Add("@ProductCategory", SqlDbType.NVarChar, 50).Value = category;
                
                conn.Open();
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(dataTable);
                }
            }
        }
        return dataTable;
    }

    private int GetTotalRecordCount(string category)
    {
        int total = 0;
        string connectionString = "Your_SQL_Server_Connection_String_Here";
        
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("GetTotalProductCount", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductCategory", SqlDbType.NVarChar, 50).Value = category;
                
                // Add output parameter for total count
                SqlParameter totalParam = new SqlParameter("@TotalCount", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(totalParam);
                
                conn.Open();
                cmd.ExecuteNonQuery();
                total = (int)totalParam.Value;
            }
        }
        return total;
    }

    private object[] ConvertDataTableToObjectArray(DataTable dt)
    {
        var rowArray = new object[dt.Rows.Count];
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var colArray = new object[dt.Columns.Count];
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                colArray[j] = dt.Rows[i][j];
            }
            rowArray[i] = colArray;
        }
        return rowArray;
    }
}

Step 2: Frontend DataTables Initialization #

This JavaScript will initialize your table, send the AJAX request with custom parameters, and render the data.

$(document).ready(function() {
    $('#productTable').DataTable({
        processing: true,
        serverSide: true,
        ajax: {
            url: 'DataHandler.aspx',
            type: 'POST',
            data: function(d) {
                // Pass your custom parameter to the backend
                d.category = $('#categoryFilter').val();
            }
        },
        columns: [
            { data: 'ProductID' },
            { data: 'ProductName' },
            { data: 'Category' },
            { data: 'Price' }
            // Match these to the columns returned by your stored procedure
        ]
    });
});

Here’s what the stored procedures might look like. We’ll use SQL Server 2012’s OFFSET/FETCH for clean pagination (no messy CTEs needed!).

Stored Procedure for Paginated, Filtered Data #

CREATE PROCEDURE GetFilteredProductData
    @StartRow INT,
    @PageSize INT,
    @SearchTerm NVARCHAR(255),
    @ProductCategory NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        ProductID,
        ProductName,
        Category,
        Price
    FROM Products
    WHERE 
        -- Filter by category (ignore if empty)
        (Category = @ProductCategory OR @ProductCategory = '')
        -- Filter by search term (ignore if empty)
        AND (ProductName LIKE '%' + @SearchTerm + '%' OR @SearchTerm = '')
    ORDER BY ProductName
    -- SQL Server 2012+ pagination syntax
    OFFSET @StartRow ROWS FETCH NEXT @PageSize ROWS ONLY;
END

Stored Procedure for Total Record Count #

CREATE PROCEDURE GetTotalProductCount
    @ProductCategory NVARCHAR(50),
    @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @TotalCount = COUNT(*)
    FROM Products
    WHERE (Category = @ProductCategory OR @ProductCategory = '');
END

• If you’re using a different backend (like Node.js, PHP, or ASP.NET Core), the core logic stays the same: accept parameters, call the stored procedure, return DataTables-compatible JSON.
• Make sure to sanitize inputs to prevent SQL injection—using parameterized queries (like we did in the C# code) is critical here.
• If your stored procedure returns the filtered record count directly (instead of a separate proc), you can adjust the backend to read that value and set recordsFiltered accordingly.

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