DataGrid连接Access的快速分页法(5)——实现快速分页

类别:.NET开发 点击:0 评论:0 推荐:
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 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            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
        }
    }
}
 
 
       我所要介绍的 DataGrid 连接 Access 的快速分页法就到这里了。如果你有其他更好的分页方法,不如也拿出来跟大家分享!
 
       小弟第一次写文章,有说得不对的地方,希望大家指正。
 
 
作者:黎波

本文地址:http://com.8s8s.com/it/it45105.htm