JQuery DataTables Paging, Sorting and Filtering in ASP.NET Core using Dapper ORM

In one of my previous posts CRUD Operations in ASP.NET Core 5 using Dapper ORM, I covered how to perform CRUD operations in ASP.NET Core 5 using Dapper ORM. Many people on social media requested me to write a post on implementing paging functionality using Dapper ORM. In this tutorial, I will show you how to implement paging, sorting, and filtering in ASP.NET Core using a Stored Procedure and Dapper ORM and as we don’t have any built-in data grid in ASP.NET Core so I will also show you how to display the data using JQuery DataTables plugin.

Implement a Stored Procedure for Paging, Sorting, and Searching

Before we start writing any code in Visual Studio 2019, the first thing we need is a database table with some data in it. I have decided to use Microsoft’s famous AdventureWorks database for this post. We will implement paging, sorting, and filtering functionality on the Product table shown below.

I have already shown you an example of calling a stored procedure using Dapper ORM in my post CRUD Operations in ASP.NET Core 5 using Dapper ORM and in this post, we will use the same technique and we will call a stored procedure that has paging, searching and sorting features in it. Create the following stored procedure in SQL Server 2016 AdventureWorks database.

USE [AdventureWorks]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetProductsList]
(  
	@SearchValue NVARCHAR(255) = NULL,  
	@PageNo INT = 0,
	@PageSize INT = 10,
	@SortColumn INT = 0,
	@SortDirection NVARCHAR(10) = 'ASC'
)
AS
BEGIN 
	SET NOCOUNT ON;
	 
	DECLARE @TotalCount AS INT = (SELECT COUNT(*) FROM [Production].[Product])

	DECLARE @FirstRec int, @LastRec int
	SET @FirstRec = @PageNo * @PageSize + 1;
	SET @LastRec = (@PageNo + 1) * @PageSize;

	SET @SearchValue = LTRIM(RTRIM(@SearchValue)) 

	; WITH CTE_Results AS   
	(  
		SELECT ROW_NUMBER() OVER (ORDER BY  

			CASE WHEN (@SortColumn = 0 AND @SortDirection='asc')  
						THEN ProductID  
			END ASC,  
			CASE WHEN (@SortColumn = 0 AND @SortDirection='desc')  
					THEN ProductID  
			END DESC, 
			CASE WHEN (@SortColumn = 1 AND @SortDirection='asc')  
						THEN Name  
			END ASC,  
			CASE WHEN (@SortColumn = 1 AND @SortDirection='desc')  
						THEN Name  
			END DESC,  
			CASE WHEN (@SortColumn = 2 AND @SortDirection='asc')  
						THEN ProductNumber  
			END ASC,  
			CASE WHEN (@SortColumn = 2 AND @SortDirection='desc')  
						THEN ProductNumber  
			END DESC,
			CASE WHEN (@SortColumn = 3 AND @SortDirection='asc')  
						THEN Color  
			END ASC,  
			CASE WHEN (@SortColumn = 3 AND @SortDirection='desc')  
						THEN Color  
			END DESC 
		)
		AS RowNum,
		COUNT(*) OVER() as FilteredCount,
		ProductID, 
		Name, 
		ProductNumber, 
		Color 
		FROM [Production].[Product] 
		   
		WHERE ISNULL(@SearchValue, '') = ''
		OR Name LIKE '%' + @SearchValue + '%'
		OR ProductNumber LIKE '%' + @SearchValue + '%'
		OR Color LIKE '%' + @SearchValue + '%'
	) 

	SELECT 
		ProductID, 
		Name, 
		ProductNumber, 
		Color,
		FilteredCount,
		@TotalCount AS TotalCount
	FROM CTE_Results
	WHERE RowNum BETWEEN @FirstRec AND @LastRec  
   
END

The stored procedure has the following five parameters

@SearchValue – This parameter will allow us to pass any keyword for searching products

@PageNo – This parameter will allow us to navigate to a specific page of the products grid. The default value of 0 will display the first page.

