ASP.NET MVC 5: Datatables Plugin Server Side Integration

Asma Khalid

In this article, I shall be focusing on how the Datatables plugin can be integrated with ASP.NET MVC 5 server-side data. I have also attached the code.

Ever since Microsoft introduced the MVC paradigm for web development many classic ASP.NET webform users have missed built-in web UI controls to boost their development. One such control that has been missed a lot is DataGridView. In the MVC paradigm, there is no concept of web UI controls, other than simple plain HTML. So, yeah, it sometimes gets annoying for classic ASP.NET webform users to switch to the MVC paradigm with ease, especially, when UI designing is concerned.

Data tables

HTML tables are quite common, especially when lists are to be shown on the web pages. There are many beautiful free-for-commercial use or open-source-based plugins out there that solve a lot of designing issues in web development to boost not just development productivity, but also provide lucid user interactivity for websites. One such cool free commercial-use plugin for lists is Datatables. There are a lot of flavors of Datatables plugins and it supports many major web programming technologies.

The following are some prerequisites before you proceed any further in this article:

Prerequisites

The prerequisites include knowledge about the following technologies:

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

You can download the complete source code for this tutorial and also follow the step-by-step discussion below. The sample code is developed in Microsoft Visual Studio 2013 Ultimate. I am using the SalesOrderDetail table extract from the Adventure Works Sample Database.

Let’s begin now

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace PluginIntegration_1.Models
{
    public class SalesOrderDetail
    {
        public int Sr
        {
            get;
            set;
        }
        public string OrderTrackNumber
        {
            get;
            set;
        }
        public int Quantity
        {
            get;
            set;
        }
        public string ProductName
        {
            get;
            set;
        }
        public string SpecialOffer
        {
            get;
            set;
        }
        public double UnitPrice   
        {
            get;
            set;
        }
        public double UnitPriceDiscount   
        {
            get;
            set;
        }
    }
}

Create a new MVC 5 web application project and name it "PluginIntegration-1".
Create a new controller and name it "Plugin".
In the "RouteConfig.cs" file change your default controller to "Plugin".

Create a new page called "Index. cshtml" under the "Views, Plugin" folder and place the following code in it.

@{
    ViewBag.Title = "Plugin Integration - Datatable";
}
<div class="row">
    <div class="panel-heading">
        <div class="col-md-8">
            <h3>
                <i class="fa fa-table"></i>
                <span>Datatables Plugin Integration with ASP.NET MVC5 C#</span>
            </h3>
        </div>
    </div>
</div>
<div class="row">
    <section class="col-md-12 col-md-push-0">
        @Html.Partial("_ViewListPartial")
    </section>
</div>

Here, I am simply creating a page heading and section for my partial view in which I will be displaying my DataTables plugin-based server-side data.

Open the "_Layout. cshtml" file under the "Views, Shared" folder and replace the existing code with the following.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
    <!-- Font Awesome -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.4.0/css/font-awesome.min.css" />
    <!-- Data table -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.10/css/dataTables.bootstrap.min.css " />
    @* Custom *@
    @Styles.Render("~/Content/css/custom-style")
</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
            </div>
        </div>
    </div>
    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <center>
                <p><strong>Copyright © @DateTime.Now.Year - <a href="http://asmak9.blogspot.com/">Asma's Blog</a>.</strong> All rights reserved.</p>
            </center>
        </footer>
    </div>
    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    <!-- Data Table -->
    <script src="https://cdn.datatables.net/1.10.10/js/jquery.dataTables.min.js" type="text/javascript"></script>
    <script src="https://cdn.datatables.net/1.10.10/js/dataTables.bootstrap.min.js" type="text/javascript"></script>
    @Scripts.Render("~/bundles/custom-datatable")
    @RenderSection("scripts", required: false)
</body>
</html>

Here, I have simply altered the existing layout and incorporated links to required scripts and styles.

Now, create a new partial page under the "Views->Plugin" folder, name it "_ViewListPartial.cshtml" and place the following code in it.

<section>
    <div class="well bs-component">
        <br />
        <div class="row">
            <div>
                <table class="table table-striped table-bordered table-hover" id="TableId" cellspacing="0" align="center" width="100%">
                    <thead>
                        <tr>
                            <th>Sr</th>
                            <th>Order Track Number</th>
                            <th>Quantity</th>
                            <th>Product Name</th>
                            <th>Special Offer</th>
                            <th>Unit Price</th>
                            <th>Unit Price Discount</th>
                        </tr>
                    </thead>
                </table>
            </div>
        </div>
    </div>
</section>

Here, I have created a table holder that will be integrated with the Datatables plugin with data from the server side. I have only provided table header information here, since, the data will be integrated from the server side.

