asp.net 中的分页

发布于 2024-07-30 00:44:34 字数 151 浏览 4 评论 0原文

您好,我有一个包含 100000 行数据的表。 现在我想以页面大小 50 的用户形式呈现我的数据。

呈现它的最佳方法是什么。 我应该选择数据列表吗? 或者我可以实现自己的选择查询,以便每次按下“下一步”按钮时获取 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.

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

生生不灭 2024-08-06 00:44:38

“ListView”和“DataPager”怎么样?

what about "ListView" and "DataPager" ?

他是夢罘是命 2024-08-06 00:44:38

我会使用 pagedDataSource 然后你可以绑定到转发器、数据列表或其他东西。

这里有一个示例

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

there is and example here.

零崎曲识 2024-08-06 00:44:37

我为 asp.net 创建了一个分页控件。 这是一个基本的控制,但它可能会对您有所帮助。 基本寻呼机控件

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

凉城凉梦凉人心 2024-08-06 00:44:37

对于100000条来说,从数据库中获取所有记录到数据集中然后对它们进行分页将是非常耗时的。 相反,我会在数据库存储过程/查询中实现分页。 这样前端代码一次只会检索 50 条记录,用户响应会更快。

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.

爱她像谁 2024-08-06 00:44:36

如果您打开 AllowPaging 并将 PageSize 设置为 50,则可以使用 GridView 轻松完成此操作。但是这将是可怕的 效率低下 - 每次您移动到新页面时,它都会读取所有 1 000 000 行,找出需要显示的 50 行,然后丢弃其余的。

相反,您需要的是数据库中的一个存储过程,它获取您想要显示的页码,计算出该页面上的行集并将它们返回到 ASP.NET 页面。 如果您使用的是 SQL Server 2005 或更高版本,则最好的选择是使用公共表表达式,因此您的存储过程将如下所示(这是针对 Northwind 数据库):

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。 您可能会发现使用一种使用页码调用存储过程的方法更容易,然后您可以添加上一个、下一个、第一个、最后一个、+10、-10 按钮 - 无论您想要什么,只需计算出页码并通过它的方法。

 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

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

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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文