DataReader分页(反向分页)

[nomber111]

对于学过asp的,又懒的学.net控件的,也可以很好的做出分页.
我写的这个方法暂时只能在sql2005上用,sql2000和access没试过,具体如下:

新建一数据库:
数据库名:myTesterDB
表名:myTable
列:id,username,point,selfintro

web.config:
//添加连接数据库代码//
<appSettings>
<add key="myTesterDBConnectionString" value="Data Source=(local)\SQLEXPRESS;Initial Catalog=myTesterDB;User ID=myTester;Password=12345"/>
</appSettings>

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

list3.aspx.cs:
using 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.SqlClient;

public partial class list3 : System.Web.UI.Page
{
private int page;//定义总页数
private int m;//此页显示记录数
private int n;//第一页至当页的总记录数
private string sql;//定义sql语句
private string sql_count;//定义统计记录数sql语句
private string admin;//标识登陆状态

protected void Page_Load(object sender, EventArgs e)
{
int RecordCount = CalculateRecord();//得到总记录数
string strConnection = ConfigurationSettings.AppSettings["myTesterDBConnectionString"];
int pageSize = 5;//定义每页显示记录数,在此设为10条
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)
{
page = 1;
if (RecordCount >= pageSize)
{
m = pageSize;
n = RecordCount;
}
else
{
m = RecordCount;
n = RecordCount;
}
}
else if (page >= totalPage)
{
page = totalPage;
m = RecordCount - (pageSize * (page - 1));
n = RecordCount - (pageSize * (page - 1));
}
else
{
m = pageSize;
n = RecordCount - (pageSize * (page - 1));
}
}
catch
{
page = 1;
if (RecordCount >= pageSize)
{
m = pageSize;
n = RecordCount;
}
else
{
m = RecordCount;
n = RecordCount;
}
}
//具体显示内容代码(连接数据库由此开始)
SqlConnection conn = new SqlConnection(strConnection);//定义连接数据库字符串
conn.Open();

sql = "select top " + m + " * from [myTable] where [myTable].[id] in (select top " + n + " [myTable].[id] from [myTable]) order by [id] desc";//SQL语句,必须理解!
SqlCommand comm = new SqlCommand(sql, conn);//sql语句用myConnection来执行
SqlDataReader dr = comm.ExecuteReader();//定义datareader对象变量
Response.Write("总记录数='" + CalculateRecord().ToString() + "' 总页数='" + totalPage.ToString() + "' 当前页='" + page.ToString() + "'");
Response.Write("<br><br>");
while (dr.Read())
{
//定义输出变量
string username = "";
string point = "";
string selfintro = "";
//变量赋值
username = dr["myUsername"].ToString();
point = dr["myPoints"].ToString();
selfintro = dr["selfIntro"].ToString();
//具体输出
Response.Write("username='" + username + "'　point='" + point + "'");
Response.Write("<br>");
Response.Write("intro='" + selfintro + "'");
Response.Write("<br>");
}
for (int i = 1; i <= totalPage; i++)
{
Response.Write("<a href=list3.aspx?page=" + i + ">" + i + "</a> ");
}
//释放资源
conn.Dispose();
conn.Close();
comm.Dispose();
dr.Dispose();
dr.Close();
}

public int CalculateRecord()
{
//连接数据库
string strConnection = ConfigurationSettings.AppSettings["myTesterDBConnectionString"];
SqlConnection conn_count = new SqlConnection(strConnection);
conn_count.Open();
int Count;//定义返回变量
string sql_count = "select count(*) from [myTable]";//sql语句
SqlCommand cmd_count = new SqlCommand(sql_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;
}
}