（转）C#高效分页代码(不用存储过程)
摘要：文章：C#高效分页代码(不用存储过程) 摘要：首先创建一张表(要求ID自动编号)：createtableredheadedfile(idintidentity(1,1，发表于北京联高软件有限公司技术文章栏目，代码以高亮显示。
关键字：过程, 存储, 代码, 高效, gt, system, web, int, viewstate, ui, id, currentpage, webcontrols, pages, this, linkbutton, using, gotopage, 数据


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

create table redheadedfile
(
   id int identity(1,1),
   filenames nvarchar(50),
   senduser nvarchar(50),
   primary key(id)
)

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

declare @i int
set @i=1
while @i<=500000
begin
    insert into redheadedfile(filenames,senduser) values("高效分页算法测试数据" + str(i) ,"北京联高软件开发有限公司")
set @i=@i+1
end
GO

用Microsoft Visual Studio .NET 2005创建一张WebForm网页。
前台代码片段如下(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)

> 纯文本方式> 拷贝到裁剪板> 打印
  1. using System; 
  2. using System.Collections; 
  3. using System.ComponentModel; 
  4. using System.Data; 
  5. using System.Drawing; 
  6. using System.Web; 
  7. using System.Web.SessionState; 
  8. using System.Web.UI; 
  9. using System.Web.UI.WebControls; 
  10. using System.Web.UI.HtmlControls; 
  11. using System.Data.SqlClient; 
  12. using System.Configuration; 
  13. namespace WebApplication6 
  14. { 
  15.     /// <summary> 
  16.     /// WebForm8 的摘要说明。 
  17.     /// </summary> 
  18.     public class WebForm8 : System.Web.UI.Page 
  19.     { 
  20.         protected System.Web.UI.WebControls.LinkButton Fistpage; 
  21.         protected System.Web.UI.WebControls.LinkButton Prevpage; 
  22.         protected System.Web.UI.WebControls.LinkButton Nextpage; 
  23.         protected System.Web.UI.WebControls.LinkButton Lastpage; 
  24.         protected System.Web.UI.WebControls.DataList datalist1; 
  25.         protected System.Web.UI.WebControls.DropDownList mydroplist; 
  26.         protected System.Web.UI.WebControls.Label LPageCount; 
  27.         protected System.Web.UI.WebControls.Label LRecordCount; 
  28.         protected System.Web.UI.WebControls.Label LCurrentPage; 
  29.         protected System.Web.UI.WebControls.TextBox gotoPage; 
  30.         //定义每页显示记录 
  31.         const int PageSize = 20; 
  32.         //定义几个保存分页参数变量 
  33.         int PageCount, RecCount, CurrentPage, Pages, JumpPage; 
  34.         private void Page_Load(object sender, System.EventArgs e) 
  35.         { 
  36.             if (!IsPostBack) 
  37.             { 
  38.                 //通过Calc()函数获取总记录数 
  39.                 RecCount = Calc(); 
  40.                 //计算总页数（加上OverPage()函数防止有余数造成显示数据不完整） 
  41.                 PageCount = RecCount / PageSize + OverPage(); 
  42.                 //保存总页参数到ViewState（减去ModPage()函数防止SQL语句执行时溢出查询范围，可以用存储过程分页算法来理解这句） 
  43.                 ViewState["PageCounts"] = RecCount / PageSize - ModPage(); 
  44.                 //保存一个为0的页面索引值到ViewState 
  45.                 ViewState["PageIndex"] = 0; 
  46.                 //保存PageCount到ViewState，跳页时判断用户输入数是否超出页码范围 
  47.                 ViewState["JumpPages"] = PageCount; 
  48.                 //显示LPageCount、LRecordCount的状态 
  49.                 LPageCount.Text = PageCount.ToString(); 
  50.                 LRecordCount.Text = RecCount.ToString(); 
  51.                 //判断跳页文本框失效 
  52.                 if (RecCount <= 20) 
  53.                 { 
  54.                     gotoPage.Enabled = false; 
  55.                 } 
  56.                 //调用数据绑定函数TDataBind()进行数据绑定运算 
  57.                 TDataBind(); 
  58.             } 
  59.         } 
  60.         //计算余页 
  61.         public int OverPage() 
  62.         { 
  63.             int pages = 0; 
  64.             if (RecCount % PageSize != 0) 
  65.                 pages = 1; 
  66.             else 
  67.                 pages = 0; 
  68.             return pages; 
  69.         } 
  70.         //计算余页，防止SQL语句执行时溢出查询范围 
  71.         public int ModPage() 
  72.         { 
  73.             int pages = 0; 
  74.             if (RecCount % PageSize == 0 && RecCount != 0) 
  75.                 pages = 1; 
  76.             else 
  77.                 pages = 0; 
  78.             return pages; 
  79.         } 
  80.         // 计算总记录的静态函数 
  81.         // 本人在这里使用静态函数的理由是：如果引用的是静态数据或静态函数， 
  82.         // 连接器会优化生成代码，去掉动态重定位项（对海量数据表分页效果更明显）。 
  83.         // 希望大家给予意见、如有不正确的地方望指正。 
  84.         public static int Calc() 
  85.         { 
  86.             int RecordCount = 0; 
  87.             SqlCommand MyCmd = new SqlCommand("select count(*) as co from redheadedfile", MyCon()); 
  88.             SqlDataReader dr = MyCmd.ExecuteReader(); 
  89.             if (dr.Read()) 
  90.                 RecordCount = Int32.Parse(dr["co"].ToString()); 
  91.             MyCmd.Connection.Close(); 
  92.             return RecordCount; 
  93.         } 
  94.         //数据库连接语句（从Web.Config中获取） 
  95.         public static SqlConnection MyCon() 
  96.         { 
  97.             SqlConnection MyConnection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]); 
  98.             MyConnection.Open(); 
  99.             return MyConnection; 
  100.         } 
  101.         //对四个按钮（首页、上一页、下一页、尾页）返回的CommandName值进行操作 
  102.         private void Page_OnClick(object sender, CommandEventArgs e) 
  103.         { 
  104.             //从ViewState中读取页码值保存到CurrentPage变量中进行参数运算 
  105.             CurrentPage = (int)ViewState["PageIndex"]; 
  106.             //从ViewState中读取总页参数运算 
  107.             Pages = (int)ViewState["PageCounts"]; 
  108.             string cmd = e.CommandName; 
  109.             //筛选CommandName 
  110.             switch (cmd) 
  111.             { 
  112.                 case "next": 
  113.                     CurrentPage++; 
  114.                     break; 
  115.                 case "prev": 
  116.                     CurrentPage--; 
  117.                     break; 
  118.                 case "last": 
  119.                     CurrentPage = Pages; 
  120.                     break; 
  121.                 default: 
  122.                     CurrentPage = 0; 
  123.                     break; 
  124.             } 
  125.             //将运算后的CurrentPage变量再次保存至ViewState 
  126.             ViewState["PageIndex"] = CurrentPage; 
  127.             //调用数据绑定函数TDataBind() 
  128.             TDataBind(); 
  129.         } 
  130.         private void TDataBind() 
  131.         { 
  132.             //从ViewState中读取页码值保存到CurrentPage变量中进行按钮失效运算 
  133.             CurrentPage = (int)ViewState["PageIndex"]; 
  134.             //从ViewState中读取总页参数进行按钮失效运算 
  135.             Pages = (int)ViewState["PageCounts"]; 
  136.             //判断四个按钮（首页、上一页、下一页、尾页）状态 
  137.             if (CurrentPage + 1 > 1) 
  138.             { 
  139.                 Fistpage.Enabled = true; 
  140.                 Prevpage.Enabled = true; 
  141.             } 
  142.             else 
  143.             { 
  144.                 Fistpage.Enabled = false; 
  145.                 Prevpage.Enabled = false; 
  146.             } 
  147.             if (CurrentPage == Pages) 
  148.             { 
  149.                 Nextpage.Enabled = false; 
  150.                 Lastpage.Enabled = false; 
  151.             } 
  152.             else 
  153.             { 
  154.                 Nextpage.Enabled = true; 
  155.                 Lastpage.Enabled = true; 
  156.             } 
  157.             //数据绑定到DataList控件 
  158.             DataSet ds = new DataSet(); 
  159.             //核心SQL语句，进行查询运算（决定了分页的效率:)） 
  160.             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()); 
  161.             MyAdapter.Fill(ds, "news"); 
  162.             datalist1.DataSource = ds.Tables["news"].DefaultView; 
  163.             datalist1.DataBind(); 
  164.             //显示Label控件LCurrentPaget和文本框控件gotoPage状态 
  165.             LCurrentPage.Text = (CurrentPage + 1).ToString(); 
  166.             gotoPage.Text = (CurrentPage + 1).ToString(); 
  167.             //释放SqlDataAdapter 
  168.             MyAdapter.Dispose(); 
  169.         } 
  170.         #region Web 窗体设计器生成的代码 
  171.         override protected void OnInit(EventArgs e) 
  172.         { 
  173.             // 
  174.             // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。 
  175.             // 
  176.             InitializeComponent(); 
  177.             base.OnInit(e); 
  178.         } 
  179.         /// <summary> 
  180.         /// 设计器支持所需的方法 - 不要使用代码编辑器修改 
  181.         /// 此方法的内容。 
  182.         /// </summary> 
  183.         private void InitializeComponent() 
  184.         { 
  185.             this.Fistpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick); 
  186.             this.Prevpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick); 
  187.             this.Nextpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick); 
  188.             this.Lastpage.Command += new System.Web.UI.WebControls.CommandEventHandler(this.Page_OnClick); 
  189.             this.gotoPage.TextChanged += new System.EventHandler(this.gotoPage_TextChanged); 
  190.             this.Load += new System.EventHandler(this.Page_Load); 
  191.         } 
  192.         #endregion 
  193.         //跳页代码 
  194.         private void gotoPage_TextChanged(object sender, System.EventArgs e) 
  195.         { 
  196.             try 
  197.             { 
  198.                 //从ViewState中读取可用页数值保存到JumpPage变量中 
  199.                 JumpPage = (int)ViewState["JumpPages"]; 
  200.                 //判断用户输入值是否超过可用页数范围值 
  201.                 if (Int32.Parse(gotoPage.Text) > JumpPage || Int32.Parse(gotoPage.Text) <= 0) 
  202.                 { 
  203.                     Response.Write("<script>alert("页码范围越界！");location.href="WebForm8.aspx"</script>"); 
  204.                 } 
  205.                 else 
  206.                 { 
  207.                     //转换用户输入值保存在int型InputPage变量中 
  208.                     int InputPage = Int32.Parse(gotoPage.Text.ToString()) - 1; 
  209.                     //写入InputPage值到ViewState["PageIndex"]中 
  210.                     ViewState["PageIndex"] = InputPage; 
  211.                     //调用数据绑定函数TDataBind()再次进行数据绑定运算 
  212.                     TDataBind(); 
  213.                 } 
  214.             } 
  215.             //捕获由用户输入不正确数据类型时造成的异常 
  216.             catch (Exception eXP) 
  217.             { 
  218.                 Response.Write("<script>alert("" + exp.Message + "");location.href="WebForm8.aspx"</script>"); 
  219.             } 
  220.         } 
  221.     } 
  222. } 
posted on 2012-10-09 08:55  王玉涛  阅读(4529)  评论(0)  编辑  收藏  举报
Powered by:
博客园
Copyright © 2024 王玉涛
Powered by .NET 9.0 on Kubernetes