jQuery DataTables With ASP.NET Core Server Side Processing

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 along with pagination and excel export 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

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

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

[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

Using the Code

Note
I’m using Asp.Net Core 3.0

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

2. Let’s begin with the server-side configuration and database setup. For jQuery DataTables to work with Asp.Net Core, we first need to call setup ConfigureServices in the Startup.cs. I have also added AutoMapper to take care of mappings and AddSession which I’ll explain later.
   
   For AutoMapper to work add the following Nuget Packages:

• AutoMapper
• Extensions.Microsoft.DependencyInjection
  
  For Asp.Net Core 3.0:
  
  If you are using System.Text.Json, then setup your ConfigureServices as below:
  
  
  1. services.AddControllersWithViews()
  2. .AddJsonOptions(options =>
  3. {
  4. options.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
  5. options.JsonSerializerOptions.PropertyNamingPolicy = null;
  6. });
  7. services.AddSession();
  8. services.AddAutoMapper(typeof(Startup));
  If you are using Json.Net, then setup your ConfigureServices as below:
  
  
  1. services.AddControllersWithViews()
  2. .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());
  3. services.AddSession();
  4. services.AddAutoMapper(typeof(Startup));
  For Asp.Net Core 2.x, setup your ConfigureServices as below:
  
  1. services.AddMvc()
  2. .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());
  3. services.AddSession();
  4. services.AddAutoMapper(typeof(Startup));
     

3. 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. });

4. 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) { }
   6. public DbSet<DemoEntity> Demos { get; set; }
   7. }

5. The DemoEntity class has the following fields.
   
   Root Entity
   
   1. public class DemoEntity
   2. {
   3. [Key]
   4. public int Id { get; set; }
   5. public string Name { get; set; }
   6. public string Position { get; set; }
   7. public string Office { get; set; }
   9. public DemoNestedLevelOneEntity DemoNestedLevelOne { get; set; }
   10. }
   Nested Level One Entity
   
   1. public class DemoNestedLevelOneEntity
   2. {
   3. [Key]
   4. public int Id { get; set; }
   6. public short? Experience { get; set; }
   7. public int? Extn { get; set; }
   9. public DemoNestedLevelTwoEntity DemoNestedLevelTwo { get; set; }
   10. }
   Nested Level Two Entity
   
   1. public class DemoNestedLevelTwoEntity
   2. {
   3. [Key]
   4. public int Id { get; set; }
   6. public DateTime? StartDate { get; set; }
   7. public long? Salary { get; set; }
   8. }

6. 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. }
   9. public static async Task AddTestData(Fingers10DbContext context)
   10. {
   11. if(context.Demos.Any())
   12. {
   13. // Already has data
   14. return;
   15. }
   17. var testData = new List<DemoEntity>()
   18. {
   19. new DemoEntity {
   20. Name = "Airi Satou",
   21. Position = "Accountant",
   22. Office = "Tokyo",
   23. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   24. {
   25. Experience = null,
   26. Extn = null,
   27. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   28. {
   29. StartDate = null,
   30. Salary = null
   31. }
   32. }
   33. },
   34. new DemoEntity {
   35. Name = "Angelica Ramos",
   36. Position = "Chief Executive Officer (CEO)",
   37. Office = "London",
   38. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   39. {
   40. Experience = 1,
   41. Extn = 5797,
   42. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   43. {
   44. StartDate = new DateTime(2009,10,09),
   45. Salary = 1200000
   46. }
   47. }
   48. },
   49. new DemoEntity {
   50. Name = "Ashton Cox",
   51. Position = "Junior Technical Author",
   52. Office = "San Francisco",
   53. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   54. {
   55. Experience = 2,
   56. Extn = 1562,
   57. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   58. {
   59. StartDate = new DateTime(2009,01,12),
   60. Salary = 86000
   61. }
   62. }
   63. },
   64. new DemoEntity {
   65. Name = "Bradley Greer",
   66. Position = "Software Engineer",
   67. Office = "London",
   68. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   69. {
   70. Experience = 3,
   71. Extn = 2558,
   72. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   73. {
   74. StartDate = new DateTime(2012,10,13),
   75. Salary = 132000
   76. }
   77. }
   78. },
   79. new DemoEntity {
   80. Name = "Brenden Wagner",
   81. Position = "Software Engineer",
   82. Office = "San Francisco",
   83. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   84. {
   85. Experience = 4,
   86. Extn = 1314,
   87. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   88. {
   89. StartDate = new DateTime(2011,06,07),
   90. Salary = 206850
   91. }
   92. }
   93. },
   94. new DemoEntity {
   95. Name = "Brielle Williamson",
   96. Position = "Integration Specialist",
   97. Office = "New York",
   98. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   99. {
   100. Experience = 5,
   101. Extn = 4804,
   102. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   103. {
   104. StartDate = new DateTime(2012,12,02),
   105. Salary = 372000
   106. }
   107. }
   108. },
   109. new DemoEntity {
   110. Name = "Bruno Nash",
   111. Position = "Software Engineer",
   112. Office = "London",
   113. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   114. {
   115. Experience = 6,
   116. Extn = 6222,
   117. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   118. {
   119. StartDate = new DateTime(2011,05,03),
   120. Salary = 163500
   121. }
   122. }
   123. },
   124. new DemoEntity {
   125. Name = "Caesar Vance",
   126. Position = "Pre-Sales Support",
   127. Office = "New York",
   128. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   129. {
   130. Experience = 7,
   131. Extn = 8330,
   132. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   133. {
   134. StartDate = new DateTime(2011,12,12),
   135. Salary = 106450
   136. }
   137. }
   138. },
   139. new DemoEntity {
   140. Name = "Cara Stevens",
   141. Position = "Sales Assistant",
   142. Office = "New York",
   143. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   144. {
   145. Experience = 8,
   146. Extn = 3990,
   147. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   148. {
   149. StartDate = new DateTime(2011,12,06),
   150. Salary = 145600
   151. }
   152. }
   153. },
   154. new DemoEntity {
   155. Name = "Cedric Kelly",
   156. Position = "Senior Javascript Developer",
   157. Office = "Edinburgh",
   158. DemoNestedLevelOne = new DemoNestedLevelOneEntity
   159. {
   160. Experience = 9,
   161. Extn = 6224,
   162. DemoNestedLevelTwo = new DemoNestedLevelTwoEntity
   163. {
   164. StartDate = new DateTime(2012,03,29),
   165. Salary = 433060
   166. }
   167. }
   168. }
   169. };
   170. context.Demos.AddRange(testData);
   172. await context.SaveChangesAsync();
   173. }
   174. }

7. 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. }
   10. public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
   11. WebHost.CreateDefaultBuilder(args)
   12. .UseStartup<Startup>();
   14. private static void InitializeDatabase(IWebHost host)
   15. {
   16. using (var scope = host.Services.CreateScope())
   17. {
   18. var services = scope.ServiceProvider;
   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. }

8. 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.
   
   
   

1. @addTagHelper *, JqueryDataTables.ServerSide.AspNetCoreWeb

11. Now add <jquery-datatables> in your html as shown below.
    

    1. <jquery-datatables id="fingers10"
    2. class="table table-sm table-dark table-bordered table-hover"
    3. model="@Model"
    4. thead-class="text-center"
    5. enable-searching="true"
    6. search-row-th-class="p-0"
    7. search-input-class="form-control form-control-sm"
    8. search-input-style="width:100%"
    9. search-input-placeholder-prefix="Search">
    10. </jquery-datatables>
    * id - to add id to the html table
    * class - to apply the given css class to the html table
    * model - view model with properties to generate columns for html table
    * thead-class - to apply the given css class to the `<thead>` in html table
    * enable-searching - `true` to add search inputs to the `<thead>` and `false` to remove search inputs from the `<thead>`
    * search-row-th-class - to apply the given css class to the search inputs row of the `<thead>` in the html table
    * search-input-class - to apply the given css class to the search input controls added in each column inside `<thead>`
    * search-input-style - to apply the given css styles to the search input controls added in each column inside `<thead>`
    * search-input-placeholder-prefix - to apply your placeholder text as prefix in search input controls in each column inside `<thead>`
12. 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.
    
    Ajax POST Configuration
    
    1. var table = $('#fingers10').DataTable({
    2. language: {
    3. processing: "Loading Data...",
    4. zeroRecords: "No matching records found"
    5. },
    6. processing: true,
    7. serverSide: true,
    8. orderCellsTop: true,
    9. autoWidth: true,
    10. deferRender: true,
    11. lengthMenu: [5, 10, 15, 20],
    12. dom: '<"row"<"col-sm-12 col-md-6"B><"col-sm-12 col-md-6 text-right"l>><"row"<"col-sm-12"tr>><"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
    13. buttons: [
    14. {
    15. text: 'Export to Excel',
    16. className: 'btn btn-sm btn-dark',
    17. action: function (e, dt, node, config) {
    18. window.location.href = "/Home/GetExcel";
    19. },
    20. init: function (api, node, config) {
    21. $(node).removeClass('dt-button');
    22. }
    23. }
    24. ],
    25. ajax: {
    26. type: "POST",
    27. url: '/Home/LoadTable/',
    28. contentType: "application/json; charset=utf-8",
    29. async: true,
    30. headers: {
    31. "XSRF-TOKEN": document.querySelector('[name="__RequestVerificationToken"]').value
    32. },
    33. data: function (data) {
    34. let additionalValues = [];
    35. additionalValues[0] = "Additional Parameters 1";
    36. additionalValues[1] = "Additional Parameters 2";
    37. data.AdditionalValues = additionalValues;
    39. return JSON.stringify(data);
    40. }
    41. },
    42. columns: [
    43. ...
    44. ]
    45. });
    Ajax GET Configuration
    
    For AJAX GET configuration, simply change the `ajax` and `buttons` options as follows,
    
    1. buttons: [
    2. {
    3. text: 'Export to Excel',
    4. className: 'btn btn-sm btn-dark',
    5. action: function (e, dt, node, config) {
    6. var data = table.ajax.params();
    7. var x = JSON.stringify(data, null, 4);
    8. window.location.href = "/Home/GetExcel?" + $.param(data);
    9. },
    10. init: function (api, node, config) {
    11. $(node).removeClass('dt-button');
    12. }
    13. }
    14. ],
    15. ajax: {
    16. url: '/Home/LoadTable/',
    17. data: function (data) {
    18. return $.extend({}, data, {
    19. "additionalValues[0]": "Additional Parameters 1",
    20. "additionalValues[1]": "Additional Parameters 2"
    21. });
    22. }
    23. }

13. 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.

14. 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.
    
    
    
    
    Note
    If you’re not using name property, then this will default to eq search operation.

15. 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. });