jQuery DataTables With ASP.NET Core Server Side Processing

In this article we will try to look at jQuery DataTables with Asp.Net Core Server Side. The goal of this article is to make multiple column server-side sorting and searching to be dynamic and easy to implement.

Hello developers, welcome to my first ever article. I feel privileged and happy to help and teach you this. In this article we will try to look at Jquery DataTables with Asp.Net Core Server Side Processing. The primary goal of this article is to make multiple column server-side sorting and searching to be dynamic and easy to implement. There is a plethora of articles available on the same topic. This article is an attempt to explain in my own words with my own implementation using the Nuget Package that I developed.

Background

DataTables is a plug-in for the jQuery JavaScript library. It helps us to add amazing functionalities to a simple HTML table with minimal lines of code. That’s cool and great for simple client-side data. Having said that, once the data grows, it becomes difficult to render all the data in the client side as it has performance issues. So, we’re going to focus on how to handle this on the server side with my implementation. This method is super simple to implement and saves you effort and time.

jQuery DataTables With ASP.NET Core Server Side Processing

Download sample demo code for this article from my GitHub Repo - JqueryDataTablesServerSideDemo

A look at Asp.Net Core Server Side

To add the above functionality to your DataTable all you need to do is to write a little bit of client side jquery logics and add few attributes to your model properties. That’s very simple right? Trust me I have written all the processing logics as JqueryDataTables.ServerSide.AspNetCoreWeb Nuget Package which will do all the heavy lifting works for you. You just need to add this package into your project.

  1. public class Demo      
  2.  {      
  3.      public int Id { getset; }      
  4.    
  5.      [SearchableString]      
  6.      [Sortable(Default = true)]      
  7.      public string Name { getset; }      
  8.    
  9.      [SearchableString]      
  10.      [Sortable]      
  11.      public string Position { getset; }      
  12.    
  13.      [SearchableString]      
  14.      [Sortable]      
  15.      public string Office { getset; }      
  16.    
  17.      [SearchableInt]      
  18.      [Sortable]      
  19.      public int Extn { getset; }      
  20.    
  21.      [SearchableDateTime]      
  22.      [Sortable]      
  23.      public DateTime StartDate { getset; }      
  24.    
  25.      [SearchableLong]      
  26.      [Sortable]      
  27.      public long Salary { getset; }      
  28.  }     

As shown in the above code, you can enable searching/sorting to the columns by adding [Searchable]/[Sortable] attributes to your model properties.

[Sortable] adds the sorting functionality to the columns while [Sortable(Default = true)] will make a default initial sorting of your records.

    • [Sortable] 
    • [Sortable(Default = true)]

[Searchable] adds the searching functionality to the columns. Searching has some flavors added to it based on the data type of the column to help build the search expression dynamically. I have created

    • [Searchable] 
    • [SearchableString]
    • [SearchableInt]
    • [SearchableShort]
    • [SearchableDecimal]
    • [SearchableDouble]
    • [SearchableDateTime]
    • [SearchableLong]

Using the Code

Though I will be explaining this demo with Asp.Net Core MVC, this tutorial is applicable to Asp.Net Core Razor Pages and Asp.Net Core Web API applications as well.

