Grouping Data In jqxDataTable Using ASP.NET MVC 4

El Mahdi Archane
8y
3.8k
0
4

Article

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.

<link href="~/Content/jqx.base.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.7.1.min.js"></script>
<script src="~/Scripts/jqxcore.js"></script>
<script src="~/Scripts/jqxdata.js"></script>
<script src="~/Scripts/jqxbuttons.js"></script>
<script src="~/Scripts/jqxscrollbar.js"></script>
<script src="~/Scripts/jqxdatatable.js"></script>
<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:

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.

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

Finally, we see that EDMX model generates OrderDetails class.

Create a controller

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

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace DataTableMVC4.Controllers
{
public class HomeController : Controller
{
//DbContext
private DbPersonnesEntities db = new DbPersonnesEntities();
//
// GET: /Home/
public ActionResult Index()
{
return View();
}
public JsonResult GetOrderDetails()
{
var DbOrdersDetails = from d in db.OrderDetails
select new
{
d.SupplierName,
d.ProductName,
d.Quantity,
d.Price,
d.Address,
d.City
};
return Json(DbOrdersDetails, JsonRequestBehavior.AllowGet);
}
}
}

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.

Index.cshtml

@model DataTableMVC4.OrderDetails
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>DataTable MVC 4</title>
<link href="~/Content/jqx.base.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.7.1.min.js"></script>
<script src="~/Scripts/jqxcore.js"></script>
<script src="~/Scripts/jqxdata.js"></script>
<script src="~/Scripts/jqxbuttons.js"></script>
<script src="~/Scripts/jqxscrollbar.js"></script>
<script src="~/Scripts/jqxdatatable.js"></script>
<script src="~/Scripts/demos.js"></script>
<script type="text/javascript">
$(document).ready(function () {
// prepare the data
var source =
{
dataType: "json",
dataFields: [
{ name: 'SupplierName', type: 'string' },
{ name: 'ProductName', type: 'string' },
{ name: 'Quantity', type: 'number' },
{ name: 'Price', type: 'number' },
{ name: 'Address', type: 'string' },
{ name: 'City', type: 'string' }
],
url: 'Home/GetOrderDetails'
};
var dataAdapter = new $.jqx.dataAdapter(source);
// create jqxDataTable.
$("#dataTable").jqxDataTable(
{
source: dataAdapter,
pageable: true,
altRows: true,
sortable: true,
groups: ['SupplierName'],
width: 850,
groupsRenderer: function (value, rowData, level) {
return "Supplier Name: " + value;
},
columns: [
{ text: 'Supplier Name', hidden: true, cellsAlign: 'left', align: 'left', dataField: 'SupplierName', width: 280 },
{ text: 'Product Name', cellsAlign: 'left', align: 'left', dataField: 'ProductName', width: 250 },
{ text: 'Quantity', dataField: 'Quantity', cellsformat: 'd', cellsAlign: 'right', align: 'right', width: 80 },
{ text: 'Price', dataField: 'Price', cellsformat: 'c2', align: 'right', cellsAlign: 'right', width: 70 },
{ text: 'Address', cellsAlign: 'center', align: 'center', dataField: 'Address', width: 250 },
{ text: 'City', cellsAlign: 'center', align: 'center', dataField: 'City' }
]
});
});
</script>
</head>
<body>
<h2> Grouping Data using DataTable - MVC4</h2>
<div id="dataTable"></div>
</body>
</html>

Output

Up Next

Ebook Download

View all

Diving into Knockout.JS

Read by 1.7k people

Download Now!

Learn

View all