Fetch records for every selected page - Datatables

Question

We have ASP.net C# MVC portal and we are using Datatables in it to display tables records.

The table records are around 45K.

So whats happening is, datatables are fetching entire 45k records and paging them for every reload. This is taking around 10 mins to display the table records.

So what I want is to do something that, data should be fetch page wise and not on every reload. If i select 1 then display 10 records, if i select 2 then display next 10 records.

I searched on google, and added "serverSide": true in my code. Still its not working, whereas now i am getting some error.

What else is needed to do so?

$('.table').DataTable({
    "paging": true,
   "lengthChange": true,
   "searching": false,
   "ordering": false,
   "autoWidth": true,
   "stateSave": true,
   "serverSide": true

});

That will depend on what technology you are using to retrieve your data. Are you using entity framework, sqlcommands, linq to sql? Each one has a different method but will involve skipping x (page size x page number) records and then selecting the top y (page size) records. — Kell, Oct 06 '17 at 09:14
Possible duplicate of [using jquery datatable for server side processing with paging, filtering and search](https://stackoverflow.com/questions/3193930/using-jquery-datatable-for-server-side-processing-with-paging-filtering-and-sea) — markpsmith, Oct 06 '17 at 09:17
this is a duplicate, though I appreciate you may not be familiar with skip & take: https://stackoverflow.com/questions/17047192/entityframework-do-paging-on-a-query-with-a-join — Kell, Oct 06 '17 at 09:29
@Kell so the thing I am referencing to... is it called Skip and take? — user3531660, Oct 06 '17 at 10:01

score 0 · Answer 1 · answered Oct 06 '17 at 11:01

You have to call database statement for each page button is clicked. In sql server 2012 and above Fetch and OFFSET are two keyword supported for retrieving particular record from the table.You can check the example in the provided link.

http://www.c-sharpcorner.com/UploadFile/rohatash/offset-and-fetch-next-keywords-in-sql-server-2012/

Fetch records for every selected page - Datatables

1 Answers1