Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/1/30 1:09:41 千万级数据分页之二---一个简单的自定义分页控件_[Asp.Net教程]
千万级数据分页详细设计1.引言1.1目的为适应大数据量分页的需要,为以后千万级数据分页提供解决方法或者参考,节省开发时间,特制定本详细设计方案1.2主要阅读对象脚本设计人员1.3参考资料http://www.cnblogs.com/SGSoft/archive/2004/10/23/55800.html2.详细设计2.1分页存储过程2.1.1简介2.1.2分页存储过程代码以下代码是网上找的分页存储过程,我是在原存储过程的基础上加了一个@IsCount bit = 0, 主要是用来返回纪录总数,当为非0值时返回。下面注释部分是原作者的测试部分。我在本机sql server 2005上的测试是在10000011纪录中查询第100000页,每页10条纪录按升序和降序时间均为0.38秒,测试语法如下:exec GetRecordFromPage tbl_Briefness,I_BriefnessID,10,100000,其中在tbl_Briefness表I_BriefnessID字段上建立了索引。/**//* 经测试,在14483461 条记录中查询第100000 页,每页10 条记录按升序和降序第一次时间均为0.47 秒,第二次时间均为0.43 秒,测试语法如下: exec GetRecordFromPage news,newsid,10,100000 news 为表名, newsid 为关键字段, 使用时请先对newsid 建立索引。
函数名称: GetRecordFromPage 函数功能: 获取指定页的数据 参数说明: @tblName 包含数据的表名 @fldName 关键字段名 @PageSize 每页记录数 @PageIndex 要获取的页码 @OrderType 排序类型, 0 - 升序, 1 - 降序 @strWhere 查询条件(注意: 不要加where) 创建时间: 2004-07-04 修改时间: 2008-02-13*/ALTER PROCEDURE [dbo].[GetRecordFromPage] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @OrderType bit = 0, -- 设置排序类型, 非0 值则降序 @IsCount bit = 0, -- 返回记录总数, 非0 值则返回 @strWhere varchar(2000) = '' -- 查询条件(注意: 不要加where) AS
declare @strSQL varchar(6000) -- 主语句declare @strTmp varchar(1000) -- 临时变量declare @strOrder varchar(500) -- 排序类型
if @OrderType != 0begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName + '] desc'endelsebegin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc'end
set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrderendif @IsCount != 0 set @strSQL = 'select count(' + @fldName + ') as Total from [' + @tblName + ']' exec (@strSQL)
2.2分页控件的实现2.2.1分页控件的详细代码using System;using System.Collections.Generic;using System.Text;using System.ComponentModel;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;
namespace CustomControls{ [ToolboxData("<{0}:AspNetPager runat='server' PageSize='25' FirstPageText='首页' PrePageText='上一页' NextPageText='下一页' EndPageText='末页' ButtonText='GO'>{0}:AspNetPager>")] public class AspNetPager : WebControl, INamingContainer { 属性块#region 属性块 private object baseState = null; private object buttonStyleState = null; private object textBoxStyleState = null; private object labelStyleState = null; private object linkButtonStyleState = null; private LinkButton _lnkbtnFrist; private LinkButton _lnkbtnPre; private LinkButton _lnkbtnNext; private LinkButton _lnkbtnLast; private Label _lblCurrentPage; private Label _lblRecodeCount; private Label _lblPageCount; private Label _lblPageSize; private TextBox _txtPageIndex; private Button _btnChangePage; private static readonly object EventPageChange = new object(); [Category("Pagination"), Description("每页显示的纪录数"), DefaultValue("25")] public virtual int PageSize { get { EnsureChildControls(); return _lblPageSize.Text.Trim() != "" ? int.Parse(_lblPageSize.Text.Trim()) : 25; } set { EnsureChildControls(); _lblPageSize.Text = value.ToString(); } } [Category("Pagination"), Description("总纪录数"), DefaultValue("0"), Bindable(true)] public virtual int RecordCount { get { EnsureChildControls(); return _lblRecodeCount.Text.Trim() != "" ? int.Parse(_lblRecodeCount.Text.Trim()) : 0; } set { EnsureChildControls(); if (value > 0) { int recodeCount = value; _lblPageCount.Text = (value % PageSize == 0 ? value / PageSize : value / PageSize + 1).ToString();//计算总页数 } _lblRecodeCount.Text = value.ToString(); } } [Category("Pagination"), Description("当前页码"), DefaultValue("1"), Bindable(true)] public virtual int PageIndex { get { EnsureChildControls(); return _lblCurrentPage.Text.Trim() != "" ? int.Parse(_lblCurrentPage.Text.Trim()) : 1; } set { EnsureChildControls(); _lblCurrentPage.Text = value.ToString(); } } [Category("Appearance"), Description("设置第一页的文本"), DefaultValue("首页"), Bindable(true)] public virtual string FirstPageText { get { EnsureChildControls(); return _lnkbtnFrist.Text.Trim() != "" ? _lnkbtnFrist.Text.Trim() : "首页"; } set { EnsureChildControls(); _lnkbtnFrist.Text = value; } } [Category("Appearance"), Description("设置上一页的文本"), DefaultValue("上一页"), Bindable(true)] public virtual string PrePageText { get { EnsureChildControls(); return _lnkbtnPre.Text.Trim() != "" ? _lnkbtnPre.Text.Trim() : "上一页"; } set { EnsureChildControls(); _lnkbtnPre.Text = value; } } [Category("Appearance"), Description("设置下一页的文本"), DefaultValue("下一页"), Bindable(true)] public virtual string NextPageText { get { EnsureChildControls(); return _lnkbtnNext.Text.Trim() != "" ? _lnkbtnNext.Text.Trim() : "下一页"; } set { EnsureChildControls(); _lnkbtnNext.Text = value; } } [Category("Appearance"), Description("设置末页的文本"), DefaultValue("末页"), Bindable(true)] public virtual string EndPageText { get { EnsureChildControls(); return _lnkbtnLast.Text.Trim() != "" ? _lnkbtnLast.Text.Trim() : "末页"; } set { EnsureChildControls(); _lnkbtnLast.Text = value; } } [Category("Appearance"), Description("设置跳转按钮的文本"), DefaultValue(":"), Bindable(true)] public virtual string ButtonText { get { EnsureChildControls(); return _btnChangePage.Text.Trim() != "" ? _btnChangePage.Text.Trim() : "GO"; } set { EnsureChildControls(); _btnChangePage.Text = value; } } #endregion
分页事件相关#region 分页事件相关 public event EventHandler PageChanged { add { Events.AddHandler(EventPageChange, value); } remove { Events.RemoveHandler(EventPageChange, value); }
} protected void OnPageChanged(EventArgs e) { EventHandler handler = (EventHandler)Events[EventPageChange]; if (handler != null) { handler(this, e); } } #endregion
样式属性#region 样式属性 private Style _buttonStyle; private Style _textBoxStyle; private Style _linkButtonStyle; [ Category("Styles"), DefaultValue(null), DesignerSerializationVisibility( DesignerSerializationVisibility.Content), PersistenceMode(PersistenceMode.InnerProperty), Description( "应用于按钮的样式") ] public virtual Style ButtonStyle { get { if (_buttonStyle == null) { _buttonStyle = new Style(); if (IsTrackingViewState) { ((IStateManager)_buttonStyle).TrackViewState(); } } return _buttonStyle; } } [ Category("Styles"), DefaultValue(null), DesignerSerializationVisibility( DesignerSerializationVisibility.Content), PersistenceMode(PersistenceMode.InnerProperty), Description( "应用于链接按钮的样式") ] public virtual Style LinkButtonStyle { get { if (_linkButtonStyle == null) { _linkButtonStyle = new Style(); if (IsTrackingViewState) { ((IStateManager)_linkButtonStyle).TrackViewState(); } } return _linkButtonStyle; } }
[ Category("Styles"), DefaultValue(null), DesignerSerializationVisibility( DesignerSerializationVisibility.Content), PersistenceMode(PersistenceMode.InnerProperty), Description( "应用于文本框的样式") ] public virtual Style TextBoxStyle { get { if (_textBoxStyle == null) { _textBoxStyle = new Style(); if (IsTrackingViewState) { ((IStateManager)_textBoxStyle).TrackViewState(); } } return _textBoxStyle; } } private Style _labelStyle; [ Category("Styles"), DefaultValue(null), DesignerSerializationVisibility( DesignerSerializationVisibility.Content), PersistenceMode(PersistenceMode.InnerProperty), Description( "应用于标签的样式") ] public virtual Style LabelStyle { get { if (_labelStyle == null) { _labelStyle = new Style(); if (IsTrackingViewState) { ((IStateManager)_labelStyle).TrackViewState(); } } return _labelStyle; } } #endregion
自定义视图状态#region 自定义视图状态 protected override void LoadViewState(object savedState) { if (savedState == null) { base.LoadViewState(null); return; } else { Triplet t = savedState as Triplet;
if (t != null) { base.LoadViewState(baseState);
if ((t.Second) != null) { ((IStateManager)ButtonStyle).LoadViewState(buttonStyleState); }
if ((t.Third) != null) { ((IStateManager)TextBoxStyle).LoadViewState(textBoxStyleState); } if (labelStyleState != null) { ((IStateManager)(_labelStyle)).LoadViewState(labelStyleState); } if (linkButtonStyleState != null) { ((IStateManager)(_linkButtonStyle)).LoadViewState(linkButtonStyleState); } } else { throw new ArgumentException("Invalid view state ."); } } }
protected override object SaveViewState() { baseState = base.SaveViewState(); buttonStyleState = null; textBoxStyleState = null; labelStyleState = null; linkButtonStyleState = null; if (_buttonStyle != null) { buttonStyleState = ((IStateManager)_buttonStyle).SaveViewState(); }
if (_textBoxStyle != null) { textBoxStyleState = ((IStateManager)_textBoxStyle).SaveViewState(); } if (_labelStyle != null) { labelStyleState = ((IStateManager)_labelStyle).SaveViewState(); } if (_linkButtonStyle != null) { linkButtonStyleState = ((IStateManager)_linkButtonStyle).SaveViewState(); } return new Triplet(baseState, buttonStyleState, textBoxStyleState);
}
protected override void TrackViewState() { base.TrackViewState(); if (_buttonStyle != null) { ((IStateManager)_buttonStyle).TrackViewState(); } if (_textBoxStyle != null) { ((IStateManager)_textBoxStyle).TrackViewState(); } if (_labelStyle != null) { ((IStateManager)_labelStyle).TrackViewState(); } if (_linkButtonStyle != null) { ((IStateManager)_linkButtonStyle).TrackViewState(); } } #endregion
生成控件#region 生成控件 protected override void CreateChildControls() { this.Controls.Clear(); _btnChangePage = new Button(); _btnChangePage.ID = "btnChangePage"; _btnChangePage.Click += new EventHandler(BtnChangePage_Click); _lblCurrentPage = new Label(); _lblCurrentPage.ID = "lblCurrentPage"; _lblPageCount = new Label(); _lblPageCount.ID = "lblPageCount"; _lblRecodeCount = new Label(); _lblRecodeCount.ID = "lblRecodeCount"; _lnkbtnFrist = new LinkButton(); _lnkbtnFrist.ID = "lnkbtnFrist"; _lnkbtnFrist.Click += new EventHandler(lnkbtnFrist_Click); _lnkbtnLast = new LinkButton(); _lnkbtnLast.ID = "lnkbtnLast"; _lnkbtnLast.Click += new EventHandler(lnkbtnLast_Click); _lnkbtnNext = new LinkButton(); _lnkbtnNext.ID = "lnkbtnNext"; _lnkbtnNext.Click += new EventHandler(lnkbtnNext_Click); _lnkbtnPre = new LinkButton(); _lnkbtnPre.ID = "lnkbtnPre"; _lnkbtnPre.Click += new EventHandler(lnkbtnPre_Click); _txtPageIndex = new TextBox(); _txtPageIndex.ID = "txtPageIndex"; _lblPageSize = new Label(); _lblPageSize.ID = "lblPageSize"; this.Controls.Add(_btnChangePage); this.Controls.Add(_lblCurrentPage); this.Controls.Add(_lblPageCount); this.Controls.Add(_lblRecodeCount); this.Controls.Add(_lnkbtnFrist); this.Controls.Add(_lnkbtnLast); this.Controls.Add(_lnkbtnNext); this.Controls.Add(_lnkbtnPre); this.Controls.Add(_txtPageIndex); base.CreateChildControls(); } #endregion
按钮点击事件#region 按钮点击事件 翻页相关的事件#region 翻页相关的事件 /**//// /// 处理翻页事件 /// /// /// protected void lnkbtnFrist_Click(object sender, EventArgs e) //第一页 { _lblCurrentPage.Text = "1"; OnPageChanged(EventArgs.Empty); } protected void lnkbtnPre_Click(object sender, EventArgs e) //上一页 { int pageIndex = int.Parse(_lblCurrentPage.Text); if (pageIndex > 0) { pageIndex--; _lblCurrentPage.Text = pageIndex.ToString(); OnPageChanged(EventArgs.Empty); } } protected void lnkbtnNext_Click(object sender, EventArgs e)//下一页 { int pageIndex = int.Parse(_lblCurrentPage.Text); int pageCount = int.Parse(_lblPageCount.Text); if (pageIndex < pageCount) { pageIndex++; _lblCurrentPage.Text = pageIndex.ToString(); } OnPageChanged(EventArgs.Empty); } protected void lnkbtnLast_Click(object sender, EventArgs e)//末页 { _lblCurrentPage.Text = _lblPageCount.Text; OnPageChanged(EventArgs.Empty); } #endregion protected void BtnChangePage_Click(object sender, EventArgs e)//跳转到指定页 { int pageIndex=0; try { pageIndex = int.Parse(_txtPageIndex.Text); } catch { System.Web.HttpContext.Current.Response.Write(""); return; } int pageCount = int.Parse(_lblPageCount.Text); if (pageIndex == 0)//如果为0,则提示错误 { System.Web.HttpContext.Current.Response.Write(""); return; } if (pageIndex > pageCount)//如果大于总页数则提示错误 { System.Web.HttpContext.Current.Response.Write(""); return; } _lblCurrentPage.Text = pageIndex.ToString(); OnPageChanged(EventArgs.Empty); } #endregion
重写TagKey#region 重写TagKey protected override HtmlTextWriterTag TagKey { get { return HtmlTextWriterTag.Table; } } #endregion
绘制控件#region 绘制控件 protected override void RenderContents(HtmlTextWriter writer) { if (ButtonStyle != null) { _btnChangePage.ApplyStyle(ButtonStyle); } if (TextBoxStyle != null) { _txtPageIndex.ApplyStyle(TextBoxStyle); } if (LabelStyle != null) { _lblCurrentPage.ApplyStyle(LabelStyle); _lblPageCount.ApplyStyle(LabelStyle); _lblRecodeCount.ApplyStyle(LabelStyle); _lblPageSize.ApplyStyle(LabelStyle); } AddAttributesToRender(writer); writer.RenderBeginTag(HtmlTextWriterTag.Tr); writer.AddAttribute(HtmlTextWriterAttribute.Align, "right"); writer.RenderBeginTag(HtmlTextWriterTag.Td); writer.Write("当前第"); if (_lblCurrentPage != null) _lblCurrentPage.RenderControl(writer); writer.Write("页,每页"); if (_lblPageSize != null) { _lblPageSize.RenderControl(writer); } writer.Write("条纪录,总共"); if (_lblRecodeCount != null) _lblRecodeCount.RenderControl(writer); writer.Write("条纪录,共"); if (_lblPageCount != null) _lblPageCount.RenderControl(writer); writer.Write("页 [ "); if (_lnkbtnFrist != null) { if (PageIndex == 1) //如果是第一页,则第一页灰显,作用是避免不必要的点击造成没必要的数据传输 { _lnkbtnFrist.Enabled = false; } else { _lnkbtnFrist.Enabled = true; } _lnkbtnFrist.RenderControl(writer); } writer.Write(" "); if (_lnkbtnPre != null) { if (PageIndex > 1) //如果当前页大于1,则上一页显示,否则灰显 { _lnkbtnPre.Enabled = true; } else { _lnkbtnPre.Enabled = false; } _lnkbtnPre.RenderControl(writer); } writer.Write(" "); if (_lnkbtnNext != null) { if (_lblPageCount == null) { _lnkbtnNext.Enabled = false; } else { int pageCount = int.Parse(_lblPageCount.Text); //获取总页数 if (PageIndex < pageCount)//如果当前页小于总页数,则下一页显示,否则灰显 { _lnkbtnNext.Enabled = true; } else { _lnkbtnNext.Enabled = false; } } _lnkbtnNext.RenderControl(writer); } writer.Write(" "); if (_lnkbtnLast != null) { if (_lblPageCount == null) { _lnkbtnLast.Enabled = false; } else { int pageCount = int.Parse(_lblPageCount.Text); //获取总页数 if (PageIndex == pageCount)//如果当前页为最后一页,则末页灰显 { _lnkbtnLast.Enabled = false; } else { _lnkbtnLast.Enabled = true; } } _lnkbtnLast.RenderControl(writer); } writer.Write(" ]跳转到第"); if (_txtPageIndex != null) _txtPageIndex.RenderControl(writer); writer.Write("页"); if (_btnChangePage != null) _btnChangePage.RenderControl(writer); writer.RenderEndTag(); writer.RenderEndTag(); //base.RenderContents(writer); } #endregion }}
2.3千万级数据分页实现2.3.1简介这次分页我是用Gridview来实现的,测试时间没算,但基本上从10000011纪录中一次查询25条纪录,在10万页以内,时间花费 在1秒以内。使用其他控件比如DataGrid,DataList或者DataReapter应该花费的时间更短。
2.3.2适用对象服务器端控件Gridview,DataGrid,DataList,DataReapter等数据绑定控件
2.3.3分页实现分页效果图如下:
前台代码如下:
后台代码如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Demo.aspx.cs" Inherits="Demo" %>
<%@ Register Assembly="CustomControls" Namespace="CustomControls" TagPrefix="cc2" %>
<%@ Register Assembly="MyLabel" Namespace="MyLabel" TagPrefix="cc1" %>
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;
public partial class Demo : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { TestDataCount(); BindPaperDefineProgramme(1); } }
绑定试卷定义方案列表#region 绑定试卷定义方案列表 /**//// /// 统计该表的所有纪录 /// private void TestDataCount() { DataTable dt = null; try { //public static DataTable GetTestData(string tableName, int pageSize, int pageIndex,string columnName,bool isCount) //第一个参数为要查询的表,第二个参数为每页的纪录数,第三个为页码,这里初始化为第1页,第四个参数为表的字段 //,第五个参数为是否返回纪录总数,这里为true表示返回纪录总数 dt = ExecProc.GetTestData("tbl_Briefness", AspNetPager1.PageSize, 1, "I_BriefnessID", true); } catch (Exception ex) { Response.Write(ex.Message); return; } if (dt.Rows.Count != 0) { AspNetPager1.RecordCount = int.Parse(dt.Rows[0]["Total"].ToString()); AspNetPager1.PageIndex = 1;//初始化当前页为第一页 }
} #endregion
protected void Page_Changed(object sender, EventArgs e) { BindPaperDefineProgramme(AspNetPager1.PageIndex); }
删除纪录#region 删除纪录 /**//// /// 删除纪录 /// /// protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) {
if (e.CommandName == "Delete") { string pagerID = GridView1.DataKeys[Convert.ToInt32(e.CommandArgument)].Value.ToString(); int flag = 0; try { flag = ExecProc.DeleteData(int.Parse(pagerID)); } catch (Exception ex) { } if (flag != 0) { Response.Write(""); TestDataCount(); BindPaperDefineProgramme(1); } else { Response.Write(""); BindPaperDefineProgramme(AspNetPager1.PageIndex); } } } #endregion
绑定试卷定义方案列表#region 绑定试卷定义方案列表 /**//// /// 根据当前页码查询需要的数据 /// /// 页码 private void BindPaperDefineProgramme(int pageIndex) { DataTable dt = null; try { dt = ExecProc.GetTestData("tbl_Briefness", AspNetPager1.PageSize, pageIndex, "I_BriefnessID", false); //根据页码查询需要的纪录 } catch (Exception ex) { Response.Write(ex.Message); return; } if (dt.Rows.Count == 0) { GridView1.Visible = false; } else { GridView1.Visible = true; GridView1.DataSource = dt; GridView1.DataBind(); }
} #endregion}来源:csdn
赞