using Infrastructure.Extensions; using Mapster; using SqlSugar; using SqlSugar.IOC; using System; using System.Collections.Generic; using System.Data; using System.Linq.Expressions; using System.Reflection; using ZR.Model; namespace ZR.Repository { /// /// /// /// public class BaseRepository : SimpleClient where T : class, new() { public ITenant itenant = null;//多租户事务 public BaseRepository(ISqlSugarClient context = null) : base(context) { //通过特性拿到ConfigId var configId = typeof(T).GetCustomAttribute()?.configId; if (configId != null) { Context = DbScoped.SugarScope.GetConnectionScope(configId);//根据类传入的ConfigId自动选择 } else { Context = context ?? DbScoped.SugarScope.GetConnectionScope(0);//没有默认db0 } //Context = DbScoped.SugarScope.GetConnectionScopeWithAttr(); itenant = DbScoped.SugarScope;//设置租户接口 } #region add /// /// 插入实体 /// /// /// public int Add(T t, bool ignoreNull = true) { return Context.Insertable(t).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand(); } public int Insert(List t) { return Context.Insertable(t).ExecuteCommand(); } public int Insert(T parm, Expression> iClumns = null, bool ignoreNull = true) { return Context.Insertable(parm).InsertColumns(iClumns).IgnoreColumns(ignoreNullColumn: ignoreNull).ExecuteCommand(); } public IInsertable Insertable(T t) { return Context.Insertable(t); } #endregion add #region update public IUpdateable Updateable(T entity) { return Context.Updateable(entity); } /// /// 实体根据主键更新 /// /// /// /// public int Update(T entity, bool ignoreNullColumns = false) { return Context.Updateable(entity).IgnoreColumns(ignoreNullColumns).ExecuteCommand(); } /// /// 实体根据主键更新指定字段 /// return Update(user, t => new { t.NickName, }, true); /// /// /// /// /// public int Update(T entity, Expression> expression, bool ignoreAllNull = false) { return Context.Updateable(entity).UpdateColumns(expression).IgnoreColumns(ignoreAllNull).ExecuteCommand(); } /// /// 根据指定条件更新指定列 eg:Update(new SysUser(){ }, it => new { it.Status }, f => f.Userid == 1)); /// 只更新Status列,条件是包含 /// /// /// /// /// public int Update(T entity, Expression> expression, Expression> where) { return Context.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand(); } public int Update(SqlSugarClient client, T entity, Expression> expression, Expression> where) { return client.Updateable(entity).UpdateColumns(expression).Where(where).ExecuteCommand(); } /// /// /// /// /// /// 默认为true /// public int Update(T entity, List list = null, bool isNull = true) { if (list == null) { list = new List() { "Create_By", "Create_time" }; } return Context.Updateable(entity).IgnoreColumns(isNull).IgnoreColumns(list.ToArray()).ExecuteCommand(); } //public bool Update(List entity) //{ // var result = base.Context.Ado.UseTran(() => // { // base.Context.Updateable(entity).ExecuteCommand(); // }); // return result.IsSuccess; //} /// /// 更新指定列 eg:Update(w => w.NoticeId == model.NoticeId, it => new SysNotice(){ Update_time = DateTime.Now, Title = "通知标题" }); /// /// /// /// public int Update(Expression> where, Expression> columns) { return Context.Updateable().SetColumns(columns).Where(where).RemoveDataCache().ExecuteCommand(); } #endregion update public DbResult UseTran(Action action) { try { var result = Context.Ado.UseTran(() => action()); return result; } catch (Exception ex) { Context.Ado.RollbackTran(); Console.WriteLine(ex.Message); throw; } } public IStorageable Storageable(T t) { return Context.Storageable(t); } public IStorageable Storageable(List t) { return Context.Storageable(t); } /// /// /// /// /// 增删改查方法 /// public DbResult UseTran(SqlSugarClient client, Action action) { try { var result = client.AsTenant().UseTran(() => action()); return result; } catch (Exception ex) { client.AsTenant().RollbackTran(); Console.WriteLine(ex.Message); throw; } } public bool UseTran2(Action action) { var result = Context.Ado.UseTran(() => action()); return result.IsSuccess; } #region delete public IDeleteable Deleteable() { return Context.Deleteable(); } /// /// 批量删除 /// /// /// public int Delete(object[] obj) { return Context.Deleteable().In(obj).ExecuteCommand(); } public int Delete(object id) { return Context.Deleteable(id).ExecuteCommand(); } public int DeleteTable() { return Context.Deleteable().ExecuteCommand(); } public bool Truncate() { return Context.DbMaintenance.TruncateTable(); } #endregion delete #region query public bool Any(Expression> expression) { return Context.Queryable().Where(expression).Any(); } public ISugarQueryable Queryable() { return Context.Queryable(); } public (List, int) QueryableToPage(Expression> expression, int pageIndex = 0, int pageSize = 10) { int totalNumber = 0; var list = Context.Queryable().Where(expression).ToPageList(pageIndex, pageSize, ref totalNumber); return (list, totalNumber); } public (List, int) QueryableToPage(Expression> expression, string order, int pageIndex = 0, int pageSize = 10) { int totalNumber = 0; var list = Context.Queryable().Where(expression).OrderBy(order).ToPageList(pageIndex, pageSize, ref totalNumber); return (list, totalNumber); } public (List, int) QueryableToPage(Expression> expression, Expression> orderFiled, string orderBy, int pageIndex = 0, int pageSize = 10) { int totalNumber = 0; if (orderBy.Equals("DESC", StringComparison.OrdinalIgnoreCase)) { var list = Context.Queryable().Where(expression).OrderBy(orderFiled, OrderByType.Desc).ToPageList(pageIndex, pageSize, ref totalNumber); return (list, totalNumber); } else { var list = Context.Queryable().Where(expression).OrderBy(orderFiled, OrderByType.Asc).ToPageList(pageIndex, pageSize, ref totalNumber); return (list, totalNumber); } } public List SqlQueryToList(string sql, object obj = null) { return Context.Ado.SqlQuery(sql, obj); } /// /// 根据主值查询单条数据 /// /// 主键值 /// 泛型实体 public T GetId(object pkValue) { return Context.Queryable().InSingle(pkValue); } /// /// 根据条件查询分页数据 /// /// /// /// public PagedInfo GetPages(Expression> where, PagerInfo parm) { var source = Context.Queryable().Where(where); return source.ToPage(parm); } public PagedInfo GetPages(Expression> where, PagerInfo parm, Expression> order, OrderByType orderEnum = OrderByType.Asc) { var source = Context.Queryable().Where(where).OrderByIF(orderEnum == OrderByType.Asc, order, OrderByType.Asc).OrderByIF(orderEnum == OrderByType.Desc, order, OrderByType.Desc); return source.ToPage(parm); } public PagedInfo GetPages(Expression> where, PagerInfo parm, Expression> order, string orderByType) { return GetPages(where, parm, order, orderByType == "desc" ? OrderByType.Desc : OrderByType.Asc); } /// /// 查询所有数据(无分页,请慎用) /// /// public List GetAll(bool useCache = false, int cacheSecond = 3600) { return Context.Queryable().WithCacheIF(useCache, cacheSecond).ToList(); } #endregion query /// /// 此方法不带output返回值 /// var list = new List(); /// list.Add(new SugarParameter(ParaName, ParaValue)); input /// /// /// /// public DataTable UseStoredProcedureToDataTable(string procedureName, List parameters) { return Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters); } /// /// 带output返回值 /// var list = new List(); /// list.Add(new SugarParameter(ParaName, ParaValue, true)); output /// list.Add(new SugarParameter(ParaName, ParaValue)); input /// /// /// /// public (DataTable, List) UseStoredProcedureToTuple(string procedureName, List parameters) { var result = (Context.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters), parameters); return result; } } /// /// 分页查询扩展 /// public static class QueryableExtension { /// /// 读取列表 /// /// /// 查询表单式 /// 分页参数 /// public static PagedInfo ToPage(this ISugarQueryable source, PagerInfo parm) { var page = new PagedInfo(); var total = 0; page.PageSize = parm.PageSize; page.PageIndex = parm.PageNum; page.Result = source.OrderByIF(parm.Sort.IsNotEmpty(), $"{parm.Sort.ToSqlFilter()} {(parm.SortType.Contains("desc") ? "desc" : "asc")}") .ToPageList(parm.PageNum, parm.PageSize, ref total); page.TotalNum = total; return page; } /// /// 转指定实体类Dto /// /// /// /// /// /// public static PagedInfo ToPage(this ISugarQueryable source, PagerInfo parm) { var page = new PagedInfo(); var total = 0; page.PageSize = parm.PageSize; page.PageIndex = parm.PageNum; var result = source .OrderByIF(parm.Sort.IsNotEmpty(), $"{parm.Sort.ToSqlFilter()} {(parm.SortType.Contains("desc") ? "desc" : "asc")}") .ToPageList(parm.PageNum, parm.PageSize, ref total); page.TotalNum = total; page.Result = result.Adapt>(); return page; } } }