高效的分页存储过程和调用代码

一、存储过程

 

ALTER   PROCEDURE   [ dbo ] . [ GetPagedData1 ]
(
@Tables   varchar ( 1000 ),
@PK   varchar ( 100 ),
@Sort   varchar ( 200 )  =   NULL ,
@PageNumber   int   =   1 ,
@PageSize   int   =   10 ,
@Fields   varchar ( 1000 )  =   ' * ' ,
@Filter   varchar ( 1000 )  =   NULL ,
@Group   varchar ( 1000 )  =   NULL )
AS

/**/ /*Default Sorting*/
IF   @Sort   IS   NULL   OR   @Sort   =   ''
  SET   @Sort   =   @PK

/**/ /*Find the @PK type*/
DECLARE   @SortTable   varchar ( 100 )
DECLARE   @SortName   varchar ( 100 )
DECLARE   @strSortColumn   varchar ( 200 )
DECLARE   @operator   char ( 2 )
DECLARE   @type   varchar ( 100 )
DECLARE   @prec   int

/**/ /*Set sorting variables.*/  
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


IF   CHARINDEX ( ' . ' ,  @strSortColumn )  >   0
  BEGIN
   SET   @SortTable   =   SUBSTRING ( @strSortColumn ,  0 ,  CHARINDEX ( ' . ' , @strSortColumn ))
   SET   @SortName   =   SUBSTRING ( @strSortColumn ,  CHARINDEX ( ' . ' , @strSortColumn )  +   1 , 
LEN ( @strSortColumn ))
  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  =   @SortName

IF   CHARINDEX ( ' char ' ,  @type )  >   0
    SET   @type   =   @type   +   ' ( '   +   CAST ( @prec   AS   varchar )  +   ' ) '


DECLARE   @strFilter   varchar ( 1000 )
DECLARE   @strSimpleFilter   varchar ( 1000 )
DECLARE   @strGroup   varchar ( 1000 )
DECLARE   @strStartRow   varchar ( 50 )
DECLARE   @StrEndRow   varchar ( 50 )

/**/ /*Default Page Number*/
IF   @PageNumber   <   1
  SET   @PageNumber   =   1

/**/ /*Set paging variables.*/

SET   @strStartRow   = cast ((( @PageNumber   -   1 ) * @PageSize   +   1 )  as   varchar ( 50 )) 
SET   @StrEndRow = cast (( @strStartRow + @PageSize - 1 )  as   varchar ( 50 ))

/**/ /*Set filter & group variables.*/
IF   @Filter   IS   NOT   NULL   AND   @Filter   !=   ''
  BEGIN
   SET   @strFilter   =   '  WHERE  '   +   @Filter   +   '   '
   SET   @strSimpleFilter   =   '  AND  '   +   @Filter   +   '   '

print ( @strFilter )
print ( @strSimpleFilter )
  END
ELSE
  BEGIN
   SET   @strSimpleFilter   =   ''
   SET   @strFilter   =   ''
  END
IF   @Group   IS   NOT   NULL   AND   @Group   !=   ''
  SET   @strGroup   =   '  GROUP BY  '   +   @Group   +   '   '
ELSE
  SET   @strGroup   =   ''
 
/**/ /*Execute dynamic query*/  
-- Exec(
-- '
-- DECLARE @SortColumn ' + @type + '
-- SET ROWCOUNT ' + @strStartRow + '
-- SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' +
-- @strGroup + ' ORDER BY ' + @Sort + '
-- SET ROWCOUNT ' + @strPageSize + '
-- SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator +
-- ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
-- '
-- )
Exec ( '
with TGetPagedData as
 ( 
  select  ' + @Fields + ' ,ROW_NUMBER() over( order by  ' + @Sort + ' ) as RowNumber from  ' + @Tables + @strFilter + '  
 )
select * from TGetPagedData where RowNumber between  ' + @strStartRow + '  and  ' + @StrEndRow + '
' )

EXEC ( ' select count(*) from  ' +   @Tables +   @strFilter )

print  ( '
with TGetPagedData as
 ( 
  select  ' + @Fields + ' ,ROW_NUMBER() over( order by  ' + @Sort + ' ) as RowNumber from  ' + @Tables + @strFilter + '  
 )
select * from TGetPagedData where RowNumber between  ' + @strStartRow + '  and  ' + @StrEndRow + '
' )

print  ( ' select count(*) from  ' +   @Tables +   @strFilter )

二、调用代码

  public   static  DataTable GetPagedData( string  tableName,  string  primaryKey,  string  sortExpress,  int  currentPage,  int  pageSize,  string  fields,  string  filter,  string  groupBy,  out   int  totalCount)
         {
            try
            {
                string sql = "GetPagedData1";
                SqlDBAccess access = new SqlDBAccess();
                DataSet result;
                SqlParameter[] sqlparam = 
            {
                 new SqlParameter("@Tables",SqlDbType.VarChar),
                new SqlParameter("@PK",SqlDbType.VarChar),
                new SqlParameter("@Sort",SqlDbType.VarChar),
                new SqlParameter("@PageNumber",SqlDbType.Int),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Fields",SqlDbType.VarChar),
                new SqlParameter("@Filter",SqlDbType.VarChar),
                new SqlParameter("@Group",SqlDbType.VarChar),
               
            };



                int i = 0;
                sqlparam[i++].Value = tableName;
                sqlparam[i++].Value = primaryKey;
                sqlparam[i++].Value = sortExpress;
                sqlparam[i++].Value = currentPage;
                sqlparam[i++].Value = pageSize;
                sqlparam[i++].Value = fields;
                sqlparam[i++].Value = filter;
                sqlparam[i++].Value = groupBy;


                result = access.ExecuteDataset("TelecomAH163", CommandType.StoredProcedure, sql, sqlparam);
                totalCount = (int)result.Tables[1].Rows[0][0];
                return result.Tables[0];
            }
            catch (Exception e)
            {
                Tracing.Error("", e);
                totalCount = 0;
                return null;
            }
        }

转载于:https://www.cnblogs.com/lt2699/archive/2008/03/28/1127860.html