Server Side Pagination Using Stored Procedure and Dapper in ASP.NET Core Razor Pages

This article explains how paging can be implemented using the stored procedure in ASP.NET Core Razor Pages. When you have millions of records in the database and need high performance and complete control over the query to be used for pagination, you can use stored procedures.  Here, I will use Dapper to execute the stored procedure and the custom Tag Helper component from the previous article "Pagination in ASP.NET Core Razor Pages using custom Tag Helper" for the paging logic. You can download the code related to this article from GitHub

Project Setup

You need the following tools locally to run this application successfully.

• .NET Core SDK - includes the .NET Core runtime and command-line tools
• Visual Studio Code/Visual Studio 2019 community edition 
• C# extension for Visual Studio Code - adds support to VS Code for developing .NET Core applications if you use visual studio code for the development.
• Microsoft SQL Server 2019 express edition

The sample web application included with this article uses .NET Core 5.0 and SQL Server 2019. You can simply download and run it locally to see the paged records. 

 I have used the following Nuget packages in the application. 

1. Microsoft.EntityFrameworkCore.SqlServer
2. Microsoft.EntityFrameworkCore.Tools
3. Dapper

Check the article “installation of entity framework core” if you are not familiar with installing Nuget packages using visual studio.

Model

Here is my Employee model, which is used to create the Employee table in the database. Note that I have added a field named TotalRows to hold the total number of records affected by the paging stored procedure. This field is not mapped to the database table.

  public class Employee
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int EmpId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        [NotMapped]
        public int TotalRows { get; set; }
    }

Paging Logic

As I mentioned before, paging is done using a Custom Tag Helper. There are two classes associated with this; one is PagingData.cs and the other one Paging.cs. The PagingData.CS class contains the properties associated with the paging, and the Paging.cs class contains the paging logic. This class has the logic to generate the paging links and the necessary code to make this a custom Tag Helper. 

PagingData.cs

 public class PagingData
    {
        public int TotalRecords { get; set; }
        public int RecordsPerPage { get; set; }
        public int CurrentPage { get; set; }
        public int TotalPages => (int)Math.Ceiling((decimal)TotalRecords / RecordsPerPage);
        public string UrlParams { get; set; }
        public int LinksPerPage { get; set; }
    }

