用临时表的GridView分页

 本例子采用sql2000下的Nowthwind数据库中的[Order Details]表

下面是存储过程脚本

 

 1ALTER    PROC OrderDetailsPaging
 2(@PageIndex int,--页码
 3 @PageSize int,--页尺寸
 4 @RowsCount int output)--总行数
 5AS
 6BEGIN
 7set nocount on
 8declare @PageLowerBound int
 9declare @PageUpperBound int
10declare @RowsToReturn int
11
12set @PageLowerBound=@PageIndex*@PageSize+1
13set @PageUpperBound=(@PageIndex+1)*@PageSize
14set @RowsToReturn=@PageUpperBound
15
16set rowcount @RowsToReturn
17--创建带一个自增键的临时表
18create table #PageIndex
19(IndexID int identity(1,1) not null,
20 OrderDetailsID int,
21 ProductID int)
22insert into #PageIndex(OrderDetailsID,ProductID)
23select OrderID,ProductID from [Order Details]
24order by OrderID asc
25
26select @RowsCount=count(OrderID) from [Order Details]
27
28select pageindex.IndexID,od.OrderID,od.ProductID,od.UnitPrice,od.Quantity,od.Discount 
29from [Order Details] od inner join #PageIndex pageindex on od.OrderID=pageindex.OrderDetailsID
30where pageindex.IndexID >= @PageLowerBound and pageindex.IndexID<= @PageUpperBound and 
31pageindex.ProductID=od.ProductID
32
33END
34
35set nocount off
36set rowcount 0
37
38GO

 

以下是页面的隐藏代码

 

  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Web;
  5using System.Web.Security;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Web.UI.WebControls.WebParts;
  9using System.Web.UI.HtmlControls;
 10using System.Data.SqlClient;
 11
 12public partial class _Default : System.Web.UI.Page 
 13{
 14    private const string sql_select_orderDetails = "select OrderID,ProductID,UnitPrice,Quantity,Discount from [Order Details]";
 15    private const string sql_select_Categories = "select CategoryID,CategoryName,Description,Picture from Categories";
 16    // 总记录数
 17    private static int Rows;
 18    // 当前页数
 19    private static int CurrentPageIndex=0;
 20    // 总页数
 21    private static int PageCount = -1;
 22    protected void Page_Load(object sender, EventArgs e)
 23    {
 24        if (!Page.IsPostBack)
 25        {
 26            this.BindData();
 27        }
 28    }
 29    private void BindData()
 30    {
 31        this.GridView1.DataSource = GetOrderDetails(CurrentPageIndex,GridView1.PageSize);
 32        this.GridView1.DataBind();
 33    }
 34    private DataSet GetReportCategories()
 35    {
 36        return DBUtility.SQLAccess.ExecuteDataSet(DBUtility.SQLAccess.ConnectionString, CommandType.StoredProcedure, sql_select_orderDetails, null);
 37    }
 38    private DataSet GetCategoryies()
 39    {
 40        return DBUtility.SQLAccess.ExecuteDataSet(DBUtility.SQLAccess.ConnectionString, CommandType.Text, sql_select_Categories, null);
 41    }
 42    /**//// <summary>
 43    /// 返回OrderDetails表中的数据
 44    /// </summary>
 45    /// <returns></returns>j
 46    private DataSet GetOrderDetails(int pageindex,int pagesize)
 47    {
 48        SqlParameter[] orderDetails_pars = new SqlParameter[3];
 49        orderDetails_pars[0] = new SqlParameter("@PageIndex", SqlDbType.Int);
 50        orderDetails_pars[0].Value = pageindex;
 51        orderDetails_pars[1] = new SqlParameter("@PageSize", SqlDbType.Int);
 52        orderDetails_pars[1].Value = pagesize;
 53        orderDetails_pars[2] = new SqlParameter("@RowsCount", SqlDbType.Int);
 54        orderDetails_pars[2].Direction = ParameterDirection.Output;
 55        DataSet ds = DBUtility.SQLAccess.ExecuteDataSet(DBUtility.SQLAccess.ConnectionString, CommandType.StoredProcedure, "OrderDetailsPaging", orderDetails_pars);
 56        Rows = (int)orderDetails_pars[2].Value;
 57        PageCount = this.GetPageCount(this.GridView1.PageSize, Rows);
 58        return ds;
 59    }
 60    private DataSet GetAllOrderDetails()
 61    {
 62        return DBUtility.SQLAccess.ExecuteDataSet(DBUtility.SQLAccess.ConnectionString, CommandType.Text, sql_select_orderDetails, null);
 63    }
 64    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
 65    {
 66        this.GridView1.PageIndex = e.NewPageIndex;
 67        this.GridView1.DataSource = this.GetOrderDetails(e.NewPageIndex, GridView1.PageSize);
 68        Response.Write(((DataSet)GridView1.DataSource).Tables[0].Rows.Count);
 69        this.GridView1.DataBind();
 70    }
 71    protected void lbtn_First_Click(object sender, EventArgs e)
 72    {
 73        CurrentPageIndex = 0;
 74        this.GridView1.DataSource = GetOrderDetails(CurrentPageIndex, GridView1.PageSize);
 75        this.GridView1.DataBind();
 76    }
 77    protected void lbtn_Pre_Click(object sender, EventArgs e)
 78    {
 79        if ((--CurrentPageIndex) < 0)
 80        {
 81            CurrentPageIndex++;
 82            return;
 83        }
 84        else
 85        {
 86            this.GridView1.DataSource = GetOrderDetails(CurrentPageIndex, GridView1.PageSize);
 87            this.GridView1.DataBind();
 88        }
 89    }
 90    /**//// <summary>
 91    /// 下一页按钮
 92    /// </summary>
 93    /// <param name="sender"></param>
 94    /// <param name="e"></param>
 95    protected void lbtn_Next_Click(object sender, EventArgs e)
 96    {
 97        // 先判断当前页索引
 98        if ((++CurrentPageIndex) > PageCount-1)
 99        {
100            CurrentPageIndex--;
101            return;
102        }
103        else
104        {
105            this.GridView1.DataSource = GetOrderDetails(CurrentPageIndex, GridView1.PageSize);
106            this.GridView1.DataBind();
107        }
108    }
109    protected void lbtn_Last_Click(object sender, EventArgs e)
110    {
111        CurrentPageIndex = PageCount - 1;
112        this.GridView1.DataSource = GetOrderDetails(CurrentPageIndex, GridView1.PageSize);
113        this.GridView1.DataBind();
114    }
115    /**//// <summary>
116    /// 计算页数
117    /// </summary>
118    /// <param name="pagesize"></param>
119    /// <param name="rows"></param>
120    /// <returns></returns>
121    private int GetPageCount(int pagesize, int rows)
122    {
123        return (rows + pagesize - 1) / pagesize;
124    }
125}
126