Note
I’m using Asp.Net Core 2.2

  1. I started with an empty solution and added the necessary Nuget Packages to get the demo up and running.

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Let’s begin with the server-side configuration and database setup. For jQuery DataTables to work with Asp.Net Core, we first need to set the Json SerializerSettings to DefaultContractResolver inside the ConfigureServices in the Startup.cs. I have also added AutoMapper to take care of mappings which I’ll explain later.

    For AutoMapper to work add the following Nuget Packages:
    • AutoMapper
    • Extensions.Microsoft.DependencyInjection
      1. services      
      2.         .AddAntiforgery(options => options.HeaderName = "XSRF-TOKEN")      
      3.         .AddMvc()      
      4.         .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());      
  1. I’ll be using an InMemoryDatabase for this demo.

    Note
    If you already have a datatabase setup and you’re familiar with database implementation you can skip to step 8.
    1.             // Use in-memory database for quick dev and testing  
    2.             services.AddDbContext<Fingers10DbContext>(  
    3.                 options =>  
    4.                 {  
    5.                     options.UseInMemoryDatabase("fingers10db");  
    6.                 });  
  1. The Fingers10DbContext class has a DemoEntityDbSet, which will be used to query the datatabase.
    1.     public class Fingers10DbContext:DbContext  
    2.     {  
    3.         public Fingers10DbContext(DbContextOptions options)  
    4.             : base(options) { }  
    5.   
    6.         public DbSet<DemoEntity> Demos { getset; }  
    7.     }  
  1. The DemoEntity class has the following fields.
    1.     public class DemoEntity  
    2.     {  
    3.         [Key]  
    4.         public int Id { getset; }  
    5.         public string Name { getset; }  
    6.         public string Position { getset; }  
    7.         public string Office { getset; }  
    8.         public int Extn { getset; }  
    9.         public DateTime StartDate { getset; }  
    10.         public long Salary { getset; }  
    11.     }  
  1. Now let’s add a static SeedData class to seed the data and call it from the Program.cs.
    1.     public static class SeedData  
    2.     {  
    3.         public static async Task InitializeAsync(IServiceProvider services)  
    4.         {  
    5.             await AddTestData(  
    6.                 services.GetRequiredService<Fingers10DbContext>());  
    7.         }  
    8.   
    9.         public static async Task AddTestData(Fingers10DbContext context)  
    10.         {  
    11.             if(context.Demos.Any())  
    12.             {  
    13.                 // Already has data  
    14.                 return;  
    15.             }  
    16.   
    17.             var testData = new List<DemoEntity>()  
    18.             {  
    19.                 new DemoEntity {  
    20.                     Name = "Airi Satou",  
    21.                     Position = "Accountant",  
    22.                     Office = "Tokyo",  
    23.                     Extn = 5407,  
    24.                     StartDate = new DateTime(2008,11,28),  
    25.                     Salary = 162700  
    26.                 },  
    27.                 new DemoEntity {  
    28.                     Name = "Angelica Ramos",  
    29.                     Position = "Chief Executive Officer (CEO)",  
    30.                     Office = "London",  
    31.                     Extn = 5797,  
    32.                     StartDate = new DateTime(2009,10,09),  
    33.                     Salary = 1200000  
    34.                 },  
    35.                 new DemoEntity {  
    36.                     Name = "Ashton Cox",  
    37.                     Position = "Junior Technical Author",  
    38.                     Office = "San Francisco",  
    39.                     Extn = 1562,  
    40.                     StartDate = new DateTime(2009,01,12),  
    41.                     Salary = 86000  
    42.                 },  
    43.                 new DemoEntity {  
    44.                     Name = "Bradley Greer",  
    45.                     Position = "Software Engineer",  
    46.                     Office = "London",  
    47.                     Extn = 2558,  
    48.                     StartDate = new DateTime(2012,10,13),  
    49.                     Salary = 132000  
    50.                 },  
    51.                 new DemoEntity {  
    52.                     Name = "Brenden Wagner",  
    53.                     Position = "Software Engineer",  
    54.                     Office = "San Francisco",  
    55.                     Extn = 1314,  
    56.                     StartDate = new DateTime(2011,06,07),  
    57.                     Salary = 206850  
    58.                 },  
    59.                 new DemoEntity {  
    60.                     Name = "Brielle Williamson",  
    61.                     Position = "Integration Specialist",  
    62.                     Office = "New York",  
    63.                     Extn = 4804,  
    64.                     StartDate = new DateTime(2012,12,02),  
    65.                     Salary = 372000  
    66.                 },  
    67.                 new DemoEntity {  
    68.                     Name = "Bruno Nash",  
    69.                     Position = "Software Engineer",  
    70.                     Office = "London",  
    71.                     Extn = 6222,  
    72.                     StartDate = new DateTime(2011,05,03),  
    73.                     Salary = 163500  
    74.                 },  
    75.                 new DemoEntity {  
    76.                     Name = "Caesar Vance",  
    77.                     Position = "Pre-Sales Support",  
    78.                     Office = "New York",  
    79.                     Extn = 8330,  
    80.                     StartDate = new DateTime(2011,12,12),  
    81.                     Salary = 106450  
    82.                 },  
    83.                 new DemoEntity {  
    84.                     Name = "Cara Stevens",  
    85.                     Position = "Sales Assistant",  
    86.                     Office = "New York",  
    87.                     Extn = 3990,  
    88.                     StartDate = new DateTime(2011,12,06),  
    89.                     Salary = 145600  
    90.                 },  
    91.                 new DemoEntity {  
    92.                     Name = "Cedric Kelly",  
    93.                     Position = "Senior Javascript Developer",  
    94.                     Office = "Edinburgh",  
    95.                     Extn = 6224,  
    96.                     StartDate = new DateTime(2012,03,29),  
    97.                     Salary = 433060  
    98.                 }  
    99.             };  
    100.   
    101.             context.Demos.AddRange(testData);  
    102.   
    103.             await context.SaveChangesAsync();  
    104.         }  
    105.     }  
  1. Inside the Program.cs, let’s call InitializeDatabase method from the Main method to seed data into the database.
    1.     public class Program  
    2.     {  
    3.         public static void Main(string[] args)  
    4.         {  
    5.             var host = CreateWebHostBuilder(args).Build();  
    6.             InitializeDatabase(host);  
    7.             host.Run();  
    8.         }  
    9.   
    10.         public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>  
    11.             WebHost.CreateDefaultBuilder(args)  
    12.                 .UseStartup<Startup>();  
    13.   
    14.         private static void InitializeDatabase(IWebHost host)  
    15.         {  
    16.             using (var scope = host.Services.CreateScope())  
    17.             {  
    18.                 var services = scope.ServiceProvider;  
    19.   
    20.                 try  
    21.                 {  
    22.                     SeedData.InitializeAsync(services).Wait();  
    23.                 }  
    24.                 catch (Exception ex)  
    25.                 {  
    26.                     var logger = services.GetRequiredService<ILogger<Program>>();  
    27.                     logger.LogError(ex, "An error occurred seeding the database.");  
    28.                 }  
    29.             }  
    30.         }  
    31.     }  
  1. Here comes the DataTables part. First install the required client-side libraries for Jquery DataTables to work. I’m using LibMan to install JQuery DataTables along with Bootstrap, jQuery and js. And don’t forget to add reference to the files in your HTML.

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Create a simple HTML table tag with required headers and add an extra row inside thead to hold the search inputs. I have used bootstrap classes to style the table.
         jQuery DataTables With ASP.NET Core Server Side Processing
  1. Now inside your script file, before initializing DataTables, write the following script to add search inputs to the last empty row in your table header.
    1.         $('#fingers10 thead tr:last th').each(function () {  
    2.             var label = $('#fingers10 thead tr:first th:eq(' + $(this).index() + ')').html();  
    3.             $(this).addClass('p-0')  
    4.                 .html('<span class="sr-only">' + 
    5.                            label + 
    6.                         '</span><input type="search" class="form-control form-control-sm" aria-label="' + label + '" />');  
    7.         });  
  1. Now initialize DataTable as shown below, make sure to add serverSide: true and orderCellsTop: true; serverSide informs DataTable that the data will be coming from the server from the URL mentioned in ajax post and orderCellsTop places the sorting icons to the first row inside the thead. You can also pass additional parameters to the server using AdditionalValues property as string and cast back to required type in server and use those for any manipulations.

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Now inside the columns array, configure the columns by specifying the title, data and name property for the individual column as shown below.
         jQuery DataTables With ASP.NET Core Server Side Processing
  1. The data property must match the name of the property in the DemoModel.cs and this is case sensitive. Title is the readable form of your Model property describing the column.
  1. I’m using the name property to send the type of search that I need to perform in each column as I’m not able to find any other properties with DataTable Columns.

    jQuery DataTables With ASP.NET Core Server Side Processing

    Note
    If you’re not using name property, then this will default to eq search operation.
  1. Before we move to server-side implementation, add the following script to perform search on press of enter key. Make sure to replace id with your table id.
    1. table.columns().every(function (index) {  
    2.             $('#fingers10 thead tr:last th:eq(' + index + ') input')  
    3.                 .on('keyup',  
    4.                     function (e) {  
    5.                         if (e.keyCode === 13) {  
    6.                             table.column($(this).parent().index() + ':visible').search(this.value).draw();  
    7.                         }  
    8.                     });  
    9.         });  
  1. Now for this to work on the server side, install the Nuget Package – JqueryDataTables.ServerSide.AspNetCoreWeb which I have created to do all the heavy lifting for you.

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Now add DTParameters class as a parameter to your action method as shown below. And return the data back to DataTable as a JsonResult using DTResult<T> class as shown below.

    Note
    Return data is of IEnumerable<T> Type.
         jQuery DataTables With ASP.NET Core Server Side Processing
  1. Now let’s focus on the Demo Service to read the data from the database. I’m using Asp.Net Core built in dependency injection to inject dbcontext and automapper mapping configuration.
         jQuery DataTables With ASP.NET Core Server Side Processing
  1. Let’s first create an IQueryable<TEntity> query variable to hold the DemoEntity DbSet. Now create an instance for SearchOptionsProcessor<T,TEntity> with DemoModel and DemoEntity and call the Apply method with the query and table columns as parameters to dynamically generate and apply search expression to the query. After filtering do the same with SortOptionsProcessor<T,TEntity> to dynamically generate and apply sort expression to the query. Now let’s apply pagination from the table parameter which will have the DataTable state and use automapperProjectTo to project to the DemoModel. For this mapping to happen we need to write a mapping profile which I have declared inside Infrastructure folder inside the solution.
    1.    public class MappingProfile : Profile  
    2.     {  
    3.         public MappingProfile()  
    4.         {  
    5.             CreateMap<DemoEntity, Demo>();  
    6.         }  
    7.     }  
    Note
    If you’re having Services in a separate project, then create an instance of SearchOptionsProcessor and SortOptionsProcessor inside the controller action method and pass it as parameters to your service calls.
  1. That’s it -- now your DataTable works with server-side dynamic multiple column searching and sorting.

Debugging Screenshots

  1. Initial Loading

    jQuery DataTables With ASP.NET Core Server Side Processing
  2. DTParameters – DataTables State

    jQuery DataTables With ASP.NET Core Server Side Processing

    Note
    The additional values, all column data and all other details get posted to the action method.
  1. Multiple Column Searching and Sorting

    jQuery DataTables With ASP.NET Core Server Side Processing
  2. Multiple Column Searching and Sorting Server-Side Binding

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Dynamic Search Query

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Dynamic Sort Query

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Final Computed Query

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Results

    jQuery DataTables With ASP.NET Core Server Side Processing
  2. Final UI and Results.

    jQuery DataTables With ASP.NET Core Server Side Processing

Point of Interest

This article was created as a tutorial for developers who are working in Jquery DataTables with Asp.Net Core as a Server Side and are totally new to this. Experienced developers might find this article quite mundane. This article is more like a first step for the beginners to explore and expand. I hope this article is useful for the beginners and intermediate developers and this has a lot of scope to improve.

Thanks for reading.

 

 

Add a star to my repo if this saved you effort and time. 

Share via LinkedIn, Twitter, Facebook and WhatsApp to spread the knowledge.