How to obtain efficient data records in a dataTable in MVC asp.net

Question

How to obtain efficient data records in a dataTable in MVC asp.net

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the following dataTable that I show from the database. In the View:

$(document).ready(function () {
$('#example').DataTable({
            "lengthMenu": [[2, 5, 10, 25], [2, 5, 10, 25]],
            "language": {
                "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"
                }
            }
        });   });
</script>
 <table id="example" class="table table-striped table-bordered dt-responsive nowrap" cellspacing="0" width="100%">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Titulo</th>
                    <th>Descripcion</th>
                    <th>Formato</th>
                    <th>Fecha</th>
                </tr>
            </thead>
            <tbody>
            <% For Each item In Model.listadoNoticias%>
            <tr id="fila_not_<%:item.IdNoticia%>">
                <td>
                    <%: item.IdNoticia%>
                </td>
                <td>
                    <%: item.TituloNoticia%>
                </td>
                <td>
                    <%: item.DescripcionNoticia%>
                </td>
                <td>
                    <%: item.FormatoFotoPortadaNoticia%>
                </td>
                <td>
                    <%: item.FechaPublicacionNoticia%>
                </td>


            </tr>
            <%Next%>
           </tbody>
        </table>

In the homeController:

Public Function listado_noticia() As ActionResult
    Using db As New BD_LOSCOPIHUESEntities1
    Dim CurrentPage=0
    Dim PageSize=15

        Dim l As New ListadoNoticiasViewModel
        Dim listadoNoticia_q = (From noti In db.NOTICIA Select noti Order By noti.FechaPublicacionNoticia Descending)
        l.listadoNoticias = listadoNoticia_q
        ' l.listadoNoticias = listadoNoticia_q.Skip(CurrentPage).Take(PageSize).ToList()            Return View(l)
    End Using
End Function

What the background datatable of the view does, is to list ALL the news record, where it automatically creates the number of rows and pagination. But if too many records were listed, it would be very inefficient. What I want, is to send a certain amount of records to the datatable of the view, for example if in total there are 15 records, send 5 records to the view, showing in the lower part of the datatable 1,2,3 to paginate and to select the page, go to the controller with the parameter of the page (.listNews = listNoticia_q.Skip (CurrentPage) .Take (PageSize) .ToList ()) to show the other records and thus not load the page with so much data. Any idea how I could manipulate the datatable to be efficient?

Note: I am working on MVC vb.net with bootstrap

jquery html5 bootstrap asp.net asp.net-mvc-5

asked by Danilo 15.04.2016 в 06:39

source

2 answers

Avoid registering duplicates in database with Codeigniter How to compare a query with 2 variables at the same time in a table of the BD?

score 1 · Answer 1

I think this implementation could help

jQuery DataTables and ASP.NET MVC Integration - Part I

As you will notice when enabling paging, you should receive in the action the values that define, for example, the number of items that must be displayed.

The example uses a jQueryDataTableParamModel class where the iDisplayLength property determines this number that you could use on the linq with the Take() and Skip()

The response of the action should be a json with only the records that are on this page, but you will see that they must respect a certain structure, where you not only send the data but also the total number of items and the amount per page .

{   "sEcho":"1",
    "iTotalRecords":97,
    "iTotalDisplayRecords":3,
    "aaData":[    ["1","Microsoft","Redmond","USA"],
                  ["2","Google","Mountain View","USA"],
                  ["3","Gowi","Pancevo","Serbia"]
        ]
}

score 0 · Answer 2

I found a solution, or the way to the solution. With some jquery events, I can manipulate and send parameters to the controller.

$('#example')
    .on( 'order.dt',  function () { console.log('Order' ); } )
    .on( 'search.dt', function () {console.log('Search' ); } )
    .on( 'page.dt',   function () { console.log('Page' ); } )
    .dataTable();
} );