In Focus

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

In this article, we will learn how we can integrate the datatable plugin with server-side pagination, sorting, and searching.

This is a basic requirement for all the web applications to display the data. So, client-side pagination is not good when we have large data.
 

Why Server Side Pagination?

 
Server-side pagination is very useful for the web applications to handle the data because the data stored by the client is actually smaller than the actual data that exists on the server. In Server-side pagination, the data is loaded on a demand basis.

Prerequisites

  • Knowledge of .NET Core Web API
  • Basic knowledge of Angular 7
  • NodeJS must be installed
  • Angular CLI must be installed
This article will be covered in two parts. Part 1 contains the back-end logic and Part 2 contains front-end development. So, let's get started with building our back-end 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 following code in it.
  1. using Microsoft.EntityFrameworkCore;  
  2. namespace AngularDatatable.Entity {  
  3.   
  4.  public class Context: DbContext {  
  5.   
  6.   public Context(DbContextOptions < Context > options): base(options) {}  
  7.   
  8.   public DbSet < Users > Users {   get;   set;  }  
  9.  }  
  10.   
  11. }  
Create Users.cs file and add this code in it.
  1. using System.ComponentModel.DataAnnotations;  
  2.   
  3. namespace AngularDatatable.Entity {  
  4.       
  5.  public class Users {  
  6.        
  7.   [Key] public int ID {  
  8.    get;  
  9.    set;  
  10.   }  
  11.   public string Name {  
  12.    get;  
  13.    set;  
  14.   }  
  15.   public string Email {  
  16.    get;  
  17.    set;  
  18.   }  
  19.   public string Company {  
  20.    get;  
  21.    set;  
  22.   }  
  23.   public string ContactNumber {  
  24.    get;  
  25.    set;  
  26.   }  
  27.  }  
  28. }  
Now, in the Models folder, create the below class files as Column, Order, PagingRequest, PagingRequest, Search, and SearchCriteria respectively.
 
Code for Column.cs file
  1. using Newtonsoft.Json;  
  2. namespace AngularDatatable.Models {  
  3.  public class Column {  
  4.   [JsonProperty(PropertyName = "data")] public string Data {  
  5.    get;  
  6.    set;  
  7.   } [JsonProperty(PropertyName = "name")] public string Name {  
  8.    get;  
  9.    set;  
  10.   } [JsonProperty(PropertyName = "searchable")] public bool Searchable {  
  11.    get;  
  12.    set;  
  13.   } [JsonProperty(PropertyName = "orderable")] public bool Orderable {  
  14.    get;  
  15.    set;  
  16.   } [JsonProperty(PropertyName = "search")] public Search Search {  
  17.    get;  
  18.    set;  
  19.   }  
  20.  }  
  21. }  
Code for Order.cs file 
  1. using Newtonsoft.Json;  
  2. namespace AngularDatatable.Models {  
  3.  public class Order {  
  4.   [JsonProperty(PropertyName = "column")] public int Column {  
  5.    get;  
  6.    set;  
  7.   } [JsonProperty(PropertyName = "dir")] public string Dir {  
  8.    get;  
  9.    set;  
  10.   }  
  11.  }  
  12. }  
Code for PagingRequest.cs file
  1. using Newtonsoft.Json;  
  2. using System.Collections.Generic;  
  3. namespace AngularDatatable.Models {  
  4.  public class PagingRequest {  
  5.   [JsonProperty(PropertyName = "draw")] public int Draw {  
  6.    get;  
  7.    set;  
  8.   } [JsonProperty(PropertyName = "columns")] public IList < Column > Columns {  
  9.    get;  
  10.    set;  
  11.   } [JsonProperty(PropertyName = "order")] public IList < Order > Order {  
  12.    get;  
  13.    set;  
  14.   } [JsonProperty(PropertyName = "start")] public int Start {  
  15.    get;  
  16.    set;  
  17.   } [JsonProperty(PropertyName = "length")] public int Length {  
  18.    get;  
  19.    set;  
  20.   } [JsonProperty(PropertyName = "search")] public Search Search {  
  21.    get;  
  22.    set;  
  23.   } [JsonProperty(PropertyName = "searchCriteria")] public SearchCriteria SearchCriteria {  
  24.    get;  
  25.    set;  
  26.   }  
  27.  }  
  28. }  
Code for PagingResponse.cs file 
  1. using AngularDatatable.Entity;  
  2.   
  3. using Newtonsoft.Json;  
  4. namespace AngularDatatable.Models {  
  5.  public class PagingResponse {  
  6.   [JsonProperty(PropertyName = "draw")] public int Draw {  
  7.    get;  
  8.    set;  
  9.   } [JsonProperty(PropertyName = "recordsFiltered")] public int RecordsFiltered {  
  10.    get;  
  11.    set;  
  12.   } [JsonProperty(PropertyName = "recordsTotal")] public int RecordsTotal {  
  13.    get;  
  14.    set;  
  15.   } [JsonProperty(PropertyName = "data")] public Users[] Users {  
  16.    get;  
  17.    set;  
  18.   }  
  19.  }  
  20. }  
Code for Search.cs file
  1. using Newtonsoft.Json;  
  2.   
  3. namespace AngularDatatable.Models {  
  4.  public class Search {  
  5.   [JsonProperty(PropertyName = "value")] public string Value {  
  6.    get;  
  7.    set;  
  8.   } [JsonProperty(PropertyName = "regex")] public bool Regex {  
  9.    get;  
  10.    set;  
  11.   }  
  12.  }  
  13. }  
