find and solve || findandsolve.com
Please wait.....

Server Side Pagination in ASP.NET Core (.NET 5)-Sorting and Searching

Server Side Pagination in ASP.NET Core (.NET 5)-Sorting and Searching

In this article, I am going to talk about sorting,searching,pagination in .NET 5.I will explain how to do sorting, searching and paging using DataTable from server-side which is very useful whenever we have to display a large number of records.There are a ton of different ways to accomplish shorting,searching and paging but here is how we've been doing it lately with .NET Core MVC  in C# with Store Procedure.


How Will it Work?

Here I am going to sorting the records based on ascending and descending order, so when we click on the header column name it will get the related number of records in order by column name and pass the value in store procedure.The pagesize and page number will calculate from store procedure and return rows.Return rows are displaying on Table usig DataTable.

The end of article you will learn

  1. Create New Application in visual studio 2019
  2. Creating the Database tables-sql script
  3. Insert record in sql datable-sql script
  4. Create store procedure
  5. Creating the DataTable Models
  6. Create razor page,call api with param
  7. Add jquery link in the page
  8. Performing the code for Server Side Pagination
  9. Shorting,searching and paging from server side programming

Download Source Code

Create New Application in visual studio 2019

  1. Open your visual studio 2017 or 2019
  2. Click on Create a New Project
  3. Select ASP.NET Core Web Application and Next
  4. Provide a Project name and confirm or change the Location. Select Create
  5. Select the latest version of ASP.NET Core in the drop-down (.NET Core 5.0)and then select Web Application
  6. Under Authentication, select Change and set the authentication to Individual User Accounts. Select OK
  7. In the Create a new .NET 5 Core Web Application window, select Create


Creating the Database tables-sql script

Create database DataTable and execute.
CREATE DATABASE DataTable
Use database and execute.
Use DataTable 
Create table Paging and execute like as given  below.
CREATE TABLE Paging
(
PagingId INT IDENTITY(1,1)PRIMARY KEY,
FirstName NVARCHAR(200),
LastName NVARCHAR(200),
Email NVARCHAR(200),
Country NVARCHAR(200)
)

Insert record in sql datable-sql script

Insert data in Paging Table and execute as given below

INSERT INTO Paging VALUES('Delis','Senchong','[email protected]','USA')
INSERT INTO Paging VALUES('Fekuili','Sandem','[email protected]','USA')
INSERT INTO Paging VALUES('Menija','Lokila','[email protected]','CANADA')
INSERT INTO Paging VALUES('Selina','Chomengo','[email protected]','CANADA')
INSERT INTO Paging VALUES('Rokila','Junkanji','[email protected]','UK')
INSERT INTO Paging VALUES('Ubena','Umrekeli','[email protected]','UK')
INSERT INTO Paging VALUES('Saira','Jukunji','[email protected]','UK')
INSERT INTO Paging VALUES('Ushrina','jambanphe','[email protected]','UAE')
INSERT INTO Paging VALUES('Tikila','Khan','[email protected]','UAE')
INSERT INTO Paging VALUES('Rojina','Khan','[email protected]','UK')

Create store procedure

I have create store procedure where pass page size,page number,search value and desc,asc like as given below.

CREATE PROCEDURE [dbo].[Paging.GetList]
(
@SearchVal NVARCHAR(300) =NULL,
@Page INT =1,
@PageSize INT =2,
@OrderBy NVARCHAR(300)=NULL,
@Result NVARCHAR(MAX)=NULL OUTPUT
)
AS
BEGIN
  DECLARE @RowNumber VARCHAR(27);
  SET @[email protected];
  SET @Result=(
  'SELECT
   P.FirstName AS FirstName
  ,P.LastName AS LastName
  ,P.Email AS Email
  ,P.Country AS Country
   FROM Paging AS P '

   );
    IF(@SearchVal!=NULL AND @SearchVal!='')
    BEGIN
     SET @[email protected] + 'WHERE P.FirstName LIKE ''%' [email protected] +'%'' OR P.LastName LIKE ''%'[email protected] +'%'' OR A.Email LIKE ''%'[email protected] +'%'' OR P.Country LIKE ''%'[email protected] +'%'' '      END      SET @[email protected] + ' ORDER BY '[email protected]+' OFFSET ' + @RowNumber + ' ROWS FETCH NEXT '+CONVERT(varchar,@PageSize)+' ROWS ONLY '     EXEC(@Result)     SELECT COUNT(*) FROM Paging
 END  

Creating the DataTable Models

Add class DataTableViewModel.cs under the Models folder like as given below.

  public class DataTableViewModel
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Country { get; set; }
        public int Total { get; set; }
        public List<DataTableViewModel> DataTableList { get; set; }
        public DataTableViewModel()
        {             DataTableList = new List<DataTableViewModel>();
        }}

Now Create DataTableHelper.cs under the Models folder. DataTableHelper class use, passing parameter from UI like PageSize,Page number Search value,shorting etc and also append api response data in table.

