Access結合AspNetPager實現高效簡潔分頁功能

分頁存儲過程查詢參數類

?

代碼 using?System;
using?System.Collections.Generic;
using?System.Text;

namespace?King.Utility
{
????///?<summary>
????///?分頁存儲過程查詢參數類
????///?</summary>
????public?class?QueryParam
????{

????????#region?"Private?Variables"
????????private?string?_TableName;
????????private?string?_ReturnFields;
????????private?string?_Where;
????????private?string?_Orderfld;
????????private?int?_OrderType?=?1;
????????private?int?_PageIndex?=?1;
????????private?int?_PageSize?=?int.MaxValue;
????????#endregion

????????#region?"Public?Variables"

????????///?<summary>
????????///?表名
????????///?</summary>
????????public?string?TableName
????????{
????????????get
????????????{
????????????????return?_TableName;
????????????}
????????????set
????????????{
????????????????_TableName?=?value;
????????????}

????????}



????????///?<summary>
????????///?返回字段
????????///?</summary>
????????public?string?ReturnFields
????????{
????????????get
????????????{
????????????????return?_ReturnFields;
????????????}
????????????set
????????????{
????????????????_ReturnFields?=?value;
????????????}
????????}




????????///?<summary>
????????///?查詢條件?需帶Where
????????///?</summary>
????????public?string?Where
????????{
????????????get
????????????{
????????????????return?_Where;
????????????}
????????????set
????????????{
????????????????_Where?=?value;
????????????}
????????}





????????///?<summary>
????????///?排序字段
????????///?</summary>
????????public?string?Orderfld
????????{
????????????get
????????????{
????????????????return?_Orderfld;
????????????}
????????????set
????????????{
????????????????_Orderfld?=?value;
????????????}
????????}


????????///?<summary>
????????///?排序類型?1:降序?其它為升序
????????///?</summary>
????????public?int?OrderType
????????{
????????????get
????????????{
????????????????return?_OrderType;
????????????}
????????????set
????????????{
????????????????_OrderType?=?value;
????????????}
????????}


????????///?<summary>
????????///?當前頁碼
????????///?</summary>
????????public?int?PageIndex
????????{
????????????get
????????????{
????????????????return?_PageIndex;
????????????}
????????????set
????????????{
????????????????_PageIndex?=?value;
????????????}

????????}


????????///?<summary>
????????///?每頁記錄數
????????///?</summary>
????????public?int?PageSize
????????{
????????????get
????????????{
????????????????return?_PageSize;
????????????}
????????????set
????????????{
????????????????_PageSize?=?value;
????????????}
????????}
????????#endregion
????}
}

OleDbHepler 數據庫操作類

?

代碼 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.Data.OleDb;
using?System.Text;
///?<summary>
///?OleDbHepler?的摘要說明
///?</summary>
public?class?OleDbHepler
{
????
????#region?"數據庫設置"
????///?<summary>
????///?獲取數據庫類型
????///?</summary>
????public?static?string?GetDBType
????{
????????get
????????{
????????????return?ConfigurationManager.AppSettings["DBType"];
????????}
????}

????///?<summary>
????///?獲取數據庫連接字符串
????///?</summary>
????public?static?string?GetConnString
????{
????????get
????????{
????????????return?ConfigurationManager.AppSettings[GetDBType];
????????}
????}
????#endregion
????///?<summary>
????///?數據庫連接字符串
????///?</summary>
????private?string?ConnString?=?string.Empty;

????public?OleDbHepler()
????{
????????ConnString?=?string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data?Source={0}{1};Persist?Security?Info=True;",?AppDomain.CurrentDomain.BaseDirectory,?GetConnString);
????}

????///?<summary>
????///?獲取數據連接
????///?</summary>
????///?<returns></returns>
????public?OleDbConnection?GetSqlConnection()
????{
????????try
????????{
????????????return?new?OleDbConnection(ConnString);
????????}
????????catch
????????{
????????????throw?new?Exception("沒有提供數據庫連接字符串Access！");
????????}
????}

????///?<summary>
????///?公共查詢數據函數Access版
????///?</summary>
????///?<param?name="pp">查詢字符串</param>
????///?<param?name="RecordCount">返回記錄總數</param>
????///?<returns>返回記錄集DataSet</returns>
????public?DataSet?GetDataSetList(QueryParam?pp,?out?int?RecordCount)
????{
????????RecordCount?=?0;
????????DataSet?ds?=?new?DataSet();
????????using?(OleDbConnection?Conn?=?GetSqlConnection())
????????{
????????????StringBuilder?sb?=?new?StringBuilder();
????????????int?TotalRecordForPageIndex?=?pp.PageIndex?*?pp.PageSize;
????????????string?OrderBy;
????????????string?CutOrderBy;
????????????if?(pp.OrderType?==?1)
????????????{
????????????????OrderBy?=?"?Order?by?"?+?pp.Orderfld?+?"?desc?";
????????????????CutOrderBy?=?"?Order?by?"?+?pp.Orderfld?+?"?asc?";
????????????}
????????????else
????????????{
????????????????OrderBy?=?"?Order?by?"?+?pp.Orderfld?+?"?asc?";
????????????????CutOrderBy?=?"?Order?by?"?+?pp.Orderfld?+?"?desc?";
????????????}
????????????sb.AppendFormat("SELECT?*?FROM?(SELECT?TOP?{0}?*?FROM?(SELECT?TOP?{1}?{2}????FROM?{3}?{4}?{5})?TB2????{6})?TB3?{5}?",?pp.PageSize,?TotalRecordForPageIndex,?pp.ReturnFields,?pp.TableName,?pp.Where,?OrderBy,?CutOrderBy);
????????????OleDbCommand?cmd?=?new?OleDbCommand();
????????????OleDbDataAdapter?dr?=?new?OleDbDataAdapter();
????????????cmd.Connection?=?Conn;
????????????cmd.CommandText?=?sb.ToString();
????????????Conn.Open();
????????????dr.SelectCommand?=?cmd;
???????????
????????????dr.Fill(ds,?pp.TableName);
????????????dr.Dispose();
????????????cmd.Parameters.Clear();
????????????//?取記錄總數
????????????cmd.CommandText?=?string.Format("SELECT?Count(1)?From?{0}?{1}",?pp.TableName,?pp.Where);
????????????RecordCount?=?Convert.ToInt32(cmd.ExecuteScalar());
????????????dr.Dispose();
????????????cmd.Dispose();
????????????Conn.Dispose();
????????????Conn.Close();
????????}
????????return?ds;
????}
}

