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

一、存储过程

 

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;
            }
        }