using Newtonsoft.Json;
using System.Collections.Generic;
namespace ShortingSearachingPaging.Models
{
    public class DataTableHelper
    {
        public class DtResult<T>
        {
            [JsonProperty("draw")]
            public int Draw { get; set; }
            [JsonProperty("recordsTotal")]
            public int RecordsTotal { get; set; }
            [JsonProperty("recordsFiltered")]
            public int RecordsFiltered { get; set; }
            [JsonProperty("data")]
            public IEnumerable<T> Data { get; set; }
            [JsonProperty("error", NullValueHandling = NullValueHandling.Ignore)]
            public string Error { get; set; }
            public string PartialView { get; set; }
        }
        public abstract class DtRow
        {           
            public virtual string DtRowId => null;
            public virtual string DtRowClass => null;
            public virtual object DtRowData => null;
            public virtual object DtRowAttr => null;
        }
        public class DtParameters
        {
            public int Draw { get; set; }
            public DtColumn[] Columns { get; set; }
            public DtOrder[] Order { get; set; }
            public int Start { get; set; }             public int Length { get; set; }
            public DtSearch Search { get; set; }
            public string SortOrder => Columns != null && Order != null && Order.Length > 0
                ? (Columns[Order[0].Column].Data +
                   (Order[0].Dir == DtOrderDir.Desc ? " " + Order[0].Dir : string.Empty))
                : null;
            public IEnumerable<string> AdditionalValues { get; set; }
        }
        public class DtColumn
        {
             public string Data { get; set; }
            public string Name { get; set; }
            public bool Searchable { get; set; }
            public bool Orderable { get; set; }
            public DtSearch Search { get; set; }
        }
        public class DtOrder
        {
            public int Column { get; set; }
            public DtOrderDir Dir { get; set; }
        }
        public enum DtOrderDir
        {
            Asc,
            Desc
        }
        public class DtSearch
        {
            public string Value { get; set; }
            public bool Regex { get; set; }
        }
    }
}


Create razor page,call api with param

I am going to create new razor page index.cshtml in HomeController.cs.In the view page I am using dataTables.min.js.

More detail DataTable

@{
    ViewData["Title"] = "Home Page";
}
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css" />
<div class="text-center">
    <div class="col-lg-12">
        <div class="card">
            <div class="card-body">
                <div class="table-responsive">
                    <table id="tableId" class="table table-bordered" cellspacing="0" width="100%">
                        <thead>
                            <tr>
                                <th>First Name</th>
                                <th>Last Name</th>
                                <th>Email</th>
                                <td>Country</td>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

@section Scripts{
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script>
        var baseUrl = "@Url.Content("~/")";
        $(function () {
            LoadTable();
            $(document).keypress(function (e) {
                if (e.which == 13) {
                    LoadTable();
                }
            });
        })
        function LoadTable() {
            $('#tableId').dataTable().fnDestroy();
            $('#tableId').dataTable({
                "pageLength":3,
                "serverSide": true,
                "processing": true,
                "filter": true,
                "ordering": true,
                "deferRender": true,
                "drawCallback": function () {
                    $("#dataTable_wrapper").children().eq(1).css("overflow", "auto");
                },
                "ajax": {
                    "type": "POST",
                    "url": baseUrl + "Home/_GetList",
                    "contentType": "application/json; charset=utf-8",
                    //"headers": { 'RequestVerificationToken': $('#__RequestVerificationToken').val() },
                    "data": function (data) {
                        return JSON.stringify(data);
                    }
                },
                "columnDefs": [
                    {
                        "searchable": true,
                        "orderable": true,
                        "targets": [0,1,2,3]
                    }, {
                        "targets": [1, 2, 3],
                        "render": $.fn.dataTable.render.text()
                    }, {
                        "targets": 1,
                        "visible": true
                    }
                ],
                "columns": [
                    { "data": "firstName" },
                    { "data": "lastName" },
                    { "data": "email" },
                    { "data": "country" },
                ],               
            "order":[1,"desc"]
            })
        }
    </script>
}

In my HomeController
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using ShortingSearachingPaging.Models;
using System;
using System.Data;
using System.Diagnostics;
using System.Linq;
using static ShortingSearachingPaging.Models.DataTableHelper;
namespace ShortingSearachingPaging.Controllers
{
    public class HomeController : Controller
    {
        private string _connectionString;
        public HomeController(DataContext dataContext, IConfiguration iconfiguration)
        {
          _connectionString =iconfiguration.GetConnectionString("DefaultConnection");         }
        public IActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public JsonResult _GetList([FromBody] DtParameters param)
        {
            var data = new DataTableViewModel();
            using (SqlConnection con = new SqlConnection(_connectionString))
            {                 con.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "[dbo].[Paging.GetList]";
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("SearchVal", param.Search.Value);
                    cmd.Parameters.AddWithValue("Page", param.Start);
                    cmd.Parameters.AddWithValue("OrderBy", param.SortOrder);
                    cmd.Parameters.AddWithValue("PageSize", param.Length);
                    cmd.CommandTimeout = 120;
                    DataSet ds = new DataSet();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    foreach (DataRow item in ds.Tables[0].Rows)
                    {
                        DataTableViewModel model = new DataTableViewModel();
                        model.Email = item["Email"].ToString();
                        model.FirstName = item["FirstName"].ToString();
                        model.LastName = item["LastName"].ToString();
                        model.Country = item["Country"].ToString();                         data.DataTableList.Add(model);
                    }
                    data.Total = Convert.ToInt32(ds.Tables[1].Rows[0].ItemArray[0]);
                }
                con.Close();
            }
            var helper = new DtResult<DataTableViewModel>()
            {
                Draw =param.Draw,
                Data = data.DataTableList.ToList(),
                RecordsFiltered =data.Total,
                RecordsTotal = data.Total
            };
            return Json(helper);
        }       
    }
}

Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

Comments



Report Response