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

How to implement dataTables Server side Side Paging/Searching/Sorting in ASP.net Core

Download source code from here
How to implement dataTables Server side Side Paging/Searching/Sorting in ASP.net Core

In this article, I am going to talk about sorting, searching, and pagination in .NET 5. I will explain how to do sorting, searching, and paging using DataTable from the 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.


Server-side pagination is a technique for dividing a large result set into smaller pages that are loaded on demand. This can improve the performance of your application, especially when dealing with large data sets. In ASP.NET Core (.NET 5), you can implement server-side pagination using the Skip and Take methods of the IQueryable interface.



How Will it Work?

Here I am going to sort 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 page size and page number will calculate from the store procedure and return rows. Return rows are displayed on the Table using DataTable.

At the end of the article, you will learn

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

Server-side pagination is a technique for dividing a large result set into smaller pages that are loaded on demand. This can improve the performance of your application, especially when dealing with large data sets. In ASP.NET Core (.NET 5), you can implement server-side pagination using the Skip and Take methods of the IQueryable interface.

To sort and search the data in your result set, you can use the OrderBy, ThenBy, and Where methods of IQueryable.

public async Task<IActionResult> Index(int page = 1, string sort = "name", string search = "")
{     var customers = _context.Customers.AsQueryable();
    // Search
    if (!string.IsNullOrEmpty(search))
    {
        customers = customers.Where(c => c.Name.Contains(search) || c.Email.Contains(search));
    }
    // Sort
    if (sort == "name")
    {
        customers = customers.OrderBy(c => c.Name);
    }
    else if (sort == "email")
    {
        customers = customers.OrderBy(c => c.Email);
    }
    // Paginate
    var pageSize = 10;
    var totalCustomers = await customers.CountAsync();
    var totalPages = (int)Math.Ceiling(totalCustomers / (double)pageSize);
    customers = customers.Skip((page - 1) * pageSize).Take(pageSize);     var model = new CustomerIndexViewModel
    {
        Customers = customers,
        Page = page,
        TotalPages = totalPages,
        Sort = sort,
        Search = search
    };
    return View(model);
}

This example defines an Index action that accepts three parameters: page, sort, and search. The page parameter specifies the current page number, the sort parameter specifies the sort column, and the search parameter specifies the search query. The action first retrieves a list of customers from the database using Entity Framework Core. It then applies the search and sort criteria using the Where and OrderBy methods of IQueryable. Finally, it paginates the result set using the Skip and Take methods. I hope this helps! Let me know if you have any other questions about server-side pagination in ASP.NET Core.

Code  Example

  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 created store procedures 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

Before working on HomeController you should install the given package from NuGet.

Microsoft.AspNetCore.Mvc.NewtonsoftJson

Add this code in your Startup.cs class under the ConfigureServices method as given below.

services.AddControllersWithViews().AddNewTonsoftjsosn();

Now 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);
        }       
    }
}

Related information

Sundar  Neupane

Sundar Neupane

I like working on projects with a team that cares about creating beautiful and usable interfaces.

If findandsolve.com felt valuable to you, feel free to share it.

Comments



Report Response