/// <summary>
/// 通用单表查询，按照模型指定的where条件，查询指定单表的全部数据
/// </summary>
/// <typeparam name="TPageSearch"></typeparam>
/// <param name="pageSearch"></param>
/// <param name="tableName"></param>
/// <param name="IngnorIntDefault">int类型的默认值是否忽略,默认忽略</param>
/// <returns></returns>
internal async Task <AttrPageResult <TEntity> > GetAll <TPageSearch, TEntity>(TPageSearch pageSearch, string tableName = "", bool IngnorIntDefault = true)
where TPageSearch : AttrPageSearch
where TEntity : AttrEntityBase, new()
{
if (string.IsNullOrEmpty(tableName))
{
tableName = typeof(TEntity).Name;
}
var page = new AttrPageResult <TEntity>(pageSearch.Index, pageSearch.Size);
string where = pageSearch.ParaWhere(IngnorIntDefault);//获取参数化查询where条件
string sort = string.Empty;
if (!string.IsNullOrEmpty(pageSearch.SortField))
{
sort = $" Order by {pageSearch.SortField} {pageSearch.SortWay}";
}
else
{
sort = $" {pageSearch.DefaultSort()}";
}
await this.TryCatch(async() =>
{
page.Rows = await DbExtend.SqlQuery <TEntity, TPageSearch>(Context, $"select * from {tableName} {where} {sort}", pageSearch, Tran);
return($"select * from {tableName} {where} {sort}");
});
return(page);
}
/// <summary>
/// 执行指定的sql语句
/// </summary>
/// <typeparam name="TResultDto"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
internal async Task <AttrPageResult <TResultDto> > ExecQuerySql <TResultDto>(string sql)
where TResultDto : AttrBaseResult, new()
{
var page = new AttrPageResult <TResultDto>();
await this.TryCatch(async() =>
{
page.Rows = await DbExtend.SqlQuery <TResultDto, AttrEntityBase>(Context, $"{sql}", null, Tran);
return(sql);
});
return(page);
}
/// <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);
}
