CODE: using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Web; /**//**//**//**//**//// <summary> /// 名称：access下的分页方案（仿sql存储过程） /// 作者：cncxz(虫虫) /// from：http://bbs.51aspx.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; } } CODE: <%@ 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> CODE: 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(); } }
评论