Creating Grid View in ASP.NET Core with Custom paging

Creating Grid View in ASP.NET Core with Custom sorting

In this article, we are going to learn how to create a custom grid view with server-side paging.

• Creating ASP.NET Core Application
• Database Design
• Adding Models
• Installing X.PagedList.Mvc.Core NuGet package
• Configuring Connection string in appsettings.json
• Adding Interface IProduct
• Installing NuGet package Microsoft.Data.SqlClient & Dapper
• Adding ProductConcrete class
• Adding Controller
• Adding View Grid
• Output

We are going to create ASP.NET Core Web Application for that we are going to choose ASP.NET Core Web Application template.

Creating ASP.NET Core Application

Next, we are going to set Project Name WebGridDemo and location and in last part, we are going to choose .Net Core framework and ASP.NET Core Version 3.0 as the framework for application and few advance settings for such as configuring https and enabling docker we are not going to enable docker settings for this project.

Now finally click on create button to create a project.
Project structure
The project structure generated according to the configuration.

Database Design

We are going to create a table with the name Products which have all products details which we are going to bind to grid view for displaying records.

Below is a Snapshot of Table Structure and Data in it. 

Product’s Details

Note: – Table script, along with data, is available for download.

After creating the table next, we are going to add two Models.

Adding Models

Adding two models, one for getting products data from database and another for manage paging in Models folder.

• Product
• ProductPagingInfo

Code snippet Product Model

namespace WebGridDemo.Models
{
    public class Product
    {
        public int ProductId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

Installing X.PagedList.Mvc.Core NuGet package

Before adding ProductPagingInfo model, we are going to adding X.PagedList.Mvc.Core package from NuGet’s.

What is X.PagedList.Mvc.Core?
PagedList is a library that enables you to easily take an IEnumerable/IQueryable, chop it up into “pages“, and grab a specific “page” by an index. PagedList.Mvc allows you to take that “page” and display a pager control that has links like “Previous“, “Next.”

Referend from: – https://github.com/dncuug/X.PagedList

We are going to use StaticPagedList class for paging which is in X.PagedList.Mvc.Core package.

Code snippet of ProductPagingInfo Model

using X.PagedList;

namespace WebGridDemo.Models
{
    public class ProductPagingInfo
    {
        public int? pageSize;
        public StaticPagedList<Product> Products { get; set; }
    }
}

Next, we are going set up a database connection for getting Products data.

Configuring Connection string in appsettings.json

Here in ASP.NET core, we don’t have web.config file which was there in the older version of ASP.NET and ASP.NET MVC here we have appsettings.json file where we are going to keep application-level settings, and this file is Json based.

appsettings.json file

In this file, we have added ConnectionStrings section inside that we have added DatabaseConnection key and other is value.

Notice: – here I am using SQL based authentication for database connection that why I have added User Id and password of SQL server.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DatabaseConnection": "Data Source=SAI-PC\\SQLEXPRESS; initial catalog=Sample2; user id=sa; password=Pass$123"
  }
}

Next, after adding the connection string for accessing it in the entire application, we are going to add a class with Name ShareConnectionString and set class as static inside that class we are going to add a static property named Value.

public static class ShareConnectionString
{
     public static string Value { get; set; }
}

After adding class Next, we are going to read the value of connection string from appsettings.json file.

 var connection = Configuration.GetConnectionString("DatabaseConnection");

After reading the value of connection string we are going to assign value to the static class.

ShareConnectionString.Value = connection;

Code Snippet

public void ConfigureServices(IServiceCollection services)
 {
     //Getting Connection String from Database
     var connection = Configuration.GetConnectionString("DatabaseConnection");
     ShareConnectionString.Value = connection;

     services.AddControllersWithViews();
 }

After assigning value we can access connection string in the entire application.
Next, we are going to add a Repository folder and add Interface and Concrete class in it.

Adding Interface IProduct

using System.Collections.Generic;
using WebGridDemo.Models;

namespace WebGridDemo.Repository
{
    public interface IProduct
    {
        int GetProductsCount();
        List<Product> ProductPagination(int? pageNumber, int pageSize);
    }
}

Installing NuGet package Microsoft.Data.SqlClient & Dapper

After adding interface Next, we are going to add a Concrete class ProductConcrete which inherit interface IProduct. Now to in ProductConcrete class, we are going to write logic to access database for accessing database we need two packages.

1. Microsoft.Data.SqlClient
2. Dapper

After installing the package next, we are going to create a stored procedure for getting products, and after that, we are going to write the logic for accessing this stored procedure.

Stored procedure GetProductCount for getting product count

CREATE PROC [dbo].[GetProductCount]
AS
BEGIN
SELECT count(ProductId) FROM Products
END

Stored procedure ProductPagination for getting products

Paging logic is implemented in this procedure using keyword fetch and offset.

Create proc [dbo].[ProductPagination]    
@PageNumber int,    
@PageSize int    
as    
begin    
  
