posted by  on  

Display tabular data on a webpage, jQuery DataTables plugin is best. jQuery Datatables is a client side grid control which is lightweight and easy to use

Here we are going to use a MVC application with jQuery and other required packages installed in it.

Using DataTables, first you need to include jquery file and then you need to include jquery-datatables.js and jquery-datatables.css files like this

<link href="http://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="http://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>

Now we will create a table. Create table in database. Below is the query to create table in database.

CREATE TABLE [dbo].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](40) NOT NULL,
[Age] [int] NULL,
[Languages] [nvarchar](60) NULL,
[Skills] [nvarchar](60) NULL,
PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert some data into Customers table for testing purpose.

You need to ensure that your HTML table is properly defined. This is particularly important for DataTable.

"Index.cshtml"

<link href="http://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />

<table id="tblEmployee" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Name</th>
            <th>Age</th>
            <th>Languages</th>
            <th>Skills</th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

To turn the table into a grid shown below, one line of function call like this

$(document).ready(function(){
   $('#tblEmployee').DataTable();
});

Add new "ADO.NET Entity Data Model".

Follow the steps given. Once you have done the processes, you can see the edmx file and other files in your project.

Add new action into Customer controller to get the view where we will implement jQuery DataTable with server-side paging and sorting.

[HttpGet]
public ActionResult Index()
{
    return View();
}

The following MVC controller method CustomerList() will be called for each DataTables draw (i.e. when paging, ordering, searching, etc.) and returns the json data.

Note: Install "System.Linq.Dynamic" from Nuget Packages

after this just include namespace in our Controller "using System.Linq.Dynamic;".

"Index.cshtml"

[HttpPost]
public ActionResult CustomerList()
{
    var draw = Request.Form.GetValues("draw").FirstOrDefault();
    var start = Request.Form.GetValues("start").FirstOrDefault();
    var length = Request.Form.GetValues("length").FirstOrDefault();
    var sortColumn = Request.Form.GetValues("columns[" +
    Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
    var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
    int pageSize = length != null ? Convert.ToInt32(length) : 0;
    int skip = start != null ? Convert.ToInt32(start) : 0;
    int totalRecords = 0;

    using (DatabaseEntities db = new DatabaseEntities())
    {
        var resultData = (from cust in db.Customers
                          //orderby sortColumn
                          select cust);
        //Sorting
        if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
        {
            resultData = resultData.OrderBy(sortColumn + " " + sortColumnDir);
        }
        totalRecords = resultData.Count();
        var data = resultData.Skip(skip).Take(pageSize).ToList();
        return Json(new
        {
            draw = draw,
            recordsFiltered = totalRecords,
            recordsTotal = totalRecords,
            data = data
        }, JsonRequestBehavior.AllowGet);
    }
}

We will do some code in our "index.cshtml" file.

@Scripts.Render("~/bundles/jquery")
<script src="http://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>

<script>
    $(document).ready(function () {
        $("#tblEmployee").DataTable({
            "processing": true, // for show progress bar
            "serverSide": true, // for process server side
            "filter": false, // this is for disable filter (search box)
            "orderMulti": false, // for disable multiple column at once
            "ajax": {
                "url": "/Home/CustomerList",
                "type": "POST",
                "datatype": "json"
            },
            "columns": [
            { "data": "Name", "name": "Name", "autoWidth": true },
            { "data": "Age", "name": "Age", "autoWidth": true },
            { "data": "Languages", "name": "Languages", "autoWidth": true },
            { "data": "Skills", "name": "Skills", "autoWidth": true },
            ]
        });
    });
</script>

Here in above code the first line is @Scripts.Render("~/bundles/jquery") Which contsins all jQuery.js file.

In this we are using "jQuery-1.10.2.min.js".

Remeber one thing there will be problem related to jQuery it will show you that $(..) is not defined.

For that you have to use first "jQuery-1.10.2.min.js" and then our "jquery.dataTables.min.js" file.

Each of the option explains in detail

Processing: true > instructs the datatable to display a "Processing" message while the ajax call is being executed

ServerSide: true > informs the datatable that the data will be coming from the server.

Columns: configure each column data source, sortablility and searchability etc

We have done everything!.

Output:

jQuery DataTable

Lateshtclick Technology Blog
Tags jQuery DataTable MVC Asp.net Ajax
posted by Latesht Click Latesht Click

Comments


Leave a Reply

Feel free to contribute!
Comment:
Name*:
Email*: