The proper way to implement paging in SqlDataReader !

Question

I Am correctly using my own way to achieve this, but I don't know if it is efficient or not , so this is the function :

public SqlDataReader GetArticlesByPage(int pageNum, int pageSize)
{
    if (pageNum == 0)
        pageNum = 1;
    SqlDataReader dr = SqlHelper.ExecuteReader(string.Format("SELECT TOP {0} Des, Id, Title, Icon FROM Threads ORDER BY Id DESC", pageSize * pageNum));
    int div = pageNum - 1;
    div = pageSize * div;
    for (int i = 0; i < div; i++)
        dr.Read();
    return dr;
}

It works fine but as you see the code, when I need to take the articles of page 10 when the page size e.g 10 per page I select the top 10*10 result then skip the unwanted results by using FOR statement .
Any suggestions , thanks in advance .

Answer 1

You can do all the paging at sql server.

For example, see

http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx

If you don't want to do it this way and insist on using TOP, then skipping the rows at start is pretty all you can do and it's ok.

(from above link)

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;