求一个Access 2003 的高效分页语句，网上找的一堆，大部分雷同、而且测试无效，郁闷。
数据量10万。系统设计当初，没想到会有10万的数据，所以当时采用的是内存分页。才导致
现在每次按分页页码，都能引起服务器CPU使用率飙升。
求解！------------
我有几个贴，都是在线的，有号的方法立马结贴http://topic.csdn.net/u/20120814/10/9ff7eb6b-96fa-4ff5-97b9-69fd268db298.htmlhttp://topic.csdn.net/u/20120310/17/e09d8b13-dfed-4204-bbc2-5c8d3876ad19.html
数据量10万。系统设计当初，没想到会有10万的数据，所以当时采用的是内存分页。才导致
现在每次按分页页码，都能引起服务器CPU使用率飙升。
求解！------------
我有几个贴，都是在线的，有号的方法立马结贴http://topic.csdn.net/u/20120814/10/9ff7eb6b-96fa-4ff5-97b9-69fd268db298.htmlhttp://topic.csdn.net/u/20120310/17/e09d8b13-dfed-4204-bbc2-5c8d3876ad19.html
程序代码
string strSql = " Select P.ProductID,P.ProClassID,P.ProductName,P.ProFlag,P.DateTime,P.ProductImage,C.ProClassName,C.ProClassKey,C.ProClassDes FROM ProductInfo P " +
" LEFT JOIN ProClass C ON P.ProClassID=C.ProClassID ";
string strShow = " ProductID,ProductName,DateTime,ProductImage,ProFlag,ProClassName,ProClassKey,ProClassDes ";
return ClassFile.AccessHelper.ExecutePager(PageIndex, PageSize, "ProductID", strShow, strSql, strWhere, " ProductID DESC ", out PageCount, out RecordCount);
程序代码
/// <summary>
/// 分页使用
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private static string recordID(string query, int passCount)
{
using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString))
{
m_Conn.Open();
OleDbCommand cmd = new OleDbCommand(query, m_Conn);
string result = string.Empty;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += "," + dr.GetInt32(0);
}
passCount--;
}
}
m_Conn.Close();
m_Conn.Dispose();
return result.Substring(1);
}
}
/// <summary>
/// ACCESS高效分页
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页容量</param>
/// <param name="strKey">主键</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 static DataTable ExecutePager(int pageIndex, int pageSize, string strKey,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 = strKey+" asc ";
using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString))
{
m_Conn.Open();
string myVw = string.Format(" ( {0} ) tempVw ", queryString);
OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) 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, strKey, myVw, whereString, orderString), pageUpperBound);
cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw,strKey, recordIDs, orderString), m_Conn); }
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
m_Conn.Close();
m_Conn.Dispose();
return dt;
}
}