Click here to Skip to main content
15,867,923 members
Articles / Web Development / ASP.NET
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
25 Nov 2015CPOL1 min read 36.6K   8   4
An easy way to implement 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:

C#
/// <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.

C#
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.

HTML
<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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Sri Lanka Sri Lanka
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to access server side without using MVC action method Pin
Mohamed Mohideen S.A2-May-17 23:21
Mohamed Mohideen S.A2-May-17 23:21 
QuestionLarge Data Pin
Rajeev Raina6-Jul-16 9:05
Rajeev Raina6-Jul-16 9:05 
QuestionoTable? Pin
Member 1196760321-Feb-16 20:57
Member 1196760321-Feb-16 20:57 
AnswerRe: oTable? Pin
Firnas10-May-16 8:14
Firnas10-May-16 8:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.