// Admin.NET 项目的版æƒã€å•†æ ‡ã€ä¸“利和其他相关æƒåˆ©å‡å—ç›¸åº”æ³•å¾‹æ³•è§„çš„ä¿æŠ¤ã€‚ä½¿ç”¨æœ¬é¡¹ç›®åº”éµå®ˆç›¸å…³æ³•律法规和许å¯è¯çš„è¦æ±‚。 // // 本项目主è¦éµå¾ª MIT 许å¯è¯å’Œ Apache 许å¯è¯ï¼ˆç‰ˆæœ¬ 2.0)进行分å‘和使用。许å¯è¯ä½äºŽæºä»£ç æ ‘æ ¹ç›®å½•ä¸çš„ LICENSE-MIT å’Œ LICENSE-APACHE 文件。 // // ä¸å¾—利用本项目从事å±å®³å›½å®¶å®‰å…¨ã€æ‰°ä¹±ç¤¾ä¼šç§©åºã€ä¾µçŠ¯ä»–äººåˆæ³•æƒç›Šç‰æ³•å¾‹æ³•è§„ç¦æ¢çš„æ´»åЍï¼ä»»ä½•基于本项目二次开å‘è€Œäº§ç”Ÿçš„ä¸€åˆ‡æ³•å¾‹çº çº·å’Œè´£ä»»ï¼Œæˆ‘ä»¬ä¸æ‰¿æ‹…ä»»ä½•è´£ä»»ï¼ namespace Admin.NET.Core; public static class SqlSugarSetup { // 多租户实例 public static ITenant ITenant { get; set; } // æ˜¯å¦æ£åœ¨å¤„ç†ç§åæ•°æ® private static bool _isHandlingSeedData = false; /// <summary> /// SqlSugar 上下文åˆå§‹åŒ– /// </summary> /// <param name="services"></param> public static void AddSqlSugar(this IServiceCollection services) { // 注册雪花Id var snowIdOpt = App.GetConfig<SnowIdOptions>("SnowId", true); YitIdHelper.SetIdGenerator(snowIdOpt); // 自定义 SqlSugar 雪花ID算法 SnowFlakeSingle.WorkId = snowIdOpt.WorkerId; StaticConfig.CustomSnowFlakeFunc = () => { return YitIdHelper.NextId(); }; var dbOptions = App.GetConfig<DbConnectionOptions>("DbConnection", true); dbOptions.ConnectionConfigs.ForEach(SetDbConfig); SqlSugarScope sqlSugar = new(dbOptions.ConnectionConfigs.Adapt<List<ConnectionConfig>>(), db => { dbOptions.ConnectionConfigs.ForEach(config => { var dbProvider = db.GetConnectionScope(config.ConfigId); SetDbAop(dbProvider, dbOptions.EnableConsoleSql); SetDbDiffLog(dbProvider, config); }); }); ITenant = sqlSugar; services.AddSingleton<ISqlSugarClient>(sqlSugar); // å•例注册 services.AddScoped(typeof(SqlSugarRepository<>)); // 仓储注册 services.AddUnitOfWork<SqlSugarUnitOfWork>(); // 事务与工作å•元注册 // åˆå§‹åŒ–æ•°æ®åº“表结构åŠç§åæ•°æ® dbOptions.ConnectionConfigs.ForEach(config => { InitDatabase(sqlSugar, config); }); } /// <summary> /// é…置连接属性 /// </summary> /// <param name="config"></param> public static void SetDbConfig(DbConnectionConfig config) { var configureExternalServices = new ConfigureExternalServices { EntityNameService = (type, entity) => // 处ç†è¡¨ { entity.IsDisabledDelete = true; // ç¦æ¢åˆ é™¤éž sqlsugar 创建的列 // åªå¤„ç†è´´äº†ç‰¹æ€§[SugarTable]表 if (!type.GetCustomAttributes<SugarTable>().Any()) return; if (config.DbSettings.EnableUnderLine && !entity.DbTableName.Contains('_')) entity.DbTableName = UtilMethods.ToUnderLine(entity.DbTableName); // 驼峰转下划线 }, EntityService = (type, column) => // 处ç†åˆ— { // åªå¤„ç†è´´äº†ç‰¹æ€§[SugarColumn]列 if (!type.GetCustomAttributes<SugarColumn>().Any()) return; if (new NullabilityInfoContext().Create(type).WriteState is NullabilityState.Nullable) column.IsNullable = true; if (config.DbSettings.EnableUnderLine && !column.IsIgnore && !column.DbColumnName.Contains('_')) column.DbColumnName = UtilMethods.ToUnderLine(column.DbColumnName); // 驼峰转下划线 }, DataInfoCacheService = new SqlSugarCache(), }; config.ConfigureExternalServices = configureExternalServices; config.InitKeyType = InitKeyType.Attribute; config.IsAutoCloseConnection = true; config.MoreSettings = new ConnMoreSettings { IsAutoRemoveDataCache = true, // å¯ç”¨è‡ªåŠ¨åˆ é™¤ç¼“å˜ï¼Œæ‰€æœ‰å¢žåˆ 改会自动调用.RemoveDataCache() IsAutoDeleteQueryFilter = true, // å¯ç”¨åˆ 除查询过滤器 IsAutoUpdateQueryFilter = true, // å¯ç”¨æ›´æ–°æŸ¥è¯¢è¿‡æ»¤å™¨ SqlServerCodeFirstNvarchar = true // 采用Nvarchar }; // 若库类型是人大金仓则默认设置PGæ¨¡å¼ if (config.DbType == SqlSugar.DbType.Kdbndp) config.MoreSettings.DatabaseModel = SqlSugar.DbType.PostgreSQL; // é…ç½®PG模å¼ä¸»è¦æ˜¯å…¼å®¹ç³»ç»Ÿè¡¨å·®å¼‚ } /// <summary> /// é…ç½®Aop /// </summary> /// <param name="db"></param> /// <param name="enableConsoleSql"></param> public static void SetDbAop(SqlSugarScopeProvider db, bool enableConsoleSql) { // 设置超时时间 db.Ado.CommandTimeOut = 30; // 打å°SQLè¯å¥ if (enableConsoleSql) { db.Aop.OnLogExecuting = (sql, pars) => { //// è‹¥å‚æ•°å€¼è¶…过100个å—ç¬¦åˆ™è¿›è¡Œæˆªå– //foreach (var par in pars) //{ // if (par.DbType != System.Data.DbType.String || par.Value == null) continue; // if (par.Value.ToString().Length > 100) // par.Value = string.Concat(par.Value.ToString()[..100], "......"); //} var log = $"ã€{DateTime.Now}——执行SQL】\r\n{UtilMethods.GetNativeSql(sql, pars)}\r\n"; 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(log); Console.ForegroundColor = originColor; App.PrintToMiniProfiler("SqlSugar", "Info", log); }; db.Aop.OnError = ex => { if (ex.Parametres == null) return; var log = $"ã€{DateTime.Now}——错误SQL】\r\n{UtilMethods.GetNativeSql(ex.Sql, (SugarParameter[])ex.Parametres)}\r\n"; Log.Error(log, ex); App.PrintToMiniProfiler("SqlSugar", "Error", log); }; db.Aop.OnLogExecuted = (sql, pars) => { //// è‹¥å‚æ•°å€¼è¶…过100个å—ç¬¦åˆ™è¿›è¡Œæˆªå– //foreach (var par in pars) //{ // if (par.DbType != System.Data.DbType.String || par.Value == null) continue; // if (par.Value.ToString().Length > 100) // par.Value = string.Concat(par.Value.ToString()[..100], "......"); //} // 执行时间超过5ç§’æ—¶ if (db.Ado.SqlExecutionTime.TotalSeconds > 5) { var fileName = db.Ado.SqlStackTrace.FirstFileName; // 文件å var fileLine = db.Ado.SqlStackTrace.FirstLine; // è¡Œå· var firstMethodName = db.Ado.SqlStackTrace.FirstMethodName; // 方法å var log = $"ã€{DateTime.Now}——超时SQL】\r\nã€æ‰€åœ¨æ–‡ä»¶å】:{fileName}\r\nã€ä»£ç 行数】:{fileLine}\r\nã€æ–¹æ³•å】:{firstMethodName}\r\n" + $"ã€SQLè¯å¥ã€‘:{UtilMethods.GetNativeSql(sql, pars)}"; Log.Warning(log); App.PrintToMiniProfiler("SqlSugar", "Slow", log); } }; } // æ•°æ®å®¡è®¡ db.Aop.DataExecuting = (oldValue, entityInfo) => { // è‹¥æ£åœ¨å¤„ç†ç§åæ•°æ®åˆ™ç›´æŽ¥è¿”回 if (_isHandlingSeedData) return; // 新增/æ’å…¥ if (entityInfo.OperationType == DataFilterType.InsertByObject) { // 若主键是长整型且空则赋值雪花Id if (entityInfo.EntityColumnInfo.IsPrimarykey && !entityInfo.EntityColumnInfo.IsIdentity && entityInfo.EntityColumnInfo.PropertyInfo.PropertyType == typeof(long)) { var id = entityInfo.EntityColumnInfo.PropertyInfo.GetValue(entityInfo.EntityValue); if (id == null || (long)id == 0) entityInfo.SetValue(YitIdHelper.NextId()); } // è‹¥åˆ›å»ºæ—¶é—´ä¸ºç©ºåˆ™èµ‹å€¼å½“å‰æ—¶é—´ else if (entityInfo.PropertyName == nameof(EntityBase.CreateTime)) { var createTime = entityInfo.EntityColumnInfo.PropertyInfo.GetValue(entityInfo.EntityValue)!; if (createTime == null || createTime.Equals(DateTime.MinValue)) entityInfo.SetValue(DateTime.Now); } // 若当å‰ç”¨æˆ·éžç©ºï¼ˆweb线程时) if (App.User != null) { dynamic entityValue = entityInfo.EntityValue; if (entityInfo.PropertyName == nameof(EntityTenantId.TenantId)) { var tenantId = entityValue.TenantId; if (tenantId == null || tenantId == 0) entityInfo.SetValue(App.User.FindFirst(ClaimConst.TenantId)?.Value); } else if (entityInfo.PropertyName == nameof(EntityBase.CreateUserId)) { var createUserId = entityValue.CreateUserId; if (createUserId == 0 || createUserId == null) entityInfo.SetValue(App.User.FindFirst(ClaimConst.UserId)?.Value); } else if (entityInfo.PropertyName == nameof(EntityBase.CreateUserName)) { var createUserName = entityValue.CreateUserName; if (string.IsNullOrEmpty(createUserName)) entityInfo.SetValue(App.User.FindFirst(ClaimConst.RealName)?.Value); } else if (entityInfo.PropertyName == nameof(EntityBaseData.CreateOrgId)) { var createOrgId = entityValue.CreateOrgId; if (createOrgId == 0 || createOrgId == null) entityInfo.SetValue(App.User.FindFirst(ClaimConst.OrgId)?.Value); } else if (entityInfo.PropertyName == nameof(EntityBaseData.CreateOrgName)) { var createOrgName = entityValue.CreateOrgName; if (string.IsNullOrEmpty(createOrgName)) entityInfo.SetValue(App.User.FindFirst(ClaimConst.OrgName)?.Value); } } } // 编辑/æ›´æ–° else if (entityInfo.OperationType == DataFilterType.UpdateByObject) { if (entityInfo.PropertyName == nameof(EntityBase.UpdateTime)) entityInfo.SetValue(DateTime.Now); else if (entityInfo.PropertyName == nameof(EntityBase.UpdateUserId)) entityInfo.SetValue(App.User?.FindFirst(ClaimConst.UserId)?.Value); else if (entityInfo.PropertyName == nameof(EntityBase.UpdateUserName)) entityInfo.SetValue(App.User?.FindFirst(ClaimConst.RealName)?.Value); } }; // é…ç½®å‡åˆ 除过滤器 db.QueryFilter.AddTableFilter<IDeletedFilter>(u => u.IsDelete == false); // 超管排除其他过滤器 if (App.User?.FindFirst(ClaimConst.AccountType)?.Value == ((int)AccountTypeEnum.SuperAdmin).ToString()) return; // é…置租户过滤器 var tenantId = App.User?.FindFirst(ClaimConst.TenantId)?.Value; if (!string.IsNullOrWhiteSpace(tenantId)) db.QueryFilter.AddTableFilter<ITenantIdFilter>(u => u.TenantId == long.Parse(tenantId)); // é…置用户机构(数æ®èŒƒå›´ï¼‰è¿‡æ»¤å™¨ SqlSugarFilter.SetOrgEntityFilter(db); // é…置自定义过滤器 SqlSugarFilter.SetCustomEntityFilter(db); } /// <summary> /// å¼€å¯åº“表差异化日志 /// </summary> /// <param name="db"></param> /// <param name="config"></param> private static void SetDbDiffLog(SqlSugarScopeProvider db, DbConnectionConfig config) { if (!config.DbSettings.EnableDiffLog) return; db.Aop.OnDiffLogEvent = async u => { var logDiff = new SysLogDiff { // æ“作åŽè®°å½•ï¼ˆå—æ®µæè¿°ã€åˆ—åã€å€¼ã€è¡¨åã€è¡¨æè¿°ï¼‰ AfterData = JSON.Serialize(u.AfterData), // æ“作å‰è®°å½•ï¼ˆå—æ®µæè¿°ã€åˆ—åã€å€¼ã€è¡¨åã€è¡¨æè¿°ï¼‰ BeforeData = JSON.Serialize(u.BeforeData), // ä¼ è¿›æ¥çš„对象(如果对象为空,则使用首个数æ®çš„表å作为业务对象) BusinessData = u.BusinessData == null ? u.AfterData.FirstOrDefault()?.TableName : JSON.Serialize(u.BusinessData), // 枚举(insertã€updateã€delete) DiffType = u.DiffType.ToString(), Sql = UtilMethods.GetNativeSql(u.Sql, u.Parameters), Parameters = JSON.Serialize(u.Parameters), Elapsed = u.Time == null ? 0 : (long)u.Time.Value.TotalMilliseconds }; var logDb = ITenant.IsAnyConnection(SqlSugarConst.LogConfigId) ? ITenant.GetConnectionScope(SqlSugarConst.LogConfigId) : db; await logDb.CopyNew().Insertable(logDiff).ExecuteCommandAsync(); Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(DateTime.Now + $"\r\n*****开始差异日志*****\r\n{Environment.NewLine}{JSON.Serialize(logDiff)}{Environment.NewLine}*****结æŸå·®å¼‚日志*****\r\n"); }; } /// <summary> /// åˆå§‹åŒ–æ•°æ®åº“ /// </summary> /// <param name="db"></param> /// <param name="config"></param> private static void InitDatabase(SqlSugarScope db, DbConnectionConfig config) { SqlSugarScopeProvider dbProvider = db.GetConnectionScope(config.ConfigId); // åˆå§‹åŒ–/创建数æ®åº“ if (config.DbSettings.EnableInitDb) { Log.Information($"åˆå§‹åŒ–æ•°æ®åº“ {config.DbType} - {config.ConfigId} - {config.ConnectionString}"); if (config.DbType != SqlSugar.DbType.Oracle) dbProvider.DbMaintenance.CreateDatabase(); } // åˆå§‹åŒ–表结构 if (config.TableSettings.EnableInitTable) { Log.Information($"åˆå§‹åŒ–表结构 {config.DbType} - {config.ConfigId}"); var entityTypes = App.EffectiveTypes.Where(u => !u.IsInterface && !u.IsAbstract && u.IsClass && u.IsDefined(typeof(SugarTable), false)) .Where(u => !u.GetCustomAttributes<IgnoreTableAttribute>().Any()) .WhereIF(config.TableSettings.EnableIncreTable, u => u.IsDefined(typeof(IncreTableAttribute), false)).ToList(); if (config.ConfigId.ToString() == SqlSugarConst.MainConfigId) // é»˜è®¤åº“ï¼ˆæœ‰ç³»ç»Ÿè¡¨ç‰¹æ€§ã€æ²¡æœ‰æ—¥å¿—表和租户表特性) entityTypes = entityTypes.Where(u => u.GetCustomAttributes<SysTableAttribute>().Any() || (!u.GetCustomAttributes<LogTableAttribute>().Any() && !u.GetCustomAttributes<TenantAttribute>().Any())).ToList(); else if (config.ConfigId.ToString() == SqlSugarConst.LogConfigId) // 日志库 entityTypes = entityTypes.Where(u => u.GetCustomAttributes<LogTableAttribute>().Any()).ToList(); else entityTypes = entityTypes.Where(u => u.GetCustomAttribute<TenantAttribute>()?.configId.ToString() == config.ConfigId.ToString()).ToList(); // 自定义的库 int count = 0, sum = entityTypes.Count; foreach (var entityType in entityTypes) { Console.WriteLine($"创建表 {entityType} ({config.ConfigId} - {++count}/{sum})"); if (entityType.GetCustomAttribute<SplitTableAttribute>() == null) dbProvider.CodeFirst.InitTables(entityType); else dbProvider.CodeFirst.SplitTables().InitTables(entityType); } } // åˆå§‹åŒ–ç§åæ•°æ® if (config.SeedSettings.EnableInitSeed) { _isHandlingSeedData = true; Log.Information($"åˆå§‹åŒ–ç§åæ•°æ® {config.DbType} - {config.ConfigId}"); var seedDataTypes = App.EffectiveTypes.Where(u => !u.IsInterface && !u.IsAbstract && u.IsClass && u.GetInterfaces().Any(i => i.HasImplementedRawGeneric(typeof(ISqlSugarEntitySeedData<>)))) .WhereIF(config.SeedSettings.EnableIncreSeed, u => u.IsDefined(typeof(IncreSeedAttribute), false)) .OrderBy(u => u.GetCustomAttributes(typeof(SeedDataAttribute), false).Length > 0 ? ((SeedDataAttribute)u.GetCustomAttributes(typeof(SeedDataAttribute), false)[0]).Order : 0).ToList(); int count = 0, sum = seedDataTypes.Count; foreach (var seedType in seedDataTypes) { var entityType = seedType.GetInterfaces().First().GetGenericArguments().First(); if (config.ConfigId.ToString() == SqlSugarConst.MainConfigId) // é»˜è®¤åº“ï¼ˆæœ‰ç³»ç»Ÿè¡¨ç‰¹æ€§ã€æ²¡æœ‰æ—¥å¿—表和租户表特性) { if (entityType.GetCustomAttribute<SysTableAttribute>() == null && (entityType.GetCustomAttribute<LogTableAttribute>() != null || entityType.GetCustomAttribute<TenantAttribute>() != null)) continue; } else if (config.ConfigId.ToString() == SqlSugarConst.LogConfigId) // 日志库 { if (entityType.GetCustomAttribute<LogTableAttribute>() == null) continue; } else { var att = entityType.GetCustomAttribute<TenantAttribute>(); // 自定义的库 if (att == null || att.configId.ToString() != config.ConfigId.ToString()) continue; } var instance = Activator.CreateInstance(seedType); var hasDataMethod = seedType.GetMethod("HasData"); var seedData = ((IEnumerable)hasDataMethod?.Invoke(instance, null))?.Cast<object>(); if (seedData == null) continue; var entityInfo = dbProvider.EntityMaintenance.GetEntityInfo(entityType); Console.WriteLine($"æ·»åŠ æ•°æ® {entityInfo.DbTableName} ({config.ConfigId} - {++count}/{sum},数æ®é‡ï¼š{seedData.Count()})"); if (entityType.GetCustomAttribute<SplitTableAttribute>(true) != null) { //拆分表的æ“作需è¦å®žä½“类型,而通过å射很难实现 //所以,这里将Init方法写在“ç§åæ•°æ®ç±»â€å†…部,å†ä¼ å…¥ db å射调用 var hasInitMethod = seedType.GetMethod("Init"); var parameters = new object[] { db }; hasInitMethod?.Invoke(instance, parameters); } else { if (entityInfo.Columns.Any(u => u.IsPrimarykey)) { // 按主键进行批é‡å¢žåŠ å’Œæ›´æ–° var storage = dbProvider.StorageableByObject(seedData.ToList()).ToStorage(); // å…ˆä¿®æ”¹å†æ’入,å¦åˆ™ä¼šæ›´æ–°ä¿®æ”¹æ—¶é—´å—段 if (seedType.GetCustomAttribute<IgnoreUpdateSeedAttribute>() == null) // 有忽略更新ç§åç‰¹æ€§æ—¶åˆ™ä¸æ›´æ–° { int updateCount = storage.AsUpdateable.IgnoreColumns(entityInfo.Columns.Where(u => u.PropertyInfo.GetCustomAttribute<IgnoreUpdateSeedColumnAttribute>() != null).Select(u => u.PropertyName).ToArray()).ExecuteCommand(); Console.WriteLine($" 修改 {updateCount}/{seedData.Count()} æ¡è®°å½•"); } int insertCount = storage.AsInsertable.ExecuteCommand(); Console.WriteLine($" æ’å…¥ {insertCount}/{seedData.Count()} æ¡è®°å½•"); } else { // æ— ä¸»é”®åˆ™åªè¿›è¡Œæ’å…¥ if (!dbProvider.Queryable(entityInfo.DbTableName, entityInfo.DbTableName).Any()) dbProvider.InsertableByObject(seedData.ToList()).ExecuteCommand(); } } } _isHandlingSeedData = false; } } /// <summary> /// åˆå§‹åŒ–租户业务数æ®åº“ /// </summary> /// <param name="iTenant"></param> /// <param name="config"></param> public static void InitTenantDatabase(ITenant iTenant, DbConnectionConfig config) { SetDbConfig(config); if (!iTenant.IsAnyConnection(config.ConfigId.ToString())) iTenant.AddConnection(config); var db = iTenant.GetConnectionScope(config.ConfigId.ToString()); db.DbMaintenance.CreateDatabase(); // èŽ·å–æ‰€æœ‰ä¸šåŠ¡è¡¨-åˆå§‹åŒ–ç§Ÿæˆ·åº“è¡¨ç»“æž„ï¼ˆæŽ’é™¤ç³»ç»Ÿè¡¨ã€æ—¥å¿—表ã€ç‰¹å®šåº“表) var entityTypes = App.EffectiveTypes .Where(u => !u.GetCustomAttributes<IgnoreTableAttribute>().Any()) .Where(u => !u.IsInterface && !u.IsAbstract && u.IsClass && u.IsDefined(typeof(SugarTable), false) && !u.IsDefined(typeof(SysTableAttribute), false) && !u.IsDefined(typeof(LogTableAttribute), false) && !u.IsDefined(typeof(TenantAttribute), false)).ToList(); if (entityTypes.Count == 0) return; foreach (var entityType in entityTypes) { var splitTable = entityType.GetCustomAttribute<SplitTableAttribute>(); if (splitTable == null) db.CodeFirst.InitTables(entityType); else db.CodeFirst.SplitTables().InitTables(entityType); } } }