Code for SearchCriteria.cs file   
  1. using Newtonsoft.Json;  
  2.   
  3. namespace AngularDatatable.Models {  
  4.  public class SearchCriteria {  
  5.   [JsonProperty(PropertyName = "filter")] public string Filter {  
  6.    get;  
  7.    set;  
  8.   } [JsonProperty(PropertyName = "isPageLoad")] public bool IsPageLoad {  
  9.    get;  
  10.    set;  
  11.   }  
  12.  }  
  13. }  
Note
You can create these all six classes into one class.
 
Add a new API Controller as DatatableApiController and add the below code into it.
  1. using System.Linq;  
  2. using AngularDatatable.Entity;  
  3. using AngularDatatable.Models;  
  4. using Microsoft.AspNetCore.Mvc;  
  5.   
  6. namespace AngularDatatable.Controllers  
  7. {  
  8.     [Route("api/[controller]")]  
  9.     [ApiController]  
  10.     public class DatatableApiController : ControllerBase  
  11.     {  
  12.         private readonly Context _context;  
  13.         public DatatableApiController(Context context)  
  14.         {  
  15.             _context = context;  
  16.         }  
  17.         [HttpGet]  
  18.         public IActionResult Get()  
  19.         {  
  20.             var users = _context.Users.ToList();  
  21.             return Ok(users);  
  22.         }  
  23.         [HttpPost]  
  24.         public IActionResult Get(int id)  
  25.         {  
  26.             var recordSkip = id == 1 ? 0 : (id - 1) * 10;  
  27.             var users = _context.Users.OrderBy(emp => emp.ID).Skip(recordSkip).Take(10).ToList();  
  28.             return Ok(users);  
  29.         }  
  30.         [HttpPut]  
  31.         public IActionResult Post([FromBody]PagingRequest paging)  
  32.         {  
  33.             var pagingResponse = new PagingResponse()  
  34.             {  
  35.                 Draw = paging.Draw  
  36.             };  
  37.   
  38.             if (!paging.SearchCriteria.IsPageLoad)  
  39.             {  
  40.                 IQueryable<Users> query = null;  
  41.   
  42.                 if (!string.IsNullOrEmpty(paging.SearchCriteria.Filter))  
  43.                 {  
  44.                     query = _context.Users.Where(emp => emp.Name.Contains(paging.SearchCriteria.Filter));  
  45.                 }  
  46.                 else  
  47.                 {  
  48.                     query = _context.Users;  
  49.                 }  
  50.   
  51.                 var recordsTotal = query.Count();  
  52.   
  53.                 var colOrder = paging.Order[0];  
  54.   
  55.                 switch (colOrder.Column)  
  56.                 {  
  57.                     case 0:  
  58.                         query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.ID) : query.OrderByDescending(emp => emp.ID);  
  59.                         break;  
  60.                     case 1:  
  61.                         query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.Name) : query.OrderByDescending(emp => emp.Name);  
  62.                         break;  
  63.                     case 2:  
  64.                         query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.Email) : query.OrderByDescending(emp => emp.Email);  
  65.                         break;  
  66.                     case 3:  
  67.                         query = colOrder.Dir == "asc" ? query.OrderBy(emp => emp.Company) : query.OrderByDescending(emp => emp.Company);  
  68.                         break;  
  69.                 }  
  70.   
  71.                 pagingResponse.Users = query.Skip(paging.Start).Take(paging.Length).ToArray();  
  72.                 pagingResponse.RecordsTotal = recordsTotal;  
  73.                 pagingResponse.RecordsFiltered = recordsTotal;  
  74.             }  
  75.   
  76.             return Ok(pagingResponse);  
  77.         }  
  78.     }  
  79. }  
Now, finally, in the startup.cs file, make these changes for allowing the Angular application to use the API.
 
Datatables Plugin In Angular 7 Using .NET Core Web API
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using AngularDatatable.Entity;  
  6. using Microsoft.AspNetCore.Builder;  
  7. using Microsoft.AspNetCore.Hosting;  
  8. using Microsoft.AspNetCore.Mvc;  
  9. using Microsoft.EntityFrameworkCore;  
  10. using Microsoft.Extensions.Configuration;  
  11. using Microsoft.Extensions.DependencyInjection;  
  12. using Microsoft.Extensions.Logging;  
  13. using Microsoft.Extensions.Options;  
  14. using Newtonsoft.Json.Serialization;  
  15.   
  16. namespace AngularDatatable  
  17. {  
  18.     public class Startup  
  19.     {  
  20.         public Startup(IConfiguration configuration)  
  21.         {  
  22.             Configuration = configuration;  
  23.         }  
  24.   
  25.         public IConfiguration Configuration { get; }  
  26.   
  27.         public void ConfigureServices(IServiceCollection services)  
  28.         {  
  29.             services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1)  
  30.             .AddJsonOptions(options =>  
  31.             {  
  32.                 var resolver = options.SerializerSettings.ContractResolver;  
  33.                 if (resolver != null)  
  34.                     (resolver as DefaultContractResolver).NamingStrategy = null;  
  35.             });  
  36.             services.AddDbContext<Context>(options =>  
  37.             options.UseSqlServer(Configuration.GetConnectionString("DevConnection")));  
  38.             services.AddCors();  
  39.         }  
  40.   
  41.         public void Configure(IApplicationBuilder app, IHostingEnvironment env)  
  42.         {  
  43.             if (env.IsDevelopment())  
  44.             {  
  45.                 app.UseDeveloperExceptionPage();  
  46.             }  
  47.             app.UseCors(options =>  
  48.             options.WithOrigins("http://localhost:4200").AllowAnyMethod().AllowAnyHeader());  
  49.             app.UseMvc();  
  50.         }  
  51.     }  
  52. }  
For front-end development, read Part 2 of this article.