?

前臺數據顯示，分頁控件采用AspNetPager,具體如何使用該控件大家上網查，資料很多。

?

代碼 <body>
????<form?id="form1"?runat="server">
????????<asp:GridView?ID="GridView1"?runat="server"?CellPadding="4"?ForeColor="#333333"?GridLines="None"?Width="98%">
????????????<RowStyle?BackColor="#FFFBD6"?ForeColor="#333333"?/>
????????????<FooterStyle?BackColor="#990000"?Font-Bold="True"?ForeColor="White"?/>
????????????<PagerStyle?BackColor="#FFCC66"?ForeColor="#333333"?HorizontalAlign="Center"?/>
????????????<SelectedRowStyle?BackColor="#FFCC66"?Font-Bold="True"?ForeColor="Navy"?/>
????????????<HeaderStyle?BackColor="#990000"?Font-Bold="True"?ForeColor="White"?/>
????????????<AlternatingRowStyle?BackColor="White"?/>
????????</asp:GridView>
??????
????????<div>
????????????<webdiyer:AspNetPager?ID="AspNetPager1"?runat="server"?OnPageChanged="AspNetPager1_PageChanged">
????????????</webdiyer:AspNetPager>
????????</div>
????</form>
</body>

?

后臺實現數據綁定

?

代碼 ?protected?void?Page_Load(object?sender,?EventArgs?e)
????{
????????if?(!Page.IsPostBack)
????????{
????????????BindData();???????????
????????}
????}
????private?void?BindData()
????{
????????QueryParam?qp?=?new?QueryParam();
????????qp.TableName?=?"t_users";
????????qp.Orderfld?=?"UserID";
????????qp.ReturnFields?=?"*";
????????qp.Where?=?SearchTerms;
????????qp.PageIndex?=?AspNetPager1.CurrentPageIndex;
????????qp.PageSize?=?AspNetPager1.PageSize;
????????int?RecordCount?=?0;
????????DataSet?ds?=?new?OleDbHepler().GetDataSetList(qp,?out?RecordCount);
????????GridView1.DataSource?=?ds;
????????GridView1.DataBind();
????????this.AspNetPager1.RecordCount?=?RecordCount;
????}
????protected?void?AspNetPager1_PageChanged(object?sender,?EventArgs?e)
????{
????????BindData();
????}

????///?<summary>
????///?查詢條件
????///?</summary>
????private?string?SearchTerms
????{
????????get
????????{
????????????if?(ViewState["SearchTerms"]?==?null)
????????????????ViewState["SearchTerms"]?=?"?Where?1?=?1";
????????????return?(string)ViewState["SearchTerms"];
????????}
????????set?{?ViewState["SearchTerms"]?=?value;?}
????}

?

一切搞定。

?

轉載于:https://www.cnblogs.com/nosnowwolf/archive/2006/06/16/427863.html

總結

以上是生活随笔為你收集整理的Access结合aspnetpager分页的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。