Now create a new model under "Model", name it "SalesOrderDetail.cs" and add the following properties to it:

Now, in the "PluginController.cs" file add the following function to load data from the "SalesOrderDetail.txt" text file.

#region Load Data
/// <summary>
/// Load data method.
/// </summary>
/// <returns>Returns - Data</returns>
private List<SalesOrderDetail> LoadData()
{
    // Initialization.
    List<SalesOrderDetail> lst = new List<SalesOrderDetail>();
    try
    {
        // Initialization.
        string line = string.Empty;
        string srcFilePath = "content/files/SalesOrderDetail.txt";
        var rootPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
        var fullPath = Path.Combine(rootPath, srcFilePath);
        string filePath = new Uri(fullPath).LocalPath;
        StreamReader sr = new StreamReader(new FileStream(filePath, FileMode.Open, FileAccess.Read));
        // Read file.
        while ((line = sr.ReadLine()) != null)
        {
            // Initialization.
            SalesOrderDetail infoObj = new SalesOrderDetail();
            string[] info = line.Split(',');
            // Setting.
            infoObj.Sr = Convert.ToInt32(info[0].ToString());
            infoObj.OrderTrackNumber = info[1].ToString();
            infoObj.Quantity = Convert.ToInt32(info[2].ToString());
            infoObj.ProductName = info[3].ToString();
            infoObj.SpecialOffer = info[4].ToString();
            infoObj.UnitPrice = Convert.ToDouble(info[5].ToString());
            infoObj.UnitPriceDiscount = Convert.ToDouble(info[6].ToString());
            // Adding.
            lst.Add(infoObj);
        }
        // Closing.
        sr.Dispose();
        sr.Close();
    }
    catch (Exception ex)
    {
        // info.
        Console.Write(ex);
    }
    // info.
    return lst;
}
#endregion

The above piece of code simply loads data from a text file into the list.

Now, create a new script file under the "Scripts" folder, name it "custom-datatable.js" and place the following code in it.

$(document).ready(function() 
{
    $('#TableId').DataTable 
    ({
        "columnDefs": [
          {
            "width": "5%",
            "targets": [0]
        },
          {
            "className": "text-center custom-middle-align",
            "targets": [0, 1, 2, 3, 4, 5, 6]
        }, ],
        "language": 
        {
            "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
        },
        "processing": true,
        "serverSide": true,
        "ajax": 
      {
            "url": "/Plugin/GetData",
            "type": "POST",
            "dataType": "JSON"
        },
        "columns": [
          {
            "data": "Sr"
        },
          {
            "data": "OrderTrackNumber"
        },
          {
            "data": "Quantity"
        },
          {
            "data": "ProductName"
        },
          {
            "data": "SpecialOffer"
        },
          {
            "data": "UnitPrice"
        },
          {
            "data": "UnitPriceDiscount"
        }]
    });
});

Now, this is the fun part which will display the server-side data in the table that we have created earlier into our partial view "_ViewListPartial.cshtml." This is how the Datatables plugin integrates server-side data with the underlying web programming language. Let’s see each piece of information here chunk by chunk.

"columnDefs": [
    { "width": "5%", "targets": [0] },
    { "className": "text-center custom-middle-align", "targets": [0, 1, 2, 3, 4, 5, 6] },
],

This chunk of code provides styling, and enables/disables information for sorting, searching, etc, for the number of columns that are being used in the table, which is why this chunk of code defines columns definition for our table.

"language": {
    "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
},

This chunk of code allows us to customize the processing message that will appear when data is being loaded. I have used the following custom styling here.

.custom-loader-color {
    color: #fff!important;
    font-size: 50px!important;
}
.custom-loader-background {
    background-color: crimson!important;
}
.custom-middle-align {
    vertical-align: middle!important;
}

Below is a snippet of what the processing loader will look like:
Below piece of code below will enable the data loading from the server side.

"processing": true,
"serverSide": true,
"ajax": {
    "url": "/Plugin/GetData",
    "type": "POST",
    "dataType": "JSON"
},
"columns": [
    {
        "data": "Sr"
    },
    {
        "data": "OrderTrackNumber"
    },
    {
        "data": "Quantity"
    },
    {
        "data": "ProductName"
    },
    {
        "data": "SpecialOffer"
    },
    {
        "data": "UnitPrice"
    },
    {
        "data": "UnitPriceDiscount"
    }
]

The columns here are the exact names of the properties that we have created in the "SalesOrderDetail.cs" file and the path "/Plugin/GetData" is the function that will be returning data from the server side.

Now, in the "PluginController.cs" file let’s create the "GetData" method as follows:
#region Get data method

