Site icon Tutexchange

Creating Grid View in ASP.NET Core with Custom sorting

Advertisements

Creating Grid View in ASP.NET Core with Custom sorting

Link to Download Source CodeDownload

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

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.

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 int sortBy;
        public bool isAsc { get; set; }
        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> ProductPaginationandSorting(string orderBy, 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_Sorting for paging and sorting products

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

Create proc [dbo].[ProductPagination_Sorting]    
 @OrderBy varchar(20) = null,
 @PageNumber int,    
 @PageSize int    
as    
begin    
  
DECLARE @sqlCommand nvarchar(1000) 
SET @sqlCommand =   
'SELECT ProductId,    
Name,    
Price    
FROM Products ORDER BY '+ @OrderBy  +  
' 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. 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> ProductPaginationandSorting(string orderBy, int? pageNumber, int pageSize)
        {
            using (SqlConnection con = new SqlConnection(ShareConnectionString.Value))
            {
                var para = new DynamicParameters();
                para.Add("@orderBy", orderBy);
                para.Add("@PageNumber", pageNumber);
                para.Add("@PageSize", pageSize);
                var data = con.Query<Product>(" ProductPagination_Sorting", 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 3 parameters as input.

Sortby: – Sortby by Column id.

isAsc: – is ascending and descending

Page: – 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 parameters (sortby, isAsc, page)

Next, we create an instance of ProductPagingInfo class and assign values and send to view to render. 

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 sortby, bool isAsc = true, int? page = 1)
        {
            ProductPagingInfo ProductPagingInfo = new ProductPagingInfo();

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

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

            string sortColumn;
            #region SortingColumn
            switch (sortby)
            {
                case 1:
                    if (isAsc)
                        sortColumn = "ProductId";
                    else
                        sortColumn = "ProductId Desc";
                    break;

                case 2:
                    if (isAsc)
                        sortColumn = "Name";
                    else
                        sortColumn = "Name Desc";
                    break;

                case 3:
                    if (isAsc)
                        sortColumn = "Price";
                    else
                        sortColumn = "Price Desc";
                    break;
                default:
                    sortColumn = "ProductId asc";
                    break;

            }
            #endregion

            int totalProductCount = _IProduct.GetProductsCount();
            var products = _IProduct.ProductPaginationandSorting(sortColumn, page, pageSize).ToList();
            var productsPagedList = new StaticPagedList<Product>(products, pageIndex + 1, pageSize, totalProductCount);
            ProductPagingInfo.Products = productsPagedList;
            ProductPagingInfo.pageSize = page;
            ProductPagingInfo.sortBy = sortby;
            ProductPagingInfo.isAsc = isAsc;
            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,
                    sortby = Model.sortBy
            }),
            PagedListRenderOptions.OnlyShowFivePagesAtATime)
                </td>
            </tr>
        </tbody>
    </table>
</div>

After we have completed with adding View Grid next we are going to create taghelper for the header for proving sorting feature.

Adding TagHelper

Before creating tag helper, we are going to add new New folder with name CustomTagHelper in this folder we are going to add a class with name SorterTagHelper which will inherit TagHelper class and override Process method where we are going to write logic for generating header and showing arrows of sorting. In SorterTagHelper class, we are also injecting dependency of IUrlHelperFactory interface, which we are going to use for generating action link.

The SorterTagHelper takes three Attributes as input (isAsc, sortby, pagesize).

In Process method we know we have fix header to generate for that I have created a list of string which contains header names. We have 3 fix headers means we are going to iterate three times.
First, we are going to add single ‘tr‘ table row inside that we are going to add ‘th‘ table header inside that table header ‘th‘ we are going to add action link which contains sorting and paging and toggle logic of ascending and descending for display arrows on headers.

Arrows of sorting are applied using CSS and images of arrows are stored in wwwroot -> images.

Code snippet of Tag helpers

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.AspNetCore.Mvc.Routing;
using Microsoft.AspNetCore.Mvc.ViewFeatures;
using Microsoft.AspNetCore.Razor.TagHelpers;
using System.Collections.Generic;

namespace WebGridDemo.CustomTagHelper
{
    [HtmlTargetElement("tr", Attributes = "isAsc,sortby,pagesize")]
    public class SorterTagHelper : TagHelper
    {
        private const int V = 0;
        private IUrlHelperFactory urlHelperFactory;
        public SorterTagHelper(IUrlHelperFactory helperFactory)
        {
            urlHelperFactory = helperFactory;
        }

        #region Input Attributes
        [ViewContext]
        [HtmlAttributeNotBound]
        public ViewContext ViewContext { get; set; }

        [HtmlAttributeName("isAsc")]
        public bool isAsc { get; set; }

        [HtmlAttributeName("sortby")]
        public int sortby { get; set; }

        [HtmlAttributeName("pagesize")]
        public int? pagesize { get; set; } 
        #endregion

        public override void Process(TagHelperContext context, TagHelperOutput output)
        {
            IUrlHelper urlHelper = urlHelperFactory.GetUrlHelper(ViewContext);

            List<string> li = new List<string>()
            {
                "ID","Name","Price"
            };

            TagBuilder tr = new TagBuilder("tr");
            int headerid = V;
            for (int row = 1; row <= 3; row++)
            {
                TagBuilder th = new TagBuilder("th");
                TagBuilder tag = new TagBuilder("a");
                var togglesort = (row == sortby ? (!isAsc).ToString() : "true");

                tag.Attributes["href"] = urlHelper.Action("Grid", "Demo", new { page = pagesize, sortby = row, isAsc = togglesort });
                tag.InnerHtml.Append(li[headerid]);

                if (sortby != 0)
                {
                    if (row == sortby)
                    {
                        TagBuilder tagspan = new TagBuilder("span");
                        tagspan.AddCssClass($"arrow {(isAsc ? "up" : "down")}");
                        th.InnerHtml.AppendHtml(tagspan);
                    }
                }
                th.InnerHtml.AppendHtml(tag);
                tr.InnerHtml.AppendHtml(th);
                headerid += 1;
            }
            output.Content.AppendHtml(tr.InnerHtml);
        }
    }
}

After creating SorterTagHelper next, we use it on View.

 <thead>
      <tr sortby="@Model.sortBy" pagesize="@Model.pageSize" isAsc="@Model.isAsc"></tr>
 </thead>

As SorterTagHelper takes 3 parameters as input, we need to pass these parameters to render header.

<table class="table table-bordered">
        <thead>
            <tr sortby="@Model.sortBy" pagesize="@Model.pageSize" isAsc="@Model.isAsc"></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,
                    sortby = Model.sortBy
            }),
            PagedListRenderOptions.OnlyShowFivePagesAtATime)
                </td>
            </tr>
        </tbody>
    </table>

After adding SorterTagHelper on view next, we are going to save this run application to Test and see how grid view and sorting features work.

Output

Exit mobile version