Asp.net与Access的DataReader分页(cool!最新献上~)

nomber111 2007-10-27 05:00:48

用Asp.net与Access结合的DataReader分页,哈哈,总算让我搞出来了!!毕竟做小站和Blog还是Access方便。
会Asp的同志会了我这个，就可以很快的转型.net了，哈哈，在外观控制上也可以随心所欲了。
这里先放上正向分页,不久我将放上反向分页!

我的思想是:根据页数得到该页的第一条记录的id值,再根据每页要显示的记录数pageSize来显示每页的数据.

webconfig:<connectionStrings>(因为access我还不会相对路径,先拿绝对路径做出来的)
<add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\DB\\kqtdata#.mdb"
providerName="System.Data.OleDb" />
</connectionStrings>

Default2.aspx<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

Default2.aspx.csusing System;
using System.Data;
using System.Configuration;
using System.Collections;
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;

public partial class Default2 : System.Web.UI.Page
{
private int page;//定义当前页数
private int currentPage;//定义当前页
private int m;//此页显示记录数
private int n;//此页第一条记录的id
private string sql;//定义sql语句
private string sql_count;//定义统计记录数sql语句
private int pageSize;//定义每页显示的记录数

protected void Page_Load(object sender, EventArgs e)
{
int RecordCount = CalculateRecord();//得到总记录数
string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
pageSize = 5;//定义每页显示记录数,在此设为5条,可以自定义改成10、20均可！
int totalPage;//定义总页数

//得到总页数代码
if (RecordCount % pageSize == 0)
{
totalPage = RecordCount / pageSize;//如果总记录数除以总页数可以整除,则总页数为总记录数除以总页数的返回值
}
else
{
totalPage = RecordCount / pageSize + 1;//如果总记录数除以总页数不可以整除,则总页数为总记录数除以总页数的返回值加1
}
//得到当前页
try
{
page = Int32.Parse(Request.QueryString["page"]);
if (page <= 0 || page == 1 || page == null)//如果当前页<=0或=1或为空，则
{
page = 1;
n = get_n();
if (RecordCount >= pageSize)
{
m = pageSize;
}
else
{
m = RecordCount;
}
}
else if (page >= totalPage)//如果当前页>=最大页，则
{
page = totalPage;
n = get_n();
m = RecordCount - (pageSize * (page - 1));
}
else//其它页，即非第一页和最后一页
{
n = get_n();
m = pageSize;
}
}
catch//发生异常时
{
page = 1;
n = get_n();
if (RecordCount >= pageSize)
{
m = pageSize;
}
else
{
m = RecordCount;
}
}
//具体显示内容代码(连接数据库由此开始)
OleDbConnection conn = new OleDbConnection(strConnection);//定义连接数据库字符串
conn.Open();

sql = "select top " + m + " * from [liuyan] where [id] >= " + n + "";//SQL语句,必须理解!

OleDbCommand comm = new OleDbCommand(sql, conn);//sql语句用myConnection来执行
OleDbDataReader dr = comm.ExecuteReader();//定义datareader对象变量
Response.Write("总记录数='" + CalculateRecord().ToString() + "' 总页数='" + totalPage.ToString() + "' 当前页='" + page.ToString() + "'");
Response.Write("<br><br>");

int i = 0;
while (dr.Read())
{
//定义输出变量
string name = "";
string content = "";
string reply = "";
//变量赋值
name = dr["name"].ToString();
content = dr["content"].ToString();
reply = dr["reply"].ToString();
//具体输出
Response.Write("name='" + name + "'　content='" + content + "'");
Response.Write("<br>");
Response.Write("reply='" + reply + "'");
Response.Write("<br>");
}
for (int v = 1; v <= totalPage; v++)
{
Response.Write("<a href=Default2.aspx?page=" + v + ">" + v + "</a> ");
}
//释放资源
conn.Dispose();
conn.Close();
comm.Dispose();
dr.Dispose();
dr.Close();
}

public int CalculateRecord()//总计录数
{
//连接数据库
string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
OleDbConnection conn_count = new OleDbConnection(strConnection);
conn_count.Open();
int Count;//定义返回变量
string oledb_count = "select count(*) from [liuyan]";//sql语句
OleDbCommand cmd_count = new OleDbCommand(oledb_count, conn_count);//sql语句用conn_count来执行
Count = Convert.ToInt32(cmd_count.ExecuteScalar());//sqlcommand的统计记录数方法executescalar方法
//释放资源
conn_count.Dispose();
conn_count.Close();
cmd_count.Dispose();
//返回总数
return Count;
}

public int get_n()//根据页数得到每页的第一条记录的id
{

int nn = 0;//定义返回值变量
int mm = 0;//临时变量
mm = (page - 1) * pageSize + 1;
//连接数据库
string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
OleDbConnection conn = new OleDbConnection(strConnection);
conn.Open();
sql = "select top " + mm + " * from [liuyan] order by [id] asc";//SQL语句,必须理解!
OleDbCommand comm = new OleDbCommand(sql, conn);//sql语句用myConnection来执行
OleDbDataReader dr = comm.ExecuteReader();//定义datareader对象变量
int i = 0;
if (dr.HasRows)//当有记录时,nn为最后一条记录的id值!
{
while (dr.Read())
{
i++;
if (i == mm)
nn = Convert.ToInt32(dr["id"]);
}
}
else//无记录或异常发生时，nn为0!
{
nn = 0;
}
comm.Dispose();
dr.Dispose();
dr.Close();
conn.Close();
return nn;
}
}

注释已经很详细了，你要还是看不懂就没办法了。
如果你用我的方法成功了,记住要帮我顶哦！！
写代码不容易哦~~

...全文