With SQL query, we can easily
1) select top records using SELECT TOP <number of rows>;
2) bottom records using DESC order with SELECT TOP <number of rows>;
3) last record using SELECT LAST(<columnName>) AS myColumnName FROM myTable. If the LAST function is not supported. use SELECT columnName FROM myTable ORDER BY columnName DESC LIMIT 1 ;
4) middle records using Where ID between <start row number> and <end row number>.
For more advanced paging results or middle records query, see the following example:
Code: int nStart = (Convert.ToInt32(TextBoxPageNumber.Text) - 1) * Convert.ToInt32(TextBoxPageSize.Text) + 1;
int nEnd = nStart + Convert.ToInt32(TextBoxPageSize.Text) - 1;
SqlDataSource1.SelectCommand = "With Cust AS (SELECT Email, FirstName," +
"ROW_NUMBER() OVER (order by Email) as RowNumber FROM Subscriber where Subscribe='True')" +
"select * from Cust Where RowNumber Between " + nStart + " and " + nEnd;
Alternatively you can use a stored procedure written by by
Frank Kerrigan:
Code:CREATE PROC GetCustomersByPage
@PageSize int, @PageNumber int
AS
Declare @RowStart int
Declare @RowEnd int
if @PageNumber > 0
Begin
SET @PageNumber = @PageNumber -1
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end
END
Code:exec GetCustomersByPage 10, 1
