Server side paging with a generic data source (C#).

braden87

I'm very new to using Datatables and I have recently picked up a project at work that uses the library. The way the previous developer set up the Datatables was to use a generic source:

public async Task<ActionResult> DataTableAsync<TEntity>(AlertModel alert, string viewName = null) where TEntity : class
        {
            // get data
            var data = (from a in await Manager.AllAsync<TEntity>()
                        select a).ToList();

            var response = new TableModel<TEntity> 
            {
                Alert = alert,
                Data = data
            };

            // return partial data view
           return  PartialView(viewName ?? $"_{typeof(TEntity).Name}Table", response);
           
        }

The AllAsync method just returns the entire context set:

Context.Set<T>().ToListAsync();

And this is how the JavaScript for DataTables is set up:

function makeDataTable(id) {
    var selector = '#' + id;
    $(selector).DataTable({
        order: [],
        search: {
            smart: false
        },
        stateSave: true,
        columnDefs: [{
            targets: 'no-sort',
            orderable: false
        },
        {
            targets: 'date-col',
            type: 'date',
            orderSequence: [
                'desc',
                'asc'
            ]
        }],
        lengthMenu: [
            [10, 15, 20, -1],
            [10, 15, 20, 'All']
        ]
    });
    $(selector).width('100%');
}

When the context set has a lot of records it will cause an out of memory exception. How can I use Skip and Take within this generic method to return portions of the data set at a time to the table?

colin

Hi @braden87 ,

It sounds like you need to implement serverSide processing - details on the protocol is here.

Cheers,

Colin

braden87

Thank you for the reply @colin,

I understand that in the javascript setup for the server-side processing that I need to use Ajax. How would I point it in the direction of the generic data method (the DataTableAsync method)? Would it be:

function makeDataTable(id) {
    var selector = '#' + id;
    $(selector).DataTable({      
       serverSide: true,
        ajax: {
            url: 'DataController/DataTableAsync',
            type: 'POST'
            },
        order: [],
        search: {
            smart: false
        },
        stateSave: true,
        columnDefs: [{
            targets: 'no-sort',
            orderable: false
        },
        {
            targets: 'date-col',
            type: 'date',
            orderSequence: [
                'desc',
                'asc'
            ]
        }],
        lengthMenu: [
            [10, 15, 20, -1],
            [10, 15, 20, 'All']
        ]
    });
    $(selector).width('100%');
}

Do I have to include any of the generic TEntity stuff? It just confuses me on how to use server side processing because of the generic nature of the source. I read somewhere that I can use Scroller as well to render only the first page upon loading.

allan

How would I point it in the direction of the generic data method (the DataTableAsync method)?

That's up to the controller. If your routing is setup for that (which I think it normally is for .NET projects like this), then it will work just fine.

You'd need to have your server-side script handle the DataTables parameters that Colin linked to. That is not something .NET does out of the box.

Allan

braden87

@allan Like in the Ajax portion of the code example I pasted above?

allan

That doesn't implement server-side processing for DataTables, but yes, that is the basis for getting Ajax sourced data.

Allan

braden87

Based off of the information Colin gave I thought that

 serverSide: true,
        ajax: {
            url: 'DataController/DataTableAsync',
            type: 'POST'
            },

set up server side processing? Unless I am missing something?

colin

Hi @braden87 ,

I thinks there's a muddle here - serverSide tells the client that the ordering, searching, etc will be done by the server. Allan was meaning that it still needs the server side script to do the grunt work.

Cheers,

Colin

braden87

@colin are you meaning like a view or a stored procedure?

allan

Full details on what server-side processing sends to the server and requires back are available in the link Colin posted above.

You can use a view or a stored procedure, but you need code around that to select the items you want from the db.

First things first. Do you actually need server-side processing? Do you had tens of thousands or more rows to display? See this section of the manual for more details.

Allan

braden87

The largest table that I have is returning 40,000+ rows.

The other option I've come across is using Skip() and Take() but I don't know if that can override the page numbers.

allan

Yes, you could try using linq. You need to be a little careful with it since you don't want to pull all of the data out of the database, and then discard the majority.

With 40k rows I'd suggest you use client-side processing first. If you find the performance too slow, then spend the time looking into how to implement server-side processing.

Allan