ASP.NET技巧:access下的分页方案
ASP.NET技巧:access下的分页方案
发布时间:2016-12-29 来源:查字典编辑
摘要:具体不多说了,只贴出相关源码~usingSystem;usingSystem.Collections.Generic;usingSystem...

具体不多说了,只贴出相关源码~

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=", HttpContext.Current.Server.MapPath(str));

}

else

m_ConnString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data source=", 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", queryString);

OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(0) as recordCount from {0", 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 ", 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 ", 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 ", pageLowerBound, "ID", myVw, whereString, orderString), pageUpperBound);

cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where id in ({2}) order by ", showString, myVw, recordIDs, orderString), 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" /><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, pageSize, strShow, strQuery, "", "ID desc", out pageCount, out recordCount);

GridView1.DataSource = dt;

GridView1.DataBind();

Label1.Text = string.Format("共{0}条记录,每页{1}条,页次{2",recordCount,pageSize,pageIndex,pageCount);

}

protected void btnJump_Click(object sender, EventArgs e)

{

int.TryParse(txtPageSize.Text, out pageIndex);

LoadData();

}

}

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新asp.net教程学习
热门asp.net教程学习
编程开发子分类