using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace DzPlatForm.DBUtility
{
public class AccessPage
{
private string _StringConnection;
public string StringConnection
{
get { return _StringConnection; }
set { _StringConnection = value; }
}
/// <summary>
/// 分页使用
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private string recordID(string query, int passCount)
{
using (OleDbConnection m_Conn = new OleDbConnection(StringConnection))
{
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 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(StringConnection))
{
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;
}
}
}
}
数据源：
/// 数据源刷新
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void StoreProject_RefreshData(object sender, StoreRefreshDataEventArgs e)
{
DateTime startdate;
DateTime enddate;
startdate = Convert.ToDateTime(this.dfstartdate.Text.Trim());
enddate = Convert.ToDateTime(this.dfenddate.Text.Trim());
int PageSize = this.PagingToolbar1.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值
int Count = 0;
int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页
StringBuilder strShow = new StringBuilder();
strShow.Append(" ID,项目编号,[项目编号-标记号], 标记号,申请日期,产品型号,样品数量,客户品名1,客户编号,销售员,样品设计人");//,采购回复最大日
strShow.Append(" ,预计完成时间 as 预计完成,实际完成时间 as 实际完成,客户要求交期,项目评分,客户品名2,备注,报价单号,客户类型,产品类型,产品性质");
strShow.Append(" ,是否开模,是否收费,是否做认证,模具费用,认证费用,设计审核人,项目类别,图纸编号,样品订单号,项目说明,客户反馈,项目状态,首单时间,文件归档,产品品牌");
StringBuilder strSql = new StringBuilder();
strSql.Append(" select ID,项目编号,[项目编号-标记号], 标记号,申请日期,产品型号,样品数量,客户品名1,客户编号,销售员,样品设计人");//,采购回复最大日
strSql.Append(" ,预计完成时间 ,实际完成时间 ,客户要求交期,项目评分,客户品名2,备注,报价单号,客户类型,产品类型,产品性质");
strSql.Append(" ,是否开模,是否收费,是否做认证,模具费用,认证费用,设计审核人,项目类别,图纸编号,样品订单号,项目说明,客户反馈,项目状态,首单时间,文件归档,产品品牌");
strSql.Append(" from 项目进度表 where format(申请日期,'yyyy-MM-dd')>='" + string.Format("{0:yyyy-MM-dd}", startdate) + "'");
strSql.Append(" and format(申请日期,'yyyy-MM-dd')<='" + string.Format("{0:yyyy-MM-dd}", enddate) + "'");
StringBuilder strWhere = new StringBuilder();
strWhere.Append("where 1=1 ");
if (txtCustomerCode.Text.Trim() != "")
{
strWhere.Append(" and 客户编号 like '" + txtCustomerCode.Text.Trim() + "'");
}
if (this.cbxCustomerType.SelectedItem.Text.Trim() != "")
{
strWhere.Append(" and 客户类型 like '" + cbxCustomerType.SelectedItem.Text.Trim() + "'");
}
if (this.cbxProductType.SelectedItem.Text.Trim() != "")
{
strWhere.Append(" and 产品类型 like '" + cbxProductType.SelectedItem.Text.Trim() + "'");
}
if (this.cbxProjectStatus.SelectedItem.Text.Trim() != "")
{
strWhere.Append(" and 项目状态 like '" + cbxProjectStatus.SelectedItem.Text.Trim() + "'");
}
int pagecount = 0;//总共多少页=PageSize
var store = this.GpProject.GetStore();
DBCon db = new DBCon("ZMProject");
AccessPage page = new AccessPage();
page.StringConnection = db.StringConnection();
DataTable dt = page.ExecutePager(CurPage, PageSize, "ID", strShow.ToString(), strSql.ToString(), strWhere.ToString(), " 申请日期 DESC ", out pagecount, out Count);
e.Total = Count;
if (Count > 0)
{
store.DataSource = dt;//绑定数据
store.DataBind();
}
else
{
X.Msg.Alert("查询结果", "<font style='color:red;'>没有找到任何数据!</font>").Show();
}
}
显示结果:
