How to Use JQuery DataTables with ASP.NET Core Web API

JQuery DataTables plugin is one of the most powerful and famous plugins for creating tabular data grids. It has built-in support for searching, sorting, and pagination without any configuration and there are so many ways to customize this data grid as per your requirements. It also has some advanced features such as support for AJAX, server-side processing, and styling according to some of the popular frameworks e.g. Bootstrap, Material Design, etc. In one of my old tutorials Using JQuery DataTables with ASP.NET Web API, I demonstrated how to connect DataTables with ASP.NET Web API. Many of my readers requested me to write an updated version of that tutorial for ASP.NET Core so in this tutorial, I will show you how you can use JQuery DataTables with ASP.NET Core Web API.

Configure Entity Framework Core to Fetch Data

Let’s start by creating a new ASP.NET Core MVC Web Application project in Visual Studio 2019 using the standard File > New > Project menu option. I have decided to use Microsoft’s popular AdventureWorks sample database for this tutorial and you can download this database from the Microsoft website. I will be using the following Product table from the AdventureWorks database and we will load its data using ASP.NET Core Web API and then finally we will display it on a page using the DataTables plugin.

I will be using Entity Framework Core (Database First) approach to quickly generate entity model class for the above Product Table and DbContext object. For this purpose, we need to install  Microsoft.EntityFrameworkCore.SqlServer NuGet package as well as Microsoft.EntityFrameworkCore.Design and Microsoft.EntityFrameworkCore.Tools NuGet packages to perform some Entity Framework Core design-time development tasks. If you don’t know how to install NuGet packages then read my tutorial Working with Packages and Libraries in ASP.NET Core.

Once the above NuGet packages are installed, generate the entity model class and DbContext using the following Scaffold-DbContext command in Package Manager Console.

Scaffold-DbContext -Connection "Server=DB_SERVER; Database=AdventureWorks; Trusted_Connection=True; MultipleActiveResultSets=true;" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models" -ContextDir "Data" -Context "AdventureWorksDbContext" -Tables "Product" -NoOnConfiguring

The command has the connection string information and some other parameters and it will generate the Product class in the Models folder and AdventureWorksDbContext class in the Data folder. I also used –Tables option with the value Product because for this tutorial, I only want to generate a Product table entity class.

Next, we need to specify the connection string in appsettings.json file as follows:

{
   "ConnectionStrings":{
      "DefaultConnection":"Server=DB_SERVER; Database=AdventureWorks; Trusted_Connection=True; MultipleActiveResultSets=true"
   },
   "Logging":{
      "LogLevel":{
         "Default":"Information",
         "Microsoft":"Warning",
         "Microsoft.Hosting.Lifetime":"Information"
      }
   },
   "AllowedHosts":"*"
}

Entity Framework provider can be configured in ConfigureServices method of Startup.cs file as shown below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddDbContext<AdventureWorksDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

If you want’ to learn more about using Entity Framework, then you can read my tutorials Data Access in ASP.NET Core using EF Core (Database First) and Data Access in ASP.NET Core using EF Core (Code First).

Loading Data using ASP.NET Core Web API

JQuery DataTables can make Ajax based server-side calls and load and display data from backend APIs. We need to create a Web API in ASP.NET Core and need to return data in the format that is acceptable by the DataTables plugin. Let’s create an API Controller ProductsController in the Controllers folder and inject AdventureWorksDbContext in the constructor.

[Route("api/[controller]")]
[ApiController]
public class ProductsController : ControllerBase
{
    private readonly AdventureWorksDbContext _context;

    public ProductsController(AdventureWorksDbContext context)
    {
        _context = context;
    }
}

Next, we will implement the following GetProducts method which is simply returning all products from the Product table available in AdventureWorks database

// GET: api/Products
[HttpGet]
public async Task<ActionResult<List<Product>>> GetProducts()
{
    return await _context.Products.ToListAsync();
}

Open the browser and test the API by adding api/Products in the URL as shown below. You should be able to see the product data available in JSON format.

If you want to learn more about ASP.NET Core Web APIs then read my tutorial A Developer’s Guide for Creating Web APIs with ASP.NET Core 5

Installing JQuery DataTables Plugin in ASP.NET Core

It is now time to download the JQuery DataTables plugin and include it in our project. At the time of writing this tutorial, the latest version of the plugin is 1.10.21 and it can be downloaded from the DataTables website and added to wwwroot folder of the project manually. You can also use the LibMan tool to install and add the DataTables plugin in the project without leaving the Visual Studio. If you want to learn more about installing client-side libraries in ASP.NET Core applications then read my tutorial Working with Packages and Libraries in ASP.NET Core.

Right-click the project name in solution explorer and choose Add > Client-Side Library… menu option. Type datatables@ in the Library field and you should see all the available versions. Select datatables@1.10.21 version and keep Include all library files option selected to download all files related to DataTables plugin. Clicking the Install button will download the plugin files and they will be added in a new datatables folder in wwwroot/lib folder.

Please keep in mind that DataTables is a JQuery plugin and we need JQuery library to use DataTables. Luckily, Visual Studio automatically adds JQuery library in every new ASP.NET Core MVC Web application so we don’t need to download it again using the LibMan tool.

To use DataTables plugin, we need to include plugin related CSS stylesheets and the best place to add these stylesheets is the element of the _Layout.cshtml file. Include jquery.dataTables.css and dataTables.bootstrap4.css stylesheets as shown below.

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

    <link href="~/lib/datatables/css/jquery.dataTables.css" rel="stylesheet" />
    <link href="~/lib/datatables/css/dataTables.bootstrap4.css" rel="stylesheet" />

    <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.css" />
    <link rel="stylesheet" href="~/css/site.css" />
</head>

The file jquery.dataTables.css is the main plugin file and the dataTables.bootstrap4.css file will be used to apply bootstrap 4 look and feel to the generated data grid. You also have the options to choose the look and feel of some popular CSS frameworks such as Foundations, SemanticUI, Material Design, etc.

We also need to add the plugin related JavaScript files such a jquery.dataTables.min.js and these files can be added just before the closing  element in the _Layout.cshtml file.

<script src="~/lib/jquery/dist/jquery.js"></script>

    <script src="~/lib/datatables/js/jquery.dataTables.js"></script>
    <script src="~/lib/datatables/js/dataTables.bootstrap4.js"></script>

    <script src="~/lib/bootstrap/dist/js/bootstrap.js"></script>
    <script src="~/js/site.js" asp-append-version="true"></script>

    @await RenderSectionAsync("Scripts", required: false)
</body>

Configuring JQuery DataTables Plugin in ASP.NET Core

We are finally ready to use the JQuery DataTables plugin on our page. The plugin normally works with HTML tables so add the following table markup in Index.cshtml file. The markup is using basic bootstrap table classes such as table-bordered, table-striped, etc. I want to display five columns of the Product table so I only included those column headers in the table.

@{
    ViewData["Title"] = "Home Page";
}
<h2>Products</h2>
<table id="example" class="table table-sm table-bordered table-striped" style="width:100%">
    <thead>
        <tr>
            <th>Product Id</th>
            <th>Name</th>
            <th>Product Number</th>
            <th>Color</th>
            <th>List Price</th>
        </tr>
    </thead>
</table>

The next step is to initialize the DataTables plugin on the page with some basic configurations. Add the following script in the site.js file and make sure that the id in our script matches the table id example in the HTML. The important setting in the following code snippet is the ajax which specifies the URL of the Web API we just created above. This setting will be used by the DataTables plugin to fetch and bind data. We also used that columns setting that specifies which data fields to use to generate the columns of the grid. You can read about all the available settings in the plugin manual here.

$(document).ready(function () {
    $('#example').DataTable({
        processing: true,
        ordering: true,
        paging: true,
        searching: true,
        ajax: "api/Products",
        columns: [
            { "data": "productId" },
            { "data": "name" },
            { "data": "productNumber" },
            { "data": "color" },
            { "data": "listPrice" }
        ]
    });
});

DataTables can be used in two different modes:

• Client-side – Filtering, paging, and sorting are done in the web-browser.
• Server-side – Filtering, paging, and sorting calculations are performed on the server.

By default, the DataTables operates on client-side processing mode, but can it be configured to operate in server-side processing mode. When operating in server-side mode, the DataTables plugin sends some parameters to the server indicating what data it needs, what page number to read, what filters to apply on data, etc. and also expects data to come back in a specific format so that it has all the information to display the table. We need to make sure that our ASP.NET Web API method GetProducts send all the parameters understood by the DataTables plugin. If you will run your project now, you will see that no data is displayed on the page and there is also the following error shown in the browser console.

To fix the above error we need to send data in plugin readable format. Following is the list of parameters DataTables expects will return from the server-side. More detail about these parameters can be found in the DataTables manual.

We need to make sure our product data is wrapped and returned in the data parameter specified above. Let’s create a model class DataTableResponse in the Models folder and declare all of the above parameters as properties in the class.

public class DataTableResponse
{
    public int Draw { get; set; }
    public long RecordsTotal { get; set; }
    public int RecordsFiltered { get; set; }
    public object[] Data { get; set; }
    public string Error { get; set; }
}

Next, we need to adjust the code of the GetProducts method and need to return the DataTableResponse objects instead of the List of Product objects. The code snippet below shows how to wrap the products in the DataTableResponse object along with other properties.

// GET: api/Products
[HttpGet]
public async Task<ActionResult<DataTableResponse>> GetProducts()
{
    var products = await _context.Products.ToListAsync();

    return new DataTableResponse
    {
        RecordsTotal = products.Count(),
        RecordsFiltered = 10,
        Data = products.ToArray()
    };
}

Build and run the project again and this time you will see the following grid rendered in the browser.

Summary

JQuery DataTables is a highly flexible and feature-rich plugin with hundreds of features. The Datatables also supports additional plugins that can be plugged with the DataTables and add even more functionality. There is also a powerful and feature-rich API available which can provide you even more ways to use DataTables plugins programmatically. It is impossible to discuss all plugin features in a single tutorial but I hope this tutorial will put you on the right track.