internal static string CreateQuerySQLWithActive <T>(Expression <Func <T, bool> > expression, Expression <Func <T, object> > fieldExp = null, DapperSort sort = null) where T : class
{
string fieldNames = "*";
if (fieldExp != null)
{
FieldsFormater format = new FieldsFormater();
format.Visit(fieldExp);
if (format.Parameters.Count > 0)
{
fieldNames = string.Join(",", format.Parameters.Select(x => x.Key));
}
}
var translate = new SqlTranslateFormater();
string sqlWhere = translate.Translate(expression);
if (sort != null && sort.Count > 0)
{
sqlWhere += $" Order By {sort}";
}
string tableName = GetTableName(typeof(T));
StringBuilder sqlBuilder = new StringBuilder($"select {fieldNames} from {tableName} where IsActive=1 AND ");
sqlBuilder.Append(sqlWhere);
return(sqlBuilder.ToString());
}
/// <summary>
/// 异步查询数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="whereExp">查询条件</param>
/// <param name="fieldExp">指定查询字段</param>
/// <param name="sort">排序对象</param>
/// <returns></returns>
public static Task <IEnumerable <T> > QueryAsync <T>(this IDbConnection connection, Expression <Func <T, bool> > whereExp, Expression <Func <T, object> > fieldExp = null, DapperSort sort = null) where T : class
{
return(connection.QueryAsync <T>(CreateQuerySQL <T>(whereExp, fieldExp, sort)));
}
/// <summary>
/// 查找数据集合，并且IsActive=1
/// </summary>
/// <typeparam name="TModel">表名</typeparam>
/// <param name="connection">数据库连接</param>
/// <param name="sort"></param>
/// <returns>实体记录集合</returns>
public static IEnumerable <TModel> GetAllIsActive <TModel>(this IDbConnection connection, DapperSort sort)
{
var modelType = typeof(TModel);
var tableName = GetTableName(modelType);
string template = $"Select * from {tableName} Where IsActive=@IsActive";
if (sort != null && sort.Count > 0)
{
template += string.Format(" Order by {0} ", sort);
}
var dynParms = new DynamicParameters();
dynParms.Add("@IsActive", 1);
return(connection.Query <TModel>(template, dynParms));
}
/// <summary>
/// 查找一条数据,并且IsActive=1
/// </summary>
/// <typeparam name="TModel">表名</typeparam>
/// <param name="connection">数据库连接</param>
/// <param name="id">表记录ID</param>
/// <param name="sort"></param>
/// <returns>一条实体记录</returns>
public static TModel GetIsActive <TModel>(this IDbConnection connection, dynamic id, DapperSort sort = null)
{
var modelType = typeof(TModel);
var tableName = GetTableName(modelType);
var keyPropertyInfo = modelType.GetProperties().FirstOrDefault(p => p.GetCustomAttributes(typeof(KeyAttribute), true).Count() > 0);
if (keyPropertyInfo == null)
{
throw new NotSupportedException("实体类没有定义主键(Key)特性！");
}
string template = $"Select * from {tableName} Where {keyPropertyInfo.Name}=@Id AND IsActive=@IsActive";
if (sort != null && sort.Count > 0)
{
template += string.Format(" Order by {0} ", sort);
}
var dynParms = new DynamicParameters();
dynParms.Add("@IsActive", 1);
dynParms.Add("@Id", id);
return(connection.QueryFirstOrDefault <TModel>(template, dynParms));
}
/// <summary>
/// 查找数据集合，并且IsActive=1
/// </summary>
/// <typeparam name="T">表名</typeparam>
/// <param name="connection">数据库连接</param>
/// <param name="whereExp">where表达式</param>
/// <param name="fieldExp">指定查询字段</param>
/// <param name="sort"></param>
/// <returns>实体记录集合</returns>
public static IEnumerable <T> GetAllForIsActive <T>(this IDbConnection connection, Expression <Func <T, bool> > whereExp, Expression <Func <T, object> > fieldExp = null, DapperSort sort = null) where T : class
{
return(connection.Query <T>(CreateQuerySQLWithActive <T>(whereExp, fieldExp, sort)));
}
/// <summary>
/// 同步查找一条数据
/// </summary>
/// <typeparam name="T">表名</typeparam>
/// <param name="connection">数据库连接</param>
/// <param name="whereExp">where表达式</param>
/// <param name="fieldExp">指定查询字段</param>
/// <param name="sort"></param>
/// <returns>一条实体记录</returns>
public static T Get <T>(this IDbConnection connection, Expression <Func <T, bool> > whereExp, Expression <Func <T, object> > fieldExp = null, DapperSort sort = null) where T : class
{
return(connection.QueryFirstOrDefault <T>(CreateQuerySQL <T>(whereExp, fieldExp, sort)));
}
