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 query 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:

  4. SQL Server Conditional Order By
  5. SQL Server Transaction inside stored procedure
  6. How to solve - Invalid length parameter passed to the LEFT or SUBSTRING function
  7. java.sql.SQLException: Invalid SQL statement or JDBC escape, terminating ''' not found
  8. How to get the physical_name of the secondary log shipping databases?
  9. Linq Performance with predicates
  10. 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;
    
  11. 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?

  • how to read varbinary max sql server
  • Switching Web Servers stops ASP.Net Membership validating user/password
  • Adding a SQL Filetable folder as a Virtual Directory
  • How can I use one Windows account for ASP.Net impersonation, and a different windows account for a Sql Server trusted connection in the same app?
  • SQL Sever getting total count from view
  • Nested Gridview with checkboxes errors
  • 3 Solutions collect form web for “Paginate rows from SQL”

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.