@PageSize – This parameter determines the no of products we want to display per page.

@SortColumn – This parameter will allow us to sort products based on any column. The default value of 0 will sort products using the first column in the grid.

@SortDirection – This parameter determines whether to sort by Ascending (ASC) or Descending (DESC) order.

Test the stored procedure filtering functionality with the following parameters

EXEC [dbo].[spGetProductsList] 'Fork', 0, 10, 0, 'asc' 

You should be able to see the products that have the word Fork in their Name. We are also returning two additional columns FilteredCount and TotalCount which are required by the JQuery DataTables plugin to determine the total no of pages and to generate the paging user interface on the page. The TotalCount specifies the total no of products we have in the Product table and FilteredCount specifies the no of products we found after applying the search filter on the Product table.

To test the paging functionality, call the stored procedure with the following parameters. The page index 0 will display the first page and the reason I am using 0 based index is that the JQuery Datatables plugin will also pass the value 0 to display the first page.

EXEC [dbo].[spGetProductsList] '', 0, 10, 0, 'asc' 

You should be able to see the first page with 10 products as shown below.

Change the page index to 1 and you should be able to see the second page as shown below.

Getting Started with ASP.NET Core 5 Web Application

Create a new project in Visual Studio 2019 using ASP.NET Core 5 MVC Web Application template. To connect our web application with the AdventureWorks database in SQL Server 2016, we need to add the following connection string in the appsettings.json file.

appsettings.json

{
   "ConnectionStrings":{
      "DefaultConnection":"Server=MyDbServer; Database=AdventureWorks; Trusted_Connection=True; MultipleActiveResultSets=true"
   },
   "Logging":{
      "LogLevel":{
         "Default":"Information",
         "Microsoft":"Warning",
         "Microsoft.Hosting.Lifetime":"Information"
      }
   },
   "AllowedHosts":"*"
}

Create a Domain folder in the project and create a domain entity class called Product. You can see that the properties of the domain entity are mapped with the Product table data we are returning from the stored procedure. Each property is mapped with one column of the Product table.

Product.cs

public partial class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; }
    public string ProductNumber { get; set; }
    public string Color { get; set; }
}

To map FilteredCount and TotalCount columns which are not part of the original Product table we can create another Product partial class and add the properties in it. The reason I am creating a separate partial class for these two columns is that you may decide to generate the above Product class using some tool or code generator and every time you will generate an entity class from the database table you will lose FilteredCount and TotalCount properties.

public partial class Product
{
    public int TotalCount { get; set; }
    public int FilteredCount { get; set; }
}

Implement Data Layer using Repository Pattern and Dapper ORM

We are now ready to write our project’s data layer but before that, we need to install the following NuGet packages in our project.

1. Dapper
2. Microsoft.Data.SqlClient

Create the Repositories folder in the project and add the following BaseRepository class that will allow us to group the common methods needed by all the repositories. One such method is the CreateConnection method that creates and returns a new instance of IDbConnection.

BaseRepository.cs

public abstract class BaseRepository
{
    private readonly IConfiguration _configuration;

    protected BaseRepository(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    protected IDbConnection CreateConnection()
    {
        return new SqlConnection(_configuration.GetConnectionString("DefaultConnection"));
    }
}

We can now create a repository interface for each domain entity in the project. In this project, we already created a Product domain entity so let’s create the following IProductRepository interface that has only one method GetProductsAsync. This method accepts the parameter of type ProductListRequest and returns the list of products returned from the database. 

IProductRepository.cs

public interface IProductRepository
{
    Task<List<Product>> GetProductsAsync(ProductListRequest request);
}

Create the following ProductListRequest model class in the Models folder. The class has properties matching with the stored procedure parameters we created above.

ProductListRequest.cs

public class ProductListRequest
{
    public string SearchValue { get; set; }
    public int PageNo { get; set; } = 1;
    public int PageSize { get; set; } = 10;
    public int SortColumn { get; set; }
    public string SortDirection { get; set; } = "ASC"; 
}

Next, we need to create a ProductRepository class that will provide the implementation of the method available in the IProductRepository interface.

ProductRepository.cs

public class ProductRepository : BaseRepository, IProductRepository
    {
        public ProductRepository(IConfiguration configuration)
            : base(configuration)
        { }

