Server Side Pagination Using DataTable In .NET Core

Today, we will learn about how to create server side DataTable with it’s full functionality like searching, sorting and pagination. DataTable is most commonly used for grid and it’s power retains as it provides the customized functionality also.

Pre-requisites

• Basic knowledge of .NET Core and Entity Framework

Roadmap for developing the application

• Creating the new .NET Core application
• Creating the Database tables
• Creating the DataTable Models and Linq Extensions
• Performing the code for Server Side Pagination
• Code in Action

Creating the new .NET Core application

Create a new .NET Core application with Identity Framework or take any existing .NET Core application in which Entity Framework is configured.

I will be create a new .NET Core application with Identity Framework as ServerSideDataTableInNetCore

Creating the Database tables

Create a folder named POCO in Data folder and add a new file Employee.cs in it.

public class Employee
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string FirstSurname { get; set; }
        public string SecondSurname { get; set; }
        public string Street { get; set; }
        public string Phone { get; set; }
        public string ZipCode { get; set; }
        public string Country { get; set; }
        public string Notes { get; set; }
    }

Open the ApplicationDbContext.cs file and add reference in it.

public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public DbSet<Employee> Employees { get; set; }
    }

Open the Package Manager console and upgrade the database.

Add-Migration EmployeeTableAdded
Update-Database

Creating the DataTable Models and Linq Extensions

Create a new file called DatatableModels.cs file in Models folder and add following classes in it. This are basically used as parameter when DataTable sends the request on server.

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 a new folder as Extension in project root directory and add following class as LinqExtensions.cs in it. We will be using it for sorting the data dynamically.

public static class LinqExtensions
    {
        public static IQueryable<T> OrderByDynamic<T>(this IQueryable<T> query, string orderByMember, DtOrderDir ascendingDirection)
        {
            var param = Expression.Parameter(typeof(T), "c");

            var body = orderByMember.Split('.').Aggregate<string, Expression>(param, Expression.PropertyOrField);

            var queryable = ascendingDirection == DtOrderDir.Asc ?
                (IOrderedQueryable<T>)Queryable.OrderBy(query.AsQueryable(), (dynamic)Expression.Lambda(body, param)) :
                (IOrderedQueryable<T>)Queryable.OrderByDescending(query.AsQueryable(), (dynamic)Expression.Lambda(body, param));

            return queryable;
        }
    }

Performing the code for Server Side Pagination

Now, open the HomeController and add following code in it.

public class HomeController : Controller
    {
        private readonly ApplicationDbContext _context;

        public HomeController(ApplicationDbContext context)
        {
            _context = context;
        }
        public async Task<IActionResult> Index()
        {
            await SeedData();
            return View();
        }

        [HttpPost]
        public async Task<IActionResult> LoadTable([FromBody]DtParameters dtParameters)
        {
            var searchBy = dtParameters.Search?.Value;
            var orderCriteria = string.Empty;
            var orderAscendingDirection = true;

            if (dtParameters.Order != null)
            {
                orderCriteria = dtParameters.Columns[dtParameters.Order[0].Column].Data;
                orderAscendingDirection = dtParameters.Order[0].Dir.ToString().ToLower() == "asc";
            }
            else
            {
                orderCriteria = "Id";
                orderAscendingDirection = true;
            }

            var result = await _context.Employees.ToListAsync();

            if (!string.IsNullOrEmpty(searchBy))
            {
                result = result.Where(r => r.Name != null && r.Name.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.FirstSurname != null && r.FirstSurname.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.SecondSurname != null && r.SecondSurname.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.Street != null && r.Street.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.Phone != null && r.Phone.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.ZipCode != null && r.ZipCode.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.Country != null && r.Country.ToUpper().Contains(searchBy.ToUpper()) ||
                                           r.Notes != null && r.Notes.ToUpper().Contains(searchBy.ToUpper()))
                    .ToList();
            }

            result = orderAscendingDirection ? result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Asc).ToList() : result.AsQueryable().OrderByDynamic(orderCriteria, DtOrderDir.Desc).ToList();

            var filteredResultsCount = result.Count();
            var totalResultsCount = await _context.Employees.CountAsync();

            return Json(new
            {
                draw = dtParameters.Draw,
                recordsTotal = totalResultsCount,
                recordsFiltered = filteredResultsCount,
                data = result
                    .Skip(dtParameters.Start)
                    .Take(dtParameters.Length)
                    .ToList()
            });
        }

        public async Task SeedData()
        {
            if (!_context.Employees.Any())
            {
                for (var i = 0; i < 1000; i++)
                {
                    await _context.Employees.AddAsync(new Employee
                    {
                        Name = $"TestName{i}",
                        FirstSurname = $"TestFirstSurname{i}",
                        SecondSurname = $"TestSecondSurname{i}",
                        Street = $"TestStreet{i}",
                        Phone = $"TestPhone{i}",
                        ZipCode = $"TestZipCode{i}",
                        Country = $"TesCountry{i}",
                        Notes = $"TestNotes{i}"
                    });
                }

                await _context.SaveChangesAsync();
            }
        }
    }

We will be adding dummy data it Database if no data exist in the table.

