/****** Object:  StoredProcedure [dbo].[PRO_PageForIdTable]    Script Date: 04/29/2014 11:23:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        Goosoz@163.com-- Create date: 2013-04-03-- Description:    -- =============================================CREATE proc [dbo].[PRO_PageForIdTable](    @TableName  NVARCHAR(50),        --要查询的表名称，单表名称    @FieldsName NVARCHAR(1024),    --要返回的列名称 ：*    @IdName NVARCHAR(50),        --标识列名称 能够唯一标识数据：ID    @StrWhere NVARCHAR(max),    --要求带WHERE关键字:where addtime>'2012'    @OrderASC bit,                --是否升序,0:DESC,1:ASC 要求和    @Page INT,                    --页码从1开始    @PageSize INT,                --    @TotalRow INT output    -- 记录总数 )asBEGIN    SET NOCOUNT ON;    DECLARE @sqlOrder AS NVARCHAR(512);    DECLARE @sql AS NVARCHAR(MAX);    if(@OrderASC=0)        set @sqlOrder='order by '+@IdName+' desc';    else        set @sqlOrder='order by '+@IdName+' asc';        begin        if(@Page<2)            --select top 20 id from tb_errorlog where id>33333 and id<444444 order by id desc            set @sql=' select top '+ CONVERT(NVARCHAR(20),@PageSize) +' '+@FieldsName+' from '+@TableName+' '+@StrWhere+' '+@sqlOrder;        else if(@OrderASC=0)            begin                if(@StrWhere='')                    --        select top 20 id from tb_errorlog                    --        where id<(select min(id) from (select top 30000 id from tb_errorlog order by id desc) as cnic_t_2012_001)                    --        order by id desc                    set @sql='select top '+ CONVERT(NVARCHAR(20),@PageSize) +' '+@FieldsName+' from '+@TableName                    +' where '+@IdName+'<(select min('+@IdName+') from (select top '+CONVERT(NVARCHAR(20),@PageSize*(@Page-1))+' '+@IdName+' from '+@TableName+' '+@sqlOrder+') as cnic_t_2012_001) '                    +@sqlOrder                else                    --        select top 20 id from tb_errorlog where id>33333 and id<444444                    --        and id<(select min(id) from (select top 30000 id from tb_errorlog where id>33333 and id<444444 order by id desc) as cnic_t_2012_001)                    --        order by id desc                    set @sql='select top '+ CONVERT(NVARCHAR(20),@PageSize) +' '+@FieldsName+' from '+@TableName+' '+ @StrWhere                    +' and '+@IdName+'<(select min('+@IdName+') from (select top '+CONVERT(NVARCHAR(20),@PageSize*(@Page-1))+' '+@IdName+' from '+@TableName+' '+ @StrWhere+' '+@sqlOrder+') as cnic_t_2012_001) '                    +@sqlOrder            end        else            begin                if(@StrWhere='')                    --        select top 20 id from tb_errorlog                    --        where id<(select min(id) from (select top 30000 id from tb_errorlog order by id desc) as cnic_t_2012_001)                    --        order by id desc                    set @sql=' select top '+ CONVERT(NVARCHAR(20),@PageSize) +' '+@FieldsName+' from '+@TableName                    +' where '+@IdName+'>(select max('+@IdName+') from (select top '+CONVERT(NVARCHAR(20),@PageSize*(@Page-1))+' '+@IdName+' from '+@TableName+' '+@sqlOrder+') as cnic_t_2012_001) '                    +@sqlOrder                else                    --        select top 20 id from tb_errorlog where id>33333 and id<444444                    --        and id<(select min(id) from (select top 30000 id from tb_errorlog where id>33333 and id<444444 order by id desc) as cnic_t_2012_001)                    --        order by id desc                    set @sql='select top '+ CONVERT(NVARCHAR(20),@PageSize) +' '+@FieldsName+' from '+@TableName+' '+ @StrWhere                    +' and '+@IdName+'>(select max('+@IdName+') from (select top '+CONVERT(NVARCHAR(20),@PageSize*(@Page-1))+' '+@IdName+' from '+@TableName+' '+ @StrWhere+' '+@sqlOrder+') as cnic_t_2012_001) '                    +@sqlOrder                end    end    --print (@sql);    exec (@sql);    set @sql='set @RowCount=(select count('+@FieldsName+') from '+@TableName+' '+@StrWhere+')';    exec sp_executesql @sql, N'@RowCount int output', @TotalRow output;ENDGO

SqlParameter[] Param = new SqlParameter[]{                new SqlParameter("@TableName","tb-xxx"),//0                new SqlParameter("@FieldsName","*"),//1                new SqlParameter("@IdName","ID"),//2                new SqlParameter("@StrWhere",sql.ToString()),//3 带where                new SqlParameter("@OrderASC",SortType==1),//4 此参数必须先强制转换为object类型                new SqlParameter("@Page",page),//5                new SqlParameter("@PageSize",pageSize),//6                new SqlParameter("@TotalRow",0)//7            };            Param[7].Direction = ParameterDirection.Output;            using (SqlDataReader dr = DbHelperSQL.RunProcedure("PRO_PageForIdTable", Param))            {                while (dr.Read())                {                    rtun.Add(GetModel(dr));                }                dr.Close();            }            TotalRow = Convert.ToInt32(Param[7].Value);            MaxPage = Units.GetPageCount(TotalRow, pageSize);            return rtun;