一次数据访问层的改进之路(续一之分页篇)

最新推荐文章于 2025-08-22 13:08:41 发布

xianyiqi

最新推荐文章于 2025-08-22 13:08:41 发布

阅读量631

点赞数

文章标签： string list sql null 测试 linq

本文介绍了一种基于min-max法的高效SQL分页查询方法，通过动态生成SQL语句实现优雅的分页查询封装，并提供了详细的实现代码及效率测试。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一次数据访问层的改进之路(续一之分页篇)

前言:上次代码打不开，今天换了一种代码展现发式。

在上篇一次数据访问层的改进之路中，我们经过改进后查询列表得到的编码结果类似下面:

 
OleDbParameter[] paras =  new ACEParameterHelper().AddParameter< string >( "@memoType" , memoType) 
 
                                                          .GetParameters(); 
 
List<DataDictItemInfo> list =  null ; 
 
list = ACEAdvanceHelper.GetList<DataDictItemInfo>( "select * from Fk_data_dict_item t where t.memo=@memoType and t.parent_id=0" , conn, paras); 
 
return list;

代码还算简洁，能满足基本的查询要求！

但是实际应用中，这是不够的，这个组件主要还是要写sql语句的，写sql语句的一大繁琐事情就是写分页sql语句。

我们的问题就是：

  
   
   如何分页？

1.分页方法选择：not in法和min-max法。

 
//not in法  
 
//说明：该法效率地下，越往后面翻，效率越地下 
 
//测试：13万数据，下面的分页查询，耗时40s左 右 
 
select top 10 * from LandRightInfo where id not  in (select top 10000 id from LandRightInfo) 
 
 
 
//min-max法 
 
//说明：该法效率最好 
 
//测试：13万数据，下面的分页查询，耗时1s左 右，而且不随翻页效率有大的降低 
 
select top 10 * from LandRightInfo where id > (select max(id) from (select top 10000 id from LandRightInfo)) 
 
//或者 
 
select top 10 * from LandRightInfo where id < (select min(id) from (select top 10000 id from LandRightInfo)) 
 
//或者加上排序 
 
select top 10 * from LandRightInfo where id > (select max(id) from (select top 10000 id from LandRightInfo order by id desc)) order by id desc

比较一下，我采用了min-max法来分页。

2.分析min-max语句的特点

就以这个为例子：

`1`	`select top 10 * from LandRightInfo where id > (select max(id) from (select top 10000 id from LandRightInfo order by id desc)) order by id desc`

作用：查询第100页，第10001-10010条记录

10,代表的就是pageSize

10000,代表的就是当前页数之前页数不显示的数据列表项

提取一下:

`1`	`select top pageSize * from LandRightInfo where orderKey > (select max(orderKey) from (select top (pageIndex-1)*pageSize id from LandRightInfo))`

这是个基本的模型。

第一个问题，经测试，这个模型有个问题，当第一页的时候，有问题，sql语句有错误。

所以，当pageIndex=1的时候，得单独写个模型，直接这样：

`1`	`select top pageSize * from LandRightInfo`

第二个问题，加排序条件，规则就是内查询和外查询(不包括min-max查询)的最后面都加上order by语句

`1`	`select top pageSize * from LandRightInfo where orderKey > (select max(orderKey) from (select top (pageIndex-1)*pageSize id from LandRightInfo order by id desc)) order by id desc`

第三个问题，加查询条件，加在order by语句前面，两个order by之前都要加

这里有个技巧，你直接加"where ..."的，就写的很死，不够灵活，咱们可以写成"where 1=1 ",再在后面Append条件"and ...",就可以多条件查询了。

代码就没什么贴的了。

3.如何动态生成这样的min-max语句

根据上面的分析和思路，我们也是很容易生成这样的语句的，方法如下：

 
private string CreatePageSql() 
 
{ 
 
     //select top 10 * from LandRightInfo where id > (select max(id) from (select top 100000 id from LandRightInfo)) 
 
     StringBuilder result =  new StringBuilder( "select top " ); 
 
     result.AppendFormat( "{0} * from {1} where " , pageSize, tableName); 
 
 
 
     if (pageIndex == 1) 
 
     { 
 
         if (orderKey !=  null ) 
 
         { 
 
             result.AppendFormat( "1=1 {0} order by {1} {2}" , condition, orderKey, orderType); 
 
         } 
 
     } 
 
     else 
 
     { 
 
         //含排序操作：字段，类 型 
 
         result.Append(orderKey); 
 
 
 
         int removePageNum = (pageIndex - 1) * pageSize; 
 
         //排序类型处理：升序，降序 
 
         if (orderType ==  "desc" ) 
 
         { 
 
             result.AppendFormat( "< (select min({0}) from (select top {1} {2} from {3} where 1=1 {4} order by {5} desc))" , orderKey, removePageNum, orderKey, tableName, condition, orderKey); 
 
             result.AppendFormat( " {0}" , condition); 
 
             result.AppendFormat( " order by {0} desc" , orderKey); 
 
         } 
 
         else 
 
         { 
 
             result.AppendFormat( "> (select max({0}) from (select top {1} {2} from {3} where 1=1 {4}))" , orderKey, removePageNum, orderKey, tableName, condition); 
 
             result.AppendFormat( " {0}" , condition); 
 
         } 
 
     } 
 
 
 
     return result.ToString(); 
 
}

