/// <summary>
/// 根据PagingInfo信息，返回分页后的实体集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="pageInfo">分页信息</param>
/// <returns>实体集合</returns>
public List <T> ToPageList <T>(PagingInfo pageInfo) where T : class, new()
{
//--需要配置的SQL语句
//select row_number() over (order by UpCount asc) as RowIndex,
// Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
//from CaoItem
//where CreateTime < @CreateTime
//--在运行时，将会生成下面二条SQL
//select * from (
//select row_number() over (order by UpCount asc) as RowIndex,
// Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
//from CaoItem
//where CreateTime < @CreateTime
//) as t1
//where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1))
//select count(*) from ( select
//-- 去掉 select row_number() over (order by UpCount asc) as RowIndex,
// Title, Tag, [Description], Creator, CreateTime, UpCount, ReadCount, ReplyCount
//from CaoItem as p
//where CreateTime < @CreateTime
//) as t1
// 为了方便得到 count 的语句，先直接定位 ") as RowIndex,"
// 然后删除这之前的部分，将 select count(*) from (select 加到SQL语句的前面。
// 所以，这里就检查SQL语句是否符合要求。
//string flag = ") as RowIndex,";
//int p = xmlCommandText.IndexOf(flag, StringComparison.OrdinalIgnoreCase);
//if( p <= 0 )
// throw new InvalidOperationException("XML中配置的SQL语句不符合分页语句的要求。");
string xmlCommandText = _query.ToString();
Match match = _pagingRegex.Match(xmlCommandText);
if (match.Success == false)
{
throw new InvalidOperationException("XML中配置的SQL语句不符合分页语句的要求。");
}
int p = match.Index;
// 获取命令参数数组
SqlParameter[] parameters1 = _query.Command.Parameters.Cast <SqlParameter>().ToArray();
_query.Command.Parameters.Clear(); // 断开参数对象与原命令的关联。
// 克隆参数数组，因为参数对象只能属于一个命令对象。
SqlParameter[] parameters2 = (from pp in parameters1
select new SqlParameter {
ParameterName = pp.ParameterName,
SqlDbType = pp.SqlDbType,
Size = pp.Size,
Scale = pp.Scale,
Value = pp.Value,
Direction = pp.Direction
}).ToArray();
// 生成 SELECT 命令
string selectCommandText = string.Format(@"select * from ( {0} ) as t1
where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1))", xmlCommandText);
CPQuery query1 = CPQuery.From(selectCommandText, parameters1);
query1.Command.Parameters.Add(new SqlParameter {
ParameterName = "@PageIndex",
SqlDbType = System.Data.SqlDbType.Int,
Value = pageInfo.PageIndex
});
query1.Command.Parameters.Add(new SqlParameter {
ParameterName = "@PageSize",
SqlDbType = System.Data.SqlDbType.Int,
Value = pageInfo.PageSize
});
// 生成 COUNT 命令
string getCountText = string.Format("select count(*) from (select {0} ) as t1",
xmlCommandText.Substring(p + match.Length));
CPQuery query2 = CPQuery.From(getCountText, parameters2);
// 执行二次数据库操作（在一个连接中）
using (ConnectionScope scope = new ConnectionScope()) {
List <T> list = query1.ToList <T>();
pageInfo.TotalRecords = query2.ExecuteScalar <int>();
return(list);
}
}