Paging.cs

  [HtmlTargetElement("div", Attributes = "paging-model")]
    public class Pageing : TagHelper
    {
        private IUrlHelperFactory _urlHelperFactory;

        public Pageing(IUrlHelperFactory urlHelperFactory)
        {
            _urlHelperFactory = urlHelperFactory;
        }

        [ViewContext]
        [HtmlAttributeNotBound]
        public ViewContext ViewContext { get; set; }
        public PagingData PagingModel { get; set; }     
        public string PageClass { get; set; }
        public string PageClassNormal { get; set; }
        public string PageClassSelected { get; set; }
        public string PageClassLabel { get; set; }
        public string PageClassLinks { get; set; }


        public override void Process(TagHelperContext helperContext, TagHelperOutput helperOutput)
        {
            IUrlHelper urlHelper = _urlHelperFactory.GetUrlHelper(ViewContext);
            TagBuilder outerDiv = new TagBuilder("div");

            int startPage;
            int endPage;
            if (PagingModel.TotalPages > 1)
            {
                if (PagingModel.TotalPages <= PagingModel.LinksPerPage)
                {
                    startPage = 1;
                    endPage = PagingModel.TotalPages;
                }
                else
                {
                    if (PagingModel.CurrentPage + PagingModel.LinksPerPage - 1 > PagingModel.TotalPages)
                    {
                        startPage = PagingModel.CurrentPage - ((PagingModel.CurrentPage + PagingModel.LinksPerPage - 1) 
                            - PagingModel.TotalPages);
                        endPage = (PagingModel.CurrentPage + PagingModel.LinksPerPage - 1) - 
                            ((PagingModel.CurrentPage + PagingModel.LinksPerPage - 1) - PagingModel.TotalPages);
                    }
                    else
                    {                      
                        if (PagingModel.LinksPerPage !=2)
                        {
                            startPage = PagingModel.CurrentPage - (PagingModel.LinksPerPage / 2);
                            if (startPage < 1)
                            {
                                startPage = 1;
                            }
                            endPage = startPage + PagingModel.LinksPerPage - 1;
                        }                    
                        else
                        {
                            startPage = PagingModel.CurrentPage;
                            endPage = PagingModel.CurrentPage + PagingModel.LinksPerPage - 1;
                        }

                    }

                }
                TagBuilder labelDiv;
                labelDiv = new TagBuilder("div");
                labelDiv.AddCssClass(PageClassLabel);
                labelDiv.InnerHtml.Append($"Showing {PagingModel.CurrentPage} of { PagingModel.TotalPages}");
                outerDiv.InnerHtml.AppendHtml(labelDiv);
                TagBuilder linkDiv = new TagBuilder("div");
                linkDiv.InnerHtml.AppendHtml(GeneratePageLinks("First", 1));
                for (int i = startPage; i <= endPage; i++)
                {
                    linkDiv.InnerHtml.AppendHtml(GeneratePageLinks(i.ToString(), i));
                }

                linkDiv.InnerHtml.AppendHtml(GeneratePageLinks("Last", PagingModel.TotalPages));
                linkDiv.AddCssClass(PageClassLinks);
                outerDiv.InnerHtml.AppendHtml(linkDiv);
                helperOutput.Content.AppendHtml(outerDiv.InnerHtml);
            }
        }

        private TagBuilder GeneratePageLinks(string iterator, int pageNumber)
        {
            string url;
            TagBuilder tag;
            tag = new TagBuilder("a");
            url = PagingModel.UrlParams.Replace("-", pageNumber.ToString());
            tag.Attributes["href"] = url;
            tag.AddCssClass(PageClass);
            if (iterator != "First" && iterator != "Last")
            {
                tag.AddCssClass(Convert.ToInt32(iterator) == PagingModel.CurrentPage ? PageClassSelected : PageClassNormal);
            }
            else
            {
                tag.AddCssClass(pageNumber == PagingModel.CurrentPage ? PageClassSelected : PageClassNormal);
            }
            tag.InnerHtml.Append(iterator.ToString());
            return tag;

        }


    }

You can see more details about these two classes in my previous article about paging.

Registering the Tag Helper

The Tag Helper has to be registered to access it in other parts of the application. It is done in _ViewImports.cshtml file as follows.

@addTagHelper *, DapperPaging

The first string after @addTagHelper specifies the Tag Helper to load (Use "*" for all tag helpers). DapperPaging is the assembly where the Tag Helper code resides.

Stored Procedure

The following stored procedure is used to get records from the employee table. I have used CTEs (Common Table Expression) named “TempResult” and “TempCount” to get the records and the total number of rows. You can also get the total number of rows here using Count(*) Over () expression as this is a simple query. But from my experience, CTE is the best option when your query gets more complex with multiple joins.

Create PROCEDURE [dbo].[DapperPaging_Employee_GetAll]
@PageNum int,
@PageSize int

AS
BEGIN

	WITH TempResult AS
	(
	          SELECT  [EmpId]
			  ,[FirstName]
			  ,[LastName]
			  ,[Email]
		  FROM [DapperPaging].[dbo].[Employee]
	), 
	TempCount AS
	(
		SELECT COUNT(*) AS  TotalRows FROM TempResult
	)
	SELECT *
	FROM TempResult, TempCount
	ORDER BY TempResult.[EmpId]
	OFFSET (@PageNum - 1) * @PageSize ROWS
	FETCH NEXT @PageSize ROWS ONLY; 


END

Execute the stored procedure using Dapper

I have created a generic repository to call the stored procedure. I defined an interface named ISPCall and created a class named SPCall which implements that interface.

ISPCall.cs

