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))); }