Paginate rows from SQL

I am trying to finish writting my news/blog ASP.net C# application and I am getting stuck on how to return only 10 items at a time after filtering items based on tag or keyword. This is an example of what I am looking for:

1. User queries sql by browsing to ?tag=Pizza
2. Backend finds all tags that equal the q开发者_如何学运维uery string (sent over as a @ parameter). This part I have figured out.

3. Now I want it to just return 10 rows at a time from this filtered statement. I was easily able to do this with this when I wasn't filtering anything out:

   sqlCommand.CommandText = "SELECT * FROM (SELECT row_number() over(ORDER BY news_id) AS row_number, * FROM news) T WHERE row_number BETWEEN (SELECT rowcnt FROM sys.sysindexes WHERE (id = OBJECT_ID('news')) AND (indid < 2))-(@start+10) AND (SELECT rowcnt FROM sys.sysindexes  WHERE (id = OBJECT_ID('news')) AND (indid < 2))-(@start) ORDER BY news_id DESC";
   
   sqlCommand.Parameters.Add("@start", SqlDbType.Int).Value = start;
   

4. I then display at the bottom of the page the user the option to view more. (This part is easy as I just need to add 10 to a query string of ?num=).

What is the best way to go about this goal?

Understanding how row_number numbers the results is a little tricky at first.

It looks like two things are making this more complicated than it could be. The row_number function must be in the same SELECT or an outer SELECT than the filtering is happening, otherwise you are numbering the whole table, rather than numbering the results. Your query numbered all the results in the table before the rows were limited because row_number was used in a SELECT that was inside the SELECT with the WHERE.

Second, it looks like you are hitting sys.indexes to get the number of rows (which I am not sure is always accurate) to the the row number range in reverse when you can just add a DESC to the row_number function.

SELECT
    *
FROM
    (
        SELECT
            row_number() over(ORDER BY news_id DESC) AS row_number,
            *
        FROM
            news
        WHERE
            --user filter goes here
            news_cat = 'Pizza'
    ) AS T
WHERE
    row_number BETWEEN @start AND @start + 10

See the following questions on SQL Server paging queries:

• Paging SQL Server 2005 results
• How to do pagination in SQL Server 2008
• How to return a page of results from SQL?
• Pagination in SQL Server
• What is the best way to paginate results in MS SQLServer
• How can I do server side pagination of results in SQL Server 2000?

And so on.

I wrote this article on codeproject 2-3 years ago when paging support is still fairly limited in the framework, perhaps still a good reference.

If you only concern about the data, just check out the section on how I retrieve paged data.

http://www.codeproject.com/KB/aspnet/GridViewNeatPaging.aspx