private string GetSetSql(string sql, int firstResult, int maxResults, string orderby)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql");
}
sql = sql.TrimStart();
int selectIndex = SqlDialectBase.GetSelectEnd(sql) + 1;
string orderByClause = string.IsNullOrEmpty(orderby) ? "CURRENT_TIMESTAMP" : orderby;
string projectedColumns = SqlDialectBase.GetColumnNames(sql).Aggregate(new StringBuilder(), (sb, s) => (sb.Length == 0 ? sb : sb.Append(", ")).Append(GetColumnName("_proj", s, null)), sb => sb.ToString());
string newSql = sql.Insert(selectIndex, string.Format("ROW_NUMBER() OVER({0}) AS {1}, ", orderByClause, "_row_number"));
string result = string.Format("SELECT TOP({0}) {1} FROM ({2}) [_proj] WHERE {3} >= {4}",
maxResults, projectedColumns.Trim(), newSql, "_proj._row_number", firstResult);
return(result);
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T">类型参数</typeparam>
/// <param name="context">上下文<see cref="context"/></param>
/// <param name="sqlNoteInfo">SQL注释<see cref="SqlNoteInfo"/></param>
/// <param name="sqlSearchInfo">内容查询SQL</param>
/// <param name="sqlOrderby">Orderby</param>
/// <param name="nCount">记录数</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页大小</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static IEnumerable <T> QueryPager <T>(this DbContext context, string sqlSearchInfo,
string sqlOrderby, ref int nCount, int pageIndex = 1, int pageSize = 10,
dynamic param = null)
{
if (string.IsNullOrEmpty(sqlSearchInfo))
{
throw new ArgumentNullException("参数sqlSearchInfo不能为空");
}
if (string.IsNullOrEmpty(sqlOrderby))
{
throw new ArgumentNullException("sqlOrderby", "分页函数必须有order by");
}
var dbFactory = new DataBaseFactory(context.DatabaseType);
string strSql = dbFactory.GetSqlPages(sqlSearchInfo, pageIndex, pageSize, sqlOrderby);
string sqlSearchCount = SqlDialectBase.GetSqlCount(sqlSearchInfo);
nCount = (int)SqlMapper.ExecuteScalar(context.Connection, sqlSearchCount.ToString(), param);
return(context.Connection.Query <T>(strSql.ToString(), param as object));
}