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;
}
}
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;
}
}
<%
...
@ 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 />
< 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 >
<! 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 />
< 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 >
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();
}
}
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();
}
}