分页存储过程运用

 1、存储过程：

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

---------------------------------------------------------------
-- 分页存储过程(使用RowCount)  --edit by SiBen
-- summary:
--          获取表或表集合的分页数据
--          当多表连接时,sort列必须指定表名
---------------------------------------------------------------

ALTER PROCEDURE [dbo].[Paging_RowCount]
(
@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,
@RecordCount int = 0 output,
@PageCount int = 0 output --总页数
)
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 @PKTable varchar(100)
DECLARE @PKName 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', '')
                ELSE
                        SET @strSortColumn = @Sort

  SET @operator = '>'
 END

/* Set PK,Sort name */
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
IF CHARINDEX('.', @PK) > 0
 BEGIN
  SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
  SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
 END
ELSE
 BEGIN
  SET @PKTable = @Tables
  SET @PKName = @PK
 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 @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

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

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@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 + ' '
 END
ELSE
 BEGIN
  SET @strSimpleFilter = ''
  SET @strFilter = ''
 END
IF @Group IS NOT NULL AND @Group != ''
 SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
 SET @strGroup = ''

/*Get rows count.*/
DECLARE @str_Count_SQL nvarchar(500)
SET @str_Count_SQL= 'SELECT @TotalCount=count(*) FROM ' + @Tables + @strFilter
EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output
SET @PageCount=CEILING((@RecordCount+0.0)/@PageSize) 
/*Execute dynamic query*/ 
IF @PKTable = @SortTable and @PKName = @SortName
 BEGIN 
  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 + '
  '
  )
 END
ELSE
 BEGIN
                /* Get PK Type */
  DECLARE @pktype varchar(100)
                DECLARE @pkprec int
  
  SELECT @pktype=t.name, @pkprec=c.prec
  FROM sysobjects o
  JOIN syscolumns c on o.id=c.id
  JOIN systypes t on c.xusertype=t.xusertype
  WHERE o.name = @PKTable AND c.name = @PKName

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

                /*Execute dynamic query*/ 
  EXEC(
  '
  DECLARE @SortColumn ' + @type + '
  DECLARE @SortNullValue ' + @type + '
  DECLARE @PKStartValue ' + @pktype + '
  SET @SortNullValue=CAST('''' as '+ @type +')
  SET ROWCOUNT ' + @strStartRow + '
  SELECT @SortColumn= isNull(' + @strSortColumn + ',@SortNullValue), @PKStartValue = '+ @PK +' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
  SET ROWCOUNT ' + @strPageSize + '
  SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE (isNull(' + @strSortColumn+',@SortNullValue)' + @operator + ' @SortColumn or (isNull(' + @strSortColumn+',@SortNullValue)=@SortColumn and '+ @PK +'<=@PKStartValue))' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + ','+ @PK +' Desc
  '
  )
 END

2、调用

 

2.1  中间层

 

 public static DataSet PageList(string connectionString, string tables, string pk, string sort, int pageNumber, int pageSize,
string fields, string filter, string group, out int recordCount, out int pageCount)
        {
            SqlParameter[] parameters ={ new SqlParameter("@Tables",SqlDbType.NVarChar,1000),
                new SqlParameter("@PK",SqlDbType.NVarChar,100),
                new SqlParameter("@Sort",SqlDbType.NVarChar,200),
                new SqlParameter("@PageNumber",SqlDbType.Int),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Fields",SqlDbType.NVarChar,1000),
                new SqlParameter("@Filter",SqlDbType.NVarChar,1000),
                new SqlParameter("@Group",SqlDbType.NVarChar,1000),
                new SqlParameter("@RecordCount",SqlDbType.Int),
                new SqlParameter("@PageCount",SqlDbType.Int)};

            parameters[0].Value = tables;
            parameters[1].Value = pk;
            parameters[2].Value = sort;
            parameters[3].Value = pageNumber;
            parameters[4].Value = pageSize;
            parameters[5].Value = fields;
            parameters[6].Value = filter;
            parameters[7].Value = group;
            parameters[8].Direction = ParameterDirection.Output;
            parameters[9].Direction = ParameterDirection.Output;

            DataSet ds = ExecuteDataset(connectionString, "Paging_RowCount", parameters);

            recordCount = (int)parameters[8].Value;
            pageCount = (int)parameters[9].Value;
            return ds;
        }

 

2.2  dal-----bll

 

   public DataSet PageList(string sort, int pageNumber, int pageSize, string fields, string filter, string group, out int recordCount, out int pageCount)
        {
            return DbHelperSQL.PageList(DbHelperSQL.ConnectionString, "V_M_Info_List", "InfoId", sort, pageNumber, pageSize, fields, filter, group, out recordCount, out pageCount);
        }

 

2.3   .cs文件

 

 private void LoadData(int pageindex)
    {
        string Where = GetCondition();
        int RecordCount;
        int pagecount;
        DataSet rs = InfoBll.PageList("infoID DESC", pageindex, 18, "*", Where, "", out RecordCount, out pagecount);
        AspNetPager1.RecordCount = RecordCount;
        this.gv_List.DataSource = rs.Tables[0];
        this.gv_List.DataBind();
    }