A Brief Guide to Implementing Pagination in a C# Endpoint

Pagination is a crucial feature in APIs that deal with large datasets, ensuring efficient data delivery by dividing it into smaller, manageable chunks. This is a brief guide to implementing pagination in a C# API endpoint.

Understand the Pagination Basics
Pagination typically involves two parameters:

• Page Number (page): Indicates which page to retrieve.
• Page Size (pageSize): Specifies the number of records per page.

Example: If you have 100 records and a page size of 10:

• page = 1 returns records 1–10.
• page = 2 returns records 11–20.

Create the API Endpoint
Here's an example of a simple Web API endpoint that supports pagination.

[HttpGet("GetData")]
public async Task<IActionResult> GetData(int page = 1, int pageSize = 10)
{
  if (page <= 0 || pageSize <= 0)
  {
    return BadRequest("Page and PageSize must be greater than zero.");
  }

  int skip = (page - 1) * pageSize;

  // Mock data for demonstration
  var data = Enumerable.Range(1, 100).Select(x => new { Id = x, Value = $"Item {x}" });

  var paginatedData = data.Skip(skip).Take(pageSize).ToList();

  var response = new
  {
    TotalItems = data.Count(),
    Page = page,
    PageSize = pageSize,
    TotalPages = (int)Math.Ceiling(data.Count() / (double)pageSize),
    Data = paginatedData
  };

  return Ok(response);
}

SQL Integration
Getting the data from a stored procedure. When working with databases, use OFFSET and FETCH clauses in SQL Server for efficient pagination.

Example of a stored procedure:

drop procedure if exists GetPagedData
go


create procedure GetPagedData
  @page int,
  @pagesize int
as
begin
  set nocount on

  declare @skip int = (@page - 1) * @pagesize

  select
  [id],
  [value]
  from [yourtable]
  order by [id]
  offset @skip rows
  fetch next @pagesize rows only
end

Example of calling the stored procedure:
I typically use Visual Studio for my development, organizing shared methods (such as GetDataFromDb) in a dedicated class file. For the sake of clarity in documents like this, that methodology is not included.

[HttpGet("GetDataFromDb")]
public async Task<IActionResult> GetDataFromDb(int page = 1, int pageSize = 10)
{
  if (page <= 0 || pageSize <= 0)
  {
    return BadRequest("Page and PageSize must be greater than zero.");
  }

  using (var connection = new SqlConnection("YourConnectionString"))
  {
    var parameters = new DynamicParameters();
    parameters.Add("@Page", page);
    parameters.Add("@PageSize", pageSize);

    var data = await connection.QueryAsync<YourModel>(
      "GetPagedData", // the stored procedure
      parameters, 
      commandType: CommandType.StoredProcedure);

    return Ok(data);
  }
}

Enhance the User Experience

• Include total record counts in the response for better navigation on the client side.
• Validate page and pageSize values to avoid exceptions or inefficiencies.
• Cache frequently accessed pages to reduce database load.

Testing The Endpoint and Pagination
I usually use Postman to test endpoints during development, allowing me to experiment with different combinations of page and pageSize, while also verifying that edge cases (such as page = 0, pageSize = 0, or page > TotalPages) are handled correctly.

Conclusion
By integrating pagination into an API endpoint, you can improve performance and user experience significantly, especially when working with large datasets.