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

时间：2005-05-20 来源：bandt

利用存储过程只提取当前页的页码，资源占用很小。有记录编辑和删除功能。

<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>

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