StackPuz

Posted on Jul 26 • Originally published at blog.stackpuz.com on Jul 22

Create an API for DataTables with .NET

#datatables #net

DataTables is a popular jQuery plugin that offers features like pagination, searching, and sorting, making it easy to handle large datasets. This article will demonstrate how to create an .NET API to work with the DataTables. What are the parameters that DataTables sends to our API and the requirements of the data that DataTables needs.

To deal with DataTables, you need to understand the information that DataTables will send to the API through the query string.

draw = 1
columns[0][data] = id
columns[0][name] = 
columns[0][searchable] = true
columns[0][orderable] = true
columns[0][search][value] = 
columns[0][search][regex] = false
columns[1][data] = name
columns[1][name] = 
columns[1][searchable] = true
columns[1][orderable] = true
columns[1][search][value] = 
columns[1][search][regex] = false
columns[2][data] = price
columns[2][name] = 
columns[2][searchable] = true
columns[2][orderable] = true
columns[2][search][value] = 
columns[2][search][regex] = false
order[0][column] = 0
order[0][dir] = asc
order[0][name] = 
start = 0
length = 10
search[value] = 
search[regex] = false

draw the request ID that is used to synchronize between the client and server.
columns[x][data] the column's field name that we define on the client-side.
order[0] the sorting information.
start the start index of the record.
length the length per page (page size).
search[value] the search value information.

The DataTables expected data will require these information.

draw DataTables sends this ID to us, and we just send it back.
recordsTotal Total records number before filtering.
recordsFiltered Total records number after filtering.
data The records data.

Prerequisites

.NET 8
MySQL

Setup project

dotnet new webapi -o dotnet_api -n App

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ Controllers
│  └─ ProductController.cs
├─ Models
│  ├─ DataContext.cs
│  └─ Product.cs
├─ wwwroot
│  └─ index.html
├─ Util.cs
├─ Program.cs
├─ App.csproj
└─ appsettings.json

Project files

App.csproj

This file is the .NET project configuration file. We added the MySql.EntityFrameworkCore package here.

<Project Sdk="Microsoft.NET.Sdk.Web">
    <PropertyGroup>
        <TargetFramework>net8.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="MySql.EntityFrameworkCore" Version="8.0.0" />
    </ItemGroup>
</Project>

appsettings.json

This is the .NET application configuration file that contains the database connection information.

{
    "Logging": {
        "LogLevel": {
            "Default": "Warning"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "Database": "server=localhost;port=3306;database=example;user id=root;password=;"
    }
}

Program.cs

This file is the main entry point for a .NET API application.

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddDbContext<App.Models.DataContext>(options => options.UseMySQL(builder.Configuration.GetConnectionString("Database")));
var app = builder.Build();
app.UseDefaultFiles();
app.UseStaticFiles();
app.UseRouting();
app.MapControllers();
app.Run();

app.UseDefaultFiles() uses index.html as the default HTML file.
app.UseStaticFiles() serves the static files in the folder wwwroot.

Util.cs

This file defines the extension method OrderBy() of the IQueryable class to implement the dynamic column sorting for the Entity Framework.

using System.Linq.Expressions;
using System.Reflection;

namespace App
{
    public static class Util
    {
        public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> query, string column, string direction)
        {
            var type = typeof(TEntity);
            var parameter = Expression.Parameter(type);
            var property = type.GetProperty(column, BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
            var member = Expression.MakeMemberAccess(parameter, property);
            var lamda = Expression.Lambda(member, parameter);
            var method = direction == "desc" ? "OrderByDescending" : "OrderBy";
            var expression = Expression.Call(typeof(Queryable), method, new Type[] { type, property.PropertyType }, query.Expression, Expression.Quote(lamda));
            return query.Provider.CreateQuery<TEntity>(expression);
        }
    }
}

DataContext.cs

This is the required file when working with Entity Framework (EF) in a .NET application. It's used to map the tables and columns information from the database to the entities.

using Microsoft.EntityFrameworkCore;

namespace App.Models
{
    public partial class DataContext : DbContext
    {
        public virtual DbSet<Product> Product { get; set; }

        public DataContext()
        {
        }

        public DataContext(DbContextOptions<DataContext> options) : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>(entity =>
            {
                entity.ToTable("Product");
                entity.HasKey(e => e.Id);
                entity.Property(e => e.Id).HasColumnName("id");
                entity.Property(e => e.Name).HasColumnName("name").HasMaxLength(50).IsUnicode(false);
                entity.Property(e => e.Price).HasColumnName("price").HasColumnType("decimal(12,2)");
            });
        }
    }
}

