Exceptions to page size when paging hierarchical data

Question

I have a webpage that displays a very large list of data. Since this was bogging down the browser, I implemented paging (using a PagedDataSource) to display 20 Marbles at a time.

My data model is such that a Bag contains multiple Marbles, and on my repeater I show a little header for the Bag information then all of the Marbles underneath it.

Oversimplified example:

Bag1            some Bag specific data
--------------------------------------
MarbleA            328 Some St. USA
MarbleB            364 another ave. USA
MarbleC            7878 Whatever Way USA

Bag2            some Bag specific data
--------------------------------------
MarbleD            684 Dummy Dr. USA

etc.

The problem is, since my page size is 20, I can cut off a Bag's Marbles on the end of a page. (Imagine MarbleB was the 20th element.) This causes the remaining Marbles to spill over to the top of the next page.

Is there any elegant way to check for this, or am I going to have to implement my own paging and add a "look ahead until the next bag" logic?

Edit: assume c# 2.0, VS2008, SQL 2005

Answer 1

You could handle some of it with your SQL query (assuming SQL Server 2005). If you introduce RANK as a result you could make a determination of how many rows each result takes up. Here's a sample query that could be within a sproc:

SELECT RANK() OVER (ORDER BY b.BagID) as RowNum, b.BagID, b.BagInfo, m.MarbleInfo
FROM Bag b
JOIN Marble m ON m.BagID = b.BagID
WHERE b.BagID > @BagID

@BagID would be the "starting" BagID (initially -1), you could call it with the previously ending BagID if you wanted.

The results would look something like this:

1,1,1
1,1,2
1,1,3
4,2,4
4,2,5
6,3,6
...

Within your pagination you could do a check on the RowNum column to see if it's within the limit of the page size. Of course, the draw back would be if you had a bag with a large amount of marbles within it, etc.

EDIT: If RANK functionality is not available to you within your RDBMS, a similar result could be accomplished within a sproc using a temp table and a cursor.