Paging in asp.net

Question

Hi I've a table with 100000 rows of data. Now i want to present my data in user form with page size 50.

What is the best approach to present it. Shall i preffer datalist? or can i implement my own select query for getting 50 records each time when i press next button?

Thanks in advance

Answer 1

For 100000, it will be very time consuming to get all the records from the database into the dataset and then page them. Instead I would go with implementing paging in the database stored procedure/ query. That way only 50 records would be retrieved in the front end code at a time and user response would be faster.

Answer 2

hi what about "ListView" and "DataPager" ?

Answer 3

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

Answer 4

I would use a pagedDataSource then you can bind to a repeater, datalist or whatever.

there is and example here.

Answer 5

It may now be outdated, but I used to use the method described here to implement pagination.

Answer 6

You could do this quite easily with a GridView if you switch on AllowPaging and set the PageSize to 50. But it will be horribly inefficient - every time you move to a new page it'll read all 1 000 000 rows, work out which 50 it needs to display, and throw the rest away.

What you want instead is a stored proc in your database that takes the page number that you want to display, works out the set of rows on that page and returns them to the ASP.NET page. If you're using SQL Server 2005/2008 your best bet is to use a Common Table Expression, so your stored proc will look something like this (this is for the Northwind db):

CREATE PROC [dbo].[PagedOrderList]
@PageNumber INTEGER
AS
SET NOCOUNT ON

DECLARE @lowerRecordNumber INTEGER  
DECLARE @upperRecordNumber INTEGER

-- Work out the record numbers that bound the page
SET @lowerRecordNumber = ((@pageNumber - 1) * 50) + 1
SET @upperRecordNumber = (@pageNumber * 50);

-- Create a CTE with all the records numbered
WITH OrdersCTE ([RowNumber],[OrderId],[OrderDate],[RequiredDate],[ShippedDate],  
[CompanyName],[Value])
AS
(
    SELECT
 ROW_NUMBER() OVER(ORDER BY o.[OrderId]),
 o.OrderID,
 o.OrderDate,
 o.RequiredDate,
 o.ShippedDate,
 c.CompanyName,
 SUM(od.Quantity * od.UnitPrice) AS [Value]
 FROM
 Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
 INNER JOIN Customers c ON o.CustomerID = c.CustomerID
 GROUP BY o.OrderID, o.OrderDate, o.RequiredDate, o.ShippedDate, c.CompanyName
)
-- Select the rows from the CTE that fall between the bounds we worked out
SELECT * 
FROM OrdersCTE
WHERE [RowNumber] BETWEEN @lowerRecordNumber AND @upperRecordNumber

Now, back to your page. You'll need to put in a DataGrid - they have better support for custom paging than anything else - and set AllowCustomPaging to True. You might find it easier to have one method that calls your stored proc with the page number, then you can add Previous, Next, First, Last, +10, -10 buttons - whatever you want, just work out the page number and pass it to the method.

 Private Sub loadData(ByVal pageNumber As Integer)

    Dim orderDataTable As DataTable
    'This uses the Microsoft Enterprise Library for data access
    Dim DAL As Database
    Dim cmd As DbCommand

    DAL = DatabaseFactory.CreateDatabase("Northwind")

    cmd = DAL.GetStoredProcCommand("PagedOrderList")

    'Pass the page number to the stored proc
    DAL.AddInParameter(cmd, "@pageNumber", DbType.Int32, pageNumber)

    'Get a DataTable back with the 50 rows we want
    orderDataTable = DAL.ExecuteDataSet(cmd).Tables(0)

    'Bind the data to the grid
    With OrderDataGrid
        .DataSource = orderDataTable
        .DataBind()
        'Set the page number so we know where we are in the dataset
        .CurrentPageIndex = pageNumber
    End With

End Sub


Private Sub PreviousButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles PreviousButton.Click

    If OrderDataGrid.CurrentPageIndex = 0 Then
        'Make sure we don't try to load a negative page number
    Else
        'Work out the previous page number and load the data for it
        Call loadData(OrderDataGrid.CurrentPageIndex - 1)
    End If

End Sub

Private Sub NextButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NextButton.Click

    'Work out the nextpage number and load the data for it
    Call loadData(OrderDataGrid.CurrentPageIndex + 1)

End Sub

Answer 7

I've created a paging control for asp.net. It's a basic control but it may help you. Basic Pager Control