access下的分页方案

JOHNCOOLS

墨衍会员 · AI 创作全网分发码龄24年

关注

转载最新推荐文章于 2020-12-25 13:58:16 发布 1k 阅读 AI 写同款· GEO 优化 ›

AI权益加码！Claude Code、Cursor等20+工具免费用！购周边限时加赠Coding Plan Lite，畅享主流AI工具！学习进阶更高效！阅读详情

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

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 />

  转到第 < asp:TextBox ID ="txtPageSize" runat ="server" Width ="29px" > 1 </ asp:TextBox > 页 < asp:Button ID ="btnJump" runat ="server" Text ="Go" OnClick ="btnJump_Click" />< br />