时间戳

首页 > 解决方案 > 如何对数据表中超过 2000 条记录进行分页？MVC

javascript - 如何对数据表中超过 2000 条记录进行分页？MVC

问题描述

我需要你的帮助。
目前我的查询返回了2000多条记录，通过存储过程从sql server获取记录后，我将它们发送到表中（我使用DataTables库1.10.19），这是它开始形成表和相应分页的时候问题是在表格中显示内容需要超过30 秒或更长时间，我该怎么做才能只显示用户看到的结果（每页 10 条记录）并计算它会的其他页面如果用户前进到下一条记录并给我带来以下 10 条记录，或者页面会如何？
这是我的代码：
控制器： 

 public JsonResult Get_Pacientes(int IdEstatus)
{
 List<Paciente> l = new List<Paciente>(Get_RowsPac(IdEstatus));
 var jsonResult = Json(new { success = true, lcontenedor= l}, JsonRequestBehavior.AllowGet);
                    jsonResult.MaxJsonLength = int.MaxValue;
                    return jsonResult;
}

**JavaScript：** 
   function ObtieneListaEmp(id) {
            $('#fader').css('display', 'block');
            var EnviaDatos =
            {
                //"e.CVE_EMPLEADO": $('#NOMINA').val(),
                "IdEstatus": id,

            };
            $.ajax({
                url: '../Empleado/EmpleadosXEstatus',
                contentType: 'application/json; charset=utf-8',
                type: 'POST',
                dataType: 'json',
                data: JSON.stringify(EnviaDatos),
                success: function (response) {
                    $('#fader').css('display', 'none');

                    if (response.success) {

                        AgregaTablaEmpPacTest(); //this function adds the table header

                        var data = [];
                        
                        data = response.lcontenedor;
                        setTimeout(function () {
                            //aqui va la tarea
                            $('#tbl').DataTable({
                                data: data,
                                deferRender: true,
                                "columns": [
                                    //{ "data": "CLAVE_AFILIACION" },
                                    { // render //clave afilacion
                                        "data": function (data, type, row, meta) {
                                            return "<a href=\"#\" onclick='LlamaRegistro(" + '"' + data.CLAVE_AFILIACION + '"' + ")'>" + data.CLAVE_AFILIACION + "</a>";
                                        }
                                    },
                                    { "data": "NOMINA" },
                                    {
                                        data: null,
                                        render: function (data, type, row) {
                                            return row.NOMBRE + ' ' + row.APELLIDO_PAT + ' ' + row.APELLIDO_MAT;
                                        }
                                    },
                                    { "data": "TELEFONO_CASA" },
                                    { "data": "CELULAR" },
                                    { "data": "SECRETARIA" },
                                    { "data": "DIRECCION" },
                                    { "data": "PUESTO" },
                                    { "data": "EDAD" },
                                    { "data": "OBSERVACION" },
                                    { "data": "FECHA_SEG" },
                                    { "data": "DESC_ESTATUS_EVO" },
                                    { "data": "FECHA_PRUEBA" },
                                    { "data": "FECHA_RESULTADO" },
                                    { "data": "LUGAR_DE_EXP" },
                                    { "data": "DESC_ESTATUS_PRU" },
                                    { "data": "FECHAINICIO_INC" },
                                    { "data": "DIASINCAPACIDAD_INC" },
                                    { "data": "FECHAFIN_INC" },
                                    //{ "data": "FECHAFIN_INC" },
                                    { // render IMPRIMIR
                                        "data": function (data, type, row, meta) {
                                            return "<button type=\"button\" class=\"btn btn-secondary fa fa-print C_Imprimir\">";
                                        }
                                    },
                                ],
                                "columnDefs": [
                                    {
                                        "targets": [3],
                                        "visible": false,
                                        "searchable": false
                                    },
                                    {
                                        "targets": [4],
                                        "visible": false,
                                        "searchable": false
                                    }
                                ],
                                dom: 'Bfrtip',
                                buttons: [
                                    //'copy', 'csv',
                                    'excel', {
                                        extend: 'pdf',
                                        orientation: 'landscape',
                                        pageSize: 'LEGAL'
                                    }, {
                                        extend: 'print'//,
                                        //messageTop: 'Lista de Empleados Registrados' + response.lOrdenesTrabajo[0].NOMBRE_PROV
                                        , text: 'Imprimir'
                                    }
                                ],
                                "order": [7, 'desc'],//ordernar por fecha de menor a mayor
                                "language": {
                                    "scrollX": true,
                                    "sProcessing": "Procesando...",
                                    "sLengthMenu": "Mostrar _MENU_ registros",
                                    "sZeroRecords": "No se encontraron resultados",
                                    "sEmptyTable": "Ningún dato disponible en esta tabla",
                                    "sInfo": "Mostrando registros del _START_ al _END_ de un total de _TOTAL_ registros",
                                    "sInfoEmpty": "Mostrando registros del 0 al 0 de un total de 0 registros",
                                    "sInfoFiltered": "(filtrado de un total de _MAX_ registros)",
                                    "sInfoPostFix": "",
                                    "sSearch": "Buscar:",
                                    "sUrl": "",
                                    "sInfoThousands": ",",
                                    "sLoadingRecords": "Cargando...",
                                    "oPaginate": {
                                        "sFirst": "Primero",
                                        "sLast": "Último",
                                        "sNext": "Siguiente",
                                        "sPrevious": "Anterior"
                                    },
                                    "oAria": {
                                        "sSortAscending": ": Activar para ordenar la columna de manera ascendente",
                                        "sSortDescending": ": Activar para ordenar la columna de manera descendente"
                                    }
                                }
                            });
                        }, 50);
                        //AgregaTablaEmpPac(response.lcontenedor);


                    }
                    else {
                        alert(response.responseText);
                    }
                },
                error: function (xhr) {
                    $('#fader').css('display', 'none');
                    alert('Ocurrio un Error Inesperado.' + $('#Datos_ID_FechaIn').val());
                }
            })

        };

