OA2023/OASystem/OASystem.Infrastructure/Repositories/BaseRepository.cs

namespace OASystem.Infrastructure.Repositories
{
    public class BaseRepository<TEntity, TDto> where TEntity : EntityBase, new()
    {
        public SqlSugarClient _sqlSugar;
        public BaseRepository(SqlSugarClient sqlSugar)
        {
            _sqlSugar = sqlSugar;
        }

        #region 多租户
        /// <summary>
        /// 变更数据库
        /// </summary>
        /// <param name="db"></param>
        public void ChangeDataBase(DBEnum db)
        {
            _sqlSugar.ChangeDatabase(db);
        }
        #endregion

        #region 原生Sql
        public virtual Task<int> ExecuteCommandAsync(string sql)
        {
            return _sqlSugar.Ado.ExecuteCommandAsync(sql);
        }
        public virtual Task<DataTable> GetDataTableAsync(string sql)
        {
            return _sqlSugar.Ado.GetDataTableAsync(sql);
        }
        public virtual Task<object> GetScalarAsync(string sql)
        {
            return _sqlSugar.Ado.GetScalarAsync(sql);
        }
        #endregion

        #region 事务操作
        public void BeginTran()
        {
            _sqlSugar.BeginTran();
        }
        public void CommitTran()
        {
            _sqlSugar.CommitTran();
        }
        public void RollbackTran()
        {
            _sqlSugar.RollbackTran();
        }
        #endregion

        #region 库表管理
        public bool CreateDataBase()
        {
            return _sqlSugar.DbMaintenance.CreateDatabase();
        }
        public bool CopyTable(string oldname, string newname)
        {
            if (!_sqlSugar.DbMaintenance.IsAnyTable(newname, false))
            {
                return _sqlSugar.DbMaintenance.BackupTable(oldname, newname, 0);
            }
            return false;
        }
        public bool TruncateTable(string tablename)
        {
            return _sqlSugar.DbMaintenance.TruncateTable(tablename);
        }
        public void CreateTable(Type entityType)
        {
            _sqlSugar.CodeFirst.SetStringDefaultLength(200).BackupTable().InitTables(entityType);
        }
        public void CreateTable(Type[] entityTypes)
        {
            _sqlSugar.CodeFirst.SetStringDefaultLength(200).BackupTable().InitTables(entityTypes);
        }
        #endregion

        #region Extension

        /// <summary>
        ///  获取单个对象  异步
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual async Task<TEntity> GetSingleInfoBySqlWithNolockAsync(string sql)
        {
            return await _sqlSugar.SqlQueryable<TEntity>(sql).FirstAsync();
        }

        /// <summary>
        /// 获取单个对象 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual TEntity GetSingleInfoBySqlWithNolock(string sql)
        {
            return _sqlSugar.SqlQueryable<TEntity>(sql).First();
        }

        /// <summary>
        /// 获取集合 异步
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual async Task<List<TEntity>> GetListBySqlWithNolockAsync(string sql)
        {
            return await _sqlSugar.SqlQueryable<TEntity>(sql).ToListAsync();
        }

        /// <summary>
        /// 获取集合 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual List<TEntity> GetListBySqlWithNolock(string sql)
        {
            return _sqlSugar.SqlQueryable<TEntity>(sql).ToList();
        }

        /// <summary>
        /// 添加返回主键Id
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual Task<int> AddAsyncReturnId(TEntity entity)
        {
            //return _sqlSugar.Insertable(entity).ExecuteCommandAsync();
            return _sqlSugar.Insertable(entity).ExecuteReturnIdentityAsync();
        }

        /// <summary>
        /// 添加返回主键Id(泛型)
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual Task<int> AddAsyncReturnId<T>(T entity) where T : EntityBase, new()
        {
            return _sqlSugar.Insertable<T>(entity).ExecuteReturnIdentityAsync();
        }

        /// <summary>
        /// 添加返回主键Id(同步、泛型)
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual int AddReturnId<T>(T entity) where T : EntityBase, new()
        {
            return _sqlSugar.Insertable<T>(entity).ExecuteReturnIdentity();
        }

        /// <summary>
        /// 删除单挑数据，加上删除者Id
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="DeleteUserId"></param>
        /// <returns></returns>
        public virtual async Task<bool> SoftDeleteByIdAsync<T>(string id, int DeleteUserId) where T : EntityBase, new()
        {
            var result = await _sqlSugar.Updateable<TEntity>().Where(a => a.Id.Equals(id)).SetColumns(a => new TEntity()
            {
                IsDel = 1,
                DeleteUserId = DeleteUserId,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }).ExecuteCommandAsync();
            return result > 0;
        }

        /// <summary>
        /// 根据Dictionary,TableName添加数据
        /// </summary>
        /// <param name="dic">key[列名],value[值]</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public virtual async Task<bool> InsertDataByDictionary(Dictionary<string, object> dic, string tableName)
        {
            var result = await _sqlSugar.Insertable(dic).AS(tableName).ExecuteReturnIdentityAsync();

            return result > 0;
        }

        #endregion

