ASPNET：DataGrid+存储过程的分页编辑代码[原创]

<%@ 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=XXXXXX;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
  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_DeleteCommand( s As Object, e As DataGridCommandEventArgs )
  Dim strDelete As String
  Dim strArticleID As String
  Dim intCurrentpage As Integer

  strArticleID = dgrdProducts.DataKeys( e.Item.ItemIndex )
  strDelete = "Delete Tb_Article Where ArticleID=@articleID"
  cmdSql = New SqlCommand( strDelete, conNorthwind )
  cmdSql.Parameters.Add( "@articleID", strArticleID )
  Try
  conNorthwind.Open()
  cmdSql.ExecuteNonQuery()
  conNorthwind.Close()
  Catch objException As Exception
    Response.Write( "We're sorry, we are experiencing technical problems..." )
    Response.Write( "<hr>" )
    Response.Write( "<li> Message: " & objException.Message )
    Response.Write( "<li> Source: " & objException.Source )
    Response.Write( "<li> Stack Trace: " & objException.StackTrace )
    Response.Write( "<li> Target Site: " & objException.TargetSite.Name )
  End Try
  If dgrdProducts.CurrentPageIndex=dgrdProducts.PageCount-1 then
    If dgrdProducts.CurrentPageIndex=0 then
      dgrdProducts.CurrentPageIndex = dgrdProducts.PageCount -1
    Else
      If (dgrdProducts.Items.Count MOD dgrdProducts.PageSize) = 1 then
     intCurrentpage=2
   Else
     intCurrentpage=1
   End If
      dgrdProducts.CurrentPageIndex = dgrdProducts.PageCount - intCurrentPage
    End If
  End If
  intStartIndex = dgrdProducts.CurrentPageIndex
  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"
  OnDeleteCommand="dgrdProducts_DeleteCommand"
  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:ButtonColumn
   HeaderText="删除"
      ButtonType="LinkButton"
   Text="Delete!"
   CommandName="Delete" />
 <asp:HyperLinkColumn
   HeaderText="编辑"
   DataNavigateUrlField="ArticleID"
   DataNavigateUrlFormatString="Details.aspx?id="
   Text="编辑"/>
  </columns>
</asp:datagrid>
<asp:linkbutton id="btnFirst" οnclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="0"></asp:linkbutton>&nbsp;
<asp:linkbutton id="btnPrev" οnclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="prev"></asp:linkbutton>&nbsp;
<asp:linkbutton id="btnNext" οnclick="PagerButtonClick" runat="server" Font-Name="verdana" Font-size="8pt" ForeColor="navy" CommandArgument="next"></asp:linkbutton>&nbsp;
<asp:linkbutton id="btnLast" οnclick="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