C# SQL Database pagination sample using Dapper ORM.

BaseRepository.cs

using System;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace Samples
{
    public abstract class BaseRepository
    {
        protected async Task<T> DbAction<T>(Func<IDbConnection, Task<T>> action, string connectionString = null)
        {
            using (var connection = new SqlConnection(EnsureConnectionString(connectionString)))
            {
                var result = await action(connection);
                return result;
            }
        }

        private string EnsureConnectionString(string connectionString = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = ConfigManager.GetConnectionString("ConnectionString");
            }
            return connectionString;
        }
    }
}

DataModels.cs

using System.Collections.Generic;

namespace Samples
{
    public class PaginationResult<T>
    {
        public int TotalRowCount { get; set; }
        public ICollection<T> Rows {get;set;}
    }

    public class ExampleData
    {
        public string Name { get; set; }
    }
}

ExampleRepository.cs

using System.Linq;
using System.Threading.Tasks;
using Dapper;

namespace Samples
{
    public interface IExampleRepository
    {
        Task<PaginationResult<ExampleData>> GetPaginatedData(int pageSize, int pageNumber, string sortOrderColumn, string sortOrderDirection);
    }

    public class ExampleRepository : BaseRepository, IExampleRepository
    {
        public async Task<PaginationResult<ExampleData>> GetPaginatedData(int pageSize, int pageNumber, string sortOrderColumn, string sortOrderDirection)
        {
            var offset = GetOffset(pageSize, pageNumber);
            
            // Depending on the use case you might want to build orderBySql from either BuildOrderBySqlUsingIntepolation() or BuildOrderByUsingSqlCaseStatement().
            var orderBySql = BuildOrderBySqlUsingIntepolation(sortOrderColumn, sortOrderDirection);
            var sql =
    $@"SELECT 
	COUNT(0) [Count]
	FROM sys.Tables t

	SELECT t.name
	FROM sys.Tables t
    ORDER BY {orderBySql}
	OFFSET @Offset ROWS
	FETCH NEXT @PageSize ROWS ONLY";
            return await DbAction(async conn =>
            {
                var multi = await conn.QueryMultipleAsync(sql, new { pageSize, offset });
                var totalRowCount = multi.Read<int>().Single();
                var gridDataRows = multi.Read<ExampleData>().ToList();
                return new PaginationResult<ExampleData> { TotalRowCount = totalRowCount, Rows = gridDataRows };
            });
        }

        private static int GetOffset(int pageSize, int pageNumber)
        {
            return (pageNumber - 1) * pageSize;
        }

        /// Use string interpolation when order by columns are different SQL types - i.e in this example Name is varchar whilst create_date is a date.
        private static string BuildOrderBySqlUsingIntepolation(string sortOrderColumn, string sortOrderDirection)
        {
            string orderBy;
            switch (sortOrderColumn)
            {
                case "Name":
                     orderBy = "t.[Name]";
                    break;
                default:
                    orderBy = "t.create_date";
                    break;
            }
            if (!string.IsNullOrEmpty(sortOrderDirection))
            {
                var sortOrder = "asc";
                if (sortOrderDirection == "desc")
                {
                    sortOrder = "desc";
                }
                orderBy = $"{orderBy} {sortOrder}";
            }
            return orderBy;
        }

        /// Use Sql case statements when all columns are of the SQL same type (i.e. all columns are varchars).
        /// Note: sortOrderColumn and sortOrderDirection need to be supplied to Dapper as a SQL parameters.
        private static string BuildOrderByUsingSqlCaseStatement(string sortOrderColumn, string sortOrderDirection)
        {
            return 
            @"select @sortOrderColumn,
case 
  when @sortOrderColumn = 'name' then t.[Name] 
  when @sortOrderColumn = 'object_id' then cast(t.[object_id] as varchar) 
  else 'else'
end 
from sys.tables t
order by case when @sortOrderDirection = 'desc' then 'desc' else 'asc' end";
        }
    }
}