asp.net(C#)海量数据表高效率分页算法（易懂，不使用存储过程）

来源：互联网发布：晚清民国期刊数据库编辑：程序博客网时间：2024/06/13 11:59

首先创建一张表(要求ID自动编号)：

createtableredheadedfile(idintidentity(1,1),filenamesnvarchar(20),sendusernvarchar(20),primarykey(id))

然后我们写入50万条记录：

declare@iintset@i=1while@i<=500000begininsertintoredheadedfile(filenames,senduser)values('我的分页算法','陆俊铭')set@i=@i 1endGO

用MicrosoftVisualStudio.net2003创建一张WebForm网页(本人起名webform8.ASPx)
前台代码片段如下(webform8.aspx)：

<%@Pagelanguage="C#"Codebehind="WebForm8.aspx.cs"AutoEventWireup="false"Inherits="WebApplication6.WebForm8"%><!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.0Transitional//EN"><HTML><HEAD><title>WebForm8</title><metacontent="MicrosoftVisualStudio.NET7.1"name="GENERATOR"><metacontent="C#"name="CODE_LANGUAGE"><metacontent="JavaScript"name="vs_defaultClientScript"><metacontent="http://schemas.microsoft.com/intellisense/ie5"name="vs_targetSchema"></HEAD><bodyMS_POSITIONING="GridLayout"><formid="Form1"method="post"runat="server"><asp:datalistid="datalist1"AlternatingItemStyle-BackColor="#f3f3f3"Width="100%"CellSpacing="0"CellPadding="0"Runat="server"><ItemTemplate><tablewidth="100%"border="0"cellspacing="0"cellpadding="0"><tr><tdwidth="30%"align="center"><%#DataBinder.Eval(Container.DataItem,"filenames")%></td><tdwidth="30%"align="center"><%#DataBinder.Eval(Container.DataItem,"senduser")%></td><tdwidth="30%"align="center"><%#DataBinder.Eval(Container.DataItem,"id")%></td></tr></table></ItemTemplate></asp:datalist><divalign="center">共<asp:labelid="LPageCount"Runat="server"ForeColor="#ff0000"></asp:label>页/共<asp:labelid="LRecordCount"Runat="server"ForeColor="#ff0000"></asp:label>记录<asp:linkbuttonid="Fistpage"Runat="server"CommandName="0">首页</asp:linkbutton>    <asp:linkbuttonid="Prevpage"Runat="server"CommandName="prev">上一页</asp:linkbutton>    <asp:linkbuttonid="Nextpage"Runat="server"CommandName="next">下一页</asp:linkbutton>    <asp:linkbuttonid="Lastpage"Runat="server"CommandName="last">尾页</asp:linkbutton>    当前第<asp:labelid="LCurrentPage"Runat="server"ForeColor="#ff0000"></asp:label>页    跳页<asp:TextBoxID="gotoPage"Runat="server"Width="30px"MaxLength="5"AutoPostBack="True"></asp:TextBox></div></form></body></HTML>

后台代码片段如下(webform8.aspx.cs)

usingSystem;usingSystem.Collections;usingSystem.ComponentModel;usingSystem.Data;

usingSystem.Drawing;
usingSystem.Web;
usingSystem.Web.SessionState;
usingSystem.Web.UI;
usingSystem.Web.UI.WebControls;
usingSystem.Web.UI.HtmlControls;
usingSystem.Data.SqlClient;
usingSystem.Configuration;
namespaceWebApplication6{///<summary>///WebForm8的摘要说明。///</summary>publicclassWebForm8:System.Web.UI.Page{protectedSystem.Web.UI.WebControls.LinkButtonFistpage;protectedSystem.Web.UI.WebControls.LinkButtonPrevpage;protectedSystem.Web.UI.WebControls.LinkButtonNextpage;protectedSystem.Web.UI.WebControls.LinkButtonLastpage;protectedSystem.Web.UI.WebControls.DataListdatalist1;protectedSystem.Web.UI.WebControls.DropDownListmydroplist;protectedSystem.Web.UI.WebControls.LabelLPageCount;protectedSystem.Web.UI.WebControls.LabelLRecordCount;protectedSystem.Web.UI.WebControls.LabelLCurrentPage;protectedSystem.Web.UI.WebControls.TextBoxgotoPage;constintPageSize=20;//定义每页显示记录intPageCount,RecCount,CurrentPage,Pages,JumpPage;//定义几个保存分页参数变量privatevoidPage_Load(objectsender,System.EventArgse){if(!IsPostBack){RecCount=Calc();//通过Calc()函数获取总记录数PageCount=RecCount/PageSize OvERPage();//计算总页数（加上OverPage()函数防止有余数造成显示
数据不完整）
ViewState["PageCounts"]=RecCount/PageSize-
ModPage();//保存总页参数到ViewState（减去ModPage()函数防止SQL语句执行时溢出查询范围，可以用存储过程分页算法来理解这句）ViewState["PageIndex"]=0;//保存一个为0的页面索引值到ViewStateViewState["JumpPages"]=PageCount;//保存PageCount到ViewState，跳页时判断用户输入数是否超出页
码范围//显示LPageCount、LRecordCount的状态LPageCount.Text=PageCount.ToString();LRecordCount.Text=RecCount.ToString();//判断跳页文本框失效if(RecCount<=20)gotoPage.Enabled=false;TDataBind();//调用数据绑定函数TDataBind()进行数据绑定运算}}//计算余页publicintOverPage(){intpages=0;if(RecCount%PageSize!=0)pages=1;elsepages=0;returnpages;}//计算余页，防止SQL语句执行时溢出查询范围publicintModPage(){intpages=0;if(RecCount%PageSize==0&&RecCount!=0)pages=1;elsepages=0;returnpages;}/**计算总记录的静态函数*本人在这里使用静态函数的理由是：如果引用的是静态数据或静态函数，连接器会优化生成代码，去掉动态重定位项（对
海量数据表分页效果更明显）。*希望大家给予意见、如有不正确的地方望指正。*/publicstaticintCalc(){intRecordCount=0;SqlCommandMyCmd=newSqlCommand("selectcount(*)ascofromredheadedfile",MyCon());SqlDataReaderdr=MyCmd.ExecuteReader();if(dr.Read())RecordCount=Int32.Parse(dr["co"].ToString());MyCmd.Connection.Close();returnRecordCount;}//数据库连接语句（从Web.Config中获取）publicstaticSqlConnectionMyCon(){SqlConnectionMyConnection=newSqlConnection(ConfigurationSettings.AppSettings["DSN"]);
MyConnection.Open();
returnMyConnection;
}

//对四个按钮（首页、上一页、下一页、尾页）返回的CommandName值进行操作
privatevoidPage_OnClick(objectsender,CommandEventArgse)
{
CurrentPage=(int)ViewState["PageIndex"];//从ViewState中读取页码值保存到CurrentPage变量中进行参数运
算Pages=(int)ViewState["PageCounts"];//从ViewState中读取总页参数运算
stringcmd=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变量再次保存至ViewStateTDataBind();//调用数据绑定函数TDataBind()}
privatevoidTDataBind(){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控件DataSetds=newDataSet();//核心SQL语句，进行查询运算（决定了分页的效率:)）SqlDataAdapterMyAdapter=newSqlDataAdapter("SelectTop" PageSize "*fromredheadedfilewhereid
notin(selecttop" PageSize*CurrentPage "idfromredheadedfileorderbyidasc)orderbyidasc",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();//释放SqlDataAdapterMyAdapter.Dispose();}
#regionWeb窗体设计器生成的代码overrideprotectedvoidOnInit(EventArgse){////CODEGEN:该调用是ASP.NETWeb窗体设计器所必需的。//InitializeComponent();base.OnInit(e);}///<summary>///设计器支持所需的方法-不要使用代码编辑器修改///此方法的内容。///</summary>privatevoidInitializeComponent(){ this.Fistpage.Command =newSystem.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);this.Prevpage.Command =newSystem.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);this.Nextpage.Command =newSystem.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);this.Lastpage.Command =newSystem.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick);this.gotoPage.TextChanged =newSystem.EventHandler(this.gotoPage_TextChanged);this.Load =newSystem.EventHandler(this.Page_Load);
}#endregion
//跳页代码
privatevoidgotoPage_TextChanged(objectsender,System.EventArgse)
{
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{intInputPage=Int32.Parse(gotoPage.Text.ToString())-1;//转换用户输入值保存在int型
InputPage变量中ViewState["PageIndex"]=InputPage;//写入InputPage值到ViewState["PageIndex"]中TDataBind();//调用数据绑定函数TDataBind()再次进行数据绑定运算}}//捕获由用户输入不正确数据类型时造成的异常catch(Exceptionexp){Response.Write("<script>alert('" exp.Message "');location.href='WebForm8.aspx'</script>");}}}}
//大家来试试，效率是不是高了很多？

引用地址：http://www.mscto.com/dotnet/2009022456629.html