<%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <Script Runat="Server"> Dim conNorthwind As SqlConnection Dim strSql As String Dim strSelect As String Dim intStartIndex As Integer Dim intEndIndex As Integer Dim intRecordCount As Integer Dim cmdSql As SqlCommand Sub Page_Load btnFirst.Text = "首页" btnPrev.Text = "上一页" btnNext.Text = "下一页" btnLast.Text = "末页" conNorthwind = New SqlConnection( "Server=192.168.4.1;UID=sa;PWD=111111;Database=yourDBname" ) If Not IsPostBack Then BindDataGrid End If End Sub Sub BindDataGrid Dim cmdSelect As SqlCommand Dim dtrRecordCount As SqlDataReader intEndIndex = dgrdProducts.PageSize cmdSelect = New SqlCommand( "newsPaged", conNorthwind ) cmdSelect.CommandType = CommandType.StoredProcedure cmdSelect.Parameters.Add( "@PageIndex", intStartIndex ) cmdSelect.Parameters.Add( "@PageSize ", intEndIndex ) conNorthwind.Open() dtrRecordCount = cmdSelect.ExecuteReader() While dtrRecordCount.read() intRecordCount=dtrRecordCount(0) End While dgrdProducts.VirtualItemCount = (intRecordCount / dgrdProducts.PageSize) dtrRecordCount.NextResult() dgrdProducts.DataSource = dtrRecordCount dgrdProducts.DataBind() conNorthwind.Close() 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 Sub dgrdProducts_EditCommand( s As Object, e As DataGridCommandEventArgs ) dgrdProducts.EditItemIndex = e.Item.ItemIndex intStartIndex = dgrdProducts.CurrentPageIndex BindDataGrid End Sub Sub dgrdProducts_UpdateCommand( s As Object, e As DataGridCommandEventArgs ) Dim intArticleID As Integer Dim txtTopic As TextBox Dim txtEditor As TextBox Dim strTopic As String Dim strEditor As String intArticleID = dgrdProducts.DataKeys( e.Item.ItemIndex ) txtTopic = e.Item.Cells( 1 ).Controls( 0 ) txtEditor = e.Item.Cells( 2 ).Controls( 0 ) strTopic = txtTopic.Text strEditor = txtEditor.Text strSql = "Update Tb_Article Set Topic=@Topic, " _ & "Editor=@Editor Where ArticleID=@ArticleID" cmdSql = New SqlCommand( strSql, conNorthwind ) cmdSql.Parameters.Add( "@Topic", strTopic ) cmdSql.Parameters.Add( "@Editor", strEditor ) cmdSql.Parameters.Add( "@ArticleID", intArticleID ) conNorthwind.Open() cmdSql.ExecuteNonQuery() conNorthwind.Close() dgrdProducts.EditItemIndex = -1 BindDataGrid End Sub Sub dgrdProducts_CancelCommand( s As Object, e As DataGridCommandEventArgs ) dgrdProducts.EditItemIndex = -1 BindDataGrid End Sub </Script> <html> <head><title>DataGridCustomPaging.aspx</title></head> <body> <form Runat="Server"> <asp:DataGrid Runat="Server" ID="dgrdProducts" OnEditCommand="dgrdProducts_EditCommand" OnUpdateCommand="dgrdProducts_UpdateCommand" OnCancelCommand="dgrdProducts_CancelCommand" DataKeyField="A_ArticleID" AutoGenerateColumns="false" showheader="true" AllowPaging="True" AllowCustomPaging="True" HeaderStyle-BackColor="Salmon" PageSize="10" OnPageIndexChanged="dgrdProducts_PageIndexChanged" PagerStyle-Mode="NumericPages" AlternatingItemStyle-BackColor="#eeaaee" Font-Size="10pt" Font-Name="Verdana" CellSpacing="0" CellPadding="3" GridLines="Both" BorderWidth="1" BorderColor="black" PagerStyle-HorizontalAlign="Right"> <AlternatingItemStyle BackColor="#EEEEEE"></AlternatingItemStyle> <Columns> <asp:BoundColumn HeaderText="序列号" DataField="ArticleID" ReadOnly="True" /> <asp:BoundColumn HeaderText="标题" DataField="Topic" /> <asp:BoundColumn HeaderText="编辑者" DataField="Editor" /> <asp:EditCommandColumn EditText="Edit!" UpdateText="Update!" CancelText="Cancel!" /> <asp:HyperLinkColumn HeaderText="编辑" DataNavigateUrlField="ArticleID" DataNavigateUrlFormatString="Details.aspx?id={0}" Text="编辑"/> </columns> </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 newsPaged ( @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, ArticleID int, ) -- Insert into the temp table INSERT INTO #PageIndex (ArticleID) SELECT ArticleID FROM tablename ORDER BY ArticleID DESC -- Return total count SELECT COUNT(ArticleID) FROM tablename -- Return paged results SELECT O.ArticleID,O.Topic,Editor FROM tablename O, #PageIndex PageIndex WHERE O.ArticleID = PageIndex.ArticleID AND PageIndex.IndexID > @PageLowerBound AND PageIndex.IndexID < @PageUpperBound ORDER BY PageIndex.IndexID END GO

|