Paging design recommendation for asp.net and sqlserver 2005

Question

I am relatively new to programming. My work basically revolves around data and analysis. I want to create a simple asp.net page which shows huge chunk of data from the database. There could be a millions of rows of data which is used for different kinds of analysis/searchin/filtering etc..

Should I write paging logic at the front end or at the back-end (in this case SQL Server 2005)?

What would be the best practice around this? Your suggestions/links to resources in this direction is greatly appreciated.

Answer 1

You may be interested in this... Paging of Large resultset in asp.net

Answer 2

please use this example Building Custom Paging with LINQ, ListView, DataPager and ObjectDataSource

Paging of Large Resultsets in ASP.NET ListView and DataPager

Custom paging in ASP.NET with ListView & DataPager

Implementing Custom Paging in ASP.NET with SQL Server 2005

Answer 3

I would suggest you create a stored procedure to query and page your data. Linq To SQL is a fast an easy way to execute the stp.

Simple example of stored procedure to take care of paging:

CREATE PROCEDURE [dbo].[stp_PagingSample]
(
    @page int,
    @pagesize int
)
AS

WITH Numbered AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'
    FROM tbl_YourTable
) 
SELECT * 
FROM Numbered
WHERE RowNumber BETWEEN ((@page - 1) * @pagesize) + 1 AND (@page * @pagesize);

The stored procedure is the tricky part. But drop a comment if you would like me to add more sample code executing the stp and rendering the data... :)