Product.cs

This file defines the model information that maps to our database table named "Product".

using System.ComponentModel.DataAnnotations;

namespace App.Models
{
    public partial class Product
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

ProductController.cs

This file is used to handle incoming requests and produce the paginated data for the client.

using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc;
using App.Models;

namespace App.Controllers
{
    public class ProductController : Controller
    {
        private readonly DataContext _context;

        public ProductController(DataContext context)
        {
            _context = context;
        }

        [HttpGet("api/products")]
        public async Task<IActionResult> Index()
        {
            int size = Request.Query["length"].Any() ? Convert.ToInt32(Request.Query["length"]) : 10;
            int start = Convert.ToInt32(Request.Query["start"]);
            string order = Request.Query["order[0][column]"].Any() ? Request.Query["columns[" + Request.Query["order[0][column]"] + "][data]"] : "Id";
            string direction = Request.Query["order[0][dir]"].Any() ? Request.Query["order[0][dir]"] : "asc";
            var query = _context.Product.Select(e => new {
                Id = e.Id,
                Name = e.Name,
                Price = e.Price
            });
            if (!String.IsNullOrEmpty(Request.Query["search[value]"])) {
                query = query.Where(e => e.Name.Contains(Request.Query["search[value]"]));
            }
            query = query.OrderBy(order, direction);
            int recordsTotal = await _context.Product.CountAsync();
            int recordsFiltered = await query.CountAsync();
            var data = await query.Skip(start).Take(size).ToListAsync();
            return Ok(new { draw = Request.Query["draw"].First(), recordsTotal, recordsFiltered, data });
        }
    }
}

We utilize the query string to get size, start, order, direction and create the paginated data by using the query.Skip(start).Take(size) method.
We use the extension method OrderBy() that is defined in the Util.cs file to perform sorting on the IQueryable object.
We return all DataTables required information including: draw, recordsTotal, recordsFiltered, data as object.

index.html

This file will be used to setup the DataTables HTML and JavaScript to work with our API.

<!DOCTYPE html>
<head>
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
</head>
<body>
    <table id="table" class="display">
        <thead>
            <td>id</td>
            <th>name</th>
            <th>price</th>
        </thead>
    </table>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
    <script>
        new DataTable('#table', {
            ajax: '/api/products',
            processing: true,
            serverSide: true,
            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'price' }
            ]
        })
    </script>
</body>

processing: true show a loading indicator when making the request.
serverSide: true makes the request to the server (API) for all operations.

Run project

dotnet run

Open the web browser and goto http://localhost:5122

You will find this test page.

Testing

Page size test

Change page size by selecting 25 from the "entries per page" drop-down. You will get 25 records per page, and the last page will change from 10 to 4.

Sorting test

Click on the header of the first column. You will see that the id column will be sorted in descending order.

Search test

Enter "no" in the search text-box, and you will see the filtered result data.

Conclusion

In this article, you have learned how to create an .NET API to work with the DataTables. Understand all the DataTables parameters sent to the API and utilize them to produce the appropriate data and send it back. You also learn how to setup the DataTables on the client-side using HTML and JavaScript. I hope this article will help you incorporate DataTables into your next project.

Source code: https://github.com/stackpuz/Example-DataTables-dotnet8

Create a CRUD Web App in Minutes: https://stackpuz.com

DEV Community