Now, create a new file as app.js in js folder in wwwroot directory and add following code in it.

$(document).ready(function () {

    $("#test-registers").DataTable({
        autoWidth: true,
        processing: true,
        serverSide: true,
        paging: true,
        searching: { regex: true },
        ajax: {
            url: "/Home/LoadTable",
            type: "POST",
            contentType: "application/json",
            dataType: "json",
            data: function (data) {
                return JSON.stringify(data);
            }
        },
        columns: [
            { data: "name" },
            { data: "firstSurname" },
            { data: "secondSurname" },
            { data: "street" },
            { data: "phone" },
            { data: "zipCode" },
            { data: "country" },
            {
                data: "notes",
                render: function (data, type, row) {
                    return `<span style="color: green;">${data}</span>`;
                }
            }
        ]
    });
});

We will also customizing the last column as to see how it works with customization.

Open the Index.cshtml file from Home folder in Views and add the following code in it.

@model IEnumerable<ServerSideDataTableInNetCore.Data.POCO.Employee>
@{
    ViewData["Title"] = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="row">
    <div class="col-md-12">
        <table id="test-registers" class="table">
            <thead>
                <tr>
                    <th>
                        @Html.DisplayNameFor(model => model.Name)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstSurname)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.SecondSurname)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Street)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Phone)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.ZipCode)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Country)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.Notes)
                    </th>
                </tr>
            </thead>
        </table>
    </div>
</div>

@section Styles{
    <link rel="stylesheet" href="~/lib/datatables/css/dataTables.bootstrap4.min.css" asp-append-version="true" />
}

@section Scripts{
    <script src="~/lib/datatables/js/jquery.dataTables.min.js" asp-append-version="true"></script>
    <script src="~/lib/datatables/js/dataTables.bootstrap4.js" asp-append-version="true"></script>
    <script src="~/js/app.js" asp-append-version="true"></script>
}

You can get the DataTable libraries from my GitHub account. I’ll be providing the source code at last.

Open the _Layout.cshtml file and add following replace it with following HTML.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - ServerSideDataTableInNetCore</title>

    <environment include="Development">
        <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.css" />
    </environment>
    <environment exclude="Development">
        <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
              asp-fallback-href="~/lib/bootstrap/dist/css/bootstrap.min.css"
              asp-fallback-test-class="sr-only" asp-fallback-test-property="position" asp-fallback-test-value="absolute"
              crossorigin="anonymous"
              integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" />
    </environment>
    <link rel="stylesheet" href="~/css/site.css" />
    @RenderSection("Styles", required: false)
</head>
<body>
    <header>
        <nav class="navbar navbar-expand-sm navbar-toggleable-sm navbar-light bg-white border-bottom box-shadow mb-3">
            <div class="container">
                <a class="navbar-brand" asp-area="" asp-controller="Home" asp-action="Index">ServerSideDataTableInNetCore</a>
                <button class="navbar-toggler" type="button" data-toggle="collapse" data-target=".navbar-collapse" aria-controls="navbarSupportedContent"
                        aria-expanded="false" aria-label="Toggle navigation">
                    <span class="navbar-toggler-icon"></span>
                </button>
                <div class="navbar-collapse collapse d-sm-inline-flex flex-sm-row-reverse">
                    <partial name="_LoginPartial" />
                    <ul class="navbar-nav flex-grow-1">
                        <li class="nav-item">
                            <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Index">Home</a>
                        </li>
                    </ul>
                </div>
            </div>
        </nav>
    </header>
    <div class="container">
        <partial name="_CookieConsentPartial" />
        <main role="main" class="pb-3">
            @RenderBody()
        </main>
    </div>

    <footer class="border-top footer text-muted">
        <div class="container">
            &copy; 2020 - ServerSideDataTableInNetCore
        </div>
    </footer>

    <environment include="Development">
        <script src="~/lib/jquery/dist/jquery.js"></script>
        <script src="~/lib/bootstrap/dist/js/bootstrap.bundle.js"></script>
    </environment>
    <environment exclude="Development">
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"
                asp-fallback-src="~/lib/jquery/dist/jquery.min.js"
                asp-fallback-test="window.jQuery"
                crossorigin="anonymous"
                integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=">
        </script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js"
                asp-fallback-src="~/lib/bootstrap/dist/js/bootstrap.bundle.min.js"
                asp-fallback-test="window.jQuery && window.jQuery.fn && window.jQuery.fn.modal"
                crossorigin="anonymous"
                integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o">
        </script>
    </environment>
    <script src="~/js/site.js" asp-append-version="true"></script>

    @RenderSection("Scripts", required: false)
</body>
</html>

Code in Action

You can find the source code from my GitHub account from here.

You can also check the Server side pagination in ASP.NET MVC 5 from here.

We have a Dedicated ASP.NET Developers & Programmers team that is completely dedicated and well-versed in their field, and we provide the most scalable, reliable, and secure solutions. They have a proven track record of completing .Net projects for small, medium, and big businesses in a variety of industries. Hire .Net developers in India who are known for their technological expertise and understanding. Our dedicated .Net developers are well-known for designing Net applications for a variety of enterprises all over the world.