using SqlSugar;
|
using Web.Core.Entities.Response;
|
using Web.Core.Exceptions;
|
|
namespace Web.Core.DataBase
|
{
|
public static class SqlSugarExtention
|
{
|
#region 同步
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="query"></param>
|
/// <param name="page"></param>
|
/// <returns></returns>
|
/// <exception cref="BizException"></exception>
|
public static PageListModel<T> GetPageResult<T>(this ISugarQueryable<T> query, PageInput page) where T : class, new()
|
{
|
int count = 0;
|
|
#region 自定义排序
|
|
string orderSql = string.Empty;
|
//设置排序方式
|
if (!string.IsNullOrWhiteSpace(page.SortFileId))
|
{
|
query.QueryBuilder.OrderByValue = null;// 移除自定义排序
|
string[] fieldArr = page.SortFileId.ToLower().Split(',');
|
string[] typeArr;
|
if (string.IsNullOrWhiteSpace(page.SortType))
|
{
|
typeArr = new string[fieldArr.Length];
|
for (int i = 0; i < typeArr.Length; i++)
|
{
|
typeArr[i] = "asc";
|
}
|
}
|
else
|
{
|
typeArr = page.SortType.ToLower().Split(",");
|
}
|
|
if (fieldArr.Length > 0 && fieldArr.Length <= 0)
|
{
|
foreach (var item in fieldArr)
|
{
|
orderSql += item + "asc,";
|
}
|
}
|
else
|
{
|
if (fieldArr.Length > 0 && fieldArr.Length != typeArr.Length)
|
{
|
throw new BizException("排序参数异常");
|
}
|
for (int i = 0; i < fieldArr.Length; i++)
|
{
|
orderSql += fieldArr[i] + $"{typeArr[i]},";
|
}
|
}
|
}
|
|
#endregion 自定义排序
|
|
#region 有排序
|
|
if (!string.IsNullOrWhiteSpace(orderSql))
|
{
|
orderSql = orderSql.Trim(',');
|
string sqlTemplate = "SELECT * FROM ({0}) ORDER BY {1}";
|
string sql = query.ToSql().Key;
|
var paramLst = query.ToSql().Value;
|
foreach (var item in paramLst)
|
{
|
if (item.Value == null)
|
{
|
continue;
|
}
|
if (item.DbType.ToString() == "DateTime")
|
{
|
DateTime dtime = (DateTime)item.Value;
|
sql = sql.Replace(item.ParameterName, "'" + dtime.ToString("yyyy-MM-dd") + "'");
|
continue;
|
}
|
sql = sql.Replace(item.ParameterName, "'" + item.Value?.ToString() + "'");
|
}
|
|
var orderLst = query.Context.SqlQueryable<T>(string.Format(sqlTemplate, sql, orderSql)).ToPageList(page.PageIndex, page.PageRows, ref count);
|
return new PageListModel<T>()
|
{
|
PageList = orderLst,
|
Total = count
|
};
|
}
|
|
#endregion 有排序
|
|
List<T> lst = query.ToPageList(page.PageIndex, page.PageRows, ref count);
|
return new PageListModel<T>()
|
{
|
PageList = lst,
|
Total = count
|
};
|
}
|
|
#endregion 同步
|
|
#region 异步
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="query"></param>
|
/// <param name="page"></param>
|
/// <returns></returns>
|
/// <exception cref="BizException"></exception>
|
public static async Task<PageListModel<T>> GetPageResultAsync<T>(this ISugarQueryable<T> query, PageInput page) where T : class, new()
|
{
|
RefAsync<int> count = 0;
|
|
#region 自定义排序
|
|
string orderSql = string.Empty;
|
if (!string.IsNullOrWhiteSpace(page.SortFileId))
|
{
|
query.QueryBuilder.OrderByValue = null;// 移除默认排序
|
string[] fieldArr = page.SortFileId.ToLower().Split(',');
|
string[] typeArr;
|
if (string.IsNullOrWhiteSpace(page.SortType))
|
{
|
typeArr = new string[fieldArr.Length];
|
for (int i = 0; i < fieldArr.Length; i++)
|
{
|
typeArr[i] = "asc";
|
}
|
}
|
else
|
{
|
typeArr = page.SortType.ToLower().Split(",");
|
}
|
// 属性校验，保证返回实体存在当前字段
|
List<string> fileds = typeof(T).GetProperties().Select(i => i.Name.ToLower()).ToList();
|
if (fieldArr.Length > 0 && typeArr.Length <= 0)
|
{
|
foreach (var item in fieldArr)
|
{
|
if (fileds.Contains(item))
|
{
|
orderSql += item + "asc,";
|
}
|
}
|
}
|
else
|
{
|
if (fieldArr.Length > 0 && fieldArr.Length != typeArr.Length)
|
{
|
throw new BizException("排序参数异常");
|
}
|
for (int i = 0; i < fieldArr.Length; i++)
|
{
|
if (fileds.Contains(fieldArr[i]))
|
{
|
orderSql += fieldArr[i] + $"{typeArr[i]}";
|
}
|
}
|
}
|
}
|
|
#endregion 自定义排序
|
|
if (!string.IsNullOrWhiteSpace(orderSql))
|
{
|
orderSql = orderSql.Trim(',');
|
string sqlTemplate = "SELECT * FROM ({0}) ORDER BY {1}";
|
string sql = query.ToSql().Key;
|
var parmLst = query.ToSql().Value;
|
foreach (var item in parmLst)
|
{
|
if (item.Value == null)
|
{
|
continue;
|
}
|
if (item.DbType == System.Data.DbType.DateTime)
|
{
|
DateTime dtime = (DateTime)item.Value;
|
sql = sql.Replace(item.ParameterName, "'" + dtime.ToString("yyyy-MM-dd") + "'");
|
continue;
|
}
|
else if (item.DbType == System.Data.DbType.String)
|
{
|
if (item.Value.ToString().Contains("'"))
|
{
|
throw new BizException($"参数值【{item.Value}】含有非法字符，无法查询！");
|
}
|
}
|
sql = sql.Replace(item.ParameterName, "'" + item.Value?.ToString() + "'");
|
}
|
|
var orderLst = await query.Context.SqlQueryable<T>(string.Format(sqlTemplate, sql, orderSql)).ToPageListAsync(page.PageIndex, page.PageRows, count);
|
return new PageListModel<T>()
|
{
|
PageList = orderLst,
|
Total = count
|
};
|
}
|
|
List<T> lst = await query.ToPageListAsync(page.PageIndex, page.PageRows, count);
|
return new PageListModel<T>()
|
{
|
PageList = lst,
|
Total = count
|
};
|
}
|
|
#endregion 异步
|
|
#region 联合查询
|
|
/// <summary>
|
///
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="queryable"></param>
|
/// <param name="unionQueryables"></param>
|
/// <returns></returns>
|
public static ISugarQueryable<T> Union<T>(this ISugarQueryable<T> queryable, params ISugarQueryable<T>[] unionQueryables)
|
{
|
return queryable.Union(unionQueryables);
|
}
|
|
#endregion 联合查询
|
}
|
}
|
