Home > DeveloperSection > Forums > How to Use Datatable With pagination and filter in mvc.
Ankita Pandey
Ankita Pandey

Total Post:183

Points:1285
Posted on    September-30-2015 6:31 AM

 ASP.NET MVC C#  JQuery  HTML  Mvc4 
Ratings:


 1 Reply(s)
 1382  View(s)
Rate this:
every one Please Help me How to use datatable with pagination and filter in mvc.


aditya kumar Patel

Total Post:242

Points:1722
Posted on    September-30-2015 6:35 AM

 

 Jquery Code serverside

var oTable = $('#datatable').DataTable({
    "serverSide": true,
     "responsive": true,
    "pageLength": 50,
    "pagingType": "full_numbers",
    "ajax": {
        "type": "POST",
        "url": '/Home/DataHandler',
        "contentType": 'application/json; charset=utf-8',
        'data': function (data) {
            return data = JSON.stringify(data);
        }
    }, 
   // "dom": 'frtiS',
   // "scrollY": 500,
   // "scrollX": false,
   // "scrollCollapse": false,
   // "scroller": {
   // loadingIndicator: false
   //},
    "processing": true,
    "paging": true,
    "deferRender": true,
    "columns": [
       //{
       //    data: "UserId", "bSearchable": false,
       //    bSortable: false,
       //    mRender: function (data, type, full) {
       //        return ' <input type="checkbox"  value="' + data + '"" class="checkbox editor-active">';
       //    }
       //},
   //{ "data": "UserId" },
   { "data": "UserName" },
   { "data": "EmailId" },
   { "data": "Address" },
   { "data": "PhoneNumber" },
    {
        data: "UserId", "bSearchable": false,
        bSortable: false,
        mRender: function (data, type, full) {
            return '<a href="Home/Edit/' + data + '"" class="openDialog btn btn-default btn-warning"  >Edit</a> &nbsp;&nbsp;'
                + '<a href="Home/Delete/' + data + '"" Class="deleteDialog btn btn-default btn-danger " >Delete</a>&nbsp;&nbsp;'
            + ' <input type="checkbox"  value="' + data + '"" class="checkbox editor-active">';
        }
    }
    ],
    "order": [1, "asc"],
});

 Filter  class

 public class ResultSet
    {
        public List<UserModel> GetResult(string search, string sortOrder, int start, int length, List<User> dtResult, List<string> columnFilters)
        {
            return FilterResult(search, dtResult, columnFilters).SortBy(sortOrder).Skip(start).Take(length).ToList();
        }
        public int Count(string search, List<User> dtResult, List<string> columnFilters)
        {
            return FilterResult(search, dtResult, columnFilters).Count();
        }
        private IQueryable<UserModel> FilterResult(string search, List<User> dtResult, List<string> columnFilters)
        {
            IQueryable<User> results = dtResult.AsQueryable();
            var res = results.Where(p => (search == null || (p.UserName != null && p.UserName.ToLower().Contains(search.ToLower()) || p.EmailId != null && p.EmailId.ToLower().Contains(search.ToLower())
                || p.PhoneNumber != null && p.PhoneNumber.ToLower().Contains(search.ToLower())
                || p.UserDetail.FirstOrDefault().UserAddress != null && p.UserDetail.FirstOrDefault().UserAddress.ToLower().Contains(search.ToLower()))
              )).Select(x => new UserModel { UserId = x.UserId, UserName = x.UserName, PhoneNumber = x.PhoneNumber, EmailId = x.EmailId, Address = x.UserDetail.FirstOrDefault().UserAddress });
            return res;
        }

       
    }

Parameter Class

 public class jQueryDataTableParamModel
    {
         
        public string sEcho { get; set; }

      
        public string sSearch { get; set; }

        public int iDisplayLength { get; set; }

     
        public int iDisplayStart { get; set; }

      
        public int iColumns { get; set; }

      
        public int iSortingCols { get; set; }

       
        public string sColumns { get; set; }
    }

 Controller Action

  public JsonResult DataHandler(CodeFirst.Models.DataTableViewModel.DTParameters param)
        {
            try
            {
                var dtsource = new List<User>();
                dtsource = db.Users.Include(x => x.UserDetail).ToList();
                List<String> columnSearch = new List<string>();
                if (param != null && param.Columns != null)
                {
                    foreach (var col in param.Columns)
                    {
                        columnSearch.Add(col.Search.Value);
                    }
                }
                List<UserModel> data = new ResultSet().GetResult(param.Search.Value,

param.SortOrder, param.Start, param.Length,

dtsource, columnSearch);
                int count = new ResultSet().Count(param.Search.Value, dtsource, columnSearch);
                CodeFirst.Models.DataTableViewModel.DTResult<UserModel> result =

new CodeFirst.Models.DataTableViewModel.DTResult<UserModel>
                {
                    draw = param.Draw,
                    data = data,
                    recordsFiltered = count,
                    recordsTotal = count
                };
                return Json(result, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new { error = ex.Message }, JsonRequestBehavior.AllowGet);
            }
        }

HTML Binding
<table class="table  " id="datatable" style="margin-top: 10px">
            <thead id="header">
                <tr>
                    <th>User Name</th>
                    <th>EmailId</th>
                    <th>Address</th>
                    <th>Phone Number</th>
                    <th style="width: 160px">Action
                        <input id="checkAll" type="checkbox" />
                    </th>
                </tr>
            </thead>
            <tbody>
            </tbody>
        </table>

 


Modified On Sep-30-2015 07:01:52 AM

Don't want to miss updates? Please click the below button!

Follow MindStick