Back to: ADO.NET Core Tutorial For Beginners and Professionals
Pagination using ADO.NET Core and Stored Procedure
In this article, I will discuss How to Implement Pagination using ADO.NET Core and Stored Procedure with Examples. Please read our previous article, which discusses How to Implement Bulk Inserts and Updates using Stored Procedures in ADO.NET Core with examples.
Pagination using ADO.NET Core and Stored Procedure
Pagination is a technique used in web design and development to divide large amounts of data into manageable pages or sections that the user can easily navigate. This method is commonly applied to search results, product listings, comments, or any content that can grow significantly over time.
What is Pagination?
Pagination splits content into separate pages, which are usually accessible by navigating through numbered page links, previous/next buttons, or sometimes a combination of both. It is a crucial user interface element for web applications and websites, enhancing the user experience by organizing content neatly and logically.
Why Do We Need Pagination?
- Improved User Experience: By breaking down content into smaller, more manageable chunks, pagination prevents information overload for the user. It makes content easier to digest and navigate, improving overall user satisfaction.
- Faster Page Load Times: Loading a large dataset simultaneously can significantly slow down page load times. Pagination allows for loading only a portion of the data simultaneously, enhancing performance and speed.
- Reduced Server Load: Fetching smaller amounts of data with each request can lessen the load on the server, which is especially important for large-scale applications dealing with extensive databases or high user traffic.
Pagination using ADO.NET Core and Stored Procedure in SQL Server:
Let us see different examples to understand this concept. I will show you different approaches to implement the Paging using ADO.NET Core and SQL Server Stored Procedure in a .NET Core Console Application.
Creating Demo Data in SQL Server
First, you’ll need a SQL Server database and an Employee table. Let’s create the EmployeeDB Database and Employee table by executing the following SQL Script.
CREATE DATABASE EmployeeDB;
GO
USE EmployeeDB;
GO
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(50),
JoiningDate DATETIME
);
GO
Now, we need to insert some demo data into the Employee table. The following script uses a WHILE loop to insert 100 records one by one.
DECLARE @count INT = 0;
WHILE @count < 1000
BEGIN
INSERT INTO Employee (FirstName, LastName, Department, JoiningDate)
VALUES (CONCAT('FirstName', @count), CONCAT('LastName', @count), 'IT', DATEADD(day, @count, '2020-01-01'));
SET @count = @count + 1;
END;
GO
Creating a Stored Procedure for Paging
Next, you’ll create a stored procedure that supports paging by accepting parameters for the page number and the number of records per page. Later, I will show you how to take the Sort column and order. So, please use the following SQL Script to create the Stored Procedure. In the below example, we are using CTE to return the employees.
CREATE PROCEDURE GetEmployeesPaged
@PageNumber INT,
@PageSize INT
AS
BEGIN
SET NOCOUNT ON;
-- Calculate the row number to start fetching from
DECLARE @RowStart INT = (@PageNumber - 1) * @PageSize + 1;
DECLARE @RowEnd INT = @PageNumber * @PageSize;
-- Fetch the range of rows
WITH EmployeeCTE AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
FROM Employee
)
SELECT *
FROM EmployeeCTE
WHERE RowNum BETWEEN @RowStart AND @RowEnd;
END
GO
Explanation:
The main goal of this stored procedure was to retrieve a subset of records from the Employee table, divided into pages of a specified size and ordered by the EmployeeID.
Parameters
- @PageNumber: Specifies which page of results to return.
- @PageSize: Specifies the number of records on each page.
SQL Elements
- Common Table Expression (CTE) with ROW_NUMBER(): It utilized a Common Table Expression (CTE) to assign a row number to each record in the Employee table based on the order of the EmployeeID. The ROW_NUMBER() function is used within the CTE to achieve this, creating a temporary, sequentially numbered column (RowNum) that reflects the order of rows.
- Pagination Logic: By calculating the row numbers for the beginning and end of the desired page (@RowStart and @RowEnd), the procedure could select only those rows within this range. This was accomplished by filtering the results of the CTE where RowNum was between @RowStart and @RowEnd.
Calling the Stored Procedure from ADO.NET Core
Create a New Console Application. Open Visual Studio or your preferred IDE and create a new .NET Core Console Application. And then modify the Program class as follows:
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
while (true)
{
Console.WriteLine("Enter Page Number ('exit' to quit): ");
var pageNumberInput = Console.ReadLine();
if (pageNumberInput.ToLower() == "exit") break;
Console.WriteLine("Enter Page Size: ");
var pageSizeInput = Console.ReadLine();
int pageNumber, pageSize;
if (!int.TryParse(pageNumberInput, out pageNumber) || !int.TryParse(pageSizeInput, out pageSize))
{
Console.WriteLine("Invalid Input. Please Enter Numeric Values.");
continue;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using(SqlCommand command = new SqlCommand("GetEmployeesPaged", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PageNumber", pageNumber);
command.Parameters.AddWithValue("@PageSize", pageSize);
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
Console.WriteLine("No records found.");
}
else
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Department: {reader["Department"]}");
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
Console.WriteLine("\nPress any key to continue or type 'exit' to quit.");
var exitOrContinue = Console.ReadLine();
if (exitOrContinue.ToLower() == "exit") break;
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Explanation:
- This application starts by displaying a prompt for the user to enter a page number or type ‘exit’ to quit the application.
- It then prompts the user to enter the page size.
- The application tries to parse the user’s input into integers. If the input is invalid (i.e., not numeric), it displays an error message and prompts the user again.
- If valid inputs are provided, the application connects to the database and executes the stored procedure GetEmployeesPaged with the provided page number and page size.
- The results are displayed on the console. If no records are found, it informs the user accordingly.
- After displaying the results, the application prompts the user to press any key to continue or type ‘exit’ to quit. This loop allows for multiple queries to be made without restarting the application.
- Exception handling is implemented to catch and display errors related to database connectivity or query execution.
Output:
Pagination with Sort Column and Sort Order:
Now, we need to modify the GetEmployeesPaged stored procedure to return data based on dynamic Sort Column and Sort Order. To modify the GetEmployeesPaged stored procedure to support dynamic sorting by column name and sort order, we can use dynamic SQL. This involves constructing the SQL query as a string and executing it with the sp_executesql stored procedure.
Dynamic SQL allows you to inject the column name and sort order into the query string, enabling sorting by different columns and orders without having to write multiple stored procedures or conditional logic for each column. Here’s how you can rewrite the GetEmployeesPaged stored procedure:
CREATE OR ALTER PROCEDURE GetEmployeesPaged
@PageNumber INT,
@PageSize INT,
@SortColumn NVARCHAR(50),
@SortOrder NVARCHAR(4) -- 'ASC' or 'DESC'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Params NVARCHAR(MAX);
DECLARE @RowStart INT = (@PageNumber - 1) * @PageSize + 1;
DECLARE @RowEnd INT = @PageNumber * @PageSize;
-- Ensure that the sort order is either 'ASC' or 'DESC'
IF @SortOrder NOT IN ('ASC', 'DESC')
SET @SortOrder = 'ASC'
-- Building the dynamic SQL
SET @SQL = N'
WITH EmployeeCTE AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortOrder + ') AS RowNum
FROM Employee
)
SELECT *
FROM EmployeeCTE
WHERE RowNum BETWEEN @RowStart AND @RowEnd;
';
-- Define the parameters used in the dynamic SQL
SET @Params = N'@RowStart INT, @RowEnd INT';
-- Execute the dynamic SQL
EXEC sp_executesql @SQL, @Params, @RowStart = @RowStart, @RowEnd = @RowEnd;
END
GO
Explanation:
Here is a detailed explanation of the above-stored procedure.
Parameters
- @PageNumber: The page number of the result set you wish to retrieve.
- @PageSize: The number of records per page.
- @SortColumn: The column name by which the results should be sorted.
- @SortOrder: Specifies the direction of the sort, either ‘ASC’ for ascending or ‘DESC’ for descending.
Key Components
- Dynamic SQL Construction: The procedure constructs a SQL query as a string (@SQL). This query includes a ROW_NUMBER() function that assigns a unique sequential integer to rows in the result set ordered according to @SortColumn and @SortOrder. Because column names and sort directions cannot be parameterized in SQL Server in the same way as values, the procedure directly concatenates these into the query string.
- Sanitization of @SortOrder: The procedure checks if @SortOrder is either ‘ASC’ or ‘DESC’. If not, it defaults to ‘ASC’. This is a basic form of input validation to prevent injection attacks through the sort order parameter.
- QUOTENAME Function: It’s used to safely quote the @SortColumn name to reduce the risk of SQL injection. QUOTENAME automatically adds the necessary square brackets around the identifier, ensuring it’s treated as a single entity and allowing for special characters in column names.
- Common Table Expression (CTE) with ROW_NUMBER(): A CTE named EmployeeCTE is defined to select all columns from the Employee table, along with a RowNum column generated by the ROW_NUMBER() function. The function’s OVER clause orders rows by the dynamically specified column and order.
- Pagination Logic: The procedure calculates the starting (@RowStart) and ending (@RowEnd) row numbers for the desired page. The final SELECT statement retrieves rows from the CTE where RowNum falls between these two values, effectively implementing pagination.
- sp_executesql: This system-stored procedure executes the constructed SQL string (@SQL). Parameters for the dynamic SQL (@RowStart and @RowEnd) are passed along with their definitions in @Params.
Execution Flow
- Upon invocation, the procedure constructs the dynamic SQL query based on the input parameters.
- It then executes this query using sp_executesql, which fetches a page of rows from the Employee table ordered and sorted according to the specified column and order.
- The result is a subset of the Employee table corresponding to the requested page, with rows numbered and ordered dynamically. This allows for flexible data retrieval suitable for displaying in a paginated format on a user interface, where the end-user can change the sorting criteria.
Calling the Stored Procedure from ADO.NET Core
Now, modify the Program class as follows:
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EmployeeDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
while (true)
{
Console.WriteLine("\nEnter Page Number ('exit' to quit): ");
var pageNumberInput = Console.ReadLine();
if (pageNumberInput?.ToLower() == "exit") break;
Console.WriteLine("Enter Page Size: ");
var pageSizeInput = Console.ReadLine();
Console.WriteLine("Enter Sort Column (e.g., 'FirstName', 'LastName'): ");
var sortColumn = Console.ReadLine();
Console.WriteLine("Enter sort order ('ASC' or 'DESC'): ");
var sortOrder = Console.ReadLine();
if (!int.TryParse(pageNumberInput, out int pageNumber) || !int.TryParse(pageSizeInput, out int pageSize))
{
Console.WriteLine("Invalid Input for Page Number or Page Size. Please Enter Numeric Values.");
continue;
}
if (sortOrder?.ToUpper() != "ASC" && sortOrder?.ToUpper() != "DESC")
{
Console.WriteLine("Invalid sort order. Please enter 'ASC' or 'DESC'.");
continue;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlCommand command = new SqlCommand("GetEmployeesPaged", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PageNumber", pageNumber);
command.Parameters.AddWithValue("@PageSize", pageSize);
command.Parameters.AddWithValue("@SortColumn", sortColumn);
command.Parameters.AddWithValue("@SortOrder", sortOrder.ToUpper());
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
Console.WriteLine("No records found.");
}
else
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}, Department: {reader["Department"]}");
}
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
Console.WriteLine("\nPress any key to continue or type 'exit' to quit.");
var exitOrContinue = Console.ReadLine();
if (exitOrContinue?.ToLower() == "exit") break;
}
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Key Changes from the Previous Version:
- Additional Inputs: This version asks for two additional pieces of information from the user: the column to sort by (sortColumn) and the order to sort in (sortOrder).
- Input Validation: Added simple validation for the sort order to ensure it is either ‘ASC’ or ‘DESC’. It does not validate the sort column against the actual columns of the Employee table, which could be an area for further enhancement.
- Parameterization: The SqlCommand object includes additional parameters for @SortColumn and @SortOrder reflecting the new stored procedure signature.
Now, run the application and test the functionality. It should work as expected, as shown in the image below.
Revised Stored Procedure Using OFFSET and FETCH
This version constructs a dynamic SQL query to implement pagination and sorting. OFFSET skips the specified number of rows, and FETCH NEXT retrieves the specified number of rows after that. This method is more straightforward for pagination and is supported in SQL Server 2012 and later versions. So, please execute the following SQL Script to modify the Stored procedure.
CREATE OR ALTER PROCEDURE GetEmployeesPaged
@PageNumber INT,
@PageSize INT,
@SortColumn NVARCHAR(50),
@SortOrder NVARCHAR(4) -- 'ASC' or 'DESC'
AS
BEGIN
SET NOCOUNT ON;
IF @SortOrder NOT IN ('ASC', 'DESC')
SET @SortOrder = 'ASC'
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
-- Building the dynamic SQL for OFFSET FETCH
SET @SQL = N'SELECT * FROM Employee
ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortOrder + '
OFFSET ' + CAST(@Offset AS NVARCHAR(10)) + ' ROWS
FETCH NEXT ' + CAST(@PageSize AS NVARCHAR(10)) + ' ROWS ONLY;';
EXEC sp_executesql @SQL;
END
GO
Explanation
- Dynamic Sorting: The sort column (@SortColumn) and the sort order (@SortOrder) are dynamically injected into the SQL command. The QUOTENAME function encapsulates the column name, safely avoiding SQL injection risks.
- Pagination with OFFSET-FETCH: This approach directly uses OFFSET to skip a calculated number of rows (@Offset) and FETCH NEXT to limit the number of rows returned to the @PageSize. This is a concise and efficient way to implement paging directly in SQL.
- Validation and Defaults: The procedure checks the sort order to ensure it is either ‘ASC’ or ‘DESC’, defaulting to ‘ASC’ if an invalid value is provided.
- Dynamic SQL Execution: Executes the constructed SQL string using sp_executesql. This approach is necessary to incorporate the variable column name and sort order into the ORDER BY clause, as these cannot be parameterized like other SQL command parameters.
Note: No changes are required in the C# code. You can run and test the application, and it should work as expected.
Difference between OFFSET FETCH Clause and CTE to implement pagination in SQL Server
Implementing pagination in SQL Server can be achieved using either the OFFSET FETCH clause or a Common Table Expression (CTE) combined with the ROW_NUMBER() function. Both methods divide a result set into manageable chunks or “pages” of data, but they have different characteristics and performance implications. Here’s a comparison of the two approaches:
OFFSET FETCH
Introduced in SQL Server 2012, the OFFSET FETCH clause provides a straightforward way to skip a specified number of rows and then fetch a defined set of rows from the result set.
Advantages:
- Simplicity: It offers a more readable and concise syntax for pagination directly in the ORDER BY clause.
- Performance: Generally, for simple queries or when fetching the first few pages of a result set, OFFSET FETCH can perform well and is optimized for paging scenarios.
- Standardization: This approach is aligned with the SQL standard for pagination, making it more consistent with pagination implementations in other SQL-based systems.
Disadvantages:
- Performance in Deep Pagination: For very large datasets or when accessing pages deep into the result set, performance might degrade because the SQL Server still has to read through the offset rows before fetching the desired page.
- Less Flexibility: It does not offer the same level of control or flexibility as a CTE with ROW_NUMBER() for complex queries that might require filtering or manipulation of rows before pagination.
CTE with ROW_NUMBER()
Using a Common Table Expression (CTE) with the ROW_NUMBER() function generates a temporary result set that assigns a unique row number to each row in the dataset based on the specified ordering. Pagination is then achieved by filtering this result set to include only rows within a specific range of row numbers.
Advantages:
- Flexibility: This method allows for more complex manipulations and filtering of the result set before applying pagination, offering greater control over the final output.
- Performance in Complex Queries: A CTE can be more efficient for complex queries, especially those involving multiple joins or where conditions, as it allows for row numbering and filtering to be done in a single pass of the data.
- Deep Pagination Efficiency: When dealing with deep pagination, a well-indexed ROW_NUMBER() column can sometimes offer better performance because the filtering on the row numbers can be more efficient than skipping a large number of rows with OFFSET.
Disadvantages:
- Complexity: The syntax and logic for implementing pagination using a CTE and ROW_NUMBER() can be more complex and less intuitive than using OFFSET FETCH, especially for simple pagination needs.
- Potential Performance Overhead: Generating row numbers for a large dataset can introduce overhead, primarily if the row numbering does not directly correspond to an indexed column or if the query complexity increases.
Conclusion
The choice between OFFSET FETCH Clause and CTE with ROW_NUMBER() depends on the pagination scenario’s specific requirements, the query’s complexity, and performance considerations. For straightforward pagination scenarios, especially with modern versions of SQL Server, OFFSET FETCH provides a simple and effective solution. For more complex data manipulation or when optimal performance is required for deep pagination or complex queries, using a CTE with ROW_NUMBER() might be more appropriate.
In the next article, I will discuss Connected and Disconnected Architecture in ADO.NET Core with Examples. In this article, I explain How to Implement Pagination using ADO.NET Core and Stored Procedure with Examples. I would like to have your feedback. Please post feedback, questions, or comments about this Pagination using the ADO.NET Core and Stored Procedure with Examples article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.