/// <summary>
/// GET: /Plugin/GetData   
/// </summary>
/// <returns>Return data</returns>
public ActionResult GetData()
{
    // Initialization.
    JsonResult result = new JsonResult();
    try
    {
        // Initialization.
        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]);
        // Loading.
        List<SalesOrderDetail> data = this.LoadData();
        // Total record count.
        int totalRecords = data.Count;
        // Verification.
        if (!string.IsNullOrEmpty(search) &&
            !string.IsNullOrWhiteSpace(search))
        {
            // Apply search
            data = data.Where(p => p.Sr.ToString().ToLower().Contains(search.ToLower()) ||
                p.OrderTrackNumber.ToLower().Contains(search.ToLower()) ||
                p.Quantity.ToString().ToLower().Contains(search.ToLower()) ||
                p.ProductName.ToLower().Contains(search.ToLower()) ||
                p.SpecialOffer.ToLower().Contains(search.ToLower()) ||
                p.UnitPrice.ToString().ToLower().Contains(search.ToLower()) ||
                p.UnitPriceDiscount.ToString().ToLower().Contains(search.ToLower())).ToList();
        }
        // Sorting.
        data = this.SortByColumnWithOrder(order, orderDir, data);
        // Filter record count.
        int recFilter = data.Count;
        // Apply pagination.
        data = data.Skip(startRec).Take(pageSize).ToList();
        // Loading drop down lists.
        result = this.Json(new
        {
            draw = Convert.ToInt32(draw),
            recordsTotal = totalRecords,
            recordsFiltered = recFilter,
            data = data
        }, JsonRequestBehavior.AllowGet);
    }
    catch (Exception ex)
    {
        // Info
        Console.Write(ex);
    }
    // Return info.
    return result;
}
#endregion

In this piece of code, which is based on searching, sorting, and pagination information sent from the Datatables plugin, the following has been done i.e.

Data is being loaded first.
Data is being churned out based on search criteria.
Data is sorted by the provided column in the provided order.
Data is then paginated.

Data is returned.
The "GetData" function will be executed each time the table is being searched, sorted, or a new page is accessed. Here are the following two lines which are important.

// Total record count.
int totalRecords = data.Count;
// Filter record count.
int recFilter = data.Count;

The first line determines the actual amount of records that exist in the list and the second line determines the amount of records that are left after applying filtering. Below is the piece of code that will do the sorting:
#region Sort by column with order method

/// <summary>
/// Sort by column with order method.
/// </summary>
/// <param name="order">Order parameter</param>
/// <param name="orderDir">Order direction parameter</param>
/// <param name="data">Data parameter</param>
/// <returns>Returns - Data</returns>
private List<SalesOrderDetail> SortByColumnWithOrder(string order, string orderDir, List<SalesOrderDetail> data)
{
    // Initialization.
    List<SalesOrderDetail> lst = new List<SalesOrderDetail>();
    try
    {
        // Sorting
        switch (order)
        {
            case "0":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Sr).ToList() : data.OrderBy(p => p.Sr).ToList();
                break;
            case "1":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.OrderTrackNumber).ToList() : data.OrderBy(p => p.OrderTrackNumber).ToList();
                break;
            case "2":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Quantity).ToList() : data.OrderBy(p => p.Quantity).ToList();
                break;
            case "3":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.ProductName).ToList() : data.OrderBy(p => p.ProductName).ToList();
                break;
            case "4":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.SpecialOffer).ToList() : data.OrderBy(p => p.SpecialOffer).ToList();
                break;
            case "5":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.UnitPrice).ToList() : data.OrderBy(p => p.UnitPrice).ToList();
                break;
            case "6":
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.UnitPriceDiscount).ToList() : data.OrderBy(p => p.UnitPriceDiscount).ToList();
                break;
            default:
                // Setting.
                lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Sr).ToList() : data.OrderBy(p => p.Sr).ToList();
                break;
        }
    }
    catch (Exception ex)
    {
        // info.
        Console.Write(ex);
    }
    // info.
    return lst;
}
#endregion

Here is how the results will look after applying the filtering.

Conclusion

This article was about the Datatable plugin's server-side integration with ASP.NET MVC 5. In this article you learned how to integrate server-side data, searching, sorting, and pagination information with Datatable plugin.

42 Comments

Join the conversation! Your thoughts help the community grow.

Samsung Tv5y ago
Hi Asma I made one Datatable that was perfect working but in 2nd getting error on " string search = Request.Form.GetValues("search[value]")[0];" at that line it show object Not Found error pls help
- Asma Khalid5y ago
  Kindly reach out to me at [email protected] in case your issue is not resolve. So, I can remote access and verify it.
Anonymous Anonymous5y ago
Hi Asma Khalid, I have this issue after setting up the scripts in the cshtml file and setting up json in the controller "The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.". Please help
- Asma Khalid5y ago
  Have you executed the source code??
