How to bind 15 (desired no. of) rows from SQL Server on every next page index change?

Question

How to bind 15 rows from SQL Server into a gridview with a condition and condition is that on every page index should be bind next 15 rows, means a query should be fired for retrieving next 15 rows (I don't want to retrieve all rows from the table in one time).

How can I implement any query or procedure to do it? Please help me

Answer 1

You need to use pagination with sql server

Declare @From int=1
Declare @To int=15

WITH CTETable AS
(
    SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
)

SELECT  * 
FROM CTETable
WHERE RowNum BETWEEN @From AND @To

You need to pass @From and @To vales as 1,15...16,30...etc from front end to fetch subsequent records

Answer 2

Use CTE and OFFSET:

Declare @RecordIndex INT=1 ---Start Row No
Declare @PageSize INT=15 ----No of Rows to fetch

;WITH CTE_Results
AS (
SELECT 
    ROW_NUMBER() OVER (ORDER BY Id) AS ROWNUM,
    Count(*) over () AS TotalCount,
    *
    FROM TableName  
)      
Select * from CTE_Results 
ORDER BY ROWNUM
OFFSET (@RecordIndex) ROWS
FETCH NEXT @PageSize ROWS ONLY;

You nay find more info from here: How can we do pagination in fetching the value with 100 records each in sql