Datatables .NET (How It Works For Developers)

Add Model Class

I am using Product Model Class for this example, you may use it as per your requirement.

public class Product
{
    public int Id { get; set; }
    public string ProductName { get; set; } = string.Empty;
    public string ProductPrice { get; set; } = string.Empty;
    public string ProductWeight { get; set; } = string.Empty;
    public string ProductDescription { get; set; } = string.Empty;
    public DateTime ProductManufacturingDate { get; set; }
    public DateTime ProductExpiryDate { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string ProductName { get; set; } = string.Empty;
    public string ProductPrice { get; set; } = string.Empty;
    public string ProductWeight { get; set; } = string.Empty;
    public string ProductDescription { get; set; } = string.Empty;
    public DateTime ProductManufacturingDate { get; set; }
    public DateTime ProductExpiryDate { get; set; }
}

Add ApplicationDBContext Class

public class ApplicationDBContext : DbContext
{
    public ApplicationDBContext(DbContextOptions<ApplicationDBContext> options) : base(options)
    {
    }
    public DbSet<Product> Products { get; set; }
}

public class ApplicationDBContext : DbContext
{
    public ApplicationDBContext(DbContextOptions<ApplicationDBContext> options) : base(options)
    {
    }
    public DbSet<Product> Products { get; set; }
}

Add Advanced Interaction Controls

We will add the ProductDatatables.js inside the wwwroot/js folder for adding advanced controls such as pagination, search, etc.

// Initialize the DataTables plugin for the HTML element with 'productDatatable' ID
$(document).ready(function () {
    $("#productDatatable").DataTable({
        "processing": true, // Enable processing indicator
        "serverSide": true, // Enable server-side processing
        "ajax": {
            "url": "/api/Product", // API endpoint for fetching product data
            "type": "POST",
            "datatype": "json"
        },
        "columnDefs": [{
            // Define properties for columns
            "targets": [0],
            "visible": false, // Hide the 'Id' column
            "searchable": false // Disable searching for the 'Id' column
        }],
        "columns": [
            { "data": "id", "name": "Id", "autoWidth": true },
            { "data": "productName", "name": "ProductName", "autoWidth": true },
            { "data": "productPrice", "name": "ProductPrice", "autoWidth": true },
            { "data": "productWeight", "name": "ProductWeight", "autoWidth": true },
            { "data": "productDescription", "name": "ProductDescription", "autoWidth": true },
            { "data": "productManufacturingDate", "name": "ProductManufacturingDate", "autoWidth": true },
            { "data": "productExpiryDate", "name": "ProductExpiryDate", "autoWidth": true },
            {
                // Add a 'Delete' button with an onclick event for deleting the product
                "render": function (data, type, row) { 
                    return "<a href='#' class='btn btn-danger' onclick=DeleteProduct('" + row.id + "');>Delete</a>"; 
                }
            }
        ]
    });
});

// Initialize the DataTables plugin for the HTML element with 'productDatatable' ID
$(document).ready(function () {
    $("#productDatatable").DataTable({
        "processing": true, // Enable processing indicator
        "serverSide": true, // Enable server-side processing
        "ajax": {
            "url": "/api/Product", // API endpoint for fetching product data
            "type": "POST",
            "datatype": "json"
        },
        "columnDefs": [{
            // Define properties for columns
            "targets": [0],
            "visible": false, // Hide the 'Id' column
            "searchable": false // Disable searching for the 'Id' column
        }],
        "columns": [
            { "data": "id", "name": "Id", "autoWidth": true },
            { "data": "productName", "name": "ProductName", "autoWidth": true },
            { "data": "productPrice", "name": "ProductPrice", "autoWidth": true },
            { "data": "productWeight", "name": "ProductWeight", "autoWidth": true },
            { "data": "productDescription", "name": "ProductDescription", "autoWidth": true },
            { "data": "productManufacturingDate", "name": "ProductManufacturingDate", "autoWidth": true },
            { "data": "productExpiryDate", "name": "ProductExpiryDate", "autoWidth": true },
            {
                // Add a 'Delete' button with an onclick event for deleting the product
                "render": function (data, type, row) { 
                    return "<a href='#' class='btn btn-danger' onclick=DeleteProduct('" + row.id + "');>Delete</a>"; 
                }
            }
        ]
    });
});

Now, we need to add an HTML table.

Add HTML table

Write the following code in the index.cshtml file to add a static HTML page.

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page";
}
<link href="~/lib/datatables/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
<div class="container">
    <br />
    <div style="width:90%; margin:0 auto;">
        <table id="productDatatable" class="table table-striped table-bordered dt-responsive nowrap" width="100%" cellspacing="0">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Product Name</th>
                    <th>Product Price</th>
                    <th>Product Weight</th>
                    <th>Product Description</th>
                    <th>Product Manufacturing Date</th>
                    <th>Product Expiry Date</th>
                    <th>Actions</th>
                </tr>
            </thead>
        </table>
    </div>
</div>

@section Scripts {
    <script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>
    <script src="~/lib/datatables/js/dataTables.bootstrap4.min.js"></script>
    <script src="~/js/ProductDatatable.js"></script>
}

We need to add the controller.

Add Product Controller

Add Product Controller for creating endpoint and direct pull requests.

[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
    private readonly ApplicationDBContext context;
    public ProductController(ApplicationDBContext context)
    {
        this.context = context;
    }

    [HttpPost]
    public IActionResult GetProducts()
    {
        try
        {
            var draw = Request.Form["draw"].FirstOrDefault();
            var start = Request.Form["start"].FirstOrDefault();
            var length = Request.Form["length"].FirstOrDefault();
            var searchValue = Request.Form["search[value]"].FirstOrDefault();
            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int recordsTotal = 0;
            var productData = context.Products.ToLis();

            // Filtering data based on provided search value
            if (!string.IsNullOrEmpty(searchValue))
            {
                productData = productData.Where(m => m.ProductName.Contains(searchValue)
                                            || m.ProductDescription.Contains(searchValue)
                                            || m.Id.ToString().Contains(searchValue)).ToList();
            }

            recordsTotal = productData.Count();
            var data = productData.Skip(skip).Take(pageSize).ToList();
            var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
            return Ok(jsonData);
        }
        catch (Exception)
        {
            throw;
        }
    }
}

[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
    private readonly ApplicationDBContext context;
    public ProductController(ApplicationDBContext context)
    {
        this.context = context;
    }

    [HttpPost]
    public IActionResult GetProducts()
    {
        try
        {
            var draw = Request.Form["draw"].FirstOrDefault();
            var start = Request.Form["start"].FirstOrDefault();
            var length = Request.Form["length"].FirstOrDefault();
            var searchValue = Request.Form["search[value]"].FirstOrDefault();
            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int recordsTotal = 0;
            var productData = context.Products.ToLis();

            // Filtering data based on provided search value
            if (!string.IsNullOrEmpty(searchValue))
            {
                productData = productData.Where(m => m.ProductName.Contains(searchValue)
                                            || m.ProductDescription.Contains(searchValue)
                                            || m.Id.ToString().Contains(searchValue)).ToList();
            }

            recordsTotal = productData.Count();
            var data = productData.Skip(skip).Take(pageSize).ToList();
            var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
            return Ok(jsonData);
        }
        catch (Exception)
        {
            throw;
        }
    }
}

Here, we have implemented pagination & searching on the server side.

Now, we need to set up our database and add configuration to the Program.cs class. If you are using .NET 5 or a lower version, you may need to do it in Startup.cs class.

First of all, add the following Connection String in the appsettings.json file.

"ConnectionStrings": {
   "ProductDB": "Server=localserver\\SQLEXPRESS;Database=ProductDB;Trusted_Connection=True;MultipleActiveResultSets=true;TrustServerCertificate=True;"
}

Now add the following code to the Program.cs class.

public static void Main(string[] args)
{
    var builder = WebApplication.CreateBuilder(args);
    builder.Services.AddDbContext<ApplicationDBContext>(options =>
    {
         options.UseSqlServer(builder.Configuration.GetConnectionString("ProductDB"));
    });
    builder.Services.AddControllers();
    // Add services to the container.
    builder.Services.AddRazorPages();
    var app = builder.Build();
    // Configure the HTTP request pipeline.
    if (!app.Environment.IsDevelopment())
    {
         app.UseExceptionHandler("/Error");
         // The default HSTS value is 30 days.
         app.UseHsts();
    }
    app.UseHttpsRedirection();
    app.UseStaticFiles();
    app.UseRouting();
    app.UseAuthorization();
    app.MapControllers();
    app.MapRazorPages();
    app.Run();
}

public static void Main(string[] args)
{
    var builder = WebApplication.CreateBuilder(args);
    builder.Services.AddDbContext<ApplicationDBContext>(options =>
    {
         options.UseSqlServer(builder.Configuration.GetConnectionString("ProductDB"));
    });
    builder.Services.AddControllers();
    // Add services to the container.
    builder.Services.AddRazorPages();
    var app = builder.Build();
    // Configure the HTTP request pipeline.
    if (!app.Environment.IsDevelopment())
    {
         app.UseExceptionHandler("/Error");
         // The default HSTS value is 30 days.
         app.UseHsts();
    }
    app.UseHttpsRedirection();
    app.UseStaticFiles();
    app.UseRouting();
    app.UseAuthorization();
    app.MapControllers();
    app.MapRazorPages();
    app.Run();
}

We need to run the migration, as we are using a code-first approach.

Run the following command in the Package Manager Console.

