DataTable Ajax Server Side Loading

Instead of Loading all items at once in clientside, we may require to load data partially with pagination for better loading.

scenario: when we have 1000s of data in table and we are showing them without filtration

Simple use Datatable Serverside Loading

Changes in Controller side:

   [HttpPost]
        public async Task<JsonResult> AjaxCustomerList(DataTableModel Data)
        {

            DataTableReponse<Customer_VM> Response = new DataTableReponse<Customer_VM>();
            //property mapping

            try
            {

                //IList<Customer> DataResult = new List<Customer>();
                IQueryable<Customer> Query = _MainRepo.GetList()
                    .Include(x => x.District)
                    .Include(x => x.Customer_Company)
                    .Include(x => x.Customer_Person)
                    .AsNoTracking();
                var AllRecordCount = await Query.CountAsync();

                Type C = typeof(Customer);

                foreach (var item in Data.columns)
                {
                    if (item.data != null && item.name != null && item.name.Length > 0)
                    {
                        //search respective data list
                        var P1 = ReflectionHelper.GetProperty(C, item.data);
                        if (P1 == null)
                        {
                            //no properpty
                            Query = Query.WhereContainsExp(item.data, item.name);
                        }
                    }
                }

                if (Data.search != null && Data.search.value != null)
                {
                    Query = Query.Where(x =>
                        x.Name.ToString().Contains(Data.search.value) ||
                        x.Address.ToUpper().Contains(Data.search.value.ToUpper()) ||
                        x.MobileNumber.Contains(Data.search.value) ||
                        x.CBSCode.Contains(Data.search.value)
                    );
                }

                string sortBy = "Id";
                bool sortDir = true;

                var FilterRecCount = await Query.CountAsync();

                if (Data.order != null && Data.order.Any() && Data.order[0].column != 0)
                {
                    // in this example we just default sort on the 1st column
                    sortBy = Data.columns[Data.order[0].column].data;
                    sortDir = Data.order[0].dir.ToLower() == "asc";
                }
                if (Data.length <= 0) { Data.length = 100; }
                // have to give a default order when skipping .. so use the PK
                IList<Customer> DataList = await Query.OrderBy<Customer>(sortBy, sortDir).Skip(Data.start).Take(Data.length)
                    //.Include(x => x.ben02document_details)
                    .ToListAsync();
                //List<Customer_VM> Customer_VMList = Mapper.Map<List<Customer_VM>>(CustomerList);

                Response.data = Mapper.Map<List<Customer_VM>>(DataList);
                Response.recordsTotal = AllRecordCount;// dataTable.CustomerList.Count;
                Response.recordsFiltered = FilterRecCount;// dataTable.CustomerList.Count;
                Response.draw = Data.draw;
            }
            catch (Exception ex)
            {
                Response.data = null;
                Response.recordsTotal = 0;// dataTable.CustomerList.Count;
                Response.recordsFiltered = 0;// dataTable.CustomerList.Count;
                Response.draw = Data.draw;
                ErrorHelper.GetMsg(ex);
            }
            _db.Configuration.LazyLoadingEnabled = false;
            _db.Configuration.ProxyCreationEnabled = false;
            return Json(Response, JsonRequestBehavior.AllowGet);
        }


For Client Side:

    <script>
            $(document).ready(function () {
                $('.dataTableAjax').DataTable({
                    "processing": true,
                    "serverSide": true,
                    "iPageLength": 100,
                    "PageLength": 100,
                    "ajax": {
                        'url': "/Customer/AjaxCustomerList",
                        'type': 'POST'
                    },
                    "columns": [
                        { "searchable": false, 'data': null },
                        { "data": "Name" },
                        { "data": "Address" },
                        { "data": "District_VM.Name" },
                        { "data": "MobileNumber" },
                        { "data": "CBSCode" },
                        { "data": "CustomerType" },
                        { "data": "FormatDate" },
                        { "data": "Status" },
                        {
                            "data": null,
                            "render": function (data, type, row, meta) {
                                return '<a class="btn btn-xs  btn-light-blue tooltips" href="/Customer/Edit/' + data.Id + '"><i class="fa fa-edit"></i></a>' + '<a class="btn btn-xs  btn-success tooltips" href="/Customer/Details/' + data.Id + '"><i class="fa fa-eye"></i></a>';
                            }
                        }
                    ],
                    "fnRowCallback": function (nRow, aData, iDisplayIndex) {
                        $("td:first", nRow).html(iDisplayIndex + 1);
                        return nRow;
                    }
                });
            });
    </script>

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.