VSA Todo App — Part 8: Server-Side Pagination, Sorting & Excel Export in VSA

Listing data is the most common operation in any application, and how you handle it in VSA depends on scale. The Blazor CRM loads all todos into memory and filters client-side — simple, fast for small datasets, and trivial to implement. The MVC Project Manager uses server-side DataTables with true pagination, sorting, and search — essential when you have thousands of records. This part implements both patterns and explains when to choose each.

The server-side approach uses the DataTables protocol — a standard request/response format where the client sends draw, start, length, search[value], and order[i][column] parameters, and the server returns draw, recordsTotal, recordsFiltered, and data. The MVC Project Manager implements this directly in the GetTodoListHandler, making it the single source of truth for todo list queries.

The DataTableRequest Protocol

The handler receives a DataTableRequest object with pagination, search, and sorting parameters. It builds an IQueryable, applies filters, applies sorting, then paginates:

public async Task<ApiResponse<object>> HandleAsync(DataTableRequest request, CancellationToken ct)
{
    var query = _context.Todo.AsNoTracking();

    // Apply search across multiple columns
    if (!string.IsNullOrEmpty(request.SearchValue))
    {
        var search = request.SearchValue.ToLower();
        query = query.Where(x => x.Name.Contains(search) ||
            x.Description.Contains(search) || x.Tags.Contains(search));
    }

    // Apply sorting by column index
    query = request.Order.FirstOrDefault()?.Column switch
    {
        0 => query.OrderBy(x => x.AutoNumber),
        1 => query.OrderBy(x => x.Name),
        _ => query.OrderByDescending(x => x.CreatedAt)
    };

    var totalRecords = await query.CountAsync(ct);
    var data = await query.Skip(request.Start).Take(request.Length)
        .Select(x => new TodoListItem { Id = x.Id, Name = x.Name, /* projection */ })
        .ToListAsync(ct);

    return ApiResponse<object>.Success(new
    {
        draw = request.Draw, recordsTotal = totalRecords,
        recordsFiltered = totalRecords, data
    });
}

This pattern is essential for datasets beyond a few hundred records. The database does the filtering, sorting, and pagination — the application only receives the current page of data. Memory usage stays constant regardless of table size.

Client-Side Filtering (Blazor CRM)

The Blazor CRM takes the simpler approach: load all todos, filter and paginate in memory. This is perfectly fine for datasets under ~500 records. The code is simpler, there's no DataTables protocol to implement, and the UI is more responsive because filtering happens instantly without a server round-trip. The trade-off is memory — all records are loaded into the browser.

Excel Export with ClosedXML

Both implementations support Excel export. The MVC Project Manager generates it server-side with a dedicated export endpoint. The handler queries all matching records (respecting the search filter but ignoring pagination), projects to a flat TodoExportItem DTO with resolved audit emails, and writes to an Excel workbook using ClosedXML with styled headers and auto-fitted columns. The endpoint returns the file as application/vnd.openxmlformats-officedocument.spreadsheetml.sheet:

using var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Todos");
worksheet.Cell(1, 1).Value = "No";
worksheet.Cell(1, 2).Value = "Name";
worksheet.Cell(1, 3).Value = "Priority";
var headerRange = worksheet.Range(1, 1, 1, 7);
headerRange.Style.Font.Bold = true;
headerRange.Style.Fill.BackgroundColor = XLColor.FromHtml("#0D47A1");
headerRange.Style.Font.FontColor = XLColor.White;

int row = 2;
foreach (var item in exportData)
{
    worksheet.Cell(row, 1).Value = item.AutoNumber;
    worksheet.Cell(row, 2).Value = item.Name;
    row++;
}
worksheet.Columns().AdjustToContents();

When to Use Each Approach

Use client-side filtering when your dataset is under 500 records, you want instant UI responsiveness, and your users typically work with the full dataset. The Blazor CRM pattern is ideal for admin dashboards and internal tools. Use server-side DataTables when you have thousands of records, need true database-level sorting, or have complex search requirements across multiple columns. The MVC Project Manager pattern is essential for production applications at scale.