ASP.NET

Grouping Data In jqxDataTable Using ASP.NET MVC 4

Introduction

In this post, I will show you how to display data in a jqxDataTable plugin with ASP.NET MVC 4, using C# and Entity Framework, JSON.

What is a jqxDataTable?

jqxDataTable is a lightweight jQuery Table widget built to easily replace your HTML tables. It can read and display the data from your HTML table, but it can also display data from various data sources, like XML, JSON, Array, CSV or TSV. jqxDataTable comes with easy to use APIs and works across devices and browsers.

Features

  • Binding to Local and Remote Data
  • Sorting
  • Filtering
  • Paging
  • Row Editing and Validation
  • Nested HTML Tables
  • Row Details
  • Localization
  • Columns Resizing
  • Columns Hierarchy
  • Pinned Columns
  • Foreign Columns
  • Cells Formatting
  • Custom Cells Rendering
  • Aggregates

Before starting, we need to download the following libraries from jqwidgets.

  1. <!--CSS -->  
  2. <link href="~/Content/jqx.base.css" rel="stylesheet" />  
  3. <!--JS -->  
  4. <script src="~/Scripts/jquery-1.7.1.min.js"></script>  
  5. <script src="~/Scripts/jqxcore.js"></script>  
  6. <script src="~/Scripts/jqxdata.js"></script>  
  7. <script src="~/Scripts/jqxbuttons.js"></script>  
  8. <script src="~/Scripts/jqxscrollbar.js"></script>  
  9. <script src="~/Scripts/jqxdatatable.js"></script>  
  10. <script src="~/Scripts/demos.js"></script>  
SQL Database part

Create Table

You will find the table, given below, used in our application:

table

After creating the table, you can fill it with the data, as shown below:

table

Create your MVC application

Open Visual Studio. Click on File > New > Project and name your project, as shown below:

MVC application

MVC application

Creating ADO.NET Entity Data Model

In this level, we need to create an Entity Data Model which allows us to retrieve the data from the database.

Right click on the project name. Click Add > Add New Item. In the dialog box, select Data > Click Add button.

ADO.NET Entity Data Model

ADO.NET Entity Data Model

After clicking the Next button, the dialog box will be displayed, as below. You need to choose your server name and select your database.

database

Finally, we see that EDMX model generates OrderDetails class.

EDMX model

Create a controller

Now, we proceed to create a controller. Right click on the controller folder > Add > Controller > Enter Controller name (‘Home Controller’).

controller

HomeController.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace DataTableMVC4.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         //DbContext  
  12.   
  13.         private DbPersonnesEntities db = new DbPersonnesEntities();  
  14.   
  15.         //  
  16.         // GET: /Home/  
  17.   
  18.         public ActionResult Index()  
  19.         {  
  20.             return View();  
  21.         }  
  22.   
  23.   
  24.         public JsonResult GetOrderDetails()  
  25.         {  
  26.             var DbOrdersDetails = from d in db.OrderDetails  
  27.                                   select new  
  28.                                   {  
  29.                                       d.SupplierName,  
  30.                                       d.ProductName,  
  31.                                       d.Quantity,  
  32.                                       d.Price,  
  33.                                       d.Address,  
  34.                                       d.City  
  35.                                   };  
  36.   
  37.             return Json(DbOrdersDetails, JsonRequestBehavior.AllowGet);  
  38.         }  
  39.   
  40.   
  41.     }  
  42. }  
As you can see, I am creating GetOrderDetails() action to retrieve the data from OrderDetails table, in JSON format.

Creating a Strongly Typed View

We are going to create a strongly typed View.

strongly typed view

Index.cshtml
  1. @model DataTableMVC4.OrderDetails  
  2.   
  3. @{  
  4.     Layout = null;  
  5. }  
  6.   
  7. <!DOCTYPE html>  
  8.   
  9. <html>  
  10. <head>  
  11.     <meta name="viewport" content="width=device-width" />  
  12.     <title>DataTable MVC 4</title>  
  13.     <!--CSS -->  
  14.     <link href="~/Content/jqx.base.css" rel="stylesheet" />  
  15.     <!--JS -->  
  16.     <script src="~/Scripts/jquery-1.7.1.min.js"></script>  
  17.     <script src="~/Scripts/jqxcore.js"></script>  
  18.     <script src="~/Scripts/jqxdata.js"></script>  
  19.     <script src="~/Scripts/jqxbuttons.js"></script>  
  20.     <script src="~/Scripts/jqxscrollbar.js"></script>  
  21.     <script src="~/Scripts/jqxdatatable.js"></script>  
  22.     <script src="~/Scripts/demos.js"></script>  
  23.   
  24.      <script type="text/javascript">  
  25.          $(document).ready(function () {  
  26.              // prepare the data  
  27.              var source =  
  28.              {  
  29.                  dataType: "json",  
  30.                  dataFields: [  
  31.                       { name: 'SupplierName', type: 'string' },  
  32.                       { name: 'ProductName', type: 'string' },  
  33.                       { name: 'Quantity', type: 'number' },  
  34.                       { name: 'Price', type: 'number' },  
  35.                       { name: 'Address', type: 'string' },  
  36.                       { name: 'City', type: 'string' }  
  37.                      
  38.                  ],  
  39.                  url: 'Home/GetOrderDetails'  
  40.                   
  41.              };  
  42.              var dataAdapter = new $.jqx.dataAdapter(source);  
  43.   
  44.              // create jqxDataTable.  
  45.              $("#dataTable").jqxDataTable(  
  46.              {  
  47.                  source: dataAdapter,  
  48.                  pageable: true,  
  49.                  altRows: true,  
  50.                  sortable: true,  
  51.                  groups: ['SupplierName'],  
  52.                  width: 850,  
  53.                  groupsRenderer: function (value, rowData, level) {  
  54.                      return "Supplier Name: " + value;  
  55.                  },  
  56.                  columns: [  
  57.                    { text: 'Supplier Name', hidden: true, cellsAlign: 'left', align: 'left', dataField: 'SupplierName', width: 280 },  
  58.                    { text: 'Product Name', cellsAlign: 'left', align: 'left', dataField: 'ProductName', width: 250 },  
  59.                    { text: 'Quantity', dataField: 'Quantity', cellsformat: 'd', cellsAlign: 'right', align: 'right', width: 80 },  
  60.                    { text: 'Price', dataField: 'Price', cellsformat: 'c2', align: 'right', cellsAlign: 'right', width: 70 },  
  61.                    { text: 'Address', cellsAlign: 'center', align: 'center', dataField: 'Address', width: 250 },  
  62.                    { text: 'City', cellsAlign: 'center', align: 'center', dataField: 'City' }  
  63.                  ]  
  64.              });  
  65.          });  
  66.     </script>  
  67.   
  68.   
  69. </head>  
  70. <body>  
  71.     <h2> Grouping Data using DataTable - MVC4</h2>  
  72.     <div id="dataTable"></div>  
  73. </body>  
  74. </html>  
Output 

Output

 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs