千万数量级分页存储过程 +AspNetPager现实分页
存储过程
USE [ForeignTradeDB] GO /****** Object: StoredProcedure [dbo].[CommonGetDataPager] Script Date: 2015/3/26 17:47:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[CommonGetDataPager] /* *************************************************************** ** 千万数量级分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字 3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc 4.CurrentPage :当前页码 5.PageSize :分页尺寸 6.Filter :过滤语句,不带Where 7.Group :Group语句,不带Group By update by Eraker 2011/11/15 ***************************************************************/ ( @Tables varchar(2000), @PrimaryKey varchar(100), @Sort varchar(300) = NULL, @CurrentPage int = 1, @PageSize int = 10, @Fields varchar(1000) = '*', @Filter varchar(8000) = NULL, @Group varchar(1000) = NULL ) AS /* 测试参数 DECLARE @return_value int EXEC @return_value = [dbo].[CommonGetDataPager] @Tables = N'xfq_CustomerVisits left join xfq_M_MemberPoints on xfq_CustomerVisits.MemberLogin=xfq_M_MemberPoints.MemberLogin', @PrimaryKey = N'xfq_CustomerVisits.ID', @Sort = N'xfq_CustomerVisits.ExtendField2 desc ', @CurrentPage =4, @PageSize = 10, @Fields = N' xfq_CustomerVisits.ID,xfq_CustomerVisits.Memberlogin,xfq_CustomerVisits.Name,xfq_CustomerVisits.Mobile,xfq_CustomerVisits.ExtendField2,xfq_CustomerVisits.ExtendField7', @Filter = N'xfq_CustomerVisits.ExtendField1<>2', @Group = NULL */ /*默认排序*/ IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @SortName2 varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int /*设定排序语句.*/ IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') --SET @operator = '<=' END ELSE BEGIN IF CHARINDEX('ASC', @Sort) = 0 SET @strSortColumn = REPLACE(@Sort, 'ASC', '') --SET @operator = '>=' END set @SortName2=0 IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) -- IF CHARINDEX(',', @strSortColumn) > 0 -- begin -- SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, CHARINDEX(',', @strSortColumn)-CHARINDEX('.',@strSortColumn) - 1) -- set @sortname2=SUBSTRING(@strSortColumn, CHARINDEX(',',@strSortColumn) +len(@SortTable)+ 2, LEN(@strSortColumn)) -- end --else -- begin -- SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) -- end END ELSE BEGIN SET @SortTable = @Tables --SET @SortName = @strSortColumn END --SELECT @type=t.name, @prec=c.prec --FROM sysobjects o --JOIN syscolumns c on o.id=c.id --JOIN systypes t on c.xusertype=t.xusertype --WHERE o.name = @SortTable AND c.name in (@SortName,@SortName2) --IF CHARINDEX('char', @type) > 0 -- SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize int DECLARE @strStartRow int DECLARE @strFilter varchar(1000) DECLARE @strSimpleFilter varchar(1000) DECLARE @strGroup varchar(1000) /*默认当前页*/ IF @CurrentPage < 1 SET @CurrentPage = 1 /*设置分页参数.*/ SET @strPageSize = CAST(@PageSize AS int) SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS int) /*筛选以及分组语句.*/ IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' WHERE ' + @Filter + ' ' --SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE SET @strGroup = '' /*执行查询语句*/ --declare @strSQL varchar(8000) --set @strSQL='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+') AS ROWID,' --set @strSQL=@strSQL+@Fields+' FROM '+@Tables+@strFilter+' '+ @strGroup --set @strSQL=@strSQL+') AS sp WHERE ROWID BETWEEN '+str((@CurrentPage-1)*@strPageSize+1) --set @strSQL=@strSQL+' AND '+str(@strStartRow+@strPageSize-1) --exec (@strSQL) declare @strSQL varchar(8000) set @strSQL='WITH TB1 AS (' set @strSQL=@strSQL+'SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+') AS ROWID,' set @strSQL=@strSQL+@Fields+' FROM '+@Tables+@strFilter+' '+ @strGroup set @strSQL=@strSQL+'), TB2 AS ( SELECT COUNT(1) AS ROWS FROM TB1 ) SELECT * FROM TB1,TB2 ' SET @strSQL=@strSQL+'WHERE ROWID BETWEEN '+str((@CurrentPage-1)*@strPageSize+1) set @strSQL=@strSQL+' AND '+str(@strStartRow+@strPageSize-1) exec (@strSQL)
后台代码
DataTable dt = new Product_Bll().CommonGetDataPager(表名称,视图, 关键字段, 排序字段, AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, 需要获取的字段, 条件,Group语句
); AspNetPager1.RecordCount = dt==null?0:Convert.ToInt32(dt.Rows[0]["ROWS"]);//AspNetPager插件
rpt_Pro_Class.DataSource = dt;
rpt_Pro_Class.DataBind();
if (dt == null) {
this.lbl_No_Record.Text = "<tr><td height=30 colspan=10>没有找到任何数据!</td></tr>"; }
else { this.lbl_No_Record.Text = ""; }