DECLARE @sqlCommand nvarchar(1000)  
SET @sqlCommand =   
'SELECT ProductId,    
Name,    
Price    
FROM Products ORDER BY ProductId'+  
' OFFSET '+ CONVERT(varchar(10),@PageSize)  +'*'+ '('+ CONVERT(varchar(10),@PageNumber) +'-' +'1) ROWS FETCH NEXT '+ CONVERT(varchar(10),@PageSize) +' ROWS ONLY OPTION (RECOMPILE); ';   
  
print @sqlCommand  
 EXEC sp_executesql @sqlCommand;  
end  

After creating a procedure for getting product count and products next, we are going to add Class with name ProductConcrete and implement IProduct Interface, and in ProductConcrete class we are going to use dapper ORM to get data from database using a stored procedure which we have created.

Adding ProductConcrete class

using Dapper;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using WebGridDemo.Models;

namespace WebGridDemo.Repository
{
    public class ProductConcrete : IProduct
    {
        public int GetProductsCount()
        {
            using (SqlConnection con = new SqlConnection(ShareConnectionString.Value))
            {
                var para = new DynamicParameters();
                var data = con.Query<int>("GetProductCount", para, commandType: CommandType.StoredProcedure).FirstOrDefault();
                return data;
            }
        }

        public List<Product> ProductPagination(int? pageNumber, int pageSize)
        {
            using (SqlConnection con = new SqlConnection(ShareConnectionString.Value))
            {
                var para = new DynamicParameters();
                para.Add("@PageNumber", pageNumber);
                para.Add("@PageSize", pageSize);
                var data = con.Query<Product>("ProductPagination", para, commandType: CommandType.StoredProcedure).ToList();
                return data;
            }
        }

    }
}

Next step after Creating a concrete class is to Register Both interface (IProduct) and Concrete (ProductConcrete) class as Transient service in ConfigureServices Method in Startup class.

Registering IProduct & ProductConcrete as Transient service

public void ConfigureServices(IServiceCollection services)
{
    var connection = Configuration.GetConnectionString("DatabaseConnection");
    ShareConnectionString.Value = connection;
    services.AddTransient<IProduct, ProductConcrete>();
    services.AddControllersWithViews();
}

Adding Controller

After registering Next, we are going to add Controller with name DemoController with grid action method which takes a single parameter as input.

Page: – the page number of the current page.

We are using constructor injection for injecting the dependency, and then we are accessing GetProductsCount, ProductPagination method to get product count and product data and base on the parameter (page number page)
Next, we are creating an instance of ProductPagingInfo class and assign values and send to view to display.

Code snippet of DemoController

using Microsoft.AspNetCore.Mvc;
using System.Linq;
using WebGridDemo.Models;
using WebGridDemo.Repository;
using X.PagedList;

namespace WebGridDemo.Controllers
{
    public class DemoController : Controller
    {
        IProduct _IProduct;
        public DemoController(IProduct IProduct)
        {
            _IProduct = IProduct;
        }
        public IActionResult Grid(int? page = 1)
        {
            ProductPagingInfo ProductPagingInfo = new ProductPagingInfo();

            if (page < 0)
            {
                page = 1;
            }

            var pageIndex = (page ?? 1) - 1;
            var pageSize = 5;

            int totalProductCount = _IProduct.GetProductsCount();
            var products = _IProduct.ProductPagination( page, pageSize).ToList();
            var productsPagedList = new StaticPagedList<Product>(products, pageIndex + 1, pageSize, totalProductCount);
            ProductPagingInfo.Products = productsPagedList;
            ProductPagingInfo.pageSize = page;
            return View(ProductPagingInfo);
        }
    }
}

After adding controller next, we are going to Add View with name Grid view expects strongly type object ProductPagingInfo.

Adding View Grid

On View, we have ProductPagingInfo Model which has products collection which we are going to iterate to show product on-grid, and for displaying paging, we are using PagedListPager helper method of X.PagedList.Mvc.Core package.

Code snippet of Grid View

@{
    Layout = null;
}
@model ProductPagingInfo
@using X.PagedList.Mvc.Core;
@using X.PagedList;
@using X.PagedList.Mvc.Core.Common;

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>

<style>

    .arrow {
        display: inline-block;
        height: 16px;
        vertical-align: middle;
        width: 16px;
    }

        .arrow.up {
            background-image: url('/images/arrow-up.png');
        }

        .arrow.down {
            background-image: url('/images/arrow-down.png');
        }
</style>

<div class="container">
    <h4>List of Products</h4>
    <table class="table table-bordered">
         <thead>
            <tr>
                <th>
                    ProductId
                </th>
                <th>
                    Name
                </th>
                <th>
                    Price
                </th>
            </tr>
        </thead>
        <tbody>
            @foreach (var product in Model.Products)
            {
                <tr>
                    <td>
                        @product.ProductId
                    </td>
                    <td>
                        @product.Name
                    </td>
                    <td>
                        @product.Price
                    </td>
                </tr>
            }
            <tr>
                <td colspan="3">
                    @Html.PagedListPager((IPagedList)Model.Products,
            page => Url.Action("Grid", new
                {
                    page = page
            }),
            PagedListRenderOptions.OnlyShowFivePagesAtATime)
                </td>
            </tr>
        </tbody>
    </table>
</div>

After we have completed with adding View Grid now we can run an application to Test and see how grid view and paging features work.

Output