Server Side Pagination And Searching With DataTable And ASP.NET MVC

Integrating DataTable with asp.net MVC website and performing server side searching and Paging with it.

Hello everyone!

 

Today, we are going to integrate DataTable plugin with ASP.NET MVC website and perform some server-side searching and paging with it. As all of you might have heard, DataTable is a jQuery plugin that provides grid functionality with inbuilt searching, sorting, and paging functionalities.

Step 1

Create two action methods in Controller, one for displaying View and another method will return the data.

public ActionResult All(int rwaId=0)
       {
           return View();
       }

       public string GetPatientList(string sEcho, int iDisplayStart, int iDisplayLength, string sSearch)
       {
           string test = string.Empty;
           sSearch = sSearch.ToLower();
           int totalRecord = ent.Patients.Count();
           var patients = new List<Patient>();
           if (!string.IsNullOrEmpty(sSearch))
               patients = ent.Patients.Where(a=>a.EmailId.ToLower().Contains(sSearch)
               || a.PatientName.ToLower().Contains(sSearch)
               || a.MobileNumber.StartsWith(sSearch)
               ).OrderBy(a => a.Id).Skip(iDisplayStart).Take(iDisplayLength).ToList();
           else
               patients = ent.Patients.OrderBy(a => a.Id).Skip(iDisplayStart).Take(iDisplayLength).ToList();

           var result = (from p in patients join s in ent.StateMasters
                        on  p.StateMaster_Id equals s.Id
                        join c in ent.CityMasters
                        on p.CityMaster_Id equals c.Id
                        select new PatientDTO
                        {
                            CityName=c.CityName,
                            StateName=s.StateName,
                            Id=p.Id,
                            IsApproved=p.IsApproved,
                            IsDeleted=p.IsDeleted,
                            CityMaster_Id=p.CityMaster_Id,
                            EmailId=p.EmailId,
                            MobileNumber=p.MobileNumber,
                            StateMaster_Id=p.StateMaster_Id,
                            PatientName=p.PatientName,
                            Rwa_Id=p.Rwa_Id,
                            Location=p.Location,
                        }
                        ).ToList();

           StringBuilder sb = new StringBuilder();
           sb.Clear();
           sb.Append("{");
           sb.Append("\"sEcho\": ");
           sb.Append(sEcho);
           sb.Append(",");
           sb.Append("\"iTotalRecords\": ");
           sb.Append(totalRecord);
           sb.Append(",");
           sb.Append("\"iTotalDisplayRecords\": ");
           sb.Append(totalRecord);
           sb.Append(",");
           sb.Append("\"aaData\": ");
           sb.Append(JsonConvert.SerializeObject(result));
           sb.Append("}");
           return sb.ToString();
       }

Step 2

Create a View for displaying your data.

<div id="mainContainer">
       <div class="row">
           <div class="col-md-6">
               Patient List
           </div>
       </div>
       <div class="row">
           <div class="col-md-12">
              <table id="tbl">
                  <thead>
                      <tr>
                          <th>Name</th>
                          <th>Email Id</th>
                          <th>Mobile Number</th>
                          <th>Location</th>
                          <th>City</th>
                          <th>State</th>
                          <th>Action</th>
                      </tr>
                  </thead>

              </table>
           </div>
       </div>

   </div>

Step 3

 

Add the required libraries and JavaScript code in the end of your View page. 

<link rel="stylesheet" href="https://cdn.datatables.net/1.10.18/css/jquery.dataTables.min.css" />
<script src="/Scripts/Library/jquery-1.7.1.min.js"></script>
<script src="https://cdn.datatables.net/1.10.18/js/jquery.dataTables.min.js"></script>
<script>
    var oTable = $( '#tbl').dataTable({
        "bServerSide": true ,
        "sAjaxSource": "/Patient/GetPatientList",
        "sServerMethod": "POST" ,
        "aoColumns": [
        { "mData": "PatientName" },
        { "mData": "EmailId" },
        { "mData": "MobileNumber" },
        { "mData": "Location" },
        { "mData": "CityName" },
        { "mData": "StateName" },
        {
            "mData": function (o) {
                return "<a href='/Patient/Edit?Id="+o.Id+"' class='btn btn-info'><i class='fa fa-pencil'></i></a>";
            }
        }
        ],
    });
</script>

Thats it. Our grid is ready.