Implementing Server-Side Data Processing for Data Tables in ASP.NET MVC

Introduction: Data tables are an essential part of modern web applications, enabling users to interactively view and manage large datasets. However, handling extensive data directly on the client-side can lead to performance issues. Server-side data processing offers an efficient solution by performing data retrieval, sorting, searching, and pagination on the server, reducing the data load on the client-side.

In this article, we will create an ASP.NET MVC application with Entity Framework and jQuery DataTables to showcase server-side data processing. We'll implement the LoadData action method in our controller to handle data processing on the server-side, and use jQuery DataTables to display the data on the client-side.

Prerequisites:

Visual Studio (2017 or later) with ASP.NET MVC installed.
Basic knowledge of C#, ASP.NET MVC, Entity Framework, and jQuery DataTables.

Step 1: Setting Up the Environment

Create a new ASP.NET MVC project in Visual Studio.
Install the necessary NuGet packages:
- EntityFramework (to work with the database).
- jQuery and jQuery.Validation (for client-side scripting).

Step 2: Creating the Database and Model

Create a database table to store customer details (e.g., Customers).
Define an entity class named Customer to represent the Customers table.

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    // Add other properties as needed.
}

Step 3: Implementing the LoadData Action Method

In the controller, create an action method named LoadData that returns a JSON result.

using System.Linq;
using System.Web.Mvc;
using YourProject.Models;

public class CustomerController : Controller
{
    private readonly YourDbContext _context;

    public CustomerController()
    {
        _context = new YourDbContext();
    }

    [HttpPost]
    public ActionResult LoadData()
    {
        try
        {
            var draw = Request.Form.GetValues("draw").FirstOrDefault();
            var start = Request.Form.GetValues("start").FirstOrDefault();
            var length = Request.Form.GetValues("length").FirstOrDefault();
            var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
            var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
            var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();

            int pageSize = length != null ? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int recordsTotal = 0;

            var customerData = _context.Customers.AsQueryable();

            if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
            {
                customerData = customerData.OrderBy(sortColumn + " " + sortColumnDir);
            }

            if (!string.IsNullOrEmpty(searchValue))
            {
                customerData = customerData.Where(m => m.Name.Contains(searchValue) || m.Email.Contains(searchValue));
            }

            recordsTotal = customerData.Count();
            var data = customerData.Skip(skip).Take(pageSize).ToList();

            return Json(new { draw, recordsFiltered = recordsTotal, recordsTotal, data });
        }
        catch (Exception)
        {
            throw;
        }
    }
}

Step 4: Creating the Client-Side

Create a view (e.g., Index.cshtml) and include the necessary scripts for jQuery and jQuery DataTables.

@{
    Layout = null;
}

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Server-Side Data Processing</title>
    <link href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css" rel="stylesheet" />
</head>
<body>
    <table id="customerTable" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Email</th>
                <!-- Add other columns as needed. -->
            </tr>
        </thead>
        <tbody></tbody>
    </table>

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function () {
            $('#customerTable').DataTable({
                "processing": true, // for show progress bar  
                "serverSide": true, // for process server side  
                "filter": true, // this is for disable filter (search box)  
                "orderMulti": false, // for disable multiple column at once  
                "pageLength": 5,  
                "ajax": {
                    "url": "/Customer/LoadData",
                    "type": "POST"
                },
                "columns": [
                    { "data": "Name" },
                    { "data": "Email" },
                    {
                      "data": null,
                       "render": function (data) {
                            return `<input type="button" class="btn btn-warning mx-1" value="Edit" id="${data.ID}" onclick="editStudent(${data.ID})"/>
                                    <input type="button" class="btn btn-danger btnDelete mx-1" value="Delete" id="${data.ID}" onclick="deleteStudent(${data.ID})"/>`;
                                }
                     }
                    // Add other columns as needed.
                ]
            });
        });
    </script>
</body>
</html>

Step 5: Running the Application

Run the application, and you'll see the data table displaying customer data with server-side processing.

Conclusion: In this article, we demonstrated how to implement server-side data processing for data tables in an ASP.NET MVC application. By handling data retrieval, sorting, searching, and pagination on the server-side, we can efficiently manage large datasets without impacting the client-side performance. By following the provided steps, you can create interactive data tables that provide a smooth user experience and handle extensive data effortlessly.