using System;
using System.Collections.Generic;
using Dapper;
namespace DapperPaging.Data
{
    public interface ISPCall : IDisposable
        {
            IEnumerable<T> ReturnList<T>(string procedureName, DynamicParameters param = null);          
        }    
}

SPCall.cs

using System.Collections.Generic;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace DapperPaging.Data
{
    public class SPCall : ISPCall
    {
        private readonly ApplicationDBContext _db;
        private static string ConnectionString = "";
        public SPCall(ApplicationDBContext db)
        {
            _db = db;
            ConnectionString = db.Database.GetDbConnection().ConnectionString;
        }
        public void Dispose()
        {
            _db.Dispose();
        }      
        public IEnumerable<T> ReturnList<T>(string procedureName, DynamicParameters param = null)
        {
            using (SqlConnection sqlCon = new SqlConnection(ConnectionString))
            {
                sqlCon.Open();
                return sqlCon.Query<T>(procedureName, param, commandType: System.Data.CommandType.StoredProcedure);
            }
        }
    
    }
}

Next, we have to add the repository to Startup.cs class for dependency injection.

public void ConfigureServices(IServiceCollection services)
        {          
            services.AddScoped<ISPCall, SPCall>();           
        }

we are now ready to call the stored procedure from our index page.

Invoking the Tag Helper

The paging Tag Helper is called on the Index page. The OnGet handler method accesses the database and retrieves all the employee records. An object of PagingData class is initialized there, and values for  CurrentPage, RecordsPerPage, TotalRecords, UrlParams, and LinksPerPage are assigned. A hyphen is used after the PageNumber parameter when query string parameters are assigned to the QParam variable. This hyphen will be replaced with the page number in the Tag Helper class.

 public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        private readonly ISPCall _SPCall;
        private readonly ApplicationDBContext _context;
        public PagingData PagingData { get; set; }
        [BindProperty]
        public IList<Employee> Employees { get; set; }
        private const int PageSize = 10;

        public IndexModel(ILogger<IndexModel> logger, ISPCall SPCall, ApplicationDBContext context)
        {
            _logger = logger;
            _SPCall = SPCall;
            _context = context;
        }

        public void OnGet(int PageNum = 1)
        {
            DynamicParameters parameter = new DynamicParameters();

            parameter.Add("@PageNum", PageNum, DbType.Int32, ParameterDirection.Input);
            parameter.Add("@pageSize", PageSize, DbType.Int32, ParameterDirection.Input);           

            Employees = _SPCall.ReturnList<Employee>("DapperPaging_Employee_GetAll", parameter).ToList<Employee>();

           
            StringBuilder QParam = new StringBuilder();
            if (PageNum != 0)
            {
                QParam.Append($"/Index?PageNum=-");

            }
            if (Employees.Count > 0)
            {
                PagingData = new PagingData
                {
                    CurrentPage = PageNum,
                    RecordsPerPage = PageSize,
                    TotalRecords = Employees[0].TotalRows,
                    UrlParams = QParam.ToString(),
                    LinksPerPage = 5
                };               
            }
        }
    }

Dependency injection is used to access the repository class, which executes the stored procedure. The stored procedure takes two parameters for page number and page size. Those parameters are passed to the stored procedure using DynamicParameters variable. The stored procedure is then called, and the result is mapped to a collection of Employee object.

We must use kabab-casing to assign values to Tag Helper attributes. As a result, PagingModel is renamed to paging-model. Once the Tag Helper is registered in the _ViewImports.cshtml file, Visual Studio recognizes it and provides IntelliSense support. The Tag Helper is invoked using the code below.

  <div paging-model="@Model.PagingData" page-class="btn border mt-3"
         page-class-links="text-center" page-class-normal="btn btn-light" page-class-selected="btn btn-dark  active"
         page-class-label="p-2 font-weight-bold  pt-md-2  text-center" class="text-center">
    </div>

I used a few Bootstrap classes to make the links more appealing. The PagingData variable is used to transfer paging-related data to the custom Tag Helper. You can fully customize the look and feel of the paging links by using custom CSS classes.

Screenshot