The proper way to implement paging in SqlDataReader !

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 .

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;

If you are using SQL Server 2008 then you can use the built in Common table Expressions and ROW_NUMBER() function to achieve this very easily and efficiently.

You will write your query as

WITH MAINSQL AS(

SELECT Des, Id, Title, Icon,
rownum = ROW_NUMBER() OVER (Order by Id desc)
 FROM Threads 
)
SELECT * FROM MAINSQL WHERE rownum between 10 AND 100

Replace 10 and 100 by the starting and ending row of your page

For more information see

http://msdn.microsoft.com/en-us/library/ms186734.aspx

And

http://msdn.microsoft.com/en-us/library/ms190766.aspx

Hope this helps