AspNetPager+ROW_NUMBER()的分页存储过程【百万数据】

最新推荐文章于 2021-02-05 03:50:50 发布

原创最新推荐文章于 2021-02-05 03:50:50 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #sql #table #string #output #textbox

DOTNET DEMO 同时被 2 个专栏收录

66 篇文章

订阅专栏

操作基类

20 篇文章

订阅专栏

本文介绍了一种基于ROW_NUMBER()函数的SQL分页存储过程实现方法，适用于SQL Server 2005及以上版本。该方法通过定义存储过程并传入表名、排序字段等参数来实现高效的数据分页查询。

测试的时候使用了200W条数据，速度还是很快的，相对来说比top not in 和top max id速度快点把

ROW_NUMBER()分页存储过程-SQL2005才支持

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[PagePro] @table varchar(100), --要分页的表 @strcondition varchar(500), --约束条件 @orderkey varchar(20), --排序的依据字段 @strorder varchar(4), --排序的类型,desc是倒序,asc是正序） @pageindex int=1, --查询页 @pagesize int=10, --每页显示数目 @totalcount int=0 output --查询总数 AS SET NOCOUNT ON declare @SQL varchar(1000) --执行分页语句1 declare @SQL1 varchar(1000) --执行分页语句2 declare @SQLCount nvarchar(4000) --查询总数语句 declare @start as int --查询开始位置 declare @end as int --查询结束位置 BEGIN --查询总数 set @SQLCount ='select @totalcount=count(*) from (select *,ROW_NUMBER() OVER(order by ' + @orderkey+' '+ @strorder+') as tempId from '+@table+') as tmp where '+@strcondition execute sp_executesql @SQLCount,N'@totalcount int output',@totalcount output --找到查询的开始位置 set @start=(@pageindex-1) * @pagesize + 1 --找到查询的结束位置 set @end=@pageindex * @pagesize --,ROW_NUMBER() OVER(order by ' + @orderkey+' '+ @strorder+') as tempId --执行分页 set @SQL ='select *,ROW_NUMBER() OVER(order by ' + @orderkey+' '+ @strorder+') as tempId from '+@table+' where ' +@strcondition set @SQL1 ='select * from ('+@SQL+') as temp2 where tempId between '+Convert(nvarchar,@start)+' and '+Convert (nvarchar,@end) execute(@SQL1) print @SQL1 END

前台代码

<table cellpadding="0" cellspacing="1"> <asp:Repeater ID="Repeater1" runat="server"> <ItemTemplate> <tr style='background-color:<%#(Container.ItemIndex%2==0)?"#fff":"#eee"%>' class="meet_tr" onmouseover="fOver(this);" onmouseout="fOut(this);"> <td style="height:30px;"><%#Eval("id")%></td> <td style="height:30px;"><%#Eval("userName")%></td> <td style="height:30px;"><%#Eval("userPWD")%></td> <td style="height:30px;"><%#Eval("userEmail")%></td> </tr> </ItemTemplate> </asp:Repeater> <tr> <td> <webdiyer:AspNetPager ID="AspNetPager1" runat="server" CurrentPageButtonClass="current" CurrentPageButtonPosition="Center" CurrentPageButtonStyle="font-color:blue" CustomInfoHTML="每页%PageSize%条记录总共%RecordCount%条记录页码：%CurrentPageIndex%/%PageCount%" FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" NumericButtonCount="8" onpagechanging="AspNetPager1_PageChanging" PageIndexBoxType="TextBox" PrevPageText="上一页" ShowCustomInfoSection="Left" ShowMoreButtons="False" ShowPageIndex="False" ShowPageIndexBox="Always" SubmitButtonText="跳转" TextAfterPageIndexBox="页" TextBeforePageIndexBox=""> </webdiyer:AspNetPager> </td> </tr> </table>

后台代码

protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { GetPage(); } } public void GetPage() { int count; Repeater1.DataSource = Pager("tb_TestTable", "id", "1=1", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, out count, "desc"); Repeater1.DataBind(); AspNetPager1.RecordCount = count; //这个也是必须的 } //翻页事件 protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e) { AspNetPager1.CurrentPageIndex = e.NewPageIndex; //设置当前的页码 GetPage(); //重新分页 } public static DataTable Pager(string tablename, string orderkey, string strcondition, int pageIndex, int pageSize, out int count, string strorder) { DataTable dt = new DataTable(); //构造存储过程page的参数 count = 0; SqlParameter[] paras = new SqlParameter[]{ new SqlParameter("@table",tablename), new SqlParameter("@pageIndex",pageIndex), new SqlParameter("@pageSize",pageSize), new SqlParameter("@strcondition",strcondition), new SqlParameter("@orderkey",orderkey), new SqlParameter("@totalcount",count), new SqlParameter("@strorder",strorder), }; paras[5].Direction = ParameterDirection.Output; //指定count为输出类型 //string connstr = ConfigurationManager.ConnectionStrings["ConnectionStringZSCX"].ConnectionString; //连接字符串，需要在数据库中配置 //SqlConnection conn = new SqlConnection(connstr); //conn.Open(); //SqlCommand cmd = new SqlCommand("PagePro", conn); //cmd.CommandType = CommandType.StoredProcedure; //cmd.Parameters.AddRange(paras); //SqlDataReader sdr; //using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) //{ // dt.Load(sdr); //} SqlDataReader sdr = SqlHelper.RunProcedure("PagePro", paras); dt.Load(sdr); count = Convert.ToInt32(paras[5].Value); return dt; }