/// <summary>
/// 获取指定Dto模型的全部结果集
/// 若前端传递排序方式，则默认设置的排序方式不生效
/// </summary>
/// <typeparam name="TResultDto"></typeparam>
/// <typeparam name="TPageSearch"></typeparam>
/// <param name="pageSearch"></param>
/// <param name="whereSql">返回指定的where语句</param>
/// <param name="IngnorIntDefault"></param>
/// <param name="usingCache">是否启用sql缓存(默认启用)</param>
/// <returns></returns>
internal async Task <AttrPageResult <TResultDto> > GetSpecifyResultDto <TResultDto, TPageSearch>(TPageSearch pageSearch,
bool IngnorIntDefault = true,
Func <string> whereSql = null,
bool usingCache = true)
where TResultDto : AttrBaseResult, new()
where TPageSearch : AttrPageSearch
{
StringBuilder sql = new StringBuilder();
string select = string.Empty, join = string.Empty, groupByHaving = string.Empty;
if (usingCache)
{
GetStackTraceModelName <TResultDto>(out select, out join, out groupByHaving);
}
else
{
TResultDto dto = new TResultDto();
select = dto.Select(); //获取查询的字段
join = dto.Join <TResultDto>(); //获取连接的表
groupByHaving = dto.GroupByHaving(); //获取分组部分
}
pageSearch = pageSearch?.WhereValueConvert();
string where = pageSearch.ParaWhere(IngnorIntDefault);//获取参数化查询where条件
if (whereSql != null)
{
if (string.IsNullOrEmpty(where))
{
where += $" Where {whereSql.Invoke()}";
}
else
{
where += $" and {whereSql.Invoke()}";
}
}
//排序规则
string sort = string.Empty;
if (!string.IsNullOrEmpty(pageSearch.SortField))
{
if (pageSearch.SortWay.ToUpper().Trim() == "ASC" || pageSearch.SortWay.ToUpper().Trim() == "DESC")
{
sort = $" Order by {pageSearch.SortField} {pageSearch.SortWay} ";
}
else
{
throw new AttrSqlException("无法识别的排序方式！");
}
}
else
{
sort = $" {pageSearch.DefaultSort()}";
}
//分页规则
string Limit = string.Empty;
if (pageSearch.Index != null && pageSearch.Size != null)
{
if (pageSearch.Index < 1 || pageSearch.Size < 1)
{
throw new AttrSqlException("无法识别的分页数据！");
}
else
{
Limit = DbExtend.PaginationSql(pageSearch.Offset, pageSearch.Size);
}
}
sql.Append(select);
sql.Append(join);
sql.Append(where);
sql.Append(groupByHaving);
sql.Append(sort);
sql.Append(Limit);
var page = new AttrPageResult <TResultDto>(pageSearch.Index, pageSearch.Size);
await TryCatch(async() =>
{
var queryTask = DbExtend.SqlQuery <TResultDto, TPageSearch>(Context, $"{sql}", pageSearch, Tran);
Task <int> countTask = null;
//如果有分页，统计当前查询共有多少条数据
if (!string.IsNullOrEmpty(Limit))
{
string Countsql = $"SELECT COUNT(1) as rownum {join} {where} {groupByHaving}";
try
{
countTask = DbExtend.SqlCountQuery(Context, Countsql, pageSearch);
}
catch (AttrSqlException ex)
{
if (ex.Message.ToLower().Contains($"unknown column"))
{
//去掉limit
Countsql = $"{select} {join} {where} {groupByHaving}";
countTask = DbExtend.SqlRowsQuery(Context, Countsql, pageSearch);
}
}
}
page.Rows = await queryTask;
if (countTask != null)
{
page.Total = await countTask;
}
return(sql.ToString());
});
return(page);
}
internal string DebugQuerySql <TResultDto, TPageSearch>(TPageSearch pageSearch,
bool IngnorIntDefault = true,
Func <string> whereSql = null)
where TPageSearch : AttrPageSearch
where TResultDto : AttrBaseResult, new()
{
TResultDto dto = new TResultDto();
string select = dto.Select(); //获取查询的字段
string join = dto.Join <TResultDto>(); //获取连接的表
string where = pageSearch.ParaWhere(IngnorIntDefault); //获取参数化查询where条件
if (whereSql != null)
{
if (string.IsNullOrEmpty(where))
{
where += $" Where {whereSql.Invoke()}";
}
else
{
where += $" and {whereSql.Invoke()}";
}
}
string groupByHaving = dto.GroupByHaving(); //获取分组部分
//排序规则
string sort = string.Empty;
if (!string.IsNullOrEmpty(pageSearch.SortField))
{
if (pageSearch.SortWay.ToUpper().Trim() == "ASC" || pageSearch.SortWay.ToUpper().Trim() == "DESC")
{
sort = $" Order by {pageSearch.SortField} {pageSearch.SortWay} ";
}
else
{
throw new AttrSqlException("无法识别的排序方式！");
}
}
else
{
sort = $" {pageSearch.DefaultSort()}";
}
//分页规则
string Limit = string.Empty;
if (pageSearch.Index != null && pageSearch.Size != null)
{
if (pageSearch.Index < 1 || pageSearch.Size < 1)
{
throw new AttrSqlException("无法识别的分页数据！");
}
else
{
Limit = DbExtend.PaginationSql(pageSearch.Offset, pageSearch.Size);
}
}
StringBuilder sql = new StringBuilder();
sql.Append(select);
sql.Append(join);
sql.Append(where);
sql.Append(groupByHaving);
sql.Append(sort);
sql.Append(Limit);
return(sql.ToString());
}