        public virtual Task<bool> AnyAsync(Expression<Func<TEntity, bool>> exp)
        {
            return _sqlSugar.Queryable<TEntity>().AnyAsync(exp);
        }
        public virtual ISugarQueryable<TEntity> Query(Expression<Func<TEntity, bool>> exp)
        {
            return _sqlSugar.Queryable<TEntity>().Where(a => a.IsDel < 1).Where(exp);
        }
        public virtual ISugarQueryable<TDto> QueryDto(Expression<Func<TEntity, bool>> exp)
        {
            return _sqlSugar.Queryable<TEntity>().Where(a => a.IsDel < 1).Where(exp).Select<TDto>();
        }
        public virtual Task<TDto> GetDtoAsync(Expression<Func<TEntity, bool>> exp)
        {
            return _sqlSugar.Queryable<TEntity>().Where(s => s.IsDel < 1).Where(exp).Select<TDto>().FirstAsync();
        }
        public virtual Task<int> AddAsync(TEntity entity)
        {
            return _sqlSugar.Insertable(entity).ExecuteCommandAsync();
        }
        public virtual Task<TEntity> GetAsync(Expression<Func<TEntity, bool>> exp)
        {
            return _sqlSugar.Queryable<TEntity>().FirstAsync(exp);
        }
        public virtual async Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> wherexp)
        {
            var result = await _sqlSugar.Deleteable<TEntity>().Where(wherexp).ExecuteCommandAsync();
            return result > 0;
        }
        public virtual async Task<bool> UpdateAsync(Expression<Func<TEntity, bool>> wherexp, Expression<Func<TEntity, TEntity>> upexp)
        {
            var result = await _sqlSugar.Updateable<TEntity>().Where(wherexp).SetColumns(upexp).ExecuteCommandAsync();
            return result > 0;
        }
        public virtual async Task<bool> SoftDeleteAsync(string id)
        {
            var result = await _sqlSugar.Updateable<TEntity>().Where(a => a.Id.Equals(id)).SetColumns(a => new TEntity()
            {
                IsDel = 1,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }).ExecuteCommandAsync();
            return result > 0;
        }

        public virtual async Task<bool> SoftDeleteAsync(Expression<Func<TEntity, bool>> wherexp, int userid)
        {
            var result = await _sqlSugar.Updateable<TEntity>().Where(wherexp).SetColumns(a => new TEntity()
            {
                DeleteUserId = userid,
                IsDel = 1,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }).ExecuteCommandAsync();
            return result > 0;
        }

        #region 泛型CRUD
        public virtual Task<bool> AnyAsync<T>(Expression<Func<T, bool>> exp) where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(s => s.IsDel < 1).AnyAsync(exp);
        }
        public virtual Task<Dto> GetDtoAsync<T, Dto>(Expression<Func<T, bool>> exp) where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(s => s.IsDel < 1).Where(exp).Select<Dto>().FirstAsync();
        }
        public virtual ISugarQueryable<Dto> QueryDto<T, Dto>(Expression<Func<T, bool>> exp) where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(a => a.IsDel < 1).Where(exp).Select<Dto>();
        }
        public virtual ISugarQueryable<Dto> QueryDto<T, Dto>() where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(a => a.IsDel < 1).Select<Dto>();
        }
        public virtual Task<int> AddAsync<T>(T entity) where T : EntityBase, new()
        {
            return _sqlSugar.Insertable(entity).ExecuteCommandAsync();
        }
        public virtual int Adds<T>(List<T> entity) where T : EntityBase, new()
        {
            return _sqlSugar.Insertable<T>(entity).ExecuteCommand();
        }
        public virtual ISugarQueryable<T> Query<T>(Expression<Func<T, bool>> exp) where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(s => s.IsDel < 1).Where(exp);
        }
        public virtual Task<T> GetAsync<T>(Expression<Func<T, bool>> exp) where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(s => s.IsDel < 1).Where(exp).FirstAsync();
        }
        public virtual T Get<T>(Expression<Func<T, bool>> exp) where T : EntityBase, new()
        {
            return _sqlSugar.Queryable<T>().Where(s => s.IsDel < 1).Where(exp).First();
        }

        public virtual async Task<bool> UpdateAsync<T>(Expression<Func<T, bool>> wherexp, Expression<Func<T, T>> upexp) where T : EntityBase, new()
        {
            var result = await _sqlSugar.Updateable<T>().Where(wherexp).SetColumns(upexp).ExecuteCommandAsync();
            return result > 0;
        }

        public virtual bool Update<T>(Expression<Func<T, bool>> wherexp, Expression<Func<T, T>> upexp) where T : EntityBase, new()
        {
            var result = _sqlSugar.Updateable<T>().Where(wherexp).SetColumns(upexp).ExecuteCommand();
            return result > 0;
        }
        public virtual async Task<bool> DeleteAsync<T>(Expression<Func<T, bool>> wherexp) where T : EntityBase, new()
        {
            var result = await _sqlSugar.Deleteable<T>().Where(wherexp).ExecuteCommandAsync();
            return result > 0;
        }

        public virtual async Task<bool> DeletesAsync<T>(List<T> List) where T : EntityBase, new()
        {
            var result = await _sqlSugar.Deleteable<T>(List).ExecuteCommandAsync();
            return result > 0;
        }


        public virtual async Task<bool> SoftDeleteAsync<T>(string id) where T : EntityBase, new()
        {
            var result = await _sqlSugar.Updateable<TEntity>().Where(a => a.Id.Equals(id)).SetColumns(a => new TEntity()
            {
                IsDel = 1,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }).ExecuteCommandAsync();
            return result > 0;
        }

        public virtual async Task<bool> SoftDeleteAsync<T>(Expression<Func<TEntity, bool>> wherexp) where T : EntityBase, new()
        {
            var result = await _sqlSugar.Updateable<TEntity>().Where(wherexp).SetColumns(a => new TEntity()
            {
                IsDel = 1,
                DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            }).ExecuteCommandAsync();
            return result > 0;
        }
        #endregion


    }
}