Add-Migration init

Add-Migration init

The above command will create a migration. Now we need to apply these migrations to our database. Run the following command in the Package Manager Console.

Update-Database

Update-Database

The above command will create tables in our database. Add dummy data in the Product Table; you may generate random data from Mockaroo.

Now, build and run this application.

Output

We can see, that we have a very interactive UI with advanced interaction controls.

Now, pagination is implemented on the server side as shown below.

Output UI

The data is then rendered on the Client Side with rich UI Control.

You can explore more from the DataTables.NET documentation by clicking explore DataTables.NET documentation.

Export Data to Excel

Let's write a code to convert our Product list into an Excel File.

public void ExportToExcel(List<Product> productList)
{
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX); // Create a new workbook instance
    WorkSheet ws = wb.DefaultWorkSheet; // Access the default worksheet
    int rowCount = 1;

    // Iterate over the product list and fill the worksheet
    foreach (Product product in productList)
    {
        ws["A" + rowCount].Value = product.Id.ToString();
        ws["B" + rowCount].Value = product.ProductName;
        ws["C" + rowCount].Value = product.ProductDescription;
        ws["D" + rowCount].Value = product.ProductPrice;
        ws["E" + rowCount].Value = product.ProductWeight;
        ws["F" + rowCount].Value = product.ProductManufacturingDate;
        ws["G" + rowCount].Value = product.ProductExpiryDate;
        rowCount++;
    }
    wb.SaveAs("product.xlsx"); // Save the workbook as an Excel file
}

public void ExportToExcel(List<Product> productList)
{
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX); // Create a new workbook instance
    WorkSheet ws = wb.DefaultWorkSheet; // Access the default worksheet
    int rowCount = 1;

    // Iterate over the product list and fill the worksheet
    foreach (Product product in productList)
    {
        ws["A" + rowCount].Value = product.Id.ToString();
        ws["B" + rowCount].Value = product.ProductName;
        ws["C" + rowCount].Value = product.ProductDescription;
        ws["D" + rowCount].Value = product.ProductPrice;
        ws["E" + rowCount].Value = product.ProductWeight;
        ws["F" + rowCount].Value = product.ProductManufacturingDate;
        ws["G" + rowCount].Value = product.ProductExpiryDate;
        rowCount++;
    }
    wb.SaveAs("product.xlsx"); // Save the workbook as an Excel file
}

We have created an Excel file from the list in a very simple and easy way.

IronXL provides comprehensive tutorials on creating XLSX files, code examples for reading Excel, and API documentation to use its comprehensive API in the best way possible.

In optimizing ASP.NET performance, we exclusively rely on Only Core Software, ensuring a lean and efficient development environment. Utilizing DataTables.NET as locally hosted packages further enhances responsiveness, minimizing external dependencies for streamlined data handling and Excel exportation. Additionally, contributing code efficiently becomes seamless within this optimized and self-contained ecosystem.

IronPDF is a solution designed to convert webpages, URLs, and HTML into PDF documents. The generated PDFs retain the original formatting and stylistic elements of the source webpages. This tool is particularly effective for creating PDF renditions of web-based content such as reports and invoices.

using IronPdf;

class Program
{
    static void Main(string[] args)
    {
        var renderer = new ChromePdfRenderer();

        // 1. Convert HTML String to PDF
        var htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>";
        var pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent);
        pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf");

        // 2. Convert HTML File to PDF
        var htmlFilePath = "path_to_your_html_file.html"; // Specify the path to your HTML file
        var pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath);
        pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf");

        // 3. Convert URL to PDF
        var url = "http://ironpdf.com"; // Specify the URL
        var pdfFromUrl = renderer.RenderUrlAsPdf(url);
        pdfFromUrl.SaveAs("URLToPDF.pdf");
    }
}

using IronPdf;

class Program
{
    static void Main(string[] args)
    {
        var renderer = new ChromePdfRenderer();

        // 1. Convert HTML String to PDF
        var htmlContent = "<h1>Hello, IronPDF!</h1><p>This is a PDF from an HTML string.</p>";
        var pdfFromHtmlString = renderer.RenderHtmlAsPdf(htmlContent);
        pdfFromHtmlString.SaveAs("HTMLStringToPDF.pdf");

        // 2. Convert HTML File to PDF
        var htmlFilePath = "path_to_your_html_file.html"; // Specify the path to your HTML file
        var pdfFromHtmlFile = renderer.RenderHtmlFileAsPdf(htmlFilePath);
        pdfFromHtmlFile.SaveAs("HTMLFileToPDF.pdf");

        // 3. Convert URL to PDF
        var url = "http://ironpdf.com"; // Specify the URL
        var pdfFromUrl = renderer.RenderUrlAsPdf(url);
        pdfFromUrl.SaveAs("URLToPDF.pdf");
    }
}