/// <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> /// 新增Dto模型返回主键 /// </summary> /// <param name="model"></param> /// <returns></returns> public async virtual Task <long> Insert(AttrBaseModel model) { long newIncreaseKet = 0; await this.TryCatch(async() => { string sql = model.InsertDtoModel(); newIncreaseKet = await DbExtend.ExecuteNonQueryByKey(Context, sql, model, Tran); return(sql); }); return(newIncreaseKet); }
/// <summary> /// 执行指定的sql语句内部使用 /// </summary> /// <typeparam name="TResult"></typeparam> /// <param name="sql"></param> /// <returns></returns> internal async Task <IEnumerable <TResult> > ExecQuerySqlInside <TResult>(string sql) where TResult : AttrBaseResult, new() { IEnumerable <TResult> result = null; await this.TryCatch(async() => { result = await DbExtend.SqlQuery <TResult, AttrEntityBase>(Context, $"{sql}", null, Tran); return(sql); }); return(result); }
/// <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> /// 直接更新实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <param name="IgnorIntDefault"></param> /// <returns></returns> internal async virtual Task <int> UpdateField <T>(T entity, string PrimaryKey = "", bool IgnorIntDefault = true) where T : AttrEntityBase { int result = 0; await this.TryCatch(async() => { string sql = entity.UpdateField(PrimaryKey); result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); return(sql); }); return(result); }
/// <summary> /// 新增返回自增主键 /// </summary> /// <param name="entity"></param> /// <returns></returns> internal async virtual Task <long> InsertReturnKey <T>(T entity) where T : AttrEntityBase { long newIncreaseKet = 0; await this.TryCatch(async() => { string sql = entity.InsertEntity(); newIncreaseKet = await DbExtend.ExecuteNonQueryByKey(Context, sql, entity, Tran); return(sql); }); return(newIncreaseKet); }
/// <summary> /// 新增返回受影响行数 /// </summary> /// <param name="entity"></param> /// <returns></returns> internal async virtual Task <int> Insert <T>(T entity) where T : AttrEntityBase { int result = 0; await this.TryCatch(async() => { string sql = entity.InsertEntity(); result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); return(sql); }); return(result); }
/// <summary> /// 根据指定的字段删除 /// </summary> /// <param name="entity"></param> /// <param name="condition"></param> /// <returns></returns> internal async virtual Task <int> Delete <T>(T entity, string[] condition) where T : AttrEntityBase { int result = 0; await this.TryCatch(async() => { string sql = entity.DeleteByCondition(condition); result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); return(sql); }); return(result); }
/// <summary> /// 软删除 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <param name="condition"></param> /// <returns></returns> internal async virtual Task <int> SoftDelete <T>(T entity, string softDeleteField, int value = 0, string PrimaryKey = "", bool IngnorIntDefault = true) where T : AttrEntityBase { int result = 0; await this.TryCatch(async() => { string sql = entity.SoftDeleteByCondition(softDeleteField, value, PrimaryKey, IngnorIntDefault); result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); return(sql); }); return(result); }
/// <summary> /// 批量新增(非参数化,建议在大批量插入数据时使用) /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="entities"></param> /// <returns></returns> internal async Task <int> NonPatameterBatchInsert <TEntity>(TEntity[] entities) where TEntity : AttrEntityBase, new() { int result = 0; if (entities?.Count() > 0) { string sql = entities.BatchInsertEntity(); await this.TryCatch(async() => { result += await DbExtend.ExecuteNonQuery <AttrEntityBase>(Context, sql.ToString(), null, Tran); return(sql.ToString()); }); } return(result); }
/// <summary> /// 判断指定字段是否重复 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <param name="IsAddOrEdit">0新增,1编辑</param> /// <returns></returns> internal async Task <bool> CheckFieldRepeat <TBaseModel>(TBaseModel model, int IsAddOrEdit) where TBaseModel : AttrBaseModel { List <string> sqls = model.NotAllowRepeatSql(); if (sqls.Count == 0) { return(true); } int nums = 1; foreach (var sql in sqls) { int resultnum = await DbExtend.SqlRowsQuery(Context, sql, model, Tran); if (IsAddOrEdit == 0) { if (resultnum != 0) { throw new AttrSqlException($"{model.GetErrorMsg(nums)}"); } } else { if (resultnum > 1) { throw new AttrSqlException($"{model.GetErrorMsg(nums)}"); } if (resultnum == 1) { //判断返回的是否是当前编辑的实体 string keysql = model.NotAllowKeySql(nums); var result = await DbExtend.SqlQuery <dynamic, TBaseModel>(Context, keysql, model, Tran); resultnum = result.Count(); if (resultnum == 0) { throw new AttrSqlException($"{model.GetErrorMsg(nums)}"); } } } ++nums; } return(true); }
/// <summary> /// 更新有值的字段,更新条件为字段中第一个包含ID的字段 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <param name="Primary">主键字段,不指定则默认实体第一个带有Id的字段为主键</param> /// <param name="IgnorIntDefault"></param> /// <returns></returns> internal async virtual Task <int> UpdateHasValueField <T>(T entity, string Primary = "", bool IgnorIntDefault = true) where T : AttrEntityBase { string sql = entity.GetUpdateField(Primary, IgnorIntDefault); int result = 0; if (string.IsNullOrEmpty(sql)) { throw new AttrSqlException("请完善需要编辑的信息!"); } await this.TryCatch(async() => { result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); return(sql); }); return(result); }
/// <summary> /// 根据Dto模型字段特性更新指定表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="dto"></param> /// <param name="IgnorIntDefault"></param> /// <returns></returns> public async virtual Task <int> UpdateDtoAttributeField <T>(T dto, bool IgnorIntDefault = true) where T : AttrBaseModel { string sql = dto.UpdateFieldByDtoAttribute <T>(IgnorIntDefault); int result = 0; if (string.IsNullOrEmpty(sql)) { throw new AttrSqlException("请完善需要编辑的信息!"); } await this.TryCatch(async() => { result = await DbExtend.ExecuteNonQuery(Context, sql, dto, Tran); return(sql); }); return(result); }
/// <summary> /// 根据Dto特性配置为更新条件,更新有值的字段 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <param name="IgnorIntDefault"></param> /// <returns></returns> internal async virtual Task <int> UpdateHasValueFieldByDto <TDo, TEntity>(TDo dto, TEntity entity, bool IgnorIntDefault = true) where TDo : AttrBaseModel where TEntity : AttrEntityBase { string sql = dto.UpdateFieldByEntityCondition(entity, IgnorIntDefault); int result = 0; if (string.IsNullOrEmpty(sql)) { throw new AttrSqlException("请完善需要编辑的信息!"); } await this.TryCatch(async() => { result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); return(sql); }); return(result); }
/// <summary> /// 批量新增(参数化) /// </summary> /// <param name="entity"></param> /// <returns></returns> internal async Task <int> BatchInsert <TEntity>(TEntity[] entities) where TEntity : AttrEntityBase, new() { int result = 0; if (entities?.Count() > 0) { await this.TryCatch(async() => { //默认事务新增 if (Tran == null) { await TransactionRun(async() => { foreach (var item in entities) { string sql = item.InsertEntity(); result += await DbExtend.ExecuteNonQuery(Context, sql.ToString(), item, Tran); } if (result == entities.Length) { return(AttrResultModel.Success()); } else { return(AttrResultModel.Error("批量新增失败!")); } }); } else { foreach (var item in entities) { string sql = item.InsertEntity(); result += await DbExtend.ExecuteNonQuery(Context, sql.ToString(), item, Tran); } } return(""); }); } return(result); }
/// <summary> /// 执行指定的更新语句 /// </summary> /// <param name="entity"></param> /// <param name="sql"></param> /// <returns></returns> internal async virtual Task <int> ExecUpdateSql <T>(T entity, string sql) where T : AttrEntityBase { int result = 0; if (string.IsNullOrEmpty(sql)) { throw new AttrSqlException("请完善需要编辑的信息!"); } await this.TryCatch(async() => { if (entity != null) { result = await DbExtend.ExecuteNonQuery(Context, sql, entity, Tran); } else { result = await DbExtend.ExecuteNonQuery(Context, sql, Tran); } return(sql); }); return(result); }
internal string DebugQuerySql <TResultDto, TPageSearch>(TPageSearch pageSearch, bool IngnorIntDefault = true, Func <string> whereSql = null) where TPageSearch : AttrPageSearch where TResultDto : AttrBaseResult, new() { TResultDto dto = new TResultDto(); string select = dto.Select(); //获取查询的字段 string join = dto.Join <TResultDto>(); //获取连接的表 string where = pageSearch.ParaWhere(IngnorIntDefault); //获取参数化查询where条件 if (whereSql != null) { if (string.IsNullOrEmpty(where)) { where += $" Where {whereSql.Invoke()}"; } else { where += $" and {whereSql.Invoke()}"; } } string groupByHaving = dto.GroupByHaving(); //获取分组部分 //排序规则 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); } } StringBuilder sql = new StringBuilder(); sql.Append(select); sql.Append(join); sql.Append(where); sql.Append(groupByHaving); sql.Append(sort); sql.Append(Limit); return(sql.ToString()); }
/// <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); }