分页在asp.net [英] Paging in asp.net

问题描述

您好我已经与100000行数据的表。现在我想present我的用户表单数据和页面大小50。

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

什么是present它最好的办法。应我preffer DataList控件？或者我可以实现我自己的选择查询，获取各50时，我preSS下一步按钮一次记录？

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?

在此先感谢

推荐答案

如果您打开 AllowPaging 你可以用一个GridView做到这一点很容易，并设置每页 50。但是这将是的可怕的低效 - 每次移动到一个新的页面时，它会读取所有1 000 000行，找出其中50需要显示，扔剩下的路程。

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.

您想要什么，而不是在你的数据库中的存储过程是需要要显示页码，工程出了一套页的行并将其返回到ASP.NET页面。如果您使用的SQL Server 2005或更高版本你最好的选择是使用公用表前pression，所以你的存储过程会是这个样子（这是为Nort​​hwind数据库）：

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 or later 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 

现在，回到你的页面。你需要把在DataGrid中 - 他们有自定义分页比什么都好支​​持 - 并设置 AllowCustomPaging 为True。您可能会发现更容易有与页码调用你的存储过程的一种方法，那么你可以添加previous，接下来，首先，最后，+ 10，-10按钮 - 任何你想要的，只要制定出页面号并将其传递给该方法。

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

这篇关于分页在asp.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！