/// <summary>
/// 获取查询的CPQuery对象
/// </summary>
/// <param name="sql">查询SQL</param>
/// <param name="parameters">参数</param>
/// <param name="pagingInfo">分页信息</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns></returns>
private static CPQuery GetCPQuery(string sql, object parameters, StandardPagingInfo pagingInfo, params object[] tables)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql");
}
if (string.IsNullOrEmpty(pagingInfo.OrderSeq))
{
throw new MyException("请在分页实体中指定 排序字段");
}
// 格式化数据库实例名
if (tables != null && tables.Length > 0)
{
sql = string.Format(sql, tables);
}
// 生成 SELECT 命令
string selectCommandText = string.Format(@"select * from (
select ROW_NUMBER() OVER(ORDER BY {3}) AS [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86],*
from ({0}) as a1) as t1 where [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] > ({1} * {2})
and [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] <= ({1} * ({2}+1)) order by [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86]", sql, pagingInfo.PageSize, pagingInfo.PageIndex, pagingInfo.OrderSeq);
CPQuery query1 = CPQuery.From(selectCommandText, parameters);
// 生成 COUNT 命令
string getCountText = string.Format(@"select count(*) from ({0}) as t1", sql);
CPQuery query2 = CPQuery.From(getCountText, parameters);
pagingInfo.TotalRecords = query2.ExecuteScalar <int>();
return(query1);
}
/// <summary>
/// 获取有效的CPQuery对象
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <param name="pagingInfo"></param>
/// <param name="tables"></param>
/// <returns></returns>
public static CPQuery GetValidCPQuery(string sql, SqlParameter[] parameters, StandardPagingInfo pagingInfo, params object[] tables)
{
CPQuery query = GetCPQuery(sql, parameters, pagingInfo, tables);
if (pagingInfo.AutoValidPage)
{
if (query.FillDataTable().Rows.Count == 0 && pagingInfo.PageIndex > 0)
{
//如果当前查询的页没有数据，但是设置的是允许自动定位页，则查找到最后一个有效的页面进行查询
int len = (int)Math.Ceiling((double)pagingInfo.TotalRecords / pagingInfo.PageSize);
pagingInfo.PageIndex = (len - 1) < 0 ? 0 : (len - 1);
query = GetCPQuery(sql, parameters, pagingInfo, tables);
}
}
return(query);
}
/// <summary>
/// 获取CPQuery
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="parameters">参数</param>
/// <param name="pagingInfo">标准分页实体</param>
/// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>CPQuery</returns>
private static CPQuery GetSpecialQuery(string sql, object parameters, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql");
}
if (string.IsNullOrEmpty(pagingInfo.OrderSeq))
{
throw new MyException("请在分页实体中指定 排序字段");
}
// 格式化数据库实例名
if (tables != null && tables.Length > 0)
{
sql = string.Format(sql, tables);
}
var extCols = "";
var joinTables = "";
if (dict != null && dict.Count > 0)
{
extCols = "," + dict.Keys.Join(" , ");
joinTables = dict.Values.Join(" ");
}
// 生成 SELECT 命令
var selectCommandText = string.Format(@"SELECT a.* {4}
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY {3} ) AS [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] ,
*
FROM ({0}) AS a
) AS a {5}
WHERE [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] > ( {1} * {2} )
AND [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86] <= ( {1} * ( {2} + 1 ) ) order by [RowIndex901ACBA3-1B21-4C1F-B55A-387BA69C1C86]", sql, pagingInfo.PageSize, pagingInfo.PageIndex, pagingInfo.OrderSeq, extCols, joinTables);
CPQuery query1 = CPQuery.From(selectCommandText, parameters);
// 生成 COUNT 命令
string getCountText = string.Format(@"select count(*) from ({0}) as t1", sql);
CPQuery query2 = CPQuery.From(getCountText, parameters);
pagingInfo.TotalRecords = query2.ExecuteScalar <int>();
return(query1);
}
/// <summary>
/// 获取分页数据表(指定数据库连接字符串)
/// </summary>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="strConn">数据库连接字符串</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>分页数据表</returns>
public static DataTable GetData(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, params object[] tables)
{
using (ConnectionScope scope = new ConnectionScope(TransactionMode.Inherits, strConn))
{
return(GetValidCPQuery(sql, parameters, pagingInfo, tables).FillDataTable());
}
}
/// <summary>
/// 获取实体分页列表
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>实体分页列表</returns>
public static List <T> GetList <T>(string sql, object parameters, StandardPagingInfo pagingInfo, params object[] tables) where T : class, new()
{
return(GetValidCPQuery(sql, parameters, pagingInfo, tables).ToList <T>());
}
/// <summary>
/// 获取分页数据表
/// </summary>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>分页数据表</returns>
public static DataTable GetData(string sql, object parameters, StandardPagingInfo pagingInfo, params object[] tables)
{
return(GetValidCPQuery(sql, parameters, pagingInfo, tables).FillDataTable());
}
/// <summary>
/// 获取实体分页列表(指定数据库连接字符串)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="strConn">数据库连接字符串</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>实体分页列表</returns>
public static List <T> GetSpecialList <T>(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables) where T : class, new()
{
return(GetSpecialData(sql, parameters, pagingInfo, dict, tables).ToList <T>());
}
/// <summary>
/// 获取数据集分页列表(指定数据库连接字符串)
/// </summary>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="strConn">数据库连接字符串</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>数据集分页列表</returns>
public static DataTable GetSpecialData(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables)
{
using (ConnectionScope scope = new ConnectionScope(TransactionMode.Inherits, strConn))
{
return(GetSpecialQuery(sql, parameters, pagingInfo, dict, tables).FillDataTable());
}
}
/// <summary>
/// 获取特殊数据集分页列表
/// </summary>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="dict">扩展条件键值对(key:列名,value:left join语句)</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>分页数据集</returns>
public static DataTable GetSpecialData(string sql, object parameters, StandardPagingInfo pagingInfo, Dictionary <string, string> dict, params object[] tables)
{
return(GetSpecialQuery(sql, parameters, pagingInfo, dict, tables).FillDataTable());
}
/// <summary>
/// 获取实体分页列表(指定数据库连接字符串)
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sql">执行SQL</param>
/// <param name="parameters">参数</param>
/// <param name="strConn">数据库连接字符串</param>
/// <param name="pagingInfo">分页信息(OrderSeq传递排序字段：(Oid desc,Name asc))</param>
/// <param name="tables">联合查询用到的数据库实例名，可以用ConnStringHelper.GetDBName(string key)</param>
/// <returns>实体分页列表</returns>
public static List <T> GetList <T>(string sql, object parameters, string strConn, StandardPagingInfo pagingInfo, params object[] tables) where T : class, new()
{
using (ConnectionScope scope = new ConnectionScope(TransactionMode.Inherits, strConn))
{
return(GetValidCPQuery(sql, parameters, pagingInfo, tables).ToList <T>());
}
}
/// <summary>
/// 获取实体分页列表
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="pageInfo">标准分页信息</param>
/// <returns>实体分页列表</returns>
public List <T> ToPageList(StandardPagingInfo pageInfo)
{
//--需要配置的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语句是否符合要求。
if (string.IsNullOrEmpty(pageInfo.OrderSeq))
{
pageInfo.OrderSeq = _sqlInfo.PrimaryKey;
}
string xmlCommandText = _query.ToString().Replace("select", "select ROW_NUMBER() OVER ( ORDER BY " + pageInfo.OrderSeq + " ) as RowIndex, ");
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);
Panto.Map.Extensions.DAL.CPQuery query1 = Panto.Map.Extensions.DAL.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));
Panto.Map.Extensions.DAL.CPQuery query2 = Panto.Map.Extensions.DAL.CPQuery.From(getCountText, parameters2);
// 执行二次数据库操作（在一个连接中）
using (Panto.Map.Extensions.DAL.ConnectionScope scope = new Panto.Map.Extensions.DAL.ConnectionScope())
{
List <T> list = query1.ToList <T>();
pageInfo.TotalRecords = query2.ExecuteScalar <int>();
return(list);
}
}