标签： javascriptc#jquerymodel-view-controllerdatatables

解决方案

我有一个同样的问题，我使用这种类型的代码来发送所需的记录数：

[DataContract]
public class Course
{
    [DataMember] public string[] UrlsCourse;
    [DataMember] public string BackgroundImage;
    [DataMember] public string KeyWords;
    [DataMember] public string TitleCourse;
    [DataMember] public string[] stats;
    [DataMember] public string description;

    public Course(string[] urlsCourse, string backgroundImage, string keyWords, string titleCourse, string[] stats, string description)
    {
        UrlsCourse = urlsCourse;
        BackgroundImage = backgroundImage;
        KeyWords = keyWords;
        TitleCourse = titleCourse;
        this.stats = stats;
        this.description = description;
    }
}

[DataContract]
public class SearchCourses
{
    [DataMember] public readonly int maxRecords;
    [DataMember] public int maxRecordsPerPage;
    [DataMember] public int currentRecord;
    [DataMember] public int currentPage;
    [DataMember] public int maxPages;
    [DataMember] public int[] minmax;
    [DataMember] public int maxRecordsSent;
    [DataMember] public int currentmaxLinks;
    [DataMember] public Course[] currentDatas;
    [DataMember] public string[] categories;
    public Course[] search;
    public string[] courses;
    public int maxLinks;
    public SearchCourses(string[] courses, int maxRecordsPerPage = 10, int currentPage = 1, int currentRecord = 0, int maxLinks = 9)
    {
        this.currentPage = currentPage;

        this.maxRecordsPerPage = maxRecordsPerPage;
        this.currentRecord = currentRecord;
        this.courses = courses;
        this.maxRecords = courses.Length;
        this.maxLinks = maxLinks;
        this.search = courses.Select(r => getRecord(r.Split(new string[] { "\r\n" }, StringSplitOptions.None))).ToArray();
        categories = this.search.Select(c => c.KeyWords.Split('-')[0].Trim()).Distinct().ToArray();
        currentDatas = search.Take(maxRecordsPerPage).ToArray();

        maxRecordsSent = maxRecords;

        maxPages = maxRecordsSent / maxRecordsPerPage + 1;
        this.currentPage = 1;

        maxPages = maxRecordsSent / maxRecordsPerPage + 1;
        currentmaxLinks = maxLinks > maxPages ? maxPages : maxLinks;
        minmax = new int[] { 1, currentmaxLinks };

    } 

    public string GetJSON()
    {
        string json;



        using (var ms = new MemoryStream())
        {
            DataContractJsonSerializer js = new DataContractJsonSerializer(typeof(SearchCourses));
            js.WriteObject(ms, this);
            ms.Position = 0;
            using (var sr = new StreamReader(ms))
            {
                json = sr.ReadToEnd();
            }
        }

        return json;
    }

它是一个提供不同课程列表的服务器，所以我将最后一次搜索保留在“搜索”中，但只在 currentDatas 中发送所需的数据，（我只序列化 currentDatas 而不是使用 GetJSON() 方法的搜索）。

我有一个带有页数的书签……等等……

我管理显示最大页数的书签（这里我有 9 个链接书签），我管理按钮下一页和前进页..等等..

推荐阅读