从上面的方法可以看出，排序的字段OrderKey是必须的。

好了，生成sql的工具也有了。

4.如何以优雅的代码封装这个分页方法？

我相信很多人，都会写这样的封装代码：

 
//传一大堆参数，满足CreatePageSql()方法里变量的调用 
 
public List<T> GetList<T>( int pageIndex, int pageSize, string OrderKey,...) 
 
{ 
 
        List<T> list =  null ; 
 
 
 
        string sql = CreatePageSql(); 
 
        list = ACEAdvanceHelper.GetList<T>(sql, conn, paras); 
 
        conn.Close(); 
 
        return list; 
 
}

自从见识了linq一起其他的一些ORM框架后，我突然觉得这种方法，代码看上去很一般，谈不上优雅，可读性并不强（个人观点）。

我觉得用一种类似函数式编程的方式给每个参数赋值更形象更美丽，可读性也更好，具体的我也不分析了，直接上类代码：

 
public class ACEPageHelper 
 
{ 
 
     /// <summary> 
 
     /// 总记录数 
 
     /// </summary> 
 
     private int recordCounts = 0; 
 
     /// <summary> 
 
     /// 页数 
 
     /// </summary> 
 
     private int pageCounts = 0; 
 
 
 
     private string tableName; 
 
 
 
     private int pageSize = 0; 
 
     private int pageIndex = 0; 
 
 
 
     private string condition =  "" ; 
 
 
 
     private string orderKey =  null ; 
 
     private string orderType =  null ; 
 
 
 
     private OleDbConnection conn =  null ; 
 
     private OleDbParameter[] paras =  null ; 
 
 
 
     // 注意每个方法返回的类型都是当前类 
 
     public ACEPageHelper Connect(OleDbConnection conn) 
 
     { 
 
         this .conn = conn; 
 
         return this ; 
 
     } 
 
 
 
     public ACEPageHelper Parameter(OleDbParameter[] paras) 
 
     { 
 
         this .paras = paras; 
 
         return this ; 
 
     } 
 
     public ACEPageHelper TableName( string tableName) 
 
     { 
 
         this .tableName = tableName; 
 
         return this ; 
 
     } 
 
     public ACEPageHelper PageSize( int pageSize) 
 
     { 
 
         this .pageSize = pageSize; 
 
         return this ; 
 
     } 
 
 
 
     public ACEPageHelper PageIndex( int pageIndex) 
 
     { 
 
         this .pageIndex = pageIndex; 
 
         return this ; 
 
     } 
 
 
 
     public ACEPageHelper Condition( string condition) 
 
     { 
 
         this .condition +=  " and " ; 
 
         this .condition += condition; 
 
         return this ; 
 
     } 
 
 
 
     public ACEPageHelper OrderKey( string orderKey) 
 
     { 
 
         this .orderKey = orderKey; 
 
         return this ; 
 
     } 
 
 
 
     public ACEPageHelper Asc() 
 
     { 
 
         this .orderType =  "asc" ; 
 
         return this ; 
 
     } 
 
 
 
     public ACEPageHelper Desc() 
 
     { 
 
         this .orderType =  "desc" ; 
 
         return this ; 
 
     } 
 
 
 
     public List<T> GetList<T>() 
 
     { 
 
         List<T> list =  null ; 
 
 
 
         string sql = CreatePageSql(); 
 
         list = ACEAdvanceHelper.GetList<T>(sql, conn, paras); 
 
         conn.Close(); 
 
         return list; 
 
 
 
     } 
 
 
 
     private string CreatePageSql() 
 
     { 
 
         //select top 10 * from LandRightInfo where id > (select max(id) from (select top 100000 id from LandRightInfo)) 
 
         StringBuilder result =  new StringBuilder( "select top " ); 
 
         result.AppendFormat( "{0} * from {1} where " , pageSize, tableName); 
 
 
 
         if (pageIndex == 1) 
 
         { 
 
             if (orderKey !=  null ) 
 
             { 
 
                 result.AppendFormat( "1=1 {0} order by {1} {2}" , condition, orderKey, orderType); 
 
             } 
 
         } 
 
         else 
 
         { 
 
             //含排序操作： 字段，类型 
 
             result.Append(orderKey); 
 
 
 
             int removePageNum = (pageIndex - 1) * pageSize; 
 
             //排序类型处理：升序，降序 
 
             if (orderType ==  "desc" ) 
 
             { 
 
                 result.AppendFormat( "< (select min({0}) from (select top {1} {2} from {3} where 1=1 {4} order by {5} desc))" , orderKey, removePageNum, orderKey, tableName, condition, orderKey); 
 
                 result.AppendFormat( " {0}" , condition); 
 
                 result.AppendFormat( " order by {0} desc" , orderKey); 
 
             } 
 
             else 
 
             { 
 
                 result.AppendFormat( "> (select max({0}) from (select top {1} {2} from {3} where 1=1 {4}))" , orderKey, removePageNum, orderKey, tableName, condition); 
 
                 result.AppendFormat( " {0}" , condition); 
 
             } 
 
         } 
 
 
 
         return result.ToString(); 
 
     } 
 
 
 
