ASP.NET MVC5: Datatables Plugin Server Side Integration

Been a while since I last blog. New years first entry is commencing now. Its never too late to blog.
Anyways, coming back to today's discussion. Ever since Microsoft has introduced MVC paradigm for web development many classic ASP.NET webform users misses built-in web UI controls to boost-up there development, one such control that has been missed a lot is DataGridView. In MVC paradigm, there is no concept of web UI controls rather simple plain HTML. So, yeah, it sometimes get annoying for classic ASP.NET webform users to switch to MVC paradigm with ease, especially, when UI designing is concern.

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 base plugins out there that solves a lot of designing issues in web development to boost up not just development productivity, but also provide lucid user interactivity for websites. One such really cool free for commercial use plugin for lists is Jquery Datatables Plugin. There are a lot of flavors of Datatables plugins and it comes to support many major web programming technologies.

For today's blog, I shall be focusing more on How Datatables plugin can be integrated with ASP.NET MVC5 server side data.

Following are some prerequisites before you proceed any further in this tutorial:

Prerequisites:

1) Knowledge about ASP.NET MVC5.
2) Knowledge about HTML.
3) Knowledge about Javascript.
4) Knowledge about AJAX.
5) Knowledge about CSS.
6) Knowledge about Bootstrap.
7) Knowledge about C# programming.
8) Knowledge about C# LINQ.
9) Knowledge about JQuery.

Phew.....Thats alot of knowledge!!!!

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

Download Now!

Let's begin now.

1) Create new MVC5 web application project and name it "PluginIntegration-1".
2) Create new controller and name it "Plugin".
3) In "RouteConfig.cs" file change you default controller to "Plugin".
4) Create new page called "Index.cshtml" under "Views->Plugin" folder and place 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 base server side data.

5) Open "_Layout.cshtml" file under "Views->Shared" folder and replace existing code with 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 &copy; @DateTime.Now.Year - <a href="http://www.asmak9.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 incorporate links to require scripts and styles.

6) Now, create new partial page under "Views->Plugin" folder, name it "_ViewListPartial.cshtml" and place 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 Datatables plugin with data from server side. I have only provided table header information here, since, the data will be integrated from server side.

7) Now create new model under "Model", name it "SalesOrderDetail.cs" and add following properties in it:

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; }  
   }  
 }

8) Now, In "PluginController.cs" file add following function to load data from "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;  
     }

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

9) Now, create new script file under "Scripts" folder, name it "custom-datatable.js" and place 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 Datatables plugin integrate server side data with underlying web programming language. Let's see each 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, enable/disable information for sorting, searching etc, for number of columns which 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 to customize processing message that will appear when data is being loaded. I have used 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 the snippet of how the processing loader will be looked like:

Below piece of code will enable the data loading from 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 name of the properties that we have created in "SalesOrderDetail.cs" file and the path "/Plugin/GetData" is the function that will be returning data from server side.

10) Now, in "PluginController.cs" file let's create "GetData" method as follow:

    #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 Datatebles plugin, following have been done i.e.

1) Data is being loaded first.
2) Data is being churned out base on searching criteria.
3) Data is sorted by provided column in provided order.
4) Data is then paginated.
5) Data is returned.

"GetData" function will be executed each time the table is being searched, sort or new page is accessed. Here are following two lines which are important:

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

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

First line determine the actual amount of records that exist in the list and second line determine 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 how the results will look like after applying the filtering:

That's about it.

Enjoy!! Coding.

Asma's Blog

Header Ads

ASP.NET MVC5: Datatables Plugin Server Side Integration

Prerequisites:

Download Now!

Post a Comment

3 comments:

Upcoming Events

Follow by Email

Get new posts by email:

Popular Posts

Archive

Categories

Sitemap

Highlights

Buy Me A Coke

Learn

Downloads

Recent Comments

Support

Follow by Email

Get new posts by email:

Recent Posts

Asma's Blog

Header Ads

ASP.NET MVC5: Datatables Plugin Server Side Integration

Prerequisites:

Download Now!

Related Posts

Post a Comment

3 comments:

Upcoming Events

Follow by Email

Get new posts by email:

Popular Posts

Archive

Categories

Sitemap

Highlights

Buy Me A Coke

Learn

Downloads

Recent Comments

Support

Follow by Email

Get new posts by email:

Recent Posts