分享我的百万级数据查询分页
有什么不好的地方欢迎指点，主要运用sql2005的ROW_NUMBER()
分页其实很简单，只要把页参数传递到以下SQL语句中即可。
where number between cast('" & PageSize & "' as int) *(cast('" & CurrentPage & "' as int)-1)+1 and cast('" & PageSize & "' as int)*cast('" & CurrentPage & "' as int)")
如果不懂vb.net可以到这个网页把代码转换为c# http://www.developerfusion.com/tools/convert/vb-to-csharp/
前台用 gridview repeater datalist 都是一样的。
前台
<asp:ScriptManager ID="ScriptManager1" runat="server"> </asp:ScriptManager> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:repeater id="Repeater1" runat="server"> <HeaderTemplate> <table id="tb01" width="260%" border="0" cellspacing="1" cellpadding="2" bgcolor="#dddddd"> <tbody> <tr style="background-color:#ffffff"> <td class="trTitle" style="width:80px;background-image:url(images/trbg02.gif);background-color:#ffffff;height:23px;" >編號</td> <td class="trTitle" style="width:80px;background-image:url(images/trbg02.gif);background-color:#ffffff;height:23px;">姓名</td> <td class="trTitle" style="width:150px;background-image:url(images/trbg02.gif);background-color:#ffffff;height:23px;">名稱</td> </tr> </HeaderTemplate> <ItemTemplate> <tr style="background-color:#ffffff"> <td height="20px" align="left"> <a style="cursor:hand" href="<%= strLinkManitCrsCat %>&recid=<%# Container.DataItem("rec_id") %>&mode=1" title="編輯/刪除"><strong><asp:label Font-Names="Tahoma" id="lblClassNum" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_id") %>'> </asp:label></strong></a> </td> <td align="left"> <asp:label id="Label4" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_chn_nam") %>'> </asp:label> </td> <td align="left"> <asp:label id="lblCrsCatDesc" Width="150px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"crs_num") %>'> </asp:label> </td> </tr> </ItemTemplate> <AlternatingItemTemplate> <tr bgcolor="#f9f9f9"> <td height="20px" align="left"> <a style="cursor:hand" href="<%= strLinkManitCrsCat %>&recid=<%# Container.DataItem("rec_id") %>&mode=1" title="編輯/刪除"><strong><asp:label Font-Names="Tahoma" id="lblClassNum" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_id") %>'> </asp:label></strong></a> </td> <td align="left"> <asp:label id="Label4" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_chn_nam") %>'> </asp:label> </td> <td align="left"> <asp:label id="lblCrsCatDesc" Width="150px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"crs_num") %>'> </asp:label> </td> <td align="left"> <asp:label id="Label8" Width="60px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"cls_num") %>'> </asp:label> </td> </tr> </AlternatingItemTemplate> <FooterTemplate> <tr bgcolor="#f2f2f2"> <td height="8" colspan="22" align="center"></td> </tr> </tbody></table> </FooterTemplate> </asp:repeater> <div style="PADDING-TOP:10px" align="center"> 共<asp:label id="LPageCount" ForeColor="#ff0000" Runat="server"></asp:label>頁 <asp:label id="LTotalCount" ForeColor="#ff0000" Runat="server"></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" ForeColor="#ff0000" Runat="server"></asp:label>頁 轉到第 <asp:textbox id="gotoPage" Width="30px" Runat="server" AutoPostBack="True" MaxLength="5" ></asp:textbox>頁 <asp:Label style=" POSITION: absolute" id="msgbox" runat="server" ForeColor="Red" BorderColor="Red"></asp:Label> </div> </ContentTemplate> </asp:UpdatePanel> <input type="hidden" id="sortfield" runat="server" name="sortfield"/> <input type="hidden" id="sortstring" runat="server" name="sortstring"/> </form> </body></html>
if (cur > count || cur < 1)
{
alert('Input page no out of range.');
event.cancelPostBack=true;
return false;
}
}
</script>
<table class="tablepagefoot" align="right">
<tr>
<td>
页次 <asp:literal id="lipagenum" Runat="server" />/<asp:literal id="lipageallnum" Runat="server" />
每页<asp:literal id="lipagesize" Runat="server" />条 共<asp:literal id="lirownum" Runat="server" />条
<asp:LinkButton id="btnFirstPage" runat="server" CausesValidation="False" CommandArgument="First">首页</asp:LinkButton>
<asp:LinkButton id="btnPrevPage" runat="server" CausesValidation="False" CommandArgument="Prev">上一页</asp:LinkButton>
<asp:LinkButton id="btnNextPage" runat="server" CausesValidation="False" CommandArgument="Next">下一页</asp:LinkButton>
<asp:LinkButton id="btnLastPage" runat="server" CausesValidation="False" CommandArgument="Last">尾页</asp:LinkButton> <ASP:TEXTBOX ID="txtCurrentPage" RUNAT="server" MAXLENGTH="5" Width="35px">0</ASP:TEXTBOX>
<input type="hidden" runat="server" id="lblTotal" />
<ASP:BUTTON ID="btnGo" RUNAT="server" SkinId="btngoto" CausesValidation="False" COMMANDARGUMENT="Go" ToolTip="转到"></ASP:BUTTON> </td>
</tr>
</table>
[解决办法]
Pager.ascx.cs
namespace Troika.Ascx
{
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
//using Service;
/// <summary>
///page 的摘要说明。
/// </summary>
public partial class Pager : System.Web.UI.UserControl
{
int size=10;//可以在web.config中配置
public event System.EventHandler NavigationClick;
protected void Page_Load(object sender, System.EventArgs e)
{
this.txtCurrentPage.Attributes.Add("onkeypress","callButtonEvent();");
this.btnGo.Attributes.Add("onclick","check();");
if(!this.IsPostBack)
{
SetStyle();
SetEnable();
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
this.btnFirstPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnPrevPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnNextPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnLastPage.Click += new System.EventHandler(this.btnGo_Click);
this.btnGo.Click += new System.EventHandler(this.btnGo_Click);
}
/// <summary>
///Required method for Designer support - do not modify
///the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
#endregion
#region btnGo_Click
public void btnGo_Click(object sender, System.EventArgs e)
{
LinkButton linkbtn = sender as LinkButton;
if( null == linkbtn )//button
{
Button btn = sender as Button;
if( null == btn )
{
return;
}
else
{
int selPage = -1;
try
{
selPage =Int32.Parse(txtCurrentPage.Text);
}
catch
{
selPage = -1;
}
if (selPage > 0 && selPage <= PageCount)
{
ViewState["CurrentPageIndex"] = selPage;
}
else
{
return;
}
}
}
else//linkbutton
{
switch ( linkbtn.CommandArgument.Trim() )
{
case "First":
ViewState["CurrentPageIndex"] = 1;
break;
case "Prev":
ViewState["CurrentPageIndex"] = (CurrentPageIndex > 1) ? CurrentPageIndex - 1 : 1;
break;
case "Next":
ViewState["CurrentPageIndex"] = (PageCount > CurrentPageIndex) ? CurrentPageIndex + 1 : PageCount;
break;
case "Last":
ViewState["CurrentPageIndex"] = PageCount;
break;
default:
break;
}
}
SetEnable();//设置显示样式
if (NavigationClick!=null)//调用事件
{
NavigationClick( sender,e );
}
}
#endregion
#region SetStyle
private void SetStyle()
{
this.btnFirstPage.Attributes["style"] = "CURSOR: hand";
this.btnLastPage.Attributes["style"] = "CURSOR: hand";
this.btnNextPage.Attributes["style"] = "CURSOR: hand";
this.btnPrevPage.Attributes["style"] = "CURSOR: hand";
}
#endregion
#region SetEnable
// 应根据当前的CurrentPageIndex和pageCount设定哪些按钮可用
public void SetEnable()
{
this.lblTotal.Value = PageCount.ToString();
txtCurrentPage.Text =CurrentPageIndex.ToString();
btnPrevPage.Enabled = false;
btnNextPage.Enabled = false;
if( PageCount >1 )
{
btnFirstPage.Enabled = btnPrevPage.Enabled = ( CurrentPageIndex >1 );
btnNextPage.Enabled = btnLastPage.Enabled = ( CurrentPageIndex < PageCount );
}
else
{
btnFirstPage.Enabled = false;
btnLastPage.Enabled = false;
btnPrevPage.Enabled = false;
btnNextPage.Enabled = false;
}
lipagenum.Text = CurrentPageIndex.ToString();
lipageallnum.Text = PageCount.ToString();
lipagesize.Text = PageSize.ToString();
lirownum.Text = RecordCount.ToString();
}
#endregion
#region Property
//获取或设置当前显示页的索引。
public int CurrentPageIndex
{
get
{
object cpage=ViewState["CurrentPageIndex"];
int pindex=(cpage==null)?1:(int)cpage;
//if(pindex>PageCount&&PageCount>0)
// return PageCount;
//else if(pindex<1)
// return 1;
return pindex;
}
set
{
int cpage=value;
if(cpage<1)
cpage=1;
//else if(cpage>this.PageCount)
//cpage=this.PageCount;
ViewState["CurrentPageIndex"]=cpage;
}
}
// 获取或设置需要分页的所有记录的总数。
public int RecordCount
{
get
{
object obj=ViewState["Recordcount"];
return (obj==null)?0:(int)obj;
}
set
{
ViewState["Recordcount"]=value;
SetEnable();
}
}
//获取当前页之后的页的总数。
public int PagesRemain
{
get
{
return PageCount-CurrentPageIndex;
}
}
// 获取或设置每页显示的项数。
public int PageSize
{
get
{
object obj=ViewState["PageSize"];
if (obj==null)
{
obj= size;
}
return (obj==null)?size:(int)obj;
}
set
{
int pageSize = value;
if (Math.Abs(pageSize) == 0)
pageSize = size;
ViewState["PageSize"]=pageSize;
}
}
// 获取在当前页之后还未显示的剩余记录的项数。
public int RecordsRemain
{
get
{
if(CurrentPageIndex<PageCount)
{
return RecordCount-(CurrentPageIndex*PageSize);
}
else
{
return 0;
}
}
}
// 获取所有要分页的记录需要的总页数。
public int PageCount
{
get{return (RecordCount > 0) ? (int)Math.Ceiling((double)RecordCount/(double)PageSize) : 1;}
}
public int XRecord
{
get
{
return int.Parse( System.Configuration.ConfigurationSettings.AppSettings["XRecord"].Trim() );
}
}
#endregion Property
}
}
[解决办法]
坚决收藏~决不手软
[解决办法]
学习学习
[解决办法]
打个标记先，再研究！ 呵呵
[解决办法]
太NB了吧，我日了，我以前还是去找了一个存储过程，试一下这个。
[解决办法]
用between效果很高吗?