DataGrid 连接 Access 的快速分页法（ 5 ）——实现快速分页
我使用Access自带的Northwind中文数据库的“订单明细”表作为例子，不过我在该表添加了一个名为“Id”的字段，数据类型为“自动编号”，并把该表命名为“订单明细表”。
FastPaging_DataSet.aspx
--------------------------------------------------------------------------------------
1@ Page language="c#" Codebehind="FastPaging_DataSet.aspx.cs" AutoEventWireup="false" Inherits="Paging.FastPaging_DataSet" EnableSessionState="False" enableViewState="True" enableViewStateMac="False"
1<html>
2<head>
3<title>DataGrid + DataReader 自定义分页</title>
4<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR"/>
5<meta content="C#" name="CODE_LANGUAGE"/>
6<meta content="JavaScript" name="vs_defaultClientScript"/>
7<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema"/>
8</head>
9<body>
10<form runat="server">
11<asp:datagrid allowcustompaging="True" allowpaging="True" allowsorting="True" alternatingitemstyle-backcolor="#eeeeee" autogeneratecolumns="False" bordercolor="Black" borderwidth="1px" cellpadding="3" font-size="12pt" headerstyle-backcolor="#aaaadd" id="DataGrid1" onpageindexchanged="MyDataGrid_Page" onsortcommand="DataGrid1_SortCommand" pagerstyle-horizontalalign="Right" pagesize="15" runat="server">
12<alternatingitemstyle backcolor="#EEEEEE"></alternatingitemstyle>
13<itemstyle borderwidth="22px" font-size="Smaller"></itemstyle>
14<headerstyle backcolor="#AAAADD"></headerstyle>
15<columns>
16<asp:boundcolumn datafield="ID" headertext="ID" sortexpression="ID"></asp:boundcolumn>
17<asp:boundcolumn datafield="订单ID" headertext="订单ID"></asp:boundcolumn>
18<asp:boundcolumn datafield="产品ID" headertext="产品ID"></asp:boundcolumn>
19<asp:boundcolumn datafield="单价" headertext="单价"></asp:boundcolumn>
20<asp:boundcolumn datafield="数量" headertext="数量"></asp:boundcolumn>
21<asp:boundcolumn datafield="折扣" headertext="折扣"></asp:boundcolumn>
22</columns>
23<pagerstyle font-bold="True" font-names="VerDana" forecolor="Coral" horizontalalign="Right" mode="NumericPages"></pagerstyle>
24</asp:datagrid></form>
25</body>
26</html>
FastPaging_DataSet.aspx.cs
--------------------------------------------------------------------------------------
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Text;
namespace Paging
{
public class FastPaging_DataSet : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
const String QUERY_FIELDS = "*"; //要查询的字段
const String TABLE_NAME = "订单明细表"; //数据表名称
const String PRIMARY_KEY = "ID"; //主键字段
const String DEF_ORDER_TYPE = "ASC"; //默认排序方式
const String SEC_ORDER_TYPE = "DESC"; //可选排序方式
const String CONDITION = "产品ID='AV-CB-1'";
OleDbConnection conn;
OleDbCommand cmd;
OleDbDataAdapter da;
#region 属性
#region CurrentPageIndex
///
1<summary>
2
3/// 获取或设置当前页的索引。
4
5/// </summary>
public int CurrentPageIndex
{
get { return (int) ViewState["CurrentPageIndex"]; }
set { ViewState["CurrentPageIndex"] = value; }
}
#endregion
#region OrderType
///
1<summary>
2
3/// 获取排序的方式：升序（ASC）或降序（DESC）。
4
5/// </summary>
public String OrderType
{
get {
String orderType = DEF_ORDER_TYPE;
if (ViewState["OrderType"] != null) {
orderType = (String)ViewState["OrderType"];
if (orderType != SEC_ORDER_TYPE)
orderType = DEF_ORDER_TYPE;
}
return orderType;
}
set { ViewState["OrderType"] = value.ToUpper(); }
}
#endregion
#endregion
private void Page_Load(object sender, System.EventArgs e)
{
#region 实现
String strConn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source="
+ Server.MapPath("Northwind.mdb");
conn = new OleDbConnection(strConn);
cmd = new OleDbCommand("",conn);
da = new OleDbDataAdapter(cmd);
if (!IsPostBack) {
// 设置用于自动计算页数的记录总数
DataGrid1.VirtualItemCount = GetRecordCount(TABLE_NAME);
CurrentPageIndex = 0;
BindDataGrid();
}
#endregion
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
///
1<summary>
2
3/// 设计器支持所需的方法 - 不要使用代码编辑器修改
4
5/// 此方法的内容。
6
7/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void BindDataGrid()
{
#region 实现
// 设置当前页的索引
DataGrid1.CurrentPageIndex = CurrentPageIndex;
// 设置数据源
DataGrid1.DataSource = GetDataView();
DataGrid1.DataBind();
#endregion
}
///
1<summary>
2
3/// 取得数据库表中的记录总数。
4
5/// </summary>
///
1<param name="tableName"/>
数据库中的表的名称。
///
1<returns>成功则为表中记录的总数；否则为 -1。</returns>
private int GetRecordCount(string tableName)
{
#region 实现
int count;
cmd.CommandText = "SELECT COUNT(*) AS RecordCount FROM " + tableName;
try {
conn.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
} catch(Exception ex) {
Response.Write (ex.Message.ToString());
count = -1;
} finally {
conn.Close();
}
return count;
#endregion
}
private DataView GetDataView()
{
#region 实现
int pageSize = DataGrid1.PageSize;
DataSet ds = new DataSet();
DataView dv = null;
cmd.CommandText = FastPaging.Paging(
pageSize,
CurrentPageIndex,
DataGrid1.VirtualItemCount,
TABLE_NAME,
QUERY_FIELDS,
PRIMARY_KEY,
FastPaging.IsAscending(OrderType) );
try {
da.Fill(ds, TABLE_NAME);
dv = ds.Tables[0].DefaultView;
} catch(Exception ex) {
Response.Write (ex.Message.ToString());
}
return dv;
#endregion
}
protected void MyDataGrid_Page(Object sender, DataGridPageChangedEventArgs e)
{
CurrentPageIndex = e.NewPageIndex;
BindDataGrid();
}
protected void DataGrid1_SortCommand(object source, DataGridSortCommandEventArgs e)
{
#region 实现
DataGrid1.CurrentPageIndex = 0;
this.CurrentPageIndex = 0;
if (OrderType == DEF_ORDER_TYPE)
OrderType = SEC_ORDER_TYPE;
else
OrderType = DEF_ORDER_TYPE;
BindDataGrid();
#endregion
}
}
}
我所要介绍的 DataGrid 连接 Access 的快速分页法就到这里了。如果你有其他更好的分页方法，不如也拿出来跟大家分享！
小弟第一次写文章，有说得不对的地方，希望大家指正。
作者：黎波