千万数量级分页存储过程 +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 = ""; }