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

来源：互联网发布：域名有什么作用编辑：程序博客网时间：2024/06/06 08:57

前台代码片段如下(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 idnot 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>");   }  } }}