access|datagrid|分頁 DataGrid串連Access的快速分頁法（5）——實現快速分頁
我使用Access內建的Northwind中文資料庫的“訂單明細”表作為例子，不過我在該表添加了一個名為“Id”的欄位，資料類型為“自動編號”，並把該表命名為“訂單明細表”。
FastPaging_DataSet.aspx
--------------------------------------------------------------------------------------
<%@ Page language="c#" Codebehind="FastPaging_DataSet.aspx.cs" AutoEventWireup="false" Inherits="Paging.FastPaging_DataSet" EnableSessionState="False" enableViewState="True" enableViewStateMac="False" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>DataGrid + DataReader 自訂分頁</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" BorderWidth="1px" BorderColor="Black" Font-Size="12pt"
AlternatingItemStyle-BackColor="#eeeeee" HeaderStyle-BackColor="#aaaadd" PagerStyle-HorizontalAlign="Right"
CellPadding="3" AllowPaging="True" AllowCustomPaging="True" AutoGenerateColumns="False" OnPageIndexChanged="MyDataGrid_Page"
PageSize="15" AllowSorting="True" OnSortCommand="DataGrid1_SortCommand">
<AlternatingItemStyle BackColor="#EEEEEE"></AlternatingItemStyle>
<ItemStyle Font-Size="Smaller" BorderWidth="22px"></ItemStyle>
<HeaderStyle BackColor="#AAAADD"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="ID" SortExpression="ID" HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="訂單ID" HeaderText="訂單ID"></asp:BoundColumn>
<asp:BoundColumn DataField="產品ID" HeaderText="產品ID"></asp:BoundColumn>
<asp:BoundColumn DataField="單價" HeaderText="單價"></asp:BoundColumn>
<asp:BoundColumn DataField="數量" HeaderText="數量"></asp:BoundColumn>
<asp:BoundColumn DataField="折扣" HeaderText="折扣"></asp:BoundColumn>
</Columns>
<PagerStyle Font-Names="VerDana" Font-Bold="True" HorizontalAlign="Right" ForeColor="Coral"
Mode="NumericPages"></PagerStyle>
</asp:datagrid></form>
</body>
</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
/// <summary>
/// 擷取或設定當前頁的索引。
/// </summary>
public int CurrentPageIndex
{
get { return (int) ViewState["CurrentPageIndex"]; }
set { ViewState["CurrentPageIndex"] = value; }
}
#endregion
#region OrderType
/// <summary>
/// 擷取排序的方式：升序（ASC）或降序（DESC）。
/// </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 Form設計器產生的程式碼
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 該調用是 ASP.NET Web Form設計器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 設計器支援所需的方法 - 不要使用代碼編輯器修改
/// 此方法的內容。
/// </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
}
/// <summary>
/// 取得資料庫表中的記錄總數。
/// </summary>
/// <param name="tableName">資料庫中的表的名稱。</param>
/// <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
}
}
}
