Introduction:

In this article we will learn how to work with  jQuey Datatables with server side data. Here we are going to use jQury Datatables with  Asp.Net MVC 4 application

Description:

DataTables is a powerful jQuery plugin for creating Html table (Grid format) with more functionality searching, sorting and pagination without any configuration.  jQuery DataTables support both client side processing and as well as server side processing.

Now here I have implemented very basic datatable in ASP.NET MVC application where we fetched all the data from a server at once and done pagination, sorting, searching at the client side. This can be a performance issue fetching a large amount of data from server side at once. To resolve this performance issue, I am going to show you datatables server-side paging, sorting and filtering in asp.net MVC 4 application

  

Let’s see an example server side data processing Using DataTables

 

Frist create a View “CountryMaster.cshtml” and add in header tag

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

 

Second create a html table on your “CountryMaster.cshtml” View

       <table id="example" class="display" cellspacing="0">
                <thead>
                   <tr>
                        <th width="100">ID</th>
                        <th width="250">Country Name</th>
                        <th width="150">Action</th>
                    </tr>
                 </thead>
                <tbody>
                </tbody>
            </table>

Now, write script inside “Scripts” tag following code

 <script>
    $(document).ready(function () {
        $('#tblCountry').DataTable({
            "language":
      {
          "processing": "Proceccing"
      },
            "processing": true,
            "serverSide": true,
            "ajax":
              {
                  "url": "/Master/CountryMasterGrid",
                  "contentType": "application/json",
                  "type": "GET",
                  "dataType": "JSON",
                  "data": function (d) {
                      return d;
                  },
                  "dataSrc": function (json) {
                      json.draw = json.draw;
                      json.recordsTotal =json.recordsTotal;
                      json.recordsFiltered =json.recordsFiltered;
                      json.data = json.data;
                      var return_data = json;
                      return return_data.data;
                  }
              },
            "columns": [
           { "data": "CountryID" },
           { "data": "CountryName" },
            {
                data: "CountryID", "bSearchable": false,
                bSortable: false,
                mRender: function (data, type, row) {
               return '<a href="Master/Edit/' + data + '""class="openDialog btn btn-default btn-
                       warning">Edit</a>'
               + '<a href="Master/Delete/' + data + '"" Class="deleteDialog btn btn-default btn-
                 danger ">Delete</a>'
                }
            }
            ],
            "order": [1, "asc"],
        });
    });
</script>

Now generated entity frame work models  "Model", name it " M_Country."  That have following properties in them.

 

public partial class M_Country
    {
        public M_Country()
        {
            this.M_State = new HashSet<M_State>();
        }
   
        public long CountryID { get; set; }
        public string CountryName { get; set; }
        public Nullable<System.DateTime> CreatedDate { get; set; }
        public Nullable<long> CreatedBy { get; set; }
        public Nullable<long> UpdatetdBy { get; set; }
        public Nullable<System.DateTime> UpdatedDate { get; set; }   
        public virtual ICollection<M_State> M_State { get; set; }
    }

Now create a new models inside “MasterController” "Model", name it " DataTables." and add following properties in them.

public class DataTables
    {
        public int draw { get; set; }
        public int recordsTotal { get; set; }
        public int recordsFiltered { get; set; }
        public List<M_Country> data { get; set; }    }  

Now, In "MasterController" file add following function to load data from database.

   private List<M_Country> LoadData()
        {
            List<M_Country> Data = new List<M_Country>();
            Data =Uow.Country.GetAll().ToList();
            return Data;
        }

 

Now, In " MasterController " file add following function to short data from server side.

 private List<M_Country> SortByColumnWithOrder(string order, string orderDir, List<M_Country> data)
        {
            //Initialization.   
            List<M_Country> lst = new List<M_Country>();
            try
            {
                //Sorting   
                switch (order)
                {
                    case "0":
                        //Setting.   
                     lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase)?
                           data.OrderByDescending(p => p.CountryID).ToList()                           
                          : data.OrderBy(p=> p.CountryID).ToList();
                        break;
                    case "1":
                        // Setting.   
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase)?                         
                             data.OrderByDescending(p => p.CountryName).ToList()                            
                             : data.OrderBy(p=> p.CountryName).ToList();
                        break;
                    default:
                        // Setting.   
                        lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase)?                            
                           data.OrderByDescending(p => p.CountryName).ToList()                           
                           : data.OrderBy(p=> p.CountryName).ToList();
                        break;
                }
            }
            catch (Exception ex)
            {
                //info.   
                Console.Write(ex);
            }
            //info.   
            return lst;
 
        }

 

Now, in "MasterController" file let's create " CountryMasterGrid " method as follow

 public ActionResult CountryMasterGrid()
        {
 
            //Initialization.   
            DataTables result = new DataTables();
            try
            {
                //Initialization.   
                string search = Request.Params["search[value]"];
                string draw = Request.Params["draw"];
                string order = Request.Params["order[0][column]"];
                string orderDir = Request.Params["order[0][dir]"];
                int PageNo = Convert.ToInt32(Request.Params["start"]);
                int pageSize = Convert.ToInt32(Request.Params["length"]);
                //Loading.   
                List<M_Country> data = LoadData();
                // Total record count.   
                int totalRecords = data.Count;
                //Verification.   
                if (!string.IsNullOrEmpty(search) &&
                  !string.IsNullOrWhiteSpace(search))
                {
                    //Apply search   
                    data = data.Where(p =>
                              
                       p.CountryID.ToString().Contains(search.ToLower()) ||
                               
                           p.CountryName.ToString().ToLower().Contains(search.ToLower())
                               
                           ).ToList();
                }
                //Sorting.   
                data =SortByColumnWithOrder(order, orderDir, data);
                //Filter record count.   
                int recFilter = data.Count;
                // Applypagination.   
                data =data.Skip(PageNo).Take(pageSize).ToList();
                //Loading drop down lists.   
                result.draw = Convert.ToInt32(draw);
                result.recordsTotal =totalRecords;
                result.recordsFiltered =recFilter;
                result.data = data;
            }
            catch (Exception ex)
            {
                //Info   
                Console.Write(ex);
            }
            //Return info.   
            return Json(result, JsonRequestBehavior.AllowGet); 
        }

 

Here how the results will look, 

server side datatable pagination searching in asp.net mvc


I hope this article will help to you.

comments


Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like