IDC资讯中心
首先创建一张表(要求id自动编号)：
create table redheadedfile(
id int identity(1,1),
filenames nvarchar(20),
senduser nvarchar(20),
primary key(id)
)
然后我们写入50万条记录：
declare @i int
set @i=1
while @i<=500000
begin
insert into redheadedfile(filenames,senduser) values(我的分页算法,陆俊铭)
set @i=@i+1
end
go
用microsoft visual studio .net 2003创建一张webform网页(本人起名webform8.aspx)
前台代码片段如下(webform8.aspx)：
<%@ page language=”c#” codebehind=”webform8.aspx.cs” autoeventwireup=”false” inherits=”webapplication6.webform8″ %>
<!doctype html public “-//w3c//dtd html 4.0 transitional//en” >
<html>
<head>
<title>webform8</title>
<meta content=”microsoft visual studio .net 7.1″ name=”generator”>
<meta content=”c#” name=”code_language”>
<meta content=”javascript” name=”vs_defaultclientscript”>
<meta content=”http://schemas.microsoft.com/intellisense/ie5″ name=”vs_targetschema”>
</head>
<body ms_positioning=”gridlayout”>
<form id=”form1″ method=”post” runat=”server”>
<asp:datalist id=”datalist1″ alternatingitemstyle-backcolor=”#f3f3f3″ width=”100%” cellspacing=”0″
cellpadding=”0″ runat=”server”>
<itemtemplate>
<table width=”100%” border=”0″ cellspacing=”0″ cellpadding=”0″>
<tr>
<td width=”30%”
align=”center”><%#databinder.eval(container.dataitem,”filenames”)%></td>
<td width=”30%”
align=”center”><%#databinder.eval(container.dataitem,”senduser”)%></td>
<td width=”30%”
align=”center”><%#databinder.eval(container.dataitem,”id”)%></td>
</tr>
</table>
</itemtemplate>
</asp:datalist>
<div align=”center”>共<asp:label id=”lpagecount” runat=”server” forecolor=”#ff0000″></asp:label>页/共
<asp:label id=”lrecordcount” runat=”server” forecolor=”#ff0000″></asp:label>记录
<asp:linkbutton id=”fistpage” runat=”server”
commandname=”0″>首页</asp:linkbutton> <asp:linkbutton id=”prevpage” runat=”server” commandname=”prev”>
上一页</asp:linkbutton> <asp:linkbutton id=”nextpage” runat=”server”
commandname=”next”>下一页</asp:linkbutton> <asp:linkbutton id=”lastpage” runat=”server”
commandname=”last”>尾页</asp:linkbutton> 当前第<asp:label id=”lcurrentpage” runat=”server”
forecolor=”#ff0000″></asp:label>页 跳页<asp:textbox id=”gotopage” runat=”server” width=”30px”
maxlength=”5″ autopostback=”true”></asp:textbox></div>
</form>
</body>
</html>
后台代码片段如下(webform8.aspx.cs)
using system;
using system.collections;
using system.componentmodel;
using system.data;
using system.drawing;
using system.web;
using system.web.sessionstate;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.data.sqlclient;
using system.configuration;
namespace webapplication6
{
/// <summary>
/// webform8 的摘要说明。
/// </summary>
public class webform8 : system.web.ui.page
{
protected system.web.ui.webcontrols.linkbutton fistpage;
protected system.web.ui.webcontrols.linkbutton prevpage;
protected system.web.ui.webcontrols.linkbutton nextpage;
protected system.web.ui.webcontrols.linkbutton lastpage;
protected system.web.ui.webcontrols.datalist datalist1;
protected system.web.ui.webcontrols.dropdownlist mydroplist;
protected system.web.ui.webcontrols.label lpagecount;
protected system.web.ui.webcontrols.label lrecordcount;
protected system.web.ui.webcontrols.label lcurrentpage;
protected system.web.ui.webcontrols.textbox gotopage;
const int pagesize=20;//定义每页显示记录
int pagecount,reccount,currentpage,pages,jumppage;//定义几个保存分页参数变量
private void page_load(object sender, system.eventargs e)
{
if(!ispostback)
{
reccount = calc();//通过calc()函数获取总记录数
pagecount = reccount/pagesize + overpage();//计算总页数（加上overpage()函数防止有余数造成显示
数据不完整）
viewstate[“pagecounts”] = reccount/pagesize –
modpage();//保存总页参数到viewstate（减去modpage()函数防止sql语句执行时溢出查询范围，可以用存储过程分页算法来理解这句）
viewstate[“pageindex”] = 0;//保存一个为0的页面索引值到viewstate
viewstate[“jumppages”] = pagecount;//保存pagecount到viewstate，跳页时判断用户输入数是否超出页
码范围
//显示lpagecount、lrecordcount的状态
lpagecount.text = pagecount.tostring();
lrecordcount.text = reccount.tostring();
//判断跳页文本框失效
if(reccount <= 20)
gotopage.enabled = false;
tdatabind();//调用数据绑定函数tdatabind()进行数据绑定运算
}
}
//计算余页
public int overpage()
{
int pages = 0;
if(reccount%pagesize != 0)
pages = 1;
else
pages = 0;
return pages;
}
//计算余页，防止sql语句执行时溢出查询范围
public int modpage()
{
int pages = 0;
if(reccount%pagesize == 0 && reccount != 0)
pages = 1;
else
pages = 0;
return pages;
}
/*
*计算总记录的静态函数
*本人在这里使用静态函数的理由是：如果引用的是静态数据或静态函数，连接器会优化生成代码，去掉动态重定位项（对
海量数据表分页效果更明显）。
*希望大家给予意见、如有不正确的地方望指正。
*/
public static int calc()
{
int recordcount = 0;
sqlcommand mycmd = new sqlcommand(“select count(*) as co from redheadedfile”,mycon());
sqldatareader dr = mycmd.executereader();
if(dr.read())
recordcount = int32.parse(dr[“co”].tostring());
mycmd.connection.close();
return recordcount;
}
//数据库连接语句（从web.config中获取）
public static sqlconnection mycon()
{
sqlconnection myconnection = new sqlconnection(configurationsettings.appsettings[“dsn”]);
myconnection.open();
return myconnection;
}
//对四个按钮（首页、上一页、下一页、尾页）返回的commandname值进行操作
private void page_onclick(object sender, commandeventargs e)
{
currentpage = (int)viewstate[“pageindex”];//从viewstate中读取页码值保存到currentpage变量中进行参数运
算
pages = (int)viewstate[“pagecounts”];//从viewstate中读取总页参数运算
string cmd = e.commandname;
switch(cmd)//筛选commandname
{
case “next”:
currentpage++;
break;
case “prev”:
currentpage–;
break;
case “last”:
currentpage = pages;
break;
default:
currentpage = 0;
break;
}
viewstate[“pageindex”] = currentpage;//将运算后的currentpage变量再次保存至viewstate
tdatabind();//调用数据绑定函数tdatabind()
}
private void tdatabind()
{
currentpage = (int)viewstate[“pageindex”];//从viewstate中读取页码值保存到currentpage变量中进行按钮失
效运算
pages = (int)viewstate[“pagecounts”];//从viewstate中读取总页参数进行按钮失效运算
//判断四个按钮（首页、上一页、下一页、尾页）状态
if (currentpage + 1 > 1)
{
fistpage.enabled = true;
prevpage.enabled = true;
}
else
{
fistpage.enabled = false;
prevpage.enabled = false;
}
if (currentpage == pages)
{
nextpage.enabled = false;
lastpage.enabled = false;
}
else
{
nextpage.enabled = true;
lastpage.enabled = true;
}
//数据绑定到datalist控件
dataset ds = new dataset();
//核心sql语句，进行查询运算（决定了分页的效率:)）
sqldataadapter myadapter = new sqldataadapter(“select top “+pagesize+” * from redheadedfile where id
not in(select top “+pagesize*currentpage+” id from redheadedfile order by id asc) order by id asc”,mycon());
myadapter.fill(ds,”news”);
datalist1.datasource = ds.tables[“news”].defaultview;
datalist1.databind();
//显示label控件lcurrentpaget和文本框控件gotopage状态
lcurrentpage.text = (currentpage+1).tostring();
gotopage.text = (currentpage+1).tostring();
//释放sqldataadapter
myadapter.dispose();
}
#region web 窗体设计器生成的代码
override protected void oninit(eventargs e)
{
//
// codegen: 该调用是 asp.net web 窗体设计器所必需的。
//
initializecomponent();
base.oninit(e);
}
/// <summary>
/// 设计器支持所需的方法 – 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void initializecomponent()
{
this.fistpage.command += new system.web.ui.webcontrols.commandeventhandler(this.page_onclick);
this.prevpage.command += new system.web.ui.webcontrols.commandeventhandler(this.page_onclick);
this.nextpage.command += new system.web.ui.webcontrols.commandeventhandler(this.page_onclick);
this.lastpage.command += new system.web.ui.webcontrols.commandeventhandler(this.page_onclick);
this.gotopage.textchanged += new system.eventhandler(this.gotopage_textchanged);
this.load += new system.eventhandler(this.page_load);
}
#endregion
//跳页代码
private void gotopage_textchanged(object sender, system.eventargs e)
{
try
{
jumppage = (int)viewstate[“jumppages”];//从viewstate中读取可用页数值保存到jumppage变量中
//判断用户输入值是否超过可用页数范围值
if(int32.parse(gotopage.text) > jumppage || int32.parse(gotopage.text) <= 0)
response.write(“<script>alert(页码范围越界！);location.href=webform8.aspx</script>”);
else
{
int inputpage = int32.parse(gotopage.text.tostring()) – 1;//转换用户输入值保存在int型
inputpage变量中
viewstate[“pageindex”] = inputpage;//写入inputpage值到viewstate[“pageindex”]中
tdatabind();//调用数据绑定函数tdatabind()再次进行数据绑定运算
}
}
//捕获由用户输入不正确数据类型时造成的异常
catch(exception exp)
{
response.write(“<script>alert(“+exp.message+”);location.href=webform8.aspx</script>”);
}
}
}
}
大家来试试，效率是不是高了很多？
