jQuery DataTable Server-Side Processing in ASP.NET

Why Server-Side Processing

jQuery DataTables is a popular table plugin that adds sorting, searching, and pagination to any HTML table. In its default client-side mode it loads the entire dataset into the browser at once and processes interactions in JavaScript. This works fine for small tables, but once you are dealing with tens of thousands of rows the page takes too long to load, the browser struggles to filter a large in-memory dataset, and the server sends far more data than the user will ever actually view.

Server-side processing flips the model. Instead of loading everything upfront, DataTables sends the current state to the server on every interaction: the page offset, page size, sort column index, sort direction, and any search string. The server queries only the rows needed and returns them. The browser renders just that slice. Response size stays small and predictable regardless of how large the underlying table is.

Setting Up the HTML Table

Start with a basic HTML table in your .aspx page. The table only needs a thead section with column headers. DataTables will populate the tbody automatically from the server response.

<table id="usersTable" class="display" style="width:100%">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Created Date</th>
        </tr>
    </thead>
</table>

Include the DataTables CSS and JS files on your page. You can use the CDN or download them locally. Make sure jQuery is loaded before DataTables since it depends on it.

The Server-Side WebMethod

To set this up in ASP.NET WebForms, you expose a [WebMethod] on your .aspx page. DataTables POSTs its parameters as a form-encoded body. The key parameters you need to extract are: draw (an integer counter for anti-replay ordering), start (zero-based row offset for paging), length (rows per page), search[value] (the global search string), order[0][column] (zero-based sort column index), and order[0][dir] which is either asc or desc. Map the column index to an actual column name using a fixed array in code so user input never touches the SQL string directly.

[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = false)]
public static string GetData(int draw, int start, int length, string searchValue, int sortCol, string sortDir)
{
    string[] columns = { "Id", "Name", "Email", "CreatedDate" };
    string orderBy = columns[sortCol];
    if (sortDir != "asc") sortDir = "desc";

    int totalRecords = 0;
    int filteredRecords = 0;
    var rows = new List<object>();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        conn.Open();

        string filter = string.IsNullOrEmpty(searchValue) ? "" :
            " WHERE Name LIKE @search OR Email LIKE @search";

        using (SqlCommand countCmd = new SqlCommand("SELECT COUNT(*) FROM Users", conn))
            totalRecords = (int)countCmd.ExecuteScalar();

        using (SqlCommand filteredCmd = new SqlCommand("SELECT COUNT(*) FROM Users" + filter, conn))
        {
            if (!string.IsNullOrEmpty(searchValue))
                filteredCmd.Parameters.AddWithValue("@search", "%" + searchValue + "%");
            filteredRecords = (int)filteredCmd.ExecuteScalar();
        }

        string query = "SELECT Id, Name, Email, CreatedDate FROM Users" + filter +
            " ORDER BY " + orderBy + " " + sortDir +
            " OFFSET @start ROWS FETCH NEXT @length ROWS ONLY";

        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            if (!string.IsNullOrEmpty(searchValue))
                cmd.Parameters.AddWithValue("@search", "%" + searchValue + "%");
            cmd.Parameters.AddWithValue("@start", start);
            cmd.Parameters.AddWithValue("@length", length);

            using (SqlDataReader dr = cmd.ExecuteReader())
                while (dr.Read())
                    rows.Add(new { id = dr["Id"], name = dr["Name"], email = dr["Email"] });
        }
    }

    return new JavaScriptSerializer().Serialize(new {
        draw = draw,
        recordsTotal = totalRecords,
        recordsFiltered = filteredRecords,
        data = rows
    });
}

The Response Format

The JSON response must contain exactly four top-level properties: draw (echo back the integer received from the request), recordsTotal (total rows in the table with no filter applied), recordsFiltered (total rows after the current search filter), and data (the array of row objects for the current page). Missing or renaming any of these causes DataTables to silently fail or show an error in the table header.

One detail that trips people up: the draw value must be returned as an integer, not a string. DataTables sends it as a number and expects it back the same way. If you declare draw as a string in your C# response object, DataTables will discard the response silently. Keep it as int throughout.