     /// <summary> 
 
     /// 计算：总记录数 
 
     /// </summary> 
 
     public int GetCounts() 
 
     { 
 
         StringBuilder sql =  new StringBuilder( "select " ); 
 
         sql.AppendFormat( "count({0})" , orderKey); 
 
         sql.AppendFormat( " from {0} where 1=1 " , tableName); 
 
         sql.Append(condition); 
 
 
 
         int result = Convert.ToInt32(ACECommonHelper.ExcuteScalar(sql.ToString(), conn, paras)); 
 
         conn.Close(); 
 
         this .recordCounts = result; 
 
         return result; 
 
     } 
 
 
 
     /// <summary> 
 
     /// 计算：页数 
 
     /// </summary> 
 
     /// <returns></returns> 
 
     public int GetPages() 
 
     { 
 
         int result = 0; 
 
 
 
         if (recordCounts == 0) 
 
         { 
 
             GetCounts(); 
 
         } 
 
 
 
         if (recordCounts % pageSize == 0) 
 
         { 
 
             result = recordCounts / pageSize; 
 
         } 
 
         else 
 
         { 
 
             result = (recordCounts / pageSize) + 1; 
 
         } 
 
 
 
         return result; 
 
     } 
 
 
 
}

怎么个优雅法，接着往下看。

5.使用上面的类以及效率测试

基本使用（最后一页）：

 
using (OleDbConnection conn =  new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|AccessHelper_390602.mdb;" )) 
 
{ 
 
     List<LandRightInfo> list =  null ; 
 
     ACEPageHelper pageData= new ACEPageHelper(); 
 
     list = pageData.Connect(conn) 
 
                    .TableName( "LandRightInfo" ) 
 
                    .PageSize(10) 
 
                    .PageIndex(39061) 
 
                    .OrderKey( "id" ) 
 
                    .GetList<LandRightInfo>(); 
 
     Response.Write( "总结果数/页数：" + pageData.GetCounts() +  "条/" +pageData.GetPages()+ "页<br/>" ); 
 
}

加个条件：

 
list = pageData.Connect(conn) 
 
                     .TableName( "LandRightInfo" ) 
 
                     .PageSize(50) 
 
                     .PageIndex(1) 
 
                     .OrderKey( "LandCode" ) 
 
                     .Condition( "LandCode like '%5%'" )  //条件可以不断的通过Condition添加 
 
                  .Condition( "id like '%5%'" ) 
 
                     .Desc() 
 
                     .GetList<LandRightInfo>();

一目了然啊，每页多少条，第几页，查询条件是什么，升序还是降序，这代码看上去就带劲啊，呵呵。

参数化查询：

 
//使用上篇中讲到的简化参数的写法 
 
OleDbParameter[] paras =  new ACEParameterHelper() 
 
                                  .AddParameter< int >( "@id" , 1000) 
 
                                  .GetParameters(); 
 
list = pageData.Connect(conn) 
 
                     .TableName( "LandRightInfo" ) 
 
                     .Parameter(paras)  //传参数Parameter()方法 
 
               .PageSize(10) 
 
                     .PageIndex(37) 
 
                     .OrderKey( "id" ) 
 
                     .Condition( "LandCode like '%5%'" ) 
 
                     .Condition( "id <@id" )  //条件中带参数 
 
               .GetList<LandRightInfo>();

注：max,min函数也可应用于文本字段，所以这里的OrderKey也可以是文本字段，效率还行

目前，这种方法只能处理单表，夺标复杂的查询还不行，但这是一个好的开始，而且这个函数的思想也是可以应用到其他数据库的分页查询中去，我觉得这篇博客的实用性还是挺强的，个人推荐一下。

完美了，查询出来的速度也是飞快飞快的。

查询语句：

 
list = pageData.Connect(conn) 
 
                     .TableName( "LandRightInfo" ) 
 
                     .PageSize(10) 
 
                     .PageIndex(30000) 
 
                     .OrderKey( "id" ) 
 
                     .GetList<LandRightInfo>(); 
 
//第一页生成的sql语句 
 
{select top 10 * from LandRightInfo where 1=1  order by id } 
 
//第二页生成的sql语句 
 
{select top 10 * from LandRightInfo where id> (select max(id) from (select top 10 id from LandRightInfo where 1=1 )) } 
 
//其他带参数生成的sql语句 
 
{select top 10 * from LandRightInfo where id> (select max(id) from (select top 360 id from LandRightInfo where 1=1  and LandCode like  '%5%' and id <@id))  and LandCode like  '%5%' and id <@id} 
 
//非id而是文本类型的sql语句 
 
{select top 50 * from LandRightInfo where LandCode< (select min(LandCode) from (select top 50 LandCode from LandRightInfo where 1=1  and LandCode like  '%5%' and id like  '%5%' order by LandCode desc))  and LandCode like  '%5%' and id like  '%5%' order by LandCode desc}