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.
- <!--CSS -->
- <link href="~/Content/jqx.base.css" rel="stylesheet" />
- <!--JS -->
- <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:
After creating the table, you can fill it with the data, as shown below:
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
- {
-
-
- private DbPersonnesEntities db = new DbPersonnesEntities();
-
-
-
-
- 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