1

I am new to C# and SQL Server. I have created a SQL Server database and a C# application. The C# application works well and retrieves all records when I use this statement:

SELEC * FROM tableName

and wWhen I use this statement

SELEC TOP 10 * FROM tableName

it displays the top 10 results only.

Now I want to display the next 10 results, when I click on a Next button. I have tried it and searched online, but didn't find a solution. Please let me know how can I do this. I need the exact SQL query which can retrieve the results from row M upto row N.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Muhammad Sohail
  • 191
  • 2
  • 2
  • 6

3 Answers3

1

@Muhammad Suhail you should use and search for paging. Just create a store procedure, when click on next button pass a pagenumber and pageSize to an sql store procedure. Following example is the best way to create paging in sql server store proedure.

Only you should pass parameter @pageSize and @pageNumber
This script is use NorthWind db
Just change the @pageNumber and see your pages

DECLARE  @pageNumber int=4
DECLARE  @pageSize int=3  

  SELECT EmployeeID,LastName,FirstName  
  FROM(
        SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) AS Row,
        EmployeeID,
        LastName,
        FirstName
        FROM dbo.Employees
    ) AS EmployeRecords
  WHERE
  Row BETWEEN (@pageSize*(@pageNumber-1)+1) AND @pageNumber * @pageSize

when @pageSize=3 and @pageNumber=1

enter image description here

when @pageSize=3 and @pageNumber=2

enter image description here

And so on.....................

Community
  • 1
  • 1
Siddique Mahsud
  • 1,453
  • 11
  • 21
  • thanks for your detailed answer. I am using sql 2012, and there is a new feature in it, that is OFFSET...FETCH, that I have found a few minutes ago. I think that is better than this long process? What do you say about this? – Muhammad Sohail Feb 08 '14 at 12:24
  • I am not still use sql 2012, if u found some good way thats best.. thanks. But above is the best way for paging if there no new feature... like you mention OFFSET FETCH etc. thanks for sharing i will study these features.. – Siddique Mahsud Feb 08 '14 at 12:33
0

Inner query adds RowIndex which is automatic number per row where the rows are ordered by columnToOrderBy, the outer query just uses that new row to filter out the rows you don't want on the page by passing the start and end row values to the BETWEEN clause: i.e. the number of the first row and the number of the last one (inclusive)

select * from (
    select  ROW_NUMBER() OVER(order by columnToOrderBy) as RowIndex
          , * 
    from    TableInQuestion) t
where RowIndex Between 10 and 19
jwwishart
  • 2,865
  • 2
  • 23
  • 26
0

Check out this discussion, it has all the info you need:

How to return a page of results from SQL?

Another option would be to start using LINQ. Then you can use something like this:

Where(query your data).Skip(x).Take(y);

This approach is also discussed in the above mentioned thread.

Community
  • 1
  • 1
Tys
  • 3,592
  • 9
  • 49
  • 71