分页查询的一个帮助类及相关存储过程

分页sql查询在编程的应用很多，主要有存储过程分页和sql分页两种，我比较喜欢用sql分页，主要是很方便。为了提高查询效率，应在排序字段上加索引。sql分页查询的原理很简单，比如你要查100条数据中的30-40条，你先查询出前40条，再把这30条倒序，再查出这倒序后的前十条，最后把这十条倒序就是你想要的结果。
        下面把sql分页查询的原理用sql语句表现一下：

        -- 分页 升序（搜出的结果再倒序）
        SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 ASC) AS T ORDER BY A00 DESC

         -- 分页 升序
        SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 ASC) AS T ORDER BY A00 DESC) AS T1 ORDER BY A00 ASC

         -- 分页 降序（搜出的结果再倒序）
         SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 DESC) AS T ORDER BY A00 ASC

         -- 分页 降序
         SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 40 * from A01 ORDER BY A00 DESC) AS T ORDER BY A00 ASC) AS T1 ORDER BY A00 DESC

        --优化的 top
        SELECT TOP 10  * FROM  Test WHERE  (testid >( select max(testid) from (SELECT TOP 100000 testid FROM test order by testid) as T )) order by testid

         为了应用的方便我把生成sql分页查询语句的写成了类SplitHelp

        应用如下：
        
       

/**//// <summary>
      /// 分页查询例子
      /// </summary>
      /// <param name="currentPage">当前页</param>
      /// <param name="pagesize">每页大小</param>
      /// <param name="count">数据总条数</param>
      /// <param name="cn">数据库连接</param>
      /// <returns>查询IDbCommand</returns>
      public IDbCommand Search(int currentPage, int pagesize, out int count, IDbConnection cn)
      {
         //得到IDbCommand
         IDbCommand cmd = cn.CreateCommand();
         cmd.CommandType = CommandType.Text;
         
         cmd.CommandText = "select count(tableTestID) from tableTest";
         if (cn.State != ConnectionState.Open)
            cn.Open();
         //得到数据总数
         count = (int)cmd.ExecuteScalar();

         //搜索的前n条
         int topAll = SplitPage.GetTopNum(currentPage, pagesize, count);

         //排序字段类
         AscDescClass ascDesc = new AscDescClass("tableTestID", AscDescEnum.desc);
         //ascDesc.Add("tableTestID1", AscDescEnum.desc);

         //此sql语名必须有排序,写出要查询前topAll条记录的sql
         string sql = String.Format(@"select top {0} * from tableTest order by {1}", topAll, ascDesc.GetAscDesString());
 
         //最终sql
         cmd.CommandText = SplitPage.GetFinalSql(sql, ascDesc, pagesize, count, topAll, currentPage);

         return cmd;
      }

当然，要想真正提高查询效率，最好还是用存储过程，这里写了两个例子，一个真对sql2000,一个真对sql2005  
sql2000

create  proc usp_UserGoldHistoryByDateRange
    (
    @StartDate        varchar(10),
    @EndDate        varchar(10),
    @PageSize        int,
    @PageIndex        int,
    @RowCount        int out
    )
as
declare @StartRow    int
declare @EndRow    int
-- 计算当前页开始行
set @StartRow = (@PageIndex - 1) * @PageSize   1
-- 计算当前页结束行
set @EndRow = @StartRow   @PageSize - 1  
-- 建一张内存表用于存储检索结果
declare @temp table
    (
    AutoID     [int] IDENTITY (1, 1) NOT NULL,
    RowID    [int]
    )
-- 执行检索
insert into @temp(RowID)
select RowID from UserGoldHistory
where left(DateTimeTag,10) between @StartDate and @EndDate  
set @RowCount = @@ROWCOUNT  
select * from UserGoldHistory where RowID in (
select RowID from @temp where AutoID between @StartRow and @EndRow)  

sql2005

create proc proc_Split
  -- 设置每页的行数
  @page_size int,
  -- 设置当前页
  @page_current int,
  -- 总记录数
  @rows_count int out
as  
select @rows_count=count(UserName) from ForumUser  
DECLARE @start_row_num int
DECLARE @end_row_num int
-- 设置开始行号
SET @start_row_num = (@page_current - 1) * @page_size   1
-- 设置结束行号
SET @end_row_num = @start_row_num   @page_size - 1;  
WITH temptesttable AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY UserName) AS row_number, * 
  FROM ForumUser
)
SELECT * from temptesttable 
WHERE row_number BETWEEN @start_row_num AND @end_row_num