Viper Dev6y ago
Hi Asma Khalid, great work. Your solution has been my salvation.
Hùng Vũ Đinh6y ago
Great. Can you develop a more multi-ordering function?
- Asma Khalid6y ago
  It will be helpful, if you could explain a bit more of your requirement?
Benjamin Otero8y ago
Thank you very much ! ! !
Mehran Shnd8y ago
Hi, thanks for your post really useful. iam trying to use for different pages bu how can I use .js file in layout ?
- Asma Khalid8y ago
  Can you elaborate the question?
stephen S8y ago
Kindly post tutorial for edit and delete functionality if possible
- Asma Khalid8y ago
  Kindly look into this http://bit.ly/2CTSdvB It will give you idea to use actions in datatables plugin
Sagar Mandke8y ago
Hey i'm getting error : cannot read property length of undefined. i'm getting records from database as expected,but while rendering datatable im getting this error and unable to figure out the issue. anybody help ?
- Asma Khalid8y ago
  Kindly verify datatables configuration and how many rows are you napping in one go?
Jack8y ago
Hey I have a question, why do I need to load 1000;s of data again again, For each next , search and filter , isn't that too costly
- Asma Khalid8y ago
  Daatatables have limitation of loading 5000 rows in one go at server side as for loading 1000 rows, it's entirely your choice.
mallika pamarthi8y ago
Hey,I'm getting Request.Form.GetValues("search[value]")[0] and others are empty upon first loading ..could you suggest me.
- Asma Khalid8y ago
  Can you share exact error with snippet?
- Faisal Pathan8y ago
  I'm facing this same issue
- Faisal Pathan8y ago
  By the way, Thanks Asma Khalid for saving my time.
Nnaemeka Nwachukwu8y ago
I'm having circular reference error on the GetData() Method. i checked and found out that Request.Form.GetValues("search[value]")[0] and others are empty upon first loading ...
- Asma Khalid8y ago
  Can you share exact error with the snippet?
Himal Rampersadh9y ago
Hey , awesome tutorial. very simple and precise however i am failing to get the excel button to show,also does the excel button get all the records or just the page that's displaying?
- Asma Khalid9y ago
  Thank you for the support. Let me know what issue you are having in adding excel button, so, I can help. As for, other question. Datatables plugin excel button only export what is on you data table I. E. If you are using static version and map all rows then excel will export all of them regardless of paging, but as for dynamic version we usually display what we want and paging is also working like that so it depends how many rows you are dynamically mapping on datatables and excel button will export it accordingly.
gaurav maurya9y ago
Awesome ....and thnx to ur valuable article
raja upen9y ago
And i have bookmarked thsi
raja upen9y ago
Good 'code'-ing, can we do like below scenario ,can we load datatable onclick and download filtered data in excel using mvc datatable.plugin.
- Asma Khalid9y ago
  1) Yes you can load datatables plugin onclick as follow initiate your datatables inside a variable i.e. var mydt = $('#TableId').DataTable ({.,,}) then call mydt.draw() after your table declaration.
- Asma Khalid9y ago
  Use following code within your datatables declaration setting to provide excel export option i.e. "dom": 'Bfrtip', "buttons": [ { "extend": 'excel' "text": '<i class="fa fa-file-excel-o fa-3x"></i>', } ]
vara reddy9y ago
Nice Post, Hi here is another one with individual col filter Web Forms: http://reddyinfosoft.blogspot.in/2016/04/jquery-datatable-in-aspnet-with-server_12.html, and MVC: http://reddyinfosoft.blogspot.in/2016/12/jquery-datatable-paging-sorting-and.html
mayank Desai10y ago
Nice Articale , Could you please share Datatable plugin integration with Asp.net web form not MVC?
- Asma Khalid10y ago
  This can be achieved by writing a [WebMethod] at asp.net web form backend and consume that method in the ajax call that is being passed from datatables.. Client side will remain same, you just have to use that in web form environment as it is. You can look into following article to write [Web Method] in asp.net web form i.e. http://www.codeproject.com/Tips/769238/Call-Server-Side-Code-using-ASP-NET-AJAX-and-jQuer
- Asma Khalid10y ago
  Here is a Webform version of Datatables Plugin http://www.c-sharpcorner.com/article/asp-net-webform-datatables-jquery-plugin-server-side-integration/
Vignesh Mani10y ago
Good
Debasis Saha10y ago
Nice share
Asma Khalid10y ago
Thank you everyone for all your support.
Rajeev Punhani10y ago
Nice.
Asfend Yar10y ago
nice
Ankur Mistry10y ago
nice share, thanks for sharing
Kashif Sohail10y ago
Nice Start, Keep it up
Humayun Kabir Mamun10y ago
Nice...