access下的分页方案

access下的分页方案

具体不多说了，只贴出相关源码~

using  System;
using  System.Collections.Generic;
using  System.Text;
using  System.Data;
using  System.Data.OleDb;
using  System.Web;

/**/ /// <summary>
/// 名称：access下的分页方案（仿sql存储过程）
/// 作者：cncxz(虫虫)
/// blog：http://cncxz.cnblogs.com
/// </summary>
public   class  AdoPager
{
    protected string m_ConnString;
    protected OleDbConnection m_Conn;

    public AdoPager()
    {
        CreateConn(string.Empty);
    }
    public AdoPager(string dbPath)
    {
        CreateConn(dbPath);
    }

    private void CreateConn(string dbPath)
    {
        if (string.IsNullOrEmpty(dbPath))
        {
            string str = System.Configuration.ConfigurationManager.AppSettings["dbPath"] as string;
            if (string.IsNullOrEmpty(str))
                str = "~/App_Data/db.mdb";
            m_ConnString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}", HttpContext.Current.Server.MapPath(str));
        }
        else
            m_ConnString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}", dbPath);

        m_Conn = new OleDbConnection(m_ConnString);
    }
    /**//// <summary>
    /// 打开连接
    /// </summary>
    public void ConnOpen()
    {
        if (m_Conn.State != ConnectionState.Open)
            m_Conn.Open();
    }
    /**//// <summary>
    /// 关闭连接
    /// </summary>
    public void ConnClose()
    {
        if (m_Conn.State != ConnectionState.Closed)
            m_Conn.Close();
    }

    private string recordID(string query, int passCount)
    {
        OleDbCommand cmd = new OleDbCommand(query, m_Conn);
        string result = string.Empty;
        using (IDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                if (passCount < 1)
                {
                    result += "," + dr.GetInt32(0);
                }
                passCount--;
            }
        }
        return result.Substring(1);
    }


    /**//// <summary>
    /// 获取当前页应该显示的记录，注意：查询中必须包含名为ID的自动编号列，若不符合你的要求，就修改一下源码吧 :)
    /// </summary>
    /// <param name="pageIndex">当前页码</param>
    /// <param name="pageSize">分页容量</param>
    /// <param name="showString">显示的字段</param>
    /// <param name="queryString">查询字符串，支持联合查询</param>
    /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
    /// <param name="orderString">排序规则</param>
    /// <param name="pageCount">传出参数：总页数统计</param>
    /// <param name="recordCount">传出参数：总记录统计</param>
    /// <returns>装载记录的DataTable</returns>
    public DataTable ExecutePager(int pageIndex, int pageSize, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
    {
        if (pageIndex < 1) pageIndex = 1;
        if (pageSize < 1) pageSize = 10;
        if (string.IsNullOrEmpty(showString)) showString = "*";
        if (string.IsNullOrEmpty(orderString)) orderString = "ID desc";
        ConnOpen();
        string myVw = string.Format(" ( {0} ) tempVw ", queryString);
        OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(0) as recordCount from {0} {1}", myVw, whereString), m_Conn);

        recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

        if ((recordCount % pageSize) > 0)
            pageCount = recordCount / pageSize + 1;
        else
            pageCount = recordCount / pageSize;
        OleDbCommand cmdRecord;
        if (pageIndex == 1)//第一页
        {
            cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn);
        }
        else if (pageIndex > pageCount)//超出总页数
        {
            cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn);
        }
        else
        {
            int pageLowerBound = pageSize * pageIndex;
            int pageUpperBound = pageLowerBound - pageSize;
            string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, "ID", myVw, whereString, orderString), pageUpperBound);
            cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where id in ({2}) order by {3} ", showString, myVw, recordIDs, orderString), m_Conn);

        }
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
        DataTable dt=new DataTable();
        dataAdapter.Fill(dt);
        ConnClose();
        return dt;
    }
}

还有调用示例：
html代码

<% @ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default"  %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" >

< html  xmlns ="http://www.w3.org/1999/xhtml"   >
< head  runat ="server" >
     < title > 分页演示 </ title >
</ head >
< body >
     < form  id ="form1"  runat ="server" >
     < div >
         < br  />
         &nbsp;  转到第 < asp:TextBox  ID ="txtPageSize"  runat ="server"  Width ="29px" > 1 </ asp:TextBox > 页 < asp:Button  ID ="btnJump"  runat ="server"  Text ="Go"  OnClick ="btnJump_Click"   />< br  />
         < asp:GridView  ID ="GridView1"  runat ="server"  CellPadding ="4"  ForeColor ="#333333"  GridLines ="None"  Width ="90%" >
             < FooterStyle  BackColor ="#507CD1"  Font-Bold ="True"  ForeColor ="White"   />
             < RowStyle  BackColor ="#EFF3FB"   />
             < EditRowStyle  BackColor ="#2461BF"   />
             < SelectedRowStyle  BackColor ="#D1DDF1"  Font-Bold ="True"  ForeColor ="#333333"   />
             < PagerStyle  BackColor ="#2461BF"  ForeColor ="White"  HorizontalAlign ="Center"   />
             < HeaderStyle  BackColor ="#507CD1"  Font-Bold ="True"  ForeColor ="White"   />
             < AlternatingRowStyle  BackColor ="White"   />
         </ asp:GridView >
    
     </ div >
         < asp:Label  ID ="Label1"  runat ="server"  Text ="Label" ></ asp:Label >
     </ form >
</ body >
</ html >

示例的codebehind代码

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.Collections.Generic;

public  partial  class  _Default : System.Web.UI.Page 
{
    private AdoPager mm_Pager;
    protected AdoPager m_Pager
    { 
        get{
            if (mm_Pager == null)
                mm_Pager = new AdoPager();
            return mm_Pager;
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
            LoadData();
    }
    private int pageIndex = 1;
    private int pageSize = 20;
    private int pageCount = -1;
    private int recordCount = -1;

    private void LoadData()
    {
        string strQuery = "select a.*,b.KindText from tableTest a left join tableKind b on a.KindCode=b.KindCode ";
        string strShow = "ID,Subject,KindCode,KindText";     
        
        DataTable dt = m_Pager.ExecutePager(pageIndex, pageSize, strShow, strQuery, "", "ID desc", out pageCount, out recordCount);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        Label1.Text = string.Format("共{0}条记录，每页{1}条，页次{2}/{3}",recordCount,pageSize,pageIndex,pageCount);
    }
    
   
    protected void btnJump_Click(object sender, EventArgs e)
    {
        int.TryParse(txtPageSize.Text, out pageIndex);
        LoadData();
    }
}

最后附上工程文件下载地址： http://www.cnblogs.com/Files/cncxz/AdoPager.rar

posted on 2006-06-28 19:23  cncxz(虫虫) 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/cncxz/archive/2006/06/28/438050.html