        public async Task<List<Product>> GetProductsAsync(ProductListRequest request)
        {
            try
            {
                var procedure = "spGetProductsList";

                var parameters = new DynamicParameters();
                parameters.Add("SearchValue", request.SearchValue, DbType.String);
                parameters.Add("PageNo", request.PageNo, DbType.Int32);
                parameters.Add("PageSize", request.PageSize, DbType.Int32);
                parameters.Add("SortColumn", request.SortColumn, DbType.Int32);
                parameters.Add("SortDirection", request.SortDirection, DbType.String);
        
                using (var connection = CreateConnection())
                {
                    return (await connection.QueryAsync<Product>(procedure, parameters, commandType: CommandType.StoredProcedure)).ToList();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
    }

The method GetProductsAsync implementation is very straightforward as it is simply calling the spGetProductsList stored procedure using the QueryAsync method of Dapper ORM and passing the parameters required by the stored procedure. If you want to know more about Dapper and all its methods you can read my post CRUD Operations in ASP.NET Core 5 using Dapper ORM

Implementing Application Services

We can directly call the ProductRepository method in our ASP.NET Core Web Application but it is always good practice to create an application services layer between your web application and repositories. This service layer allows us to define all the business logic related to our application. In our current application, the main requirement of application service are followings:

1. Accepts paging, filtering, and sorting related parameters coming from the front end and passes them to the GetProductsAsync method of ProductRepository class.
2. Receive the products list from the backend database and send the products to the front-end application in the required format.

We will use the JQuery DataTables plugin on the front-end so we need to know how this plugin passes parameters to the server and the format in which it expects data to be returned from the server. All this information is available in the DataTables manual. I have decided to map all JQuery DataTables request and response parameters to C# classes so that we can use them in different layers of our project.

Create the following DataTableResponse class in the Models folder to map DataTables response parameters to C# class.

DataTableResponse.cs

public class DataTableResponse<T>
{
    public int Draw { get; set; }
    public int RecordsTotal { get; set; }
    public int RecordsFiltered { get; set; }
    public T[] Data { get; set; }
    public string Error { get; set; }
}

Next, create the following DataTableRequest class in the Models folder and map DataTables request parameters to this class.

DataTableRequest.cs

public class DataTableRequest
{
    public int Draw { get; set; }
    public int Start { get; set; }
    public int Length { get; set; }

    public DataTableOrder[] Order { get; set; }
    public DataTableColumn[] Columns { get; set; }
    public DataTableSearch Search { get; set; }
}

This class is also using three other classes which are mapping the Search, Column, and Order information DataTables sends to the server.

public class DataTableSearch
{
    public string Value { get; set; }
    public bool Regex { get; set; }
}

public class DataTableOrder
{
    public int Column { get; set; }
    public string Dir { get; set; }
}

public class DataTableColumn
{
    public string Data { get; set; }
    public string Name { get; set; }
    public bool Searchable { get; set; }
    public bool Orderable { get; set; }

    public DataTableSearch Search { get; set; }
}

We are now ready to implement our application services. Create the following IProductService interface in the Services folder of the project. The interface has just a single method that accepts a request parameter of type DataTableRequest and returns the DataTableResponse<Product> object of type Product.

IProductService.cs

public interface IProductService
{
    public Task<DataTableResponse<Product>> GetProductsAsync(DataTableRequest request);
}

Next, create the following ProductService class in the Services folder. The ProductService class implements the IProductService interface and defines the GetProductsAsync method we declare in the IProductService interface above.

ProductService.cs

public class ProductService : IProductService
{
    private readonly IProductRepository _productRepository;

    public ProductService(IProductRepository productRepository)
    {
        _productRepository = productRepository;
    }

    public async Task<DataTableResponse<Product>> GetProductsAsync(DataTableRequest request)
    {
        var req = new ProductListRequest()
        {
            PageNo = request.Start,
            PageSize = request.Length,
            SortColumn = request.Order[0].Column,
            SortDirection = request.Order[0].Dir,
            SearchValue = request.Search != null ? request.Search.Value.Trim() : ""
        };

        var products = await _productRepository.GetProductsAsync(req);

        return new DataTableResponse<Product>()
        {
            Draw = request.Draw,
            RecordsTotal = products[0].TotalCount,
            RecordsFiltered = products[0].FilteredCount,
            Data = products.ToArray(),
            Error = ""
        };

    }
}

Inside the GetProductAsync method, we are creating an object of ProductListRequest and settings its properties with the properties of the DataTableRequest object. Next, we are passing the ProductListRequest object into the GetProductsAsync method of ProductsRepository class which returns the list of products from the database. Finally, we are creating and returning the DataTableResponse object from our method.

Implementing ASP.NET Core Web API for JQuery DataTables

JQuery DataTables do not call our application services directly. It can call an ASP.NET Core Web API using the AJAX request and can send different parameters to server-side Web API. This means we need to create a Web API that will receive JQuery DataTables related parameters from HTTP requests and will send those parameters to our product service.

Create the following Web API ProductsController in Controllers folder and create a method GetProducts in it.

ProductsController.cs

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private readonly IProductService _productService;

    public ProductsController(IProductService productService)
    {
        _productService = productService;
    }

    // GET: api/Products
    [HttpPost]
    public async Task<DataTableResponse<Product>> GetProducts()
    {
        var request = new DataTableRequest();

        request.Draw = Convert.ToInt32(Request.Form["draw"].FirstOrDefault());
        request.Start = Convert.ToInt32(Request.Form["start"].FirstOrDefault());
        request.Length = Convert.ToInt32(Request.Form["length"].FirstOrDefault());
        request.Search = new DataTableSearch()
        {
            Value = Request.Form["search[value]"].FirstOrDefault()
        };
        request.Order = new DataTableOrder[] { 
            new DataTableOrder()
            {
                Dir = Request.Form["order[0][dir]"].FirstOrDefault(),
                Column = Convert.ToInt32(Request.Form["order[0][column]"].FirstOrDefault())
            }};

        return await _productService.GetProductsAsync(request);
    } 
}

The GetProducts method will accept HTTP Post requests and will create a new DataTableRequest object. It will then convert the JQuery DataTables parameters it receives in the HTTP request and will set the properties of the DataTableRequest object. Finally, it will call the GetProductsAsync method of ProductService class and will pass the DataTableRequest object as the parameter into the method.

Before we implement our Products List Page, we need to register our repositories and services in Startup.cs file as follows otherwise ASP.NET Core dependency container will not be able to inject these classes into the constructors.

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddScoped<IProductRepository, ProductRepository>();
    services.AddScoped<IProductService, ProductService>();
}

Installing JQuery DataTables in ASP.NET Core Web Application

JQuery DataTables plugin is one of the most powerful and famous plugins for creating tabular data grids. It has built-in support for searching, sorting, and pagination without any configuration and there are so many ways to customize this data grid as per your requirements. It also has some advanced features such as support for AJAX, server-side processing, and styling according to some of the popular frameworks e.g. Bootstrap, Material Design, JQuery UI, Foundation, etc. You also have the option to create your own theme using the Theme Creator feature available on the DataTables website.

There are many ways to use JQuery DataTables in your projects

1. We can download the plugin from the DataTables website manually and add it to wwwroot folder of the project.
2. We can install it using tools such as npm or LibMan and add the plugin in wwwroot folder of the project. You can read my post Working with Packages and Libraries in ASP.NET Core to learn more about downloading and installing client-side libraries using npm and LibMan tools.
3. You can directly use the plugin from the Content Delivery Network (CDN). I will use this option to include the plugin in our project.

Open the _Layout.cshtml file and add the JQuery DataTables stylesheet inside <head> element. DataTables I have decided to use the Bootstrap 4 theme and this is why I am including dataTables.bootstrap4.min.css file. If you want to use different theme then choose the theme specific stylesheet file available on DataTables website.

<link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" /> 
<link href="https://cdn.datatables.net/1.10.24/css/dataTables.bootstrap4.min.css" rel="stylesheet" />

We also need to include DataTables related JavaScript files at the bottom of the _Layout.cshtml file just before the closing </body> element. The jquery.dataTables.min.js is the main plugin file and the dataTables.bootstrap4.min.js file is specific to Bootstrap 4 theme.

<script src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"></script>

<script src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.24/js/dataTables.bootstrap4.min.js"></script>

Implementing Products List Page with JQuery DataTables

We are finally ready to use the JQuery DataTables plugin on our page. The plugin normally works with HTML tables so add the following table markup in Index.cshtml file. The markup is using basic bootstrap table classes such as table-bordered, table-striped, etc. I want to display four columns of the Product table so I only included those column headers in the table.

<div class="container">
    <h1>Products</h1>
    <br />

