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(),

@PrimaryKey varchar(),

@Sort varchar() = NULL,

@CurrentPage int = ,

@PageSize int = ,

@Fields varchar() = '*',

@Filter varchar() = NULL,

@Group varchar() = 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()

DECLARE @SortName varchar()

DECLARE @SortName2 varchar()

DECLARE @strSortColumn varchar()

DECLARE @operator char()

DECLARE @type varchar()

DECLARE @prec int

/*设定排序语句.*/

IF CHARINDEX('DESC',@Sort)>

    BEGIN

        SET @strSortColumn = REPLACE(@Sort, 'DESC', '')

        --SET @operator = '<='

    END

ELSE

    BEGIN

        IF CHARINDEX('ASC', @Sort) =

            SET @strSortColumn = REPLACE(@Sort, 'ASC', '')

        --SET @operator = '>='

    END

set @SortName2=

IF CHARINDEX('.', @strSortColumn) >

    BEGIN

        SET @SortTable = SUBSTRING(@strSortColumn, , CHARINDEX('.',@strSortColumn))

  --      IF CHARINDEX(',', @strSortColumn) >

  --        begin

        --    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + , CHARINDEX(',', @strSortColumn)-CHARINDEX('.',@strSortColumn) - )

        --    set @sortname2=SUBSTRING(@strSortColumn, CHARINDEX(',',@strSortColumn) +len(@SortTable)+ , LEN(@strSortColumn))

        --  end

        --else

        --  begin

        --    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + , 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) >

--   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize int

DECLARE @strStartRow int

DECLARE @strFilter varchar()

DECLARE @strSimpleFilter varchar()

DECLARE @strGroup varchar()

/*默认当前页*/

IF @CurrentPage <

    SET @CurrentPage = 

/*设置分页参数.*/

SET @strPageSize = CAST(@PageSize AS int)

SET @strStartRow = CAST(((@CurrentPage - )*@PageSize + ) 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()

    --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-)*@strPageSize+)

    --set @strSQL=@strSQL+' AND '+str(@strStartRow+@strPageSize-)

    --exec (@strSQL)

    declare @strSQL varchar()

    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-)*@strPageSize+)

    set @strSQL=@strSQL+' AND '+str(@strStartRow+@strPageSize-)

    exec (@strSQL)

DataTable dt = new Product_Bll().CommonGetDataPager(表名称,视图, 关键字段, 排序字段, AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, 需要获取的字段, 条件,Group语句

); AspNetPager1.RecordCount = dt==null?:Convert.ToInt32(dt.Rows[]["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 = ""; }