public static class SqlSugarSetup
{
public static void AddSqlSugar(this IServiceCollection services, ILogger logger, IConfiguration configuration)
{
var dbConfig = configuration.GetConfig<DbConnectionConfig>("ConnectionConfig");
SetDbConfig(dbConfig);
SqlSugarScope sqlSugar = new SqlSugarScope(dbConfig,
db =>
{
db.Ado.CommandTimeOut = dbConfig.CommandTimeOut;
db.Aop.OnLogExecuting = (sql, pars) => //执行前
{
//Debug.WriteLine("【" + DateTime.Now + "——执行SQL】\r\n" + UtilMethods.GetSqlString(dbConfig.DbType, sql, pars) + "\r\n");
//logger.Information("{DbType} " + UtilMethods.GetSqlString(dbConfig.DbType, sql, pars).Replace("\r\n", ""), "[SQL]");
};
db.Aop.OnLogExecuted = (sql, pars) => //后
{
logger.Information("{DbType} {ExecutionTime}ms " + UtilMethods.GetSqlString(dbConfig.DbType, sql, pars).Replace("\r\n", ""), "[SQL]", db.Ado.SqlExecutionTime.TotalMilliseconds);
//Debug.WriteLine($"执行时间:{db.Ado.SqlExecutionTime.ToString()}");
};
db.Aop.OnError = ex =>
{
if (ex.Parametres == null) return;
var pars = db.Utilities.SerializeObject(((SugarParameter[])ex.Parametres).ToDictionary(it => it.ParameterName, it => it.Value));
//Debug.WriteLine("【" + DateTime.Now + "——错误SQL】\r\n" + UtilMethods.GetSqlString(dbConfig.DbType, ex.Sql, (SugarParameter[])ex.Parametres) + "\r\n");
logger.Error(UtilMethods.GetSqlString(dbConfig.DbType, ex.Sql, (SugarParameter[])ex.Parametres));
};
//数据审计，自动填充
db.Aop.DataExecuting = (oldValue, entityInfo) =>
{
if (entityInfo.OperationType == DataFilterType.InsertByObject)
{
if (entityInfo.PropertyName == "CreateTime")
entityInfo.SetValue(DateTime.Now);
}
if (entityInfo.OperationType == DataFilterType.UpdateByObject)
{
if (entityInfo.PropertyName == "UpdateTime")
entityInfo.SetValue(DateTime.Now);
}
};
//db.Aop.OnExecutingChangeSql = (oldValue, entityInfo) => { }; //可修改sql与参数
//全局实体过滤器，逻辑删除的数据不查询
db.QueryFilter.AddTableFilter<IDeletedFilter>(u => u.IsDelete == false);
});
services.AddSingleton<ISqlSugarClient>(sqlSugar);
services.AddScoped(typeof(SqlSugarRepository<>)); // 仓储
InitDatabase(sqlSugar, dbConfig);
}
/// <summary>
/// 初始化数据库与表
/// </summary>
/// <param name="sqlSugar"></param>
/// <param name="config"></param>
private static void InitDatabase(SqlSugarScope sqlSugar, DbConnectionConfig config)
{
// Sqlite不支持删除列和修改列只能添加列
if (!config.EnableInitDb) return;
// 创建数据库
if (config.DbType != SqlSugar.DbType.Oracle)
sqlSugar.DbMaintenance.CreateDatabase();
//[SugarTable]
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 splitTable = entityType.GetCustomAttribute<SplitTableAttribute>();
if (splitTable == null)
sqlSugar.CodeFirst.InitTables(entityType);
else
sqlSugar.CodeFirst.SplitTables().InitTables(entityType);
}
}
/// <summary>
/// 配置连接属性
/// </summary>
/// <param name="config"></param>
private static void SetDbConfig(ConnectionConfig config)
{
var configureExternalServices = new ConfigureExternalServices
{
EntityNameService = (type, entity) => // 处理表
{
entity.IsDisabledDelete = true; // 禁止删除非 sqlsugar 创建的列
if (!type.GetCustomAttributes<SugarTable>().Any())// 只处理贴了特性[SugarTable]表
return;
if (!entity.DbTableName.Contains('_'))
entity.DbTableName = UtilMethods.ToUnderLine(entity.DbTableName); // 驼峰转下划线
},
EntityService = (type, column) => // 处理列
{
if (!type.GetCustomAttributes<SugarColumn>().Any()) // 只处理贴了特性[SugarColumn]列
return;
if (new NullabilityInfoContext().Create(type).WriteState is NullabilityState.Nullable)
column.IsNullable = true;
if (!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,
//IsAutoDeleteQueryFilter = true, // 启用删除查询过滤器
//IsAutoUpdateQueryFilter = true, // 启用更新查询过滤器
SqlServerCodeFirstNvarchar = true // 采用Nvarchar
};
}
}