    <table id="products" class="table table-striped table-bordered" style="width:100%">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Number</th>
                <th>Color</th>
            </tr>
        </thead>
    </table>

</div>

The next step is to initialize the DataTables plugin on the page with some basic configurations. Add the following script section at the bottom of the page and make sure that the id in our script matches the table id products in the HTML.

@section Scripts
{
    <script type="text/javascript">
        $(document).ready(function () {
            $('#products').DataTable({
                processing: true,
                serverSide: true,
                searching: true,
                ordering: true,
                paging: true,
                "ajax": {
                    "url": "api/Products",
                    "type": "POST",
                    "datatype": "json"
                },
                columns: [
                    { "data": "productID", "name": "Product Id", "autoWidth": true },
                    { "data": "name", "name": "Product Name", "autoWidth": true },
                    { "data": "productNumber", "name": "Product Number", "autoWidth": true },
                    { "data": "color", "name": "Product Color", "autoWidth": true }
                ]
            });
        });
    </script>
}

The most important configuration in the above code snippet is the ajax setting that specifies the URL of the Web API we just created above. This setting will be used by the DataTables plugin to fetch and bind product data. We also used the columns setting that specifies which data fields to use to generate the columns of the grid. We also enabled searching, ordering, and paging to use our backend API which we know supports all these features. You can read more about all the available configurations in the plugin manual here.

Run the project now and if you have implemented everything properly above, you should be able to see a grid similar to the following.

Try to navigate to any page from the paging interface shown at the bottom of the grid and you should see the products on that page. For example, I clicked to page 3 and the grid is refreshed to display the products on page 3 as shown below.

Try to sort products by clicking on the header of any column. For example, I clicked the Name column header and all products are now sorted alphabetically by their names.  

Try to search products using the search text field shown on top of the grid. For example, I typed the keyword Fork and the grid is refreshed with the products matching with my keyword. Notice how the description is shown at the bottom of the grid is also updated accordingly.

If you want to learn more about JQuery DataTables then you may also want to read my following posts.

1. How to Use JQuery DataTables with ASP.NET Core Web API
2. Using JQuery DataTables with ASP.NET Web API
3. JQuery DataTables Paging, Sorting and Searching with ASP.NET Web API

Summary

JQuery DataTables is a highly flexible and feature-rich plugin with hundreds of features. The Datatables also supports additional plugins that can be plugged with the DataTables to add even more functionality. There is also a powerful and feature-rich API available that can provide you even more ways to use DataTables plugins programmatically. In this tutorial, I have shown you how to implement paging, searching, and filtering features in ASP.NET Core Web Applications using JQuery DataTables plugin. I also showed you how to implement a layered-based architecture and use Dapper ORM to call a SQL Server stored procedure. If you liked this tutorial, please share it with others.