jQuery Datatables Server-side Pagination in ASP.NET MVC with Entity Framework

Introduction

jQuery Datatables is my favorite and best library available for displaying data in a table. It also allows you to bring data from server on-demand, i.e., 10 rows at a time. This tip explains how to implement jquery datatables in ASP.NET MVC application with easy steps.

Background

You must be familiar with ASP.NET MVC applications in order to understand the flow of this process. It is also advised to read about jquery datatables and bootstrap frameworks to understand the implementation.

Using the Code

In order to capture datatable events, we need to create a class as follows:

/// <summary>
/// Class that encapsulates most common parameters sent by Jquery DataTables
/// </summary>
public class JQueryDataTableParams
{
    /// <summary>
    /// Request sequence number sent by DataTable,
    /// same value must be returned in response
    /// </summary>       
    public string sEcho { get; set; }

    /// <summary>
    /// Text used for filtering
    /// </summary>
    public string sSearch { get; set; }

    /// <summary>
    /// Number of records that should be shown in table
    /// </summary>
    public int iDisplayLength { get; set; }

    /// <summary>
    /// First record that should be shown(used for paging)
    /// </summary>
    public int iDisplayStart { get; set; }

    /// <summary>
    /// Number of columns in table
    /// </summary>
    public int iColumns { get; set; }

    /// <summary>
    /// Number of columns that are used in sorting
    /// </summary>
    public int iSortingCols { get; set; }

    /// <summary>
    /// Comma separated list of column names
    /// </summary>
    public string sColumns { get; set; }

    /// <summary>
    /// Order no of the column that is used to do sorting
    /// </summary>
    public int iSortCol_0 { get; set; }

    /// <summary>
    /// Sort direction
    /// </summary>
    public string sSortDir_0 { get; set; }
}

Now, we have to have a method to load data for datatable. Assuming that we are going to show firstname, lastname, company and jobtitle from member table. I have put all the logic in Controller itself. But as a best practice, you can move the logic separately in a Business logic class.

public ActionResult GetDataForDatatable(jQueryDataTableParams param)  
{  
    IQueryable<Member> memberCol = myDbContext.Member  
                      .Select(m => m.FirstName,  
                          m.LastName,  
                          m.Company,  
                          m.JobTitle)  
                      .AsQueryable();  


    int totalCount = memberCol.Count();  
    IEnumerable<Member> filteredMembers = memberCol; 

    if (!string.IsNullOrEmpty(param.sSearch))  
    {  
      filteredMembers = memberCol  
              .Where(m => m.FirstName.Contains(param.sSearch) ||  
                 m.LastName.Contains(param.sSearch) ||  
                 m.Company.Contains(param.sSearch) ||  
                 m.JobTitle.Contains(param.sSearch));  
    }  

    Func<Member,string> orderingFunction = (m => param.iSortCol_0 == 0 ? m.FirstName :  
                         sortIdx == 1 ? m.LastName :  
                         sortIdx == 2 ? m.Company :  
                         m.JobTitle);  

    if (param.sSortDir_0 == "asc")  
      filteredMembers = filteredMembers.OrderBy(orderingFunction);  
    else  
      filteredMembers = filteredMembers.OrderByDescending(orderingFunction);  

    var displayedMembers = filteredMembers  
             .Skip(param.iDisplayStart)  
             .Take(param.iDisplayLength);  
    var result = from a in displayedMembers  
           select new[] { a.FirstName, a.LastName, a.Company,   
                  a.JobTitle, a.ID.ToString() };  

    return Json(new  
       {  
         sEcho = param.sEcho,  
         iTotalRecords = totalCount,  
         iTotalDisplayRecords = filteredMembers.Count(),  
         aaData = result  
       },  
       JsonRequestBehavior.AllowGet);  
}

Now the backend is done. Following is how we initialize our datatable and design the HTML in our view.

Make sure you give reference to jQuery js, jQuery Datatable js, jQuery Datatable css in your view or _Layout.

<table id="tblMember" class="table" style="width: 100%">  
   <thead>  
     <tr>  
       <th>First Name</th>  
       <th>Last Name</th>  
       <th>Company</th>  
       <th>Job Title</th>  
       <th>Actions</th>  
     </tr>  
   </thead>  
   <tbody>  
   </tbody>  
 </table>  
 <script> 
   var oTable;
   $(document).ready(function () {  
     // destroy existing datatable before, initiating  
     if (oTable != undefined) {  
       oTable.fnDestroy();  
     };  
     oTable = $('#tblMember').dataTable({  
       "bServerSide": true,  
       "sAjaxSource": '@Url.Action("GetDataForDatatable","Member")',  
       "bProcessing": true,  
       "aoColumns": [  
               { "sName": "FirstName" },  
               { "sName": "LastName" },  
               { "sName": "Company" },  
               { "sName": "JobTitle" },  
               {  
                 "sName": "ID",  
                 "bSearchable": false,  
                 "bSortable": false,  
                 'mRender': function (data) {  
                   return '<a href="@Url.Action("Edit", "Member")/' + 
                   data + '" class="btn btn-warning btn-xs" 
                   role="button">@labels.btn_edit</a>' + '&nbsp;' +  
                       '<button onclick="confirmDelete(' + data + ');" 
                       class="btn btn-danger btn-xs">@labels.btn_delete</button>';  
                 }  
               },  
       ]  
     });  
   }  
</script>

Note that I also set action buttons based on the ID of each record.

Points of Interest

It was very interesting to find a way like this where I can duplicate these codes in order to do another datatable page. However, I have found easier ways to implement datatable in MVC that I will post in the future.

History

• 2015-11-25 Initial version
• 2016-05-10 Updated code