Access结合aspnetpager分页

Access结合AspNetPager实现高效简洁分页功能

分页存储过程查询参数类

using System;
using System.Collections.Generic;
using System.Text;

namespace King.Utility
{
    /// <summary>
    /// 分页存储过程查询参数类
    /// </summary>
    public class QueryParam
    {

        #region "Private Variables"
        private string _TableName;
        private string _ReturnFields;
        private string _Where;
        private string _Orderfld;
        private int _OrderType = 1;
        private int _PageIndex = 1;
        private int _PageSize = int.MaxValue;
        #endregion

        #region "Public Variables"

        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            get
            {
                return _TableName;
            }
            set
            {
                _TableName = value;
            }

        }



        /// <summary>
        /// 返回字段
        /// </summary>
        public string ReturnFields
        {
            get
            {
                return _ReturnFields;
            }
            set
            {
                _ReturnFields = value;
            }
        }




        /// <summary>
        /// 查询条件 需带Where
        /// </summary>
        public string Where
        {
            get
            {
                return _Where;
            }
            set
            {
                _Where = value;
            }
        }





        /// <summary>
        /// 排序字段
        /// </summary>
        public string Orderfld
        {
            get
            {
                return _Orderfld;
            }
            set
            {
                _Orderfld = value;
            }
        }


        /// <summary>
        /// 排序类型 1:降序 其它为升序
        /// </summary>
        public int OrderType
        {
            get
            {
                return _OrderType;
            }
            set
            {
                _OrderType = value;
            }
        }


        /// <summary>
        /// 当前页码
        /// </summary>
        public int PageIndex
        {
            get
            {
                return _PageIndex;
            }
            set
            {
                _PageIndex = value;
            }

        }


        /// <summary>
        /// 每页记录数
        /// </summary>
        public int PageSize
        {
            get
            {
                return _PageSize;
            }
            set
            {
                _PageSize = value;
            }
        }
        #endregion
    }
}

OleDbHepler 数据库操作类

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Text;
/// <summary>
/// OleDbHepler 的摘要说明
/// </summary>
public class OleDbHepler
{
    
    #region "数据库设置"
    /// <summary>
    /// 获取数据库类型
    /// </summary>
    public static string GetDBType
    {
        get
        {
            return ConfigurationManager.AppSettings["DBType"];
        }
    }

    /// <summary>
    /// 获取数据库连接字符串
    /// </summary>
    public static string GetConnString
    {
        get
        {
            return ConfigurationManager.AppSettings[GetDBType];
        }
    }
    #endregion
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private string ConnString = string.Empty;

    public OleDbHepler()
    {
        ConnString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0}{1};Persist Security Info=True;", AppDomain.CurrentDomain.BaseDirectory, GetConnString);
    }

    /// <summary>
    /// 获取数据连接
    /// </summary>
    /// <returns></returns>
    public OleDbConnection GetSqlConnection()
    {
        try
        {
            return new OleDbConnection(ConnString);
        }
        catch
        {
            throw new Exception("没有提供数据庫连接字符串Access！");
        }
    }

    /// <summary>
    /// 公共查询数据函数Access版
    /// </summary>
    /// <param name="pp">查询字符串</param>
    /// <param name="RecordCount">返回记录总数</param>
    /// <returns>返回记录集DataSet</returns>
    public DataSet GetDataSetList(QueryParam pp, out int RecordCount)
    {
        RecordCount = 0;
        DataSet ds = new DataSet();
        using (OleDbConnection Conn = GetSqlConnection())
        {
            StringBuilder sb = new StringBuilder();
            int TotalRecordForPageIndex = pp.PageIndex * pp.PageSize;
            string OrderBy;
            string CutOrderBy;
            if (pp.OrderType == 1)
            {
                OrderBy = " Order by " + pp.Orderfld + " desc ";
                CutOrderBy = " Order by " + pp.Orderfld + " asc ";
            }
            else
            {
                OrderBy = " Order by " + pp.Orderfld + " asc ";
                CutOrderBy = " Order by " + pp.Orderfld + " desc ";
            }
            sb.AppendFormat("SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} {2}    FROM {3} {4} {5}) TB2    {6}) TB3 {5} ", pp.PageSize, TotalRecordForPageIndex, pp.ReturnFields, pp.TableName, pp.Where, OrderBy, CutOrderBy);
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter dr = new OleDbDataAdapter();
            cmd.Connection = Conn;
            cmd.CommandText = sb.ToString();
            Conn.Open();
            dr.SelectCommand = cmd;
           
            dr.Fill(ds, pp.TableName);
            dr.Dispose();
            cmd.Parameters.Clear();
            // 取记录总数
            cmd.CommandText = string.Format("SELECT Count(1) From {0} {1}", pp.TableName, pp.Where);
            RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
            dr.Dispose();
            cmd.Dispose();
            Conn.Dispose();
            Conn.Close();
        }
        return ds;
    }
}

前台数据显示，分页控件采用AspNetPager,具体如何使用该控件大家上网查，资料很多。

<body>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" Width="98%">
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
      
        <div>
            <webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged">
            </webdiyer:AspNetPager>
        </div>
    </form>
</body>

后台实现数据绑定

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindData();           
        }
    }
    private void BindData()
    {
        QueryParam qp = new QueryParam();
        qp.TableName = "t_users";
        qp.Orderfld = "UserID";
        qp.ReturnFields = "*";
        qp.Where = SearchTerms;
        qp.PageIndex = AspNetPager1.CurrentPageIndex;
        qp.PageSize = AspNetPager1.PageSize;
        int RecordCount = 0;
        DataSet ds = new OleDbHepler().GetDataSetList(qp, out RecordCount);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        this.AspNetPager1.RecordCount = RecordCount;
    }
    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        BindData();
    }

    /// <summary>
    /// 查询条件
    /// </summary>
    private string SearchTerms
    {
        get
        {
            if (ViewState["SearchTerms"] == null)
                ViewState["SearchTerms"] = " Where 1 = 1";
            return (string)ViewState["SearchTerms"];
        }
        set { ViewState["SearchTerms"] = value; }
    }

一切搞定。