Jquery Datatable serverSide pagination

29 Views Asked by At

my problem is that my jquery datatable opens very slowly. My scenario is as follows. I have a filtering screen and after making the necessary filtering, my results page opens with the see results button. In this button, I get the data I want by submitting the <form> tag. My table consists of 87 columns and 1207 data in total. Unfortunately, for these values, the opening time takes a total of 15-16 seconds. I wanted to do serverSide pagination, but the return type of my method on the .net core side is return View("Result", result). If I share my codes, I would be very happy if experienced friends would answer me about what changes I can make. Happy forum :)

public IActionResult FastFiltering(FastFilteringRequest model)
{
    try
    {
        var fastFilteringParameters = _mapper.Map<FastFilteringDto>(model);
        List<FundAndDistributionCombinationDto> result = new List<FundAndDistributionCombinationDto>();
        result = _fundService.FastFiltering(fastFilteringParameters, userId).ToList();
        foreach (var fund in result)
        {
            fund.FundValue.DailyReturn = fund.FundValue.DailyReturn.Value < 1 && fund.FundValue.DailyReturn > -1 ? GetFirstDigitNonZeroDecimal((decimal)fund.FundValue.DailyReturn) : decimal.Parse(fund.FundValue.DailyReturn.Value.ToString("0.00"));
            fund.FundValue.WeeklyReturn = fund.FundValue.WeeklyReturn.Value < 1 && 
        }
        int pageSize = 0;
        var draw = Convert.ToInt32(Request.Form["draw"].FirstOrDefault() ?? "0");,
        var start = Request.Form["start"].FirstOrDefault() ?? "0";
        var length = Request.Form["length"].FirstOrDefault() ?? "10";
        var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
        var sortColumnDir = Request.Form["order[0][dir]"].FirstOrDefault();
        var searchValue = Request.Form["search[value]"].FirstOrDefault();
        pageSize = length !=null ? Convert.ToInt32(length) : 0;
        int skip = start !=null ? Convert.ToInt32(start) : 0;
        var data = (from resultPagingData in result select resultPagingData);
        if(!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortColumnDir))
        {
            data = data.AsQueryable().OrderBy(sortColumn + "" + sortColumnDir);
        }

        int totalRecord = data.Count();
        var cData = data.Skip(skip).Take(pageSize).ToList();

        var settings = new JsonSerializerSettings
        {
            ReferenceLoopHandling = ReferenceLoopHandling.Ignore
        };

        var jsonData = JsonConvert.SerializeObject(new
        {
            draw = draw,
            recordsFiltered = totalRecord,
            recordsTotal = totalRecord,
            data = cData
        }, settings);

        return Content(jsonData, "application/json");
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

this is my C# Code

var codeListTable = $('#' + tableId).DataTable({
    stateSave: true,
    "processing": true,
    "serverSide": true,
    data:jsonData,
    "ajax": {
        "type": "POST",
        "url": '/Fund/FastFiltering',
        "dataType": 'json',
        "data": function (d) {
            d.customData = "Özelleştirilmiş Veri"; // İstekle sunucuya özel veri göndermek istiyorsanız kullanabilirsiniz
        }
    },
    colReorder: {
        fixedColumnsLeft: 4,
        fixedColumnsRight: 1,
    },
    fixedColumns: {
        left: 1
    },
    stateSaveCallback: function (settings, data) {
        $("#" + tableId).parent().addClass("scrollbar-custom");

        searchSetValue(codeListTable);
        var columnOrder = codeListTable.colReorder.order();
        data.ColReorder = columnOrder;
        var columnOrderData = data.ColReorder;

        var columnFilter = codeListTable.columns().visible();
        var colVisState = $.map(columnFilter, function (col, i) {
            return col ? true : false;
        });
        var newColVisState = [];
        for (var i = 0; i < columnOrderData.length; i++) {
            var index = columnOrderData[i];
            newColVisState[index] = colVisState[i];
        }
        $.ajax({
            type: 'POST',
            url: '@Url.Action("DataTableVisibilityCustomization", "Fund")',
            data: { FilterColumnStatus: JSON.stringify(newColVisState), FilterColumnOrder: JSON.stringify(columnOrderData)},
            success: function (result) {
            }
        });

    },
    stateLoadCallback: function (settings, callback) {
        $.ajax({
            type: 'POST',
            url: '@Url.Action("GetDataTableColumnVisibility", "Fund")',
            success: function (result) {
                if (result.data) {
                    var filterColumns = JSON.parse(eval(result.data)[0]);

                    if (filterColumns != null) {
                        $('#' + tableId + ' th').each(function (index) {
                            var column = filterColumns[index];
                            if (column === false) {
                                settings.aoColumns[index].bVisible = false;
                            }
                        });
                    }
                } else {
                    for (var i = 0; i <= 87; i++) {
                        settings.aoColumns[i].bVisible = [0, 1, 2, 3, 4, 87].includes(i);
                    }
                }
                if (result.order) {
                    var columnOrderData = JSON.parse(result.order);
                }

                callback();
                codeListTable.colReorder.order(columnOrderData);
                codeListTable.draw();
                codeListTable.buttons().container().appendTo('.tableActions');
            }
        });
    },

    autoWidth: false,
    columnDefs: [
        { "data": "Code", "name": "fundCode", "width": "0.2%", "targets": 0 },
        { "data": "FundDtoValue.Name", "name": "fundName", "targets": 1 },
        { "data": "FundDtoValue.subCategoryDto.mainCategoryDto.Name", "name": "fundCategory", "targets": 2 },
        { "data": "FundDtoValue.subCategoryDto.Name", "name": "fundSubCategory", "targets": 3 },
       .... Continue
        {
            type: 'turkish-numeric-comma',
            targets: [4, 5, 6, 7 ...Continue]
        },
        { bSortable: false, aTargets: [87] }
    ],

this is my Jquery Datatable Render code

I can return the data as json and open the Result page as before and use the json data in my jquery datatable.

0

There are 0 best solutions below