以下是页面设计代码

 

 1<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
 2
 3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4
 5<html xmlns="http://www.w3.org/1999/xhtml" >
 6<head runat="server">
 7    <title>无标题页</title>
 8</head>
 9<body>
10    <form id="form1" runat="server">
11    <div>
12        &nbsp;
13        <div style="font-size: 12px; z-index: 101; left: 92px; width: 542px; position: absolute;
14            top: 76px; height: 276px">
15        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" BorderStyle="None"
16            OnPageIndexChanging="GridView1_PageIndexChanging" GridLines="Horizontal" HorizontalAlign="Center" Width="100%">
17            <PagerSettings Mode="NumericFirstLast" Position="TopAndBottom" Visible="False" />
18            <RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
19            <HeaderStyle BorderStyle="Dotted" />
20            <AlternatingRowStyle BorderStyle="Dotted" HorizontalAlign="Center" VerticalAlign="Middle" />
21        </asp:GridView>
22            &nbsp; &nbsp;&nbsp;
23            <div align="center" nowrap="nowrap" style="font-size: 12px; z-index: 101; left: 183px;
24                width: 152px; position: absolute; top: 246px; height: 15px">
25                <asp:LinkButton ID="lbtn_First" runat="server" OnClick="lbtn_First_Click">首页</asp:LinkButton>
26                <asp:LinkButton ID="lbtn_Pre" runat="server" OnClick="lbtn_Pre_Click">上一页</asp:LinkButton>
27                <asp:LinkButton ID="lbtn_Next" runat="server" OnClick="lbtn_Next_Click">下一页</asp:LinkButton>
28                <asp:LinkButton ID="lbtn_Last" runat="server" OnClick="lbtn_Last_Click">尾页</asp:LinkButton></div>
29        </div>
30    
31    </div>
32    </form>
33</body>
34</html>

 

此存储过程利用临时表来分页，并不通用，也不带排序，我想排序可以由服务器来完成，

而没有必要由数据库来做。

还有待进一步完善。

如需让以上代码正常运行，还需要写一个ExecuteDataSet方法来完成从数据库中读取数据的功能。