ASP.NET MVC

Datatables Plugin Server Side Integration In MVC 5

In this article, we will discuss how to integrate the Datatable plugin in ASP.NET MVC 5 with server-side pagination and ordering, sorting and searching.

Prerequisites:

HTML
Javascript
jQuery
AJAX
Bootstrap
CSS
ASP.NET MVC 5
C# Programming
C# LINQ

Now Lets Begin:

  1. Create a new project and add the following JS and CSS in it.
  • bootstrap.min.css
  • dataTables.bootstrap.min.css
  • jquery-3.3.1.min.js
  • jquery.dataTables.min.js
  • dataTables.bootstrap.min.js

Now First we will make the database. We are going to use the Code First Approach. If you are not familiar with it then you can see it in our article.

Add the connection string in Web.Config file in root folder.

<connectionStrings>
    <add name="StringDBContext" connectionString="Server=DESKTOP-CGB025P;Initial Catalog=DemoDB;Persist Security Info=False;User ID=sa;Password=vision;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />
  </connectionStrings>

Now Go to Models -> Create Context.cs file

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace DatatablePlugin.Models
{
    public class Context : DbContext
    {
        public Context() : base("StringDBContext")
        {
        }
        public DbSet<DemoTable> DemoTables { get; set; }
    }
}

Now in the Models folder only create DemoTable.cs file

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace DatatablePlugin.Models
{
    public class DemoTable
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Quantity { get; set; }
        public string Price { get; set; }
        public string Status { get; set; }
    }
}

Now open the _Layout.cshtml file from View -> Shared -> _Layout.cshtml

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - Server Side Datatable Demo</title>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
    <link href="~/Content/dataTables.bootstrap.min.css" rel="stylesheet" />
    <script src="~/Scripts/jquery-3.3.1.min.js"></script>
    <script src="~/Scripts/jquery.dataTables.min.js"></script>
    <script src="~/Scripts/dataTables.bootstrap.min.js"></script>
</head>
<body>
    <div class="container body-content" style="margin-top:5%">
        @RenderBody()
        <hr />
        <footer>
            <p>&copy; @DateTime.Now.Year - Server Side Datatable Demo</p>
        </footer>
    </div>
    @RenderSection("scripts", required: false)
</body>
</html>

The JS and CSS files should be added to the Content and Scripts folder before continuing.

Now we will add the View for the data table. So go to View -> Home -> Index.cshtml

@{
    ViewBag.Title = "Home Page";
}
<table id="demodata" class="table table-bordered">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Quantity</th>
            <th>Price</th>
            <th>Status</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>
<script src="~/Scripts/Index.js"></script>

Now make the Index.js file in the Scripts folder and add the code in it.

DemoDatatable();
function DemoDatatable() {
    $("#demodata").DataTable({
        "processing": true,
        "serverSide": true,
        "filter": true,
        "orderMulti": false,
        "destroy": true,
        "ordering": true,
        "ajax": {
            "url": '/Home/GetTableData',
            "type": "POST",
            "datatype": "json"
        },

        "columns": [
            { "data": "Id", "name": "Id", "autoWidth": true }
            , { "data": "Name", "Name": "Name", "autoWidth": true }
            , { "data": "Quantity", "name": "Quantity", "autoWidth": true }
            , { "data": "Price", "name": "Price", "autoWidth": true }
            , { "data": "Status", "name": "Status", "autoWidth": true }
        ]
    });
}

Now finally the main login for server-side pagination and ordering begins.

Now go-to HomeController.

using DatatablePlugin.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace DatatablePlugin.Controllers
{
    public class HomeController : Controller
    {
        private readonly Context _context = new Context();
        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public ActionResult GetTableData()
        {
            JsonResult result = new JsonResult();
            try
            {
                string search = Request.Form.GetValues("search[value]")[0];
                string draw = Request.Form.GetValues("draw")[0];
                string order = Request.Form.GetValues("order[0][column]")[0];
                string orderDir = Request.Form.GetValues("order[0][dir]")[0];
                int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
                int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
                List<DemoTable> data = _context.DemoTables.ToList();
                int totalRecords = data.Count;
                if (!string.IsNullOrEmpty(search) &&
                    !string.IsNullOrWhiteSpace(search))
                {
                    data = data.Where(p => p.Id.ToString().ToLower().Contains(search.ToLower()) ||
                        p.Name.ToString().Contains(search.ToLower()) ||
                        p.Quantity.ToString().Contains(search.ToLower()) ||
                        p.Price.ToString().Contains(search.ToLower()) ||
                        p.Status.ToString().Contains(search.ToLower())
                     ).ToList();
                }

                data = SortTableData(order, orderDir, data);

                int recFilter = data.Count;
                data = data.Skip(startRec).Take(pageSize).ToList();
                var modifiedData = data.Select(d =>
                    new
                    {
                        d.Id,
                        d.Name,
                        d.Quantity,
                        d.Price,
                        d.Status
                    }
                    );
                result = this.Json(new
                {
                    draw = Convert.ToInt32(draw),
                    recordsTotal = totalRecords,
                    recordsFiltered = recFilter,
                    data = modifiedData
                }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            return result;
        }
        private List<DemoTable> SortTableData(string order, string orderDir, List<DemoTable> data)
        {
            List<DemoTable> lst = new List<DemoTable>();
            try
            {
                switch (order)
                {
                    case "0":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
                                                                                                 : data.OrderBy(p => p.Id).ToList();
                        break;
                    case "1":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Name).ToList()
                                                                                                 : data.OrderBy(p => p.Name).ToList();
                        break;
                    case "2":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Quantity).ToList()
                                                                                                 : data.OrderBy(p => p.Quantity).ToList();
                        break;
                    case "3":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Price).ToList()
                                                                                                 : data.OrderBy(p => p.Price).ToList();
                        break;
                    case "4":
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Status).ToList()
                                                                                                   : data.OrderBy(p => p.Status).ToList();
                        break;
                    default:
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
                                                                                                 : data.OrderBy(p => p.Id).ToList();
                        break;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            return lst;
        }

    }
}

Finally Done with the Login for server-side data table pagination and all the kinds of stuff.

Output:

You can download this demo from here.

Faisal Pathan

Faisal Pathan is a founder of TheCodeHubs, .NET Project Manager/Team Leader, and C# Corner MVP. He has extensive experience with designing and developing enterprise-scale applications. He has good skills in ASP.NET C#, ASP.NET Core, ASP.NET MVC, AngularJS, Angular, React, NodeJS, Amazon S3, Web API, EPPlus, Amazon MWS, eBay Integration, SQL, Entity Framework, JavaScript, eCommerce Integration like Walmart, Tanga, Newegg, Group-on Store, etc. and Windows services.

Share
Published by
Faisal Pathan

Recent Posts

Testing hk

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Operation

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

TETS NEW

test

3 years ago