DataGrid和存储过程结合的分页，只读取当前页数据
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<Script Runat="Server">
Dim conNorthwind As SqlConnection
Dim strSelect As String
Dim intStartIndex As Integer
Dim intEndIndex As Integer
Sub Page_Load
Dim cmdSelect As SqlCommand
btnFirst.Text = "首页"
btnPrev.Text = "上一页"
btnNext.Text = "下一页"
btnLast.Text = "末页"
conNorthwind = New SqlConnection( "Server=192.168.4.1;UID=sa;PWD=XXXXXX;Database=NorthWind" )
If Not IsPostBack Then
' Get Total Pages
strSelect = "SELECT COUNT(OrderID) FROM Orders"
cmdSelect = New SqlCommand( strSelect, conNorthwind )
conNorthwind.Open()
dgrdProducts.VirtualItemCount = (cmdSelect.ExecuteScalar() / dgrdProducts.PageSize)
conNorthwind.Close()
BindDataGrid
End If
End Sub
Sub BindDataGrid
Dim dadProducts As SqlDataAdapter
Dim dstProducts As DataSet
intEndIndex = dgrdProducts.PageSize
dadProducts = New SqlDataAdapter( "OrdersPaged", conNorthwind )
dadProducts.SelectCommand.CommandType = CommandType.StoredProcedure
dadProducts.SelectCommand.Parameters.Add( "@PageIndex", intStartIndex )
dadProducts.SelectCommand.Parameters.Add( "@PageSize ", intEndIndex )
dstProducts = New DataSet
dadProducts.Fill( dstProducts )
dgrdProducts.DataSource = dstProducts
dgrdProducts.DataBind()
End Sub
Sub dgrdProducts_PageIndexChanged( s As Object, e As DataGridPageChangedEventArgs )
intStartIndex = e.NewPageIndex
dgrdProducts.CurrentPageIndex = e.NewPageIndex
BindDataGrid
End Sub
Sub PagerButtonClick(ByVal sender As Object, ByVal e As EventArgs)
Dim arg As String = sender.CommandArgument
Select Case arg
Case "next"
If (dgrdProducts.CurrentPageIndex < (dgrdProducts.PageCount - 1)) Then
dgrdProducts.CurrentPageIndex += 1
End If
Case "prev"
If (dgrdProducts.CurrentPageIndex > 0) Then
dgrdProducts.CurrentPageIndex -= 1
End If
Case "last"
dgrdProducts.CurrentPageIndex = (dgrdProducts.PageCount - 1)
Case Else
'page number
dgrdProducts.CurrentPageIndex = System.Convert.ToInt32(arg)
End Select
intStartIndex=dgrdProducts.CurrentPageIndex
BindDataGrid
End Sub
</Script>
<html>
<head><title>Paging.aspx</title></head>
<body>
<form Runat="Server">
<asp:DataGrid Runat="Server"
ID="dgrdProducts"
showheader="false"
AllowPaging="True"
AllowCustomPaging="True"
PageSize="10"
OnPageIndexChanged="dgrdProducts_PageIndexChanged"
PagerStyle-Mode="NumericPages"
AlternatingItemStyle-BackColor="#eeaaee"
HeaderStyle-BackColor="#aaFFdd"
Font-Size="10pt"
Font-Name="Verdana"
CellSpacing="0"
CellPadding="3"
GridLines="Both"
BorderWidth="1"
BorderColor="black"
PagerStyle-HorizontalAlign="Right">
<AlternatingItemStyle BackColor="#EEEEEE"></AlternatingItemStyle>
</asp:datagrid>
<asp:linkbutton id="btnFirst" onclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="0"></asp:linkbutton>
<asp:linkbutton id="btnPrev" onclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="prev"></asp:linkbutton>
<asp:linkbutton id="btnNext" onclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="next"></asp:linkbutton>
<asp:linkbutton id="btnLast" onclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="last"></asp:linkbutton>
</form>
</html>
下面是存储过程：
CREATE PROCEDURE OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
-- Return total count
--SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
参考资料：
《编写高性能 Web 应用程序的 10 个技巧》
《ASP.NET揭秘》