具体不多说了,只贴出相关源码~ 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,whereString,orderString),m_Conn); } else if (pageIndex > pageCount)//超出总页数 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ","where 1=2",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",pageUpperBound); cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where id in ({2}) order by {3} ",recordIDs,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 /> <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> 示例的codebehind代码 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,strShow,strQuery,"","ID desc",out pageCount,out recordCount); GridView1.DataSource = dt; GridView1.DataBind(); Label1.Text = string.Format("共{0}条记录,每页{1}条,页次{2}/{3}",recordCount,pageIndex,pageCount); }
protected void btnJump_Click(object sender,EventArgs e) { int.TryParse(txtPageSize.Text,out pageIndex); LoadData(); } } (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|