HOW TO USE DATATABLE WITH PAGINATION AND FILTER IN MVC.

Ankita Pandey

Total Post:183

Points:1285
Posted by  Ankita Pandey
C#  JQuery  HTML  Mvc4 
 3030  View(s)
Ratings:
Rate this:
every one Please Help me How to use datatable with pagination and filter in mvc.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: How to Use Datatable With pagination and filter in mvc.

     

     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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!