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
If by any chance you are using Sql Server 2012 onwards, you can use
Fetch and Offset
-- 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
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