用存储过程自定义分页

//通过user_id返回留言集合
  public ArrayList GetCommentboardsByUidPage(int user_id,int pagenumber)
  {
   
   IDbCommand m_Command = DataAccess.GetCommand();
   m_Command.CommandType = CommandType.StoredProcedure;
   m_Command.CommandText = "turnpage";
   string qTables="commentboard where user_id="+user_id.ToString();
   string qCols="*";
   string iKey="comment_id";
   string oKey="commenttime";

   IDataParameter[] parameters = DataAccess.GetParameters(7);

   parameters[0].ParameterName="@qCols";
   parameters[0].DbType=DbType.String;
   parameters[0].Value=qCols;

   parameters[1].ParameterName = "@qTables";
   parameters[1].DbType = DbType.String;
   parameters[1].Value =qTables ;

   parameters[2].ParameterName="@iKey";
   parameters[2].DbType=DbType.String;
   parameters[2].Value=iKey;

   parameters[3].ParameterName="@oKey";
   parameters[3].DbType=DbType.String;
   parameters[3].Value=oKey;

   parameters[4].ParameterName="@pageSize";
   parameters[4].DbType=DbType.Int32;
   parameters[4].Value=15;
   
   parameters[5].ParameterName="@pageNumber";
   parameters[5].DbType=DbType.Int32;
   parameters[5].Value=pagenumber;

   parameters[6].ParameterName="@order";
   parameters[6].DbType=DbType.Int32;
   parameters[6].Value=0;

   ArrayList ClientArray = new ArrayList();
   SafeDataReader reader = new SafeDataReader(DataAccess.ExecuteReader(m_Command,parameters));

   try
   {    
    while (reader.Read())
    {     
     ClientArray.Add(GetClientByDataReader(reader));
    }
   }
   
   catch(System.SystemException e)
   {
    throw e;
   }     

   finally
   {
    reader.Close();
    reader.Dispose();
    
   }

   return ClientArray;

  } 

  public int GetPageCount(int user_id)
  {
   IDbCommand m_Command = DataAccess.GetCommand();
   m_Command.CommandType = CommandType.StoredProcedure;
   m_Command.CommandText = "GetPageCount";
   string qTables="commentboard where user_id="+user_id.ToString();
   

   IDataParameter[] parameters = DataAccess.GetParameters(3);

   parameters[0].ParameterName="@qTables";
   parameters[0].DbType=DbType.String;
   parameters[0].Value=qTables;

   parameters[1].ParameterName="@pageSize";
   parameters[1].DbType=DbType.Int32;
   parameters[1].Value=15;

   parameters[2].ParameterName="@pagecount";
   parameters[2].DbType=DbType.Int32;
   parameters[2].Direction=ParameterDirection.Output;
   int i=DataAccess.ExecuteNonQuery(m_Command,parameters);
   return (int) parameters[2].Value;
   
  }


//分页储存过程
ALTER PROC turnpage
    @qCols varchar(200),                --需要查询的列
    @qTables         varchar(200),       --需要查询的表 和条件
    @iKey         varchar (20),       --标识字段
    @oKey        varchar(20),        --排序字段
    @pageSize         int,                 --每页的行数
    @pageNumber       int,                   --要显示的页码, 从0开始
    @order            int               --排序1为升,0为降
AS
set nocount on
BEGIN
    DECLARE @sqlText AS varchar(1000)
    DECLARE @sqlTable AS varchar(1000)
   
    if @order !=0
    begin
    SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @qCols +' from '+ @qTables + '  order by '+@oKey
    end
   
    else
    begin
    SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @qCols +' from '+ @qTables + '  order by '+@oKey+' desc '
    end
   
    SET @sqlText =
        'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
        'FROM (' + @sqlTable + ') AS tableA ' +
        'WHERE ' + @iKey + ' NOT IN(SELECT TOP ' +
        CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @iKey +
        ' FROM (' + @sqlTable + ') AS tableB)'
  EXEC (@sqlText)
 --print(@sqltext)
END


ALTER PROCEDURE dbo.GetPageCount
   @qTables         varchar(200),
   @pageSize         int,                --每页的行数
   @pagecount       int output
AS

 

//取得总页数
 set nocount on
 DECLARE @icount int

 declare @sqlstr nvarchar(100)
 set @sqlstr = 'select @icount=Count(*) from '+@qTables
 
 exec sp_executesql @sqlstr,N'@icount int out ',@icount out
 select @pagecount=
 case
 when @icount%@pageSize != 0 then @icount/@pageSize+1
 when @icount%@pageSize = 0 then @icount/@pageSize
 end