Custom paging using Data Column with gap in Records

ado.net c# dapper pagination sql-server

Question

For implementing a custom paging solution using C#, ADO.Net, with SQL Server as database, let us assume following details:

Total Records = 1000
Page Size = 100

This would mean there are 10 pages, I use a query as follows:

Min and Max Id (Primary Key) are fetched using scalar Queries:

Select Min(Id), Max(Id) from Table;

Select * from Table where Id >= (Min Record) and Id < (Max Record)

Here for every transaction, Min and Max Record Value is incremented by Page Size, till the point Max Record reach or exceed the Actual Max value.

This solution works fine for an auto increment / identity column, which have continuous values, but let's assume the column used has gap in values, though they can still be ordered, like 1000 values are between Min = 1 and Max = 3000.

  • I have following possible work around, one suggested in following SO link, where an extra RowId column is used to achieve the same.

  • Other options would be opening a Reader and read a record for given Page, create a List and thus fetch a Page

What I ideally want is executing a Top <PageSize> sql query, where it is executed from a pre-defined row or value that I suggest, instead of beginning, thus I would avoid creating an extra column, I can work with disconnected architecture, no Reader is required.

Any suggestion or pointer, please let me know if a clarification is required, for any detail in the question

Accepted Answer

If by any chance you are using Sql Server 2012 onwards, you can use

Fetch and Offset

e.g.

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 20000
--Set the value of rows to fetch
 Set @RowFetch = 50000

Select *
From dbo.tblSample 
Order by (Select 1)  
Offset @RowSkip Row 
Fetch  Next @RowFetch Rows Only;

Please refer : Usage 1: Server Side Paging for more details


Popular Answer

You can use the ROW_NUMBER function in a common table expression(CTE):

WITH CTE AS
(
   SELECT 
    t.*, 
    RN = ROW_NUMBER() OVER (ORDER BY t.ID)
   FROM dbo.TableName t
)
SELECT ID, Col2, Col3, ...
FROM CTE 
WHERE RN >= (@pageIndedx * @pageSize) AND RN <= (@pageIndedx * @pageSize) + @pageSize


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why