pagination w/ Dapper (Tabulator)

[jay8anks]

I've gotten a WebApi to work as the backend for Tabulator. The backend is C# with a repository pattern, using Dapper. I've got the progressive loading working, but I'm kind of unsure how to fully get the page count to work. I have some ideas, but I'm not sure if they would actually be the best way to implement things.

Basically, here is what Tabulator is expecting back:

{
"last_page":15, //the total number of available pages (this value must be greater than 0)
"data":[ // an array of row data objects
{id:1, name:"bob", age:"23"}, //example row data object
]
}

Getting the "data" part of this back was relatively easy. Right now, I just hard coded a value in for "last_page". Using progressive loading, that "last_page" isn't really used, but I should know how to do this if I want to add real pagination to the table.

It would be much appreciated if someone could point me in the right direction for getting that "last_page" value and returning it together with the "data" object.

One thing I have thought about is a separate url that returns just the page count, and then sending this back from the front end when getting the data. This seems like it would work but doesn't exactly seem like the optimal way to go.

Right now, this is my controller, and you can see I just manually created a page count:

[HttpGet]

        public async Task<IActionResult> GetCompanies([FromQuery]int page, int size)

        {

            Pager pager = new Pager(page,size);

            string strUrl = Microsoft.AspNetCore.Http.Extensions.UriHelper.GetDisplayUrl(Request);
            string strUrl0 = Microsoft.AspNetCore.Http.Extensions.UriHelper.GetEncodedUrl(Request);

            try
            {

                var companies = await _companyRepo.GetCompanies(pager);             

                var result = new
                {
                    last_page = "1000",
                    data = companies
                };

                return Ok(result);

                // return Ok(companies);
            }

            catch (Exception ex)
            {

                //log error
                return StatusCode(500, ex.Message);

            }

        }

Is it good practice to get this page count on every single call to the backend? That's kind of why I was thinking about doing this in two calls. That way I could kind of be selective about when I made it do the math for the page count.

On the other hand, if new data was going in a table and it wasn't being recalculated every time...

Thanks,

Jay

 

[Skydiver]

Isn't the last page simply (the number of companies divided by the page size) plus one? What am I missing?

 

[jay8anks]

Isn't the last page simply (the number of companies divided by the page size) plus one? What am I missing?

With progressive loading, if you look at my controller, it is just sending a small number of rows. On the client side, these rows are being added in the background as the user scrolls down, so a huge table is only loaded on demand.

If my controller is only sending 20 rows at a time, where exactly is it simply getting the number of rows and dividing by the page size in order to include the last_page?

In some way, this has to be two separate database calls. One to get the total number of records and do some math, and one to get the 20 rows of data.

So...is it common practice to get a potentially large row count on every single call? Is there some good way to avoid doing the math on every single call?

Does Dapper have some handy extensions somewhere that handles pagination and the page count?

Honestly, I have a test db going with a small amount of test data in it, so two calls is nothing. But I could see counting a large number of rows in table with a lot of users as not being optimal, especially just to get 10 or 20 rows at a time. I do have some tables in production pushing a couple of million rows.

 

[Skydiver]

I missed that you are using a class called Pager. Where is that implemented? Does it likely knows the total number of items or total number of pages.

 

[Skydiver]

Normally for paging, there is some kind of session id or cookie that is passed around along with the current page number being requested. The id/cookie is then used to look up an entry in a cache. The cache entry will normally contain the total number of pages, and a few pages worth of recent data sent. Some more aggressive implementations will continue to get the data and pre-fill the next few pages in another thread in anticipation of more data being requested.

 

[jay8anks]

I missed that you are using a class called Pager. Where is that implemented? Does it likely knows the total number of items or total number of pages.

No. It's just a class I made to pass the paging variables to the repository/dapper.

 

[jay8anks]

Is it good practice to get this page count on every single call to the backend? That's kind of why I was thinking about doing this in two calls. That way I could kind of be selective about when I made it do the math for the page count.

On the other hand, if new data was going in a table and it wasn't being recalculated every time...

Thanks,

Jay

I have limited experience with Dapper, having mostly used ADO.Net for probably longer than I should have.

I finally got my search terminology right for Dapper and came up with something. You have to get multiple result sets back in Dapper. I did test this yesterday and it does work. Though, setting up my Dapper connection is a little different and it took me a while to get this to work:

[SOLVED] => dapper: Get result and count at same time using...

.net - I wrote two segments of SQL command and want to process in one query like this: SELECT COUNT(*) FROM books

dapper-tutorial.net

var sql = @"SELECT COUNT(*) FROM books
            SELECT * FROM books ORDER BY bookID OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY";

using(var multi = connection.QueryMultiple(sql))
{
    var count = multi.Read<int>().Single();
    var results = multi.Read<YourObject>().ToList();
}

I think I could just put a bool in the controller, and from the front end I could tell it to pass the page count back or to ignore it. That way it would be fairly simple to do the math or not depending on the situation.

My second idea is to put something like a lastPage = "0" in the controller. Then I could get the page count on the front end and send it back to the controller each time. If it == "0", then do the math. If it has a value, just pass that value back to the front end. Doing that, you could do the math on the first trip and then skip it while the client is paging.

My concern with the second option is that if new rows were being added to the table, that page count would start to be off. In my setup, this wouldn't actually be an issue...but I could see it being an issue in some setups.

Like I said, in my case, Tabulator is doing progressive loading, so it never actually shows how many pages there are.

Thanks,

 

[Skydiver]

Good plan about doing the math on the first call.

As for new rows being added, you have more than just the number of pages issue. What if the new rows are going to be in the pages that you've already sent previously?