namespace Admin.NET.Core; public static class SqlSugarSetup { /// /// SqlSugar 上下文初始化 /// /// public static void AddSqlSugar(this IServiceCollection services) { var dbOptions = App.GetOptions(); dbOptions.ConnectionConfigs.ForEach(SetDbConfig); SqlSugarScope sqlSugar = new(dbOptions.ConnectionConfigs.Adapt>(), db => { dbOptions.ConnectionConfigs.ForEach(config => { var dbProvider = db.GetConnectionScope(config.ConfigId); SetDbAop(dbProvider); SetDbDiffLog(dbProvider, config); }); }); services.AddSingleton(sqlSugar); // 单例注册 services.AddScoped(typeof(SqlSugarRepository<>)); // 仓储注册 services.AddUnitOfWork(); // 事务与工作单元注册 // 初始化数据库表结构及种子数据 dbOptions.ConnectionConfigs.ForEach(config => { InitDatabase(sqlSugar, config); }); } /// /// 配置连接属性 /// /// private static void SetDbConfig(DbConnectionConfig config) { var configureExternalServices = new ConfigureExternalServices { EntityNameService = (type, entity) => // 处理表 { if (config.EnableUnderLine && !entity.DbTableName.Contains('_')) entity.DbTableName = UtilMethods.ToUnderLine(entity.DbTableName); // 驼峰转下划线 }, EntityService = (type, column) => // 处理列 { if (new NullabilityInfoContext().Create(type).WriteState is NullabilityState.Nullable) column.IsNullable = true; if (config.EnableUnderLine && !column.IsIgnore && !column.DbColumnName.Contains('_')) column.DbColumnName = UtilMethods.ToUnderLine(column.DbColumnName); // 驼峰转下划线 if (config.DbType == SqlSugar.DbType.Oracle) { if (type.PropertyType == typeof(long) || type.PropertyType == typeof(long?)) column.DataType = "number(18)"; if (type.PropertyType == typeof(bool) || type.PropertyType == typeof(bool?)) column.DataType = "number(1)"; } }, DataInfoCacheService = new SqlSugarCache(), }; config.ConfigureExternalServices = configureExternalServices; config.InitKeyType = InitKeyType.Attribute; config.IsAutoCloseConnection = true; config.MoreSettings = new ConnMoreSettings { IsAutoRemoveDataCache = true, IsAutoDeleteQueryFilter = true, // 启用删除查询过滤器 IsAutoUpdateQueryFilter = true, // 启用更新查询过滤器 SqlServerCodeFirstNvarchar = true // 采用Nvarchar }; } /// /// 配置Aop /// /// public static void SetDbAop(SqlSugarScopeProvider db) { var config = db.CurrentConnectionConfig; // 设置超时时间 db.Ado.CommandTimeOut = 30; // 打印SQL语句 db.Aop.OnLogExecuting = (sql, pars) => { var originColor = Console.ForegroundColor; if (sql.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)) Console.ForegroundColor = ConsoleColor.Green; if (sql.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) || sql.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase)) Console.ForegroundColor = ConsoleColor.Yellow; if (sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase)) Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine("【" + DateTime.Now + "——执行SQL】\r\n" + UtilMethods.GetSqlString(config.DbType, sql, pars) + "\r\n"); Console.ForegroundColor = originColor; App.PrintToMiniProfiler("SqlSugar", "Info", sql + "\r\n" + db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value))); }; db.Aop.OnError = ex => { if (ex.Parametres == null) return; var originColor = Console.ForegroundColor; Console.ForegroundColor = ConsoleColor.DarkRed; var pars = db.Utilities.SerializeObject(((SugarParameter[])ex.Parametres).ToDictionary(it => it.ParameterName, it => it.Value)); Console.WriteLine("【" + DateTime.Now + "——错误SQL】\r\n" + UtilMethods.GetSqlString(config.DbType, ex.Sql, (SugarParameter[])ex.Parametres) + "\r\n"); Console.ForegroundColor = originColor; App.PrintToMiniProfiler("SqlSugar", "Error", $"{ex.Message}{Environment.NewLine}{ex.Sql}{pars}{Environment.NewLine}"); }; // 数据审计 db.Aop.DataExecuting = (oldValue, entityInfo) => { // 演示环境判断 if (entityInfo.EntityColumnInfo.IsPrimarykey) { if (entityInfo.EntityName != nameof(SysJobDetail) && entityInfo.EntityName != nameof(SysJobTrigger) && entityInfo.EntityName != nameof(SysLogOp) && entityInfo.EntityName != nameof(SysLogVis) && entityInfo.EntityName != nameof(SysOnlineUser)) { var isDemoEnv = App.GetService().GetConfigValue(CommonConst.SysDemoEnv).GetAwaiter().GetResult(); if (isDemoEnv) throw Oops.Oh(ErrorCodeEnum.D1200); } } if (entityInfo.OperationType == DataFilterType.InsertByObject) { // 主键(long类型)且没有值的---赋值雪花Id if (entityInfo.EntityColumnInfo.IsPrimarykey && entityInfo.EntityColumnInfo.PropertyInfo.PropertyType == typeof(long)) { var id = entityInfo.EntityColumnInfo.PropertyInfo.GetValue(entityInfo.EntityValue); if (id == null || (long)id == 0) entityInfo.SetValue(YitIdHelper.NextId()); } if (entityInfo.PropertyName == "CreateTime") entityInfo.SetValue(DateTime.Now); if (App.User != null) { if (entityInfo.PropertyName == "TenantId") { var tenantId = ((dynamic)entityInfo.EntityValue).TenantId; if (tenantId == null || tenantId == 0) entityInfo.SetValue(App.User.FindFirst(ClaimConst.TenantId)?.Value); } if (entityInfo.PropertyName == "CreateUserId") { var createUserId = ((dynamic)entityInfo.EntityValue).CreateUserId; if (createUserId == 0 || createUserId == null) entityInfo.SetValue(App.User.FindFirst(ClaimConst.UserId)?.Value); } if (entityInfo.PropertyName == "CreateOrgId") { var createOrgId = ((dynamic)entityInfo.EntityValue).CreateOrgId; if (createOrgId == 0 || createOrgId == null) entityInfo.SetValue(App.User.FindFirst(ClaimConst.OrgId)?.Value); } } } if (entityInfo.OperationType == DataFilterType.UpdateByObject) { if (entityInfo.PropertyName == "UpdateTime") entityInfo.SetValue(DateTime.Now); if (entityInfo.PropertyName == "UpdateUserId") entityInfo.SetValue(App.User?.FindFirst(ClaimConst.UserId)?.Value); } }; // 超管时排除各种过滤器 if (App.User?.FindFirst(ClaimConst.AccountType)?.Value == ((int)AccountTypeEnum.SuperAdmin).ToString()) return; // 配置实体假删除过滤器 db.QueryFilter.AddTableFilter(u => u.IsDelete == false); // 配置租户过滤器 var tenantId = App.User?.FindFirst(ClaimConst.TenantId)?.Value; if (!string.IsNullOrWhiteSpace(tenantId)) db.QueryFilter.AddTableFilter(u => u.TenantId == long.Parse(tenantId)); // 配置用户机构(数据范围)过滤器 SqlSugarFilter.SetOrgEntityFilter(db); // 配置自定义过滤器 SqlSugarFilter.SetCustomEntityFilter(db); } /// /// 开启库表差异化日志 /// /// /// private static void SetDbDiffLog(SqlSugarScopeProvider db, DbConnectionConfig config) { if (!config.EnableDiffLog) return; db.Aop.OnDiffLogEvent = async u => { var logDiff = new SysLogDiff { // 操作后记录(字段描述、列名、值、表名、表描述) AfterData = JSON.Serialize(u.AfterData), // 操作前记录(字段描述、列名、值、表名、表描述) BeforeData = JSON.Serialize(u.BeforeData), // 传进来的对象 BusinessData = JSON.Serialize(u.BusinessData), // 枚举(insert、update、delete) DiffType = u.DiffType.ToString(), Sql = UtilMethods.GetSqlString(config.DbType, u.Sql, u.Parameters), Parameters = JSON.Serialize(u.Parameters), Elapsed = u.Time == null ? 0 : (long)u.Time.Value.TotalMilliseconds }; await db.Insertable(logDiff).ExecuteCommandAsync(); Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(DateTime.Now + $"\r\n*****差异日志开始*****\r\n{Environment.NewLine}{JSON.Serialize(logDiff)}{Environment.NewLine}*****差异日志结束*****\r\n"); }; } /// /// 初始化数据库 /// /// /// private static void InitDatabase(SqlSugarScope db, DbConnectionConfig config) { if (!config.EnableInitDb) return; SqlSugarScopeProvider dbProvider = db.GetConnectionScope(config.ConfigId); // 创建数据库 if (config.DbType != SqlSugar.DbType.Oracle) dbProvider.DbMaintenance.CreateDatabase(); // 获取所有实体表-初始化表结构 var entityTypes = App.EffectiveTypes.Where(u => !u.IsInterface && !u.IsAbstract && u.IsClass && u.IsDefined(typeof(SugarTable), false)).ToList(); if (!entityTypes.Any()) return; foreach (var entityType in entityTypes) { var tAtt = entityType.GetCustomAttribute(); if (tAtt != null && tAtt.configId.ToString() != config.ConfigId) continue; if (tAtt == null && config.ConfigId != SqlSugarConst.ConfigId) continue; var splitTable = entityType.GetCustomAttribute(); if (splitTable == null) dbProvider.CodeFirst.InitTables(entityType); else dbProvider.CodeFirst.SplitTables().InitTables(entityType); } if (!config.EnableInitSeed) return; // 获取所有种子配置-初始化数据 var seedDataTypes = App.EffectiveTypes.Where(u => !u.IsInterface && !u.IsAbstract && u.IsClass && u.GetInterfaces().Any(i => i.HasImplementedRawGeneric(typeof(ISqlSugarEntitySeedData<>)))).ToList(); if (!seedDataTypes.Any()) return; foreach (var seedType in seedDataTypes) { var instance = Activator.CreateInstance(seedType); var hasDataMethod = seedType.GetMethod("HasData"); var seedData = ((IEnumerable)hasDataMethod?.Invoke(instance, null))?.Cast(); if (seedData == null) continue; var entityType = seedType.GetInterfaces().First().GetGenericArguments().First(); var tAtt = entityType.GetCustomAttribute(); if (tAtt != null && tAtt.configId.ToString() != config.ConfigId) continue; if (tAtt == null && config.ConfigId != SqlSugarConst.ConfigId) continue; var entityInfo = dbProvider.EntityMaintenance.GetEntityInfo(entityType); if (entityInfo.Columns.Any(u => u.IsPrimarykey)) { // 按主键进行批量增加和更新 var storage = dbProvider.StorageableByObject(seedData.ToList()).ToStorage(); storage.AsInsertable.ExecuteCommand(); var ignoreUpdate = hasDataMethod.GetCustomAttribute(); if (ignoreUpdate == null) storage.AsUpdateable.ExecuteCommand(); } else { // 无主键则只进行插入 if (!dbProvider.Queryable(entityInfo.DbTableName, entityInfo.DbTableName).Any()) dbProvider.InsertableByObject(seedData.ToList()).ExecuteCommand(); } } } /// /// 初始化租户业务数据库 /// /// /// public static void InitTenantDatabase(ITenant iTenant, DbConnectionConfig config) { SetDbConfig(config); iTenant.AddConnection(config); var db = iTenant.GetConnectionScope(config.ConfigId); db.DbMaintenance.CreateDatabase(); // 获取所有实体表-初始化租户业务表 var entityTypes = App.EffectiveTypes.Where(u => !u.IsInterface && !u.IsAbstract && u.IsClass && u.IsDefined(typeof(SugarTable), false) && !u.IsDefined(typeof(SystemTableAttribute), false)).ToList(); if (!entityTypes.Any()) return; foreach (var entityType in entityTypes) { var splitTable = entityType.GetCustomAttribute(); if (splitTable == null) db.CodeFirst.InitTables(entityType); else db.CodeFirst.SplitTables().InitTables(entityType); } } }