AG-Grid with .NET API
Published in AG-Grid
Prasomsak Khunmuen

Create an API for AG-Grid with .NET

AG-Grid is a versatile JavaScript data grid library, well-suited for creating dynamic, high-performance tables with advanced features like sorting, filtering, and pagination. In this article, we’ll implement an API in .NET to support AG-Grid, enabling efficient server-side data management for operations such as filtering, sorting, and pagination. By integrating AG-Grid with .NET, we’ll build a powerful solution that ensures smooth performance, even with large datasets.

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

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 page = Request.Query["page"].Any() ? Convert.ToInt32(Request.Query["page"]) : 1;
            int size = Request.Query["size"].Any() ? Convert.ToInt32(Request.Query["size"]) : 10;
            int offset = (page - 1) * size;
            string order = Request.Query["order"].Any() ? Request.Query["order"].First() : "Id";
            string direction = Request.Query["direction"].Any() ? Request.Query["direction"].First() : "asc";
            var query = _context.Product.Select(e => new {
                Id = e.Id,
                Name = e.Name,
                Price = e.Price
            });
            if (!String.IsNullOrEmpty(Request.Query["search"])) {
                query = query.Where(e => e.Name.Contains(Request.Query["search"]));
            }
            query = query.OrderBy(order, direction);
            int count = await query.CountAsync();
            var data = await query.Skip(offset).Take(size).ToListAsync();
            return Ok(new { data, count });
        }
    }
}

The ProductController.cs file defines an ASP.NET Core controller for handling API requests related to products. It features an endpoint (api/products) that retrieves a paginated list of products based on query parameters for pagination, sorting, and searching. By utilizing dependency injection to access the DataContext, the Index method constructs a query that selects product properties, applies any specified filters and ordering, and returns a JSON response with the product data and total count, facilitating frontend integration.

index.html

<!DOCTYPE html>
<head>
    <script src="https://cdn.jsdelivr.net/npm/ag-grid-community/dist/ag-grid-community.min.js"></script>
</head>
<body>
    <div id="grid" class="ag-theme-alpine" style="height: 400px; width: 640px; margin: 1em"></div>
    <script>
        function getQuery(params) {
            let query = new URLSearchParams()
            let size = params.endRow - params.startRow
            let page = Math.floor(params.startRow / size) + 1
            query.append('page', page)
            query.append('size', size)
            if (params.sortModel.length) {
                let sort = params.sortModel[0]
                query.append('order', sort.colId)
                query.append('direction', sort.sort)
            }
            if (params.filterModel.name) {
                query.append('search', params.filterModel.name.filter)
            }
            return query.toString()
        }
        let columns = [
            { headerName: 'ID', field: 'id', sortable: true },
            {
                headerName: 'Name', field: 'name', sortable: true, filter: true,
                filterParams: {
                    filterOptions: ['contains'],
                    maxNumConditions: 1,
                }
            },
            { headerName: 'Price', field: 'price', sortable: true }
        ]
        let gridOptions = {
            columnDefs: columns,
            rowModelType: 'infinite',
            pagination: true,
            paginationPageSize: 20,
            cacheBlockSize: 20,
            datasource: {
                getRows: function (params) {
                    let query = getQuery(params)
                    fetch(`/api/products?${query}`)
                        .then(response => response.json())
                        .then(json => {
                            params.successCallback(json.data, json.count)
                        })
                }
            }
        }
        document.addEventListener('DOMContentLoaded', () => {
            agGrid.createGrid(document.querySelector('#grid'), gridOptions)
        })
    </script>
</body>
</html>

The index.html file sets up a web page that uses the AG-Grid library to display a dynamic data grid for products. It includes a grid styled with the AG-Grid theme and a JavaScript section that constructs query parameters for pagination, sorting, and filtering. The grid is configured with columns for ID, Name, and Price, and it fetches product data from an API endpoint based on user interactions. Upon loading, the grid is initialized, allowing users to view and manipulate the product list effectively.

Run project

dotnet run

Open the web browser and goto http://localhost:5122
You will find this test page.

test page

Testing

Page size test

Change page size by selecting 50 from the "Page Size" drop-down. You will get 50 records per page, and the last page will change from 5 to 2.

page size test

Sorting test

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

sorting test

Search test

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

search test

Conclusion

In conclusion, we’ve successfully integrated AG-Grid with .NET to develop a powerful and efficient data grid solution. By leveraging the capabilities of .NET on the backend, we enabled AG-Grid to perform server-side filtering, sorting, and pagination, ensuring smooth handling of large datasets. This integration not only streamlines data management but also enhances the user experience with dynamic, responsive tables on the frontend. With AG-Grid and .NET working together, we’ve created a scalable and high-performance grid system ready for real-world applications.

Source code: https://github.com/stackpuz/Example-AG-Grid-dotnet-8

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

Related post

Recommended Topics

StackPuz is the Web App Code Generator. It will help you bootstrap a new project FASTER. Please check out it on stackpuz.com