public IList <Table> QueryListPagedByDynamic(PageFilterDto pageFilterDto)
{
return(_dapperRepository.QueryListPagedByDynamic <Table>(pageFilterDto));
}
public async Task <IList <Table> > QueryListPagedByDynamicAsync(PageFilterDto pageFilterDto)
{
return(await _dapperRepository.QueryListPagedByDynamicAsync <Table>(pageFilterDto));
}
public async Task <int> QueryCountByDynamicAsync(PageFilterDto pageFilterDto)
{
return(await _dapperRepository.QueryCountByDynamicAsync <Table>(pageFilterDto));
}
public int QueryCountByDynamic(PageFilterDto pageFilterDto)
{
return(_dapperRepository.QueryCountByDynamic <Table>(pageFilterDto));
}
/// <summary>
/// 分页查询(动态传参)
/// </summary>
/// <param name="pageFilterDto"></param>
/// <returns></returns>
public static Task <IEnumerable <T> > GetListPagedByDynamicAsync <T>(this IDbConnection connection, PageFilterDto pageFilterDto, IDbTransaction transaction = null)
{
var currenttype = typeof(T);
var name = GetTableName(currenttype);
var sb = new StringBuilder();
sb.Append("Select ");
BuildSelect(sb, GetScaffoldableProperties <T>().ToArray());
sb.AppendFormat(" from {0}", name);
StringBuilder conditions = new StringBuilder(" where 1=1 ");
var dynamicParameters = new DynamicParameters();
foreach (var sqlfilter in pageFilterDto.Filters)
{
var keyvalue = SetSqlFilterAndParameterByMysql(sqlfilter);
dynamicParameters.Add(sqlfilter.FieldName, keyvalue.Value);
conditions.Append(keyvalue.Key);
}
if (pageFilterDto.Sorts != null && pageFilterDto.Sorts.Any())
{
conditions.Append(" ORDER BY ");
int tempLength = conditions.Length;
foreach (var sort in pageFilterDto.Sorts)
{
if (conditions.Length != tempLength)
{
conditions.Append(",");
}
conditions.Append($" {sort.Key} {sort.Value} ");
}
conditions.Append(";");
if (pageFilterDto.PageSize != 0)
{
int skip = (pageFilterDto.PageIndex - 1) * pageFilterDto.PageSize;
dynamicParameters.AddDynamicParams(new { skip = skip });
dynamicParameters.AddDynamicParams(new { pageSize = pageFilterDto.PageSize });
conditions.Append(" LIMIT @pageSize OFFSET @skip; ");
}
}
sb.Append(" " + conditions);
if (Debugger.IsAttached)
{
Trace.WriteLine(String.Format("GetList<{0}>: {1}", currenttype, sb));
}
return(connection.QueryAsync <T>(sb.ToString(), dynamicParameters, transaction));
}
/// <summary>
/// 查询总数(动态传参)
/// </summary>
/// <param name="pageFilterDto"></param>
/// <returns></returns>
public static Task <int> GetCountByDynamicAsync <T>(this IDbConnection connection, PageFilterDto pageFilterDto, IDbTransaction transaction = null)
{
var currenttype = typeof(T);
var name = GetTableName(currenttype);
var sb = new StringBuilder();
sb.Append("Select Count(*) as `count`");
sb.AppendFormat(" from {0}", name);
StringBuilder conditions = new StringBuilder(" where 1=1 ");
var dynamicParameters = new DynamicParameters();
foreach (var sqlfilter in pageFilterDto.Filters)
{
var keyvalue = SetSqlFilterAndParameterByMysql(sqlfilter);
dynamicParameters.Add(sqlfilter.FieldName, keyvalue.Value);
conditions.Append(keyvalue.Key);
}
sb.Append(" " + conditions);
var result = connection.QueryFirstAsync <int>(sb.ToString(), dynamicParameters, transaction);
return(result);
}
public IActionResult BlogPage(PageFilterDto pageFilterDto)
{
var pageData = _blogService.LoadDataPage(pageFilterDto);
return(Json(pageData));
}