The JavaScript Initialisation

On the JavaScript side, initialise the DataTable with processing: true, serverSide: true, and an ajax object pointing to your WebMethod URL with type: "POST". Because ASP.NET WebMethods wrap JSON responses in a d property, you need a dataSrc function in the ajax config to unwrap and parse it correctly before DataTables consumes the result.

$('#usersTable').DataTable({
    processing: true,
    serverSide: true,
    ajax: {
        url: 'Users.aspx/GetData',
        type: 'POST',
        contentType: 'application/json',
        data: function(d) {
            return JSON.stringify({
                draw: d.draw,
                start: d.start,
                length: d.length,
                searchValue: d.search.value,
                sortCol: d.order[0].column,
                sortDir: d.order[0].dir
            });
        },
        dataSrc: function(json) {
            var result = JSON.parse(json.d);
            json.recordsTotal = result.recordsTotal;
            json.recordsFiltered = result.recordsFiltered;
            json.draw = result.draw;
            return result.data;
        }
    },
    columns: [
        { data: 'id',    orderable: true  },
        { data: 'name',  orderable: true  },
        { data: 'email', orderable: true  }
    ]
});

Column Configuration

For columns you do not want to be sortable, set orderable: false in the column definition. For columns that should not participate in the global search, set searchable: false. This prevents DataTables from including those columns in the parameters it sends to the server, which avoids errors when the server tries to map a column index to a name and comes up short.

You can also format column output using the render function. This is useful for dates, currency values, or columns that need an action button.

columns: [
    { data: 'id', orderable: true },
    { data: 'name', orderable: true },
    { data: 'email', orderable: true, searchable: true },
    {
        data: 'createdDate',
        orderable: true,
        searchable: false,
        render: function(data) {
            var date = new Date(parseInt(data.replace('/Date(', '')));
            return date.toLocaleDateString();
        }
    },
    {
        data: 'id',
        orderable: false,
        searchable: false,
        render: function(data) {
            return '<a href="Edit.aspx?id=' + data + '">Edit</a>';
        }
    }
]

Optimising the SQL Queries

The example above runs three separate queries: one for total count, one for filtered count, and one for the actual data page. On large tables this can be slow. You can reduce it to two queries by combining the filtered count with the data query using a window function.

string query = @"
    SELECT Id, Name, Email, CreatedDate,
           COUNT(*) OVER() AS FilteredCount
    FROM Users" + filter + @"
    ORDER BY " + orderBy + " " + sortDir + @"
    OFFSET @start ROWS FETCH NEXT @length ROWS ONLY";

The COUNT(*) OVER() window function returns the total number of rows that match the filter across all pages, not just the current page. This saves a round trip to the database and makes search responses noticeably faster on large datasets.

Also make sure you have proper indexes on columns used in the WHERE clause and ORDER BY. Without an index, SQL Server does a full table scan on every request. For a table with a million rows, the difference between an indexed and non-indexed search column is the difference between 2ms and 2 seconds.

Handling Errors Gracefully

If the server throws an exception, DataTables shows a generic alert popup by default. This is not a great user experience. Override the error handler to show something more friendly.

$.fn.dataTable.ext.errMode = 'none';

$('#usersTable').on('error.dt', function(e, settings, techNote, message) {
    console.error('DataTables error:', message);
    alert('Something went wrong while loading the data. Please refresh the page.');
});

Adding a Search Delay

By default, DataTables fires a server request on every keystroke in the search box. On a slow network or a heavy query, this creates a queue of requests. Add a debounce delay so the request only fires after the user stops typing.

$('#usersTable').DataTable({
    // ... other options
    searchDelay: 500  // wait 500ms after the last keystroke
});

Once wired up, the table behaves identically to client-side mode from the user's perspective. Every sort click, search keystroke, and page change fires a request to the server, which returns only the rows needed. The database handles filtering and sorting at the SQL level where it is most efficient, and your page stays fast regardless of how many rows are in the table.