Datatables Plugin in Angular 7 Using .NET Core Web API Part One

Here, we will learn about how can we integrate the datatable plugin with server-side pagination, sorting and searching. As it is the basic requirement for all the web application to display the data. So client side pagination is not good.

Why Server Side Pagination?

Server side pagination is very useful for the web application to handle the data by the client is very small than the actual data exist in the server. The data are loaded on a demand basis.

Prerequisite

• Knowledge of .NET Core Web API
• Basic knowledge of Angular 7
• Node JS must be installed
• Angular CLI must be installed

This article will be covered in two parts. Part-1 contains the backend logic and Part-2 contains frontend development.

So, let’s get started building our backend application

Create a new project. We are going to use the code first approach for it.

Add a new folder as Entity and Create a Context.cs file inside the folder and add the code in it.

using Microsoft.EntityFrameworkCore;

namespace AngularDatatable.Entity
{
    public class Context : DbContext
    {
        public Context(DbContextOptions<Context> options) : base(options) { }
        public DbSet<Users> Users { get; set; }
    }
}

Create Users.cs file and add the code in it.

using System.ComponentModel.DataAnnotations;

namespace AngularDatatable.Entity
{
    public class Users
    {
        [Key]
        public int ID { get; set; }

        public string Name { get; set; }

        public string Email { get; set; }

        public string Company { get; set; }

        public string ContactNumber { get; set; }
    }
}

Now in the Models folder create below class files as Column, Order, PagingRequest, PagingRequest, Search and SearchCriteria respectively.

Code for Column.cs file

using Newtonsoft.Json;

namespace AngularDatatable.Models
{
    public class Column
    {
        [JsonProperty(PropertyName = "data")]
        public string Data { get; set; }

        [JsonProperty(PropertyName = "name")]
        public string Name { get; set; }

        [JsonProperty(PropertyName = "searchable")]
        public bool Searchable { get; set; }

        [JsonProperty(PropertyName = "orderable")]
        public bool Orderable { get; set; }

        [JsonProperty(PropertyName = "search")]
        public Search Search { get; set; }
    }
}

Code for Order.cs file

using Newtonsoft.Json;

namespace AngularDatatable.Models
{
    public class Order
    {
        [JsonProperty(PropertyName = "column")]
        public int Column { get; set; }

        [JsonProperty(PropertyName = "dir")]
        public string Dir { get; set; }
    }
}

Code for PagingRequest.cs file

using Newtonsoft.Json;
using System.Collections.Generic;

namespace AngularDatatable.Models
{
    public class PagingRequest
    {
        [JsonProperty(PropertyName = "draw")]
        public int Draw { get; set; }

        [JsonProperty(PropertyName = "columns")]
        public IList<Column> Columns { get; set; }

        [JsonProperty(PropertyName = "order")]
        public IList<Order> Order { get; set; }

        [JsonProperty(PropertyName = "start")]
        public int Start { get; set; }

        [JsonProperty(PropertyName = "length")]
        public int Length { get; set; }

        [JsonProperty(PropertyName = "search")]
        public Search Search { get; set; }

        [JsonProperty(PropertyName = "searchCriteria")]
        public SearchCriteria SearchCriteria { get; set; }
    }
}

Code for PagingResponse.cs file

using AngularDatatable.Entity;
using Newtonsoft.Json;

namespace AngularDatatable.Models
{
    public class PagingResponse
    {
        [JsonProperty(PropertyName = "draw")]
        public int Draw { get; set; }

        [JsonProperty(PropertyName = "recordsFiltered")]
        public int RecordsFiltered { get; set; }

        [JsonProperty(PropertyName = "recordsTotal")]
        public int RecordsTotal { get; set; }

        [JsonProperty(PropertyName = "data")]
        public Users[] Users { get; set; }
    }
}

Code for Search.cs file

using Newtonsoft.Json;

namespace AngularDatatable.Models
{
    public class Search
    {
        [JsonProperty(PropertyName = "value")]
        public string Value { get; set; }

        [JsonProperty(PropertyName = "regex")]
        public bool Regex { get; set; }
    }
}

Code for SearchCriteria.cs file

using Newtonsoft.Json;

namespace AngularDatatable.Models
{
    public class SearchCriteria
    {
        [JsonProperty(PropertyName = "filter")]
        public string Filter { get; set; }

        [JsonProperty(PropertyName = "isPageLoad")]
        public bool IsPageLoad { get; set; }
    }
}

Add a new Api Controller as DatatableApiController and add the code in it.

using System.Linq;
using AngularDatatable.Entity;
using AngularDatatable.Models;
using Microsoft.AspNetCore.Mvc;

namespace AngularDatatable.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DatatableApiController : ControllerBase
    {
        private readonly Context _context;
        public DatatableApiController(Context context)
        {
            _context = context;
        }
        [HttpGet]
        public IActionResult Get()
        {
            var users = _context.Users.ToList();
            return Ok(users);
        }
        [HttpPost]
        public IActionResult Get(int id)
        {
            var recordSkip = id == 1 ? 0 : (id - 1) * 10;
            var users = _context.Users.OrderBy(emp => emp.ID).Skip(recordSkip).Take(10).ToList();
            return Ok(users);
        }
        [HttpPut]
        public IActionResult Post([FromBody]PagingRequest paging)
        {
            var pagingResponse = new PagingResponse()
            {
                Draw = paging.Draw
            };

            if (!paging.SearchCriteria.IsPageLoad)
            {
                IQueryable<Users> query = null;

                if (!string.IsNullOrEmpty(paging.SearchCriteria.Filter))
                {
                    query = _context.Users.Where(emp => emp.Name.Contains(paging.SearchCriteria.Filter));
                }
                else
                {
                    query = _context.Users;
                }

                var recordsTotal = query.Count();

                var colOrder = paging.Order[0];

                switch (colOrder.Column)
                {
                    case 0:
                        query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.ID) : query.OrderByDescending(emp => emp.ID);
                        break;
                    case 1:
                        query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.Name) : query.OrderByDescending(emp => emp.Name);
                        break;
                    case 2:
                        query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.Email) : query.OrderByDescending(emp => emp.Email);
                        break;
                    case 3:
                        query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.Company) : query.OrderByDescending(emp => emp.Company);
                        break;
                }

                pagingResponse.Users = query.Skip(paging.Start).Take(paging.Length).ToArray();
                pagingResponse.RecordsTotal = recordsTotal;
                pagingResponse.RecordsFiltered = recordsTotal;
            }

            return Ok(pagingResponse);
        }
    }
}

Now finally in the startup.cs file make the changes for allowing the angular application to use the API.

Code for StartUp.cs file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AngularDatatable.Entity;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json.Serialization;

namespace AngularDatatable
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1)
            .AddJsonOptions(options =>
            {
                var resolver = options.SerializerSettings.ContractResolver;
                if (resolver != null)
                    (resolver as DefaultContractResolver).NamingStrategy = null;
            });
            services.AddDbContext<Context>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DevConnection")));
            services.AddCors();
        }

        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseCors(options =>
            options.WithOrigins("http://localhost:4200").AllowAnyMethod().AllowAnyHeader());
            app.UseMvc();
        }
    }
}

We will continue the frontend development in Part 2 of this article.