/// <summary>
/// 创建数据库
/// </summary>
/// <param name="dbConfig"></param>
/// <returns></returns>
public async static Task CreateDatabaseAsync(DbConfig dbConfig)
{
if (!dbConfig.CreateDb || dbConfig.Type == DataType.Sqlite)
{
return;
}
var db = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.CreateDbConnectionString)
.Build();
try
{
Console.WriteLine("\r\n create database started");
await db.Ado.ExecuteNonQueryAsync(dbConfig.CreateDbSql);
Console.WriteLine(" create database succeed");
}
catch (Exception e)
{
Console.WriteLine($" create database failed.\n {e.Message}");
}
}
public static FreeSqlBuilder CreateDatabaseIfNotExistsMySql(this FreeSqlBuilder builder,
string connectionString = "")
{
if (connectionString == "")
{
connectionString = GetConnectionString(builder);
}
MySqlConnectionStringBuilder conStrBuilder = new MySqlConnectionStringBuilder(connectionString);
string createDatabaseSql =
$"USE mysql;CREATE DATABASE IF NOT EXISTS `{conStrBuilder.Database}` CHARACTER SET '{conStrBuilder.CharacterSet}' COLLATE 'utf8mb4_general_ci'";
using MySqlConnection cnn = new MySqlConnection(
$"Data Source={conStrBuilder.Server};Port={conStrBuilder.Port};User ID={conStrBuilder.UserID};Password={conStrBuilder.Password};Initial Catalog=mysql;Charset=utf8;SslMode=none;Max pool size=1");
cnn.Open();
using (MySqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = createDatabaseSql;
cmd.ExecuteNonQuery();
}
return(builder);
}
/// <summary>
/// 直接从配置文件读取配置
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="service"></param>
public static void CreateFreeSql <T>(this IServiceCollection service)
{
service.AddSingleton(f =>
{
var current = f.GetService <FreeSqlCollectionConfig>().FreeSqlConfigs?.FirstOrDefault(x => x.Key == typeof(T).Name) ?? throw new ArgumentNullException(nameof(FreeSqlCollectionConfig),
$"appSettings.json文件未检测到{typeof(T).Name}对象");
var builder = new FreeSqlBuilder()
.UseConnectionString(current.DataType, current.MasterConnection)
.UseAutoSyncStructure(current.IsSyncStructure);
if (current.SlaveConnections.Count > 0)
{
builder.UseSlave(current.SlaveConnections.ToArray());
}
var res = builder.Build <T>();
res.Aop.CurdAfter += (s, e) => Aop_CurdAfter(s, e, f.GetService <ILogger <IFreeSql <T> > >());
res.Aop.AuditValue += (s, e) => Aop_Auditer(s, e, f);
res.GlobalFilter.Apply <IDeleted>(SysConsts.DELETED_FILTER, x => !x.IsDeleted);
res.GlobalFilter.Apply <IEnabled>(SysConsts.ENABLED_FILTER, x => x.Enabled == true);
return(res);
});
service.AddScoped <IUnitOfWork <T>, UnitOfWork <T> >();
}
/// <summary>
/// FreeSql
/// </summary>
/// <param name="services"></param>
public static void AddContext(this IServiceCollection services)
{
var configuration = services.BuildServiceProvider().GetRequiredService <IConfiguration>();
IFreeSql fsql = new FreeSqlBuilder()
.UseConnectionString(configuration)
.UseNameConvert(NameConvertType.PascalCaseToUnderscoreWithLower)
.UseAutoSyncStructure(true)
.UseMonitorCommand(cmd =>
{
Trace.WriteLine(cmd.CommandText);
}
)
.Build();
fsql.CodeFirst.IsSyncStructureToLower = true;
services.AddSingleton(fsql);
services.AddScoped <UnitOfWorkManager>();
services.AddFreeRepository(filter =>
{
filter.Apply <IDeleteAduitEntity>("IsDeleted", a => a.IsDeleted == false);
});
}
public static void AddFreeSql(this IServiceCollection services)
{
IFreeSql fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.MySql,
DesEncryptUtil.Decrypt(Appsettings.app("ConnectionStrings", "MySqlConnectionString")))
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd => { Trace.WriteLine(cmd.CommandText + ";"); })
.Build();
fsql.Aop.CurdAfter += (s, e) =>
{
Console.WriteLine(e.Sql);
LogHelper.Info(e.Sql);
if (e.ElapsedMilliseconds > 200)
{
LogHelper.Warning("Sql执行超时，请注意查看");
}
};
services.AddSingleton(fsql);
services.AddScoped <UnitOfWorkManager>();
services.AddFreeRepository(filter => filter.Apply <ISoftDeleteEntity>("IsDelete", a => a.IsDelete == false));
}
public Startup(IConfiguration configuration)
{
Configuration = configuration;
IConfigurationSection Default = Configuration.GetSection("Default");
IConfigurationSection SqlServer = Configuration.GetSection("SqlServer");
IConfigurationSection MariaDB = Configuration.GetSection("MariaDB");
Fsql = new FreeSqlBuilder()
//.UseConnectionString(DataType.Sqlite, @"Data Source=|DataDirectory|\document.db;Pooling=true;Max Pool Size=10")
.UseConnectionString(DataType.MySql, Default.Value)
//.UseConnectionString(DataType.MySql, MariaDB.Value)
//.UseConnectionString(DataType.SqlServer, SqlServer.Value)
.UseAutoSyncStructure(true)
.UseNameConvert(NameConvertType.PascalCaseToUnderscoreWithLower)
.UseMonitorCommand(cmd => Trace.WriteLine(cmd.CommandText))
.Build().SetDbContextOptions(opt => opt.EnableAddOrUpdateNavigateList = true);
Fsql.CodeFirst.IsAutoSyncStructure = true;
Fsql.Aop.CurdAfter += (s, e) =>
{
//Trace.WriteLine(
// $"ManagedThreadId:{Thread.CurrentThread.ManagedThreadId}: FullName:{e.EntityType.FullName}" +
// $" ElapsedMilliseconds:{e.ElapsedMilliseconds}ms, {e.Sql}");
if (e.ElapsedMilliseconds > 200)
{
}
};
Fsql.CodeFirst.Entity <Blog>(e =>
{
e.HasData(new List <Blog>()
{
new Blog("title", "content", DateTime.Now, false)
{
Posts = new List <Post>
{
new Post("replyContent", DateTime.Now, false),
new Post("replyContent", DateTime.Now, false),
new Post("replyContent", DateTime.Now, false),
}
},
new Blog("title", "content", DateTime.Now, false)
{
Posts = new List <Post>
{
new Post("replyContent", DateTime.Now, false),
new Post("replyContent", DateTime.Now, false),
new Post("replyContent", DateTime.Now, false),
}
}
});
});
//using Object<DbConnection> objPool = Fsql.Ado.MasterPool.Get();
//using (DbConnection dbConnection = objPool.Value)
//{
//}
}
/// <summary>
/// 添加数据库
/// </summary>
/// <param name="services"></param>
/// <param name="env"></param>
/// <param name="appConfig"></param>
public async static void AddDb(this IServiceCollection services, IHostEnvironment env, AppConfig appConfig)
{
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", env.EnvironmentName);
//创建数据库
if (dbConfig.CreateDb)
{
await DbHelper.CreateDatabase(dbConfig);
}
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(dbConfig.SyncStructure)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
//Console.WriteLine($"{cmd.CommandText}\n{traceLog}\r\n");
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
//fsql.GlobalFilter.Apply<IEntitySoftDelete>("SoftDelete", a => a.IsDeleted == false);
services.AddFreeRepository(filter => filter.Apply <IEntitySoftDelete>("SoftDelete", a => a.IsDeleted == false));
services.AddScoped <UnitOfWorkManager>();
services.AddSingleton(fsql);
#region 初始化数据库
//同步结构
if (dbConfig.SyncStructure)
{
DbHelper.SyncStructure(fsql, dbConfig: dbConfig);
}
//同步数据
if (dbConfig.SyncData)
{
await DbHelper.SyncData(fsql, dbConfig);
}
#endregion
//生成数据包
if (dbConfig.GenerateData && !dbConfig.CreateDb && !dbConfig.SyncData)
{
await DbHelper.GenerateSimpleJsonData(fsql);
}
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
System.Threading.Tasks.Parallel.For(0, 1, body =>
{
Console.WriteLine($"{e.Sql}\r\n");
});
};
}
#endregion
#region 审计数据
//计算服务器时间
//var serverTime = fsql.Select<T>().Limit(1).First(a => DateTime.local);
//var timeOffset = DateTime.UtcNow.Subtract(serverTime);
var user = services.BuildServiceProvider().GetService <IUser>();
fsql.Aop.AuditValue += (s, e) =>
{
if (user == null || user.Id <= 0)
{
return;
}
if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert)
{
switch (e.Property.Name)
{
case "CreatedUserId":
e.Value = user.Id;
break;
case "CreatedUserName":
e.Value = user.Name;
break;
//case "CreatedTime":
// e.Value = DateTime.Now.Subtract(timeOffset);
// break;
}
}
else if (e.AuditValueType == FreeSql.Aop.AuditValueType.Update)
{
switch (e.Property.Name)
{
case "ModifiedUserId":
e.Value = user.Id;
break;
case "ModifiedUserName":
e.Value = user.Name;
break;
//case "ModifiedTime":
// e.Value = DateTime.Now.Subtract(timeOffset);
// break;
}
}
};
#endregion
#endregion
Console.WriteLine($"{appConfig.Urls}\r\n");
}
/// <summary>
/// 请在UseConnectionString配置后调用此方法
/// </summary>
/// <param name="this"></param>
/// <returns></returns>
public static FreeSqlBuilder CreateDatabaseIfNotExists(this FreeSqlBuilder builder)
{
FieldInfo dataTypeFieldInfo = builder.GetType().GetField("_dataType", BindingFlags.NonPublic | BindingFlags.Instance);
if (dataTypeFieldInfo is null)
{
throw new ArgumentException("_dataType is null");
}
string connectionString = GetConnectionString(builder);
DataType dbType = (DataType)dataTypeFieldInfo.GetValue(builder);
switch (dbType)
{
case DataType.MySql:
return(builder.CreateDatabaseIfNotExistsMySql(connectionString));
case DataType.SqlServer:
return(builder.CreateDatabaseIfNotExistsSqlServer(connectionString));
case DataType.PostgreSQL:
break;
case DataType.Oracle:
break;
case DataType.Sqlite:
return(builder);
case DataType.OdbcOracle:
break;
case DataType.OdbcSqlServer:
break;
case DataType.OdbcMySql:
break;
case DataType.OdbcPostgreSQL:
break;
case DataType.Odbc:
break;
case DataType.OdbcDameng:
break;
case DataType.MsAccess:
break;
case DataType.Dameng:
break;
case DataType.OdbcKingbaseES:
break;
case DataType.ShenTong:
break;
case DataType.KingbaseES:
break;
case DataType.Firebird:
break;
default:
break;
}
Log.Error($"不支持创建数据库");
return(builder);
}
protected override void Load(ContainerBuilder builder)
{
IFreeSql fsql = new FreeSqlBuilder()
.UseConnectionString(_configuration)
.UseNameConvert(NameConvertType.PascalCaseToUnderscoreWithLower)
.UseAutoSyncStructure(true)
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd =>
{
Trace.WriteLine(cmd.CommandText + ";");
}
)
.Build()
.SetDbContextOptions(opt => opt.EnableAddOrUpdateNavigateList = true);//联级保存功能开启（默认为关闭）
builder.RegisterInstance(fsql).SingleInstance();
fsql.Aop.CurdAfter += (s, e) =>
{
Log.Debug($"ManagedThreadId:{Thread.CurrentThread.ManagedThreadId}: FullName:{e.EntityType.FullName}" +
$" ElapsedMilliseconds:{e.ElapsedMilliseconds}ms, {e.Sql}");
if (e.ElapsedMilliseconds > 200)
{
//记录日志
//发送短信给负责人
}
};
//敏感词处理
if (_configuration["AuditValue:Enable"].ToBoolean())
{
IllegalWordsSearch illegalWords = ToolGoodUtils.GetIllegalWordsSearch();
fsql.Aop.AuditValue += (s, e) =>
{
if (e.Column.CsType == typeof(string) && e.Value != null)
{
string oldVal = (string)e.Value;
string newVal = illegalWords.Replace(oldVal);
//第二种处理敏感词的方式
//string newVal = oldVal.ReplaceStopWords();
if (newVal != oldVal)
{
e.Value = newVal;
}
}
};
}
//services.AddFreeRepository();
builder.RegisterType(typeof(UnitOfWorkManager)).InstancePerLifetimeScope();
fsql.GlobalFilter.Apply <IDeleteAduitEntity>("IsDeleted", a => a.IsDeleted == false);
try
{
using var objPool = fsql.Ado.MasterPool.Get();
}
catch (Exception e)
{
Log.Logger.Error(e + e.StackTrace + e.Message + e.InnerException);
return;
}
//在运行时直接生成表结构
try
{
fsql.CodeFirst
.SeedData()
.SyncStructure(ReflexHelper.GetTypesByTableAttribute());
}
catch (Exception e)
{
Log.Logger.Error(e + e.StackTrace + e.Message + e.InnerException);
}
}
public void WhereByPropertyTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
var company = new Company {
Id = Guid.NewGuid(), Code = "CO001"
};
var department = new Department {
Id = Guid.NewGuid(), Code = "D001", CompanyId = company.Id
};
var orgnization = new Orgnization {
Code = "C001", CompanyId = company.Id
};
freeSql.Insert(company).ExecuteAffrows();
freeSql.Insert(orgnization).ExecuteAffrows();
freeSql.Insert(department).ExecuteAffrows();
var materials = new[]
{
new Material {
Code = "TEST1", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
},
new Material {
Code = "TEST2", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
}
};
var repo1 = freeSql.GetGuidRepository <Material>();
repo1.DbContextOptions.EnableCascadeSave = true;
repo1.Insert(materials);
var order = new Order
{
Code = "X001",
OrgnizationId = orgnization.Id,
OrderItems = new List <OrderItem>
{
new OrderItem {
ItemCode = "01", MaterialId = materials[0].Id
},
new OrderItem {
ItemCode = "02", MaterialId = materials[1].Id
},
}
};
var repo2 = freeSql.GetGuidRepository <Order>();
repo2.DbContextOptions.EnableCascadeSave = true;
repo2.Insert(order);
// 根据导航属性过滤数据
//var list1 = freeSql.Select<Order>().Where(t => t.OrderItems.Any(t1 => t1.Material.Units.Any(t2 => t2.Code == "KG"))).ToList();
var filterInfo1 = new DynamicFilterInfo
{
Field = "Code",
Operator = DynamicFilterOperator.Eq,
Value = "KG",
};
var list1 = freeSql.Select <Order>().Where(t => t.OrderItems.Any(t1 => t1.Material.Units.AsSelect().WhereDynamicFilter(filterInfo1).Any())).ToList();
// 导航属性如果是 OneToOne 或者 ManyToOne 默认支持
var filterInfo2 = new DynamicFilterInfo
{
Field = "Orgnization.Company.Code",
Operator = DynamicFilterOperator.Eq,
Value = "CO001",
};
//var list2 = freeSql.Select<Order>().Where(t => t.Orgnization.Company.Code == "CO001").ToList();
var list2 = freeSql.Select <Order>().WhereDynamicFilter(filterInfo2).ToList();
// 实现效果 OrderItems.Material.Units.Code == "KG"
var list3 = freeSql.Select <Order>().Where("OrderItems.Material.Units.Code", DynamicFilterOperator.Eq, "KG").ToList();
// 实现效果 OrderItems.Material.Code == "TEST1"
// Error SQL:
// SELECT a."Id", a."Code", a."OrgnizationId"
// FROM "Order" a
// WHERE (exists(SELECT 1
// FROM "OrderItem" a
// LEFT JOIN "Material" a__Material ON a__Material."Id" = a."MaterialId"
// WHERE (a__Material."Code" = 'TEST1') AND (a."OrderId" = a."Id")
// limit 0,1))
var list4 = freeSql.Select <Order>().Where("OrderItems.Material.Code", DynamicFilterOperator.Eq, "TEST1").ToList();
// 拓展 DynamicFilter
var dynmaicFilterInfo = new DynamicFilterInfo
{
Field = $"{nameof(DynamicLinqCustom.WhereNavigation)} {typeof(DynamicLinqCustom).FullName},{typeof(DynamicLinqCustom).Assembly.FullName}",
Operator = DynamicFilterOperator.Custom,
Value = JsonConvert.SerializeObject(new DynamicFilterInfo {
Field = "OrderItems.Material.Units.Code", Operator = DynamicFilterOperator.Eq, Value = "KG"
}),
};
var list5 = freeSql.Select <Order>()
.WhereDynamicFilter(dynmaicFilterInfo)
.ToList();
}
}
public static void AddSimpleFreeSql(this IServiceCollection service)
{
var thisFontColor = (int)Console.ForegroundColor;
var newFontColor = (ConsoleColor)((thisFontColor + 1) > 15 ? 0 : (thisFontColor + 1));
if (service == null)
{
throw new ArgumentNullException(nameof(service));
}
var freeSql = service.BuildServiceProvider().GetRequiredService <IOptions <FreeSqlCollectionConfig> >().Value;
var config = freeSql.FreeSqlCollections.Where(it => it.Key == "IHRSystem").FirstOrDefault();
if (config == null)
{
throw new ArgumentNullException(nameof(config));
}
//注入FreeSql
service.AddScoped(f =>
{
var log = f.GetRequiredService <ILogger <IFreeSql> >();
var freeBuilder = new FreeSqlBuilder()
.UseAutoSyncStructure(false)
.UseConnectionString(config.DataType, config.MasterConnetion)
.UseLazyLoading(true)
.UseMonitorCommand(aop =>
{
//Console.ForegroundColor = newFontColor;
//Console.WriteLine("=================================================================================\n");
//Console.WriteLine(aop.CommandText + "\n");
string parametersValue = "";
for (int i = 0; i < aop.Parameters.Count; i++)
{
parametersValue += $"{aop.Parameters[i].ParameterName}:{aop.Parameters[i].Value}" + ";\n";
}
if (!string.IsNullOrWhiteSpace(parametersValue))
{
//Console.WriteLine(parametersValue);
log.LogInformation
(
"\n=================================================================================\n\n"
+ aop.CommandText + "\n\n"
+ parametersValue +
"\n=================================================================================\n\n"
);
}
log.LogInformation
(
"\n=================================================================================\n\n"
+ aop.CommandText +
"\n\n=================================================================================\n"
);
//Console.WriteLine("=================================================================================\n");
//Console.ForegroundColor = (ConsoleColor)thisFontColor;
});
if (config.SlaveConnections?.Count > 0)//判断是否存在从库
{
freeBuilder.UseSlave(config.SlaveConnections.Select(x => x.ConnectionString).ToArray());
}
var freesql = freeBuilder.Build();
//我这里禁用了导航属性联级插入的功能
freesql.SetDbContextOptions(opt => opt.EnableAddOrUpdateNavigateList = false);
return(freesql);
});
//注入Uow
service.AddScoped(f => f.GetRequiredService <IFreeSql>().CreateUnitOfWork());
}
/// <summary>
/// 添加数据库
/// </summary>
/// <param name="services"></param>
/// <param name="dbConfig"></param>
/// <returns></returns>
public async static Task AddDbAsync(this IServiceCollection services)
{
var codeSettings = new ConfigHelper().Get <CodeSettings>("codesettings", AppContext.BaseDirectory);
var dbConfig = services.BuildServiceProvider().GetRequiredService <IOptions <DbConfig> >().Value;
//创建数据库
if (dbConfig.CreateDb && codeSettings.InitDb)
{
await DbHelper.CreateDatabase(dbConfig);
}
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(dbConfig.SyncStructure && codeSettings.InitDb)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand && codeSettings.InitDb)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
//Console.WriteLine($"{cmd.CommandText}\n{traceLog}\r\n");
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
#region 初始化数据库
//同步结构
if (dbConfig.SyncStructure && codeSettings.InitDb)
{
DbHelper.SyncStructure(fsql, dbConfig: dbConfig);
}
//同步数据
if (dbConfig.SyncData && codeSettings.InitDb)
{
await DbHelper.SyncData(fsql, dbConfig);
}
#endregion
//生成数据包
if (dbConfig.GenerateData && !dbConfig.CreateDb && !dbConfig.SyncData)
{
await DbHelper.GenerateSimpleJsonData(fsql);
}
#region 监听Curd操作
if (dbConfig.Curd && codeSettings.InitDb)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
#endregion
#region 审计数据
/*
* //计算服务器时间
* var serverTime = fsql.Select<T>().Limit(1).First(a => DateTime.local);
* var timeOffset = DateTime.UtcNow.Subtract(serverTime);
* fsql.Aop.AuditValue += (s, e) =>
* {
* if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert)
* {
* switch (e.Property.Name)
* {
* case "CreatedTime":
* e.Value = DateTime.Now.Subtract(timeOffset);
* break;
* }
* }
* else if (e.AuditValueType == FreeSql.Aop.AuditValueType.Update)
* {
* switch (e.Property.Name)
* {
* case "ModifiedTime":
* e.Value = DateTime.Now.Subtract(timeOffset);
* break;
* }
* }
* };
*/
#endregion
services.AddSingleton(fsql);
#endregion
codeSettings.InitDb = false;
SuperCodeHelper.SaveSettings(codeSettings);
}
/// <summary>
/// 直接从配置文件读取配置
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="service"></param>
public static void AddFreeSql <T>(this IServiceCollection service)
{
//var thisFontColor = (int)Console.ForegroundColor;
//var newFontColor = (ConsoleColor)((thisFontColor + 1) > 15 ? 0 : (thisFontColor + 1));
service.AddSingleton(f =>
{
var log = f.GetRequiredService <ILogger <IFreeSql> >();
var current = f.GetRequiredService <IOptions <FreeSqlCollectionConfig> >().Value.FreeSqlCollections.FirstOrDefault(x => x.Key == typeof(T).Name);
var builder = new FreeSqlBuilder()
.UseConnectionString(current.DataType, current.MasterConnetion)
.UseAutoSyncStructure(current.IsSyncStructure)
.UseMonitorCommand(executing =>
{
executing.CommandTimeout = current.CommandTimeout;
if (current.DebugShowSql)
{
//Console.ForegroundColor = newFontColor;
//Console.WriteLine("\n=================================================================================\n");
//Console.WriteLine(executed.CommandText + "\n");
string parametersValue = "";
if (current.DebugShowSqlPparameters)
{
for (int i = 0; i < executing.Parameters.Count; i++)
{
parametersValue += $"{executing.Parameters[i].ParameterName}:{executing.Parameters[i].Value}" + ";\n";
}
}
if (!string.IsNullOrWhiteSpace(parametersValue))
{
//Console.WriteLine(parametersValue);
log.LogDebug
(
"\n=================================================================================\n\n"
+ executing.CommandText + "\n\n"
+ "\n" + parametersValue +
"\n=================================================================================\n\n"
);
}
else
{
log.LogDebug
(
"\n=================================================================================\n\n"
+ executing.CommandText +
"\n\n=================================================================================\n"
);
}
//Console.WriteLine("=================================================================================\n");
//Console.ResetColor();
}
});
if (current.SlaveConnections.Count > 0)//判断是否存在从库
{
builder.UseSlave(current.SlaveConnections.Select(x => x.ConnectionString).ToArray());
}
var res = builder.Build <T>();
#region //使用FreeSql AOP做对应的业务拓展，有需要自行实现
//res.GlobalFilter.Apply<IDeleted>(SysConsts.IsDeletedDataFilter, x => !x.IsDeleted);
//res.GlobalFilter.Apply<IEnabled>(SysConsts.IsEnabledDataFilter, x => x.Enabled == true);
//res.Aop.ConfigEntity += new EventHandler<ConfigEntityEventArgs>((_, e) =>
//{
// var attrs = e.EntityType.GetCustomAttributes(typeof(IndexAttribute), false);
// foreach (var attr in attrs)
// {
// var temp = attr as IndexAttribute;
// e.ModifyIndexResult.Add(new FreeSql.DataAnnotations.IndexAttribute(temp.Name, temp.Fields, temp.IsUnique));
// }
//});
#endregion //使用FreeSql AOP做对应的业务拓展，有需要自行实现
return(res);
});
service.AddScoped <IUnitOfWork <T>, UnitOfWork <T> >();
}
/// <summary>
/// 添加租户数据库
/// </summary>
/// <param name="services"></param>
/// <param name="env"></param>
public static void AddTenantDb(this IServiceCollection services, IHostEnvironment env)
{
services.AddScoped <MyUnitOfWorkManager>();
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", env.EnvironmentName);
var appConfig = new ConfigHelper().Get <AppConfig>("appconfig", env.EnvironmentName);
var user = services.BuildServiceProvider().GetService <IUser>();
int idleTime = dbConfig.IdleTime > 0 ? dbConfig.IdleTime : 10;
IdleBus <IFreeSql> ib = new IdleBus <IFreeSql>(TimeSpan.FromMinutes(idleTime));
var tenantName = AdminConsts.TenantName;
if (appConfig.TenantType == TenantType.Own)
{
tenantName = "tenant_" + user.TenantId?.ToString();
}
ib.TryRegister(tenantName, () =>
{
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(false)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
fsql.GlobalFilter.Apply <IEntitySoftDelete>("SoftDelete", a => a.IsDeleted == false);
//配置实体
DbHelper.ConfigEntity(fsql, appConfig);
//共享数据库
if (appConfig.TenantType == TenantType.Share)
{
fsql.GlobalFilter.ApplyIf <ITenant>("Tenant", () => user.TenantId > 0, a => a.TenantId == user.TenantId);
}
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
#endregion
#region 审计数据
//计算服务器时间
var serverTime = fsql.Select <DualEntity>().Limit(1).First(a => DateTime.UtcNow);
var timeOffset = DateTime.UtcNow.Subtract(serverTime);
fsql.Aop.AuditValue += (s, e) =>
{
DbHelper.AuditValue(e, timeOffset, user);
};
#endregion
#endregion
return(fsql);
});
services.AddSingleton(ib);
}
public void ArrayUseGenerateCommandParameterWithLambda()
{
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseGenerateCommandParameterWithLambda(true)
.UseAutoSyncStructure(true)
.UseMonitorCommand(null, (cmd, log) => Trace.WriteLine(log))
.Build())
{
var arr = new[] { 1L, 2L, 3L }.Select(x => x);
var ids = arr.Select(x => x);
var sql001 = fsql.Select <TableAllType>().Where(x => ids.Contains(x.Id)).ToSql();
Assert.Equal(@"SELECT a.""Id"", a.""id2"", a.""Bool"", a.""SByte"", a.""Short"", a.""Int"", a.""Long"", a.""Byte"", a.""UShort"", a.""UInt"", a.""ULong"", a.""Double"", a.""Float"", a.""Decimal"", a.""TimeSpan"", a.""DateTime"", a.""DateTimeOffSet"", a.""Bytes"", a.""String"", a.""Guid"", a.""BoolNullable"", a.""SByteNullable"", a.""ShortNullable"", a.""IntNullable"", a.""testFielLongNullable"", a.""ByteNullable"", a.""UShortNullable"", a.""UIntNullable"", a.""ULongNullable"", a.""DoubleNullable"", a.""FloatNullable"", a.""DecimalNullable"", a.""TimeSpanNullable"", a.""DateTimeNullable"", a.""DateTimeOffSetNullable"", a.""GuidNullable"", a.""Enum1"", a.""Enum1Nullable"", a.""Enum2"", a.""Enum2Nullable""
FROM ""tb_alltype"" a
WHERE (((a.""Id"") in (1,2,3)))", sql001);
IEnumerable <int> testlinqlist = new List <int>(new[] { 1, 2, 3 });
var testlinq = fsql.Select <TableAllType>().Where(a => testlinqlist.Contains(a.Int)).ToList();
var testlinq2list = new string[] { };
var testlinq2 = g.sqlite.Delete <TableAllType>().Where(a => testlinq2list.Contains(a.String)).ToSql();
Assert.Equal("DELETE FROM \"tb_alltype\" WHERE (((\"String\") in (NULL)))", testlinq2);
//in not in
var sql111 = fsql.Select <TableAllType>().Where(a => new[] { 1, 2, 3 }.Contains(a.Int)).ToList();
var sql112 = fsql.Select <TableAllType>().Where(a => new[] { 1, 2, 3 }.Contains(a.Int) == false).ToList();
var sql113 = fsql.Select <TableAllType>().Where(a => !new[] { 1, 2, 3 }.Contains(a.Int)).ToList();
var inarray = new[] { 1, 2, 3 };
var sql1111 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int)).ToList();
var sql1122 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int) == false).ToList();
var sql1133 = fsql.Select <TableAllType>().Where(a => !inarray.Contains(a.Int)).ToList();
//in not in
var sql11111 = fsql.Select <TableAllType>().Where(a => new List <int>()
{
1, 2, 3
}.Contains(a.Int)).ToList();
var sql11222 = fsql.Select <TableAllType>().Where(a => new List <int>()
{
1, 2, 3
}.Contains(a.Int) == false).ToList();
var sql11333 = fsql.Select <TableAllType>().Where(a => !new List <int>()
{
1, 2, 3
}.Contains(a.Int)).ToList();
var sql11111a = fsql.Select <TableAllType>().Where(a => new List <int>(new[] { 1, 2, 3 }).Contains(a.Int)).ToList();
var sql11222b = fsql.Select <TableAllType>().Where(a => new List <int>(new[] { 1, 2, 3 }).Contains(a.Int) == false).ToList();
var sql11333c = fsql.Select <TableAllType>().Where(a => !new List <int>(new[] { 1, 2, 3 }).Contains(a.Int)).ToList();
var inarray2 = new List <int>()
{
1, 2, 3
};
var sql111111 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int)).ToList();
var sql112222 = fsql.Select <TableAllType>().Where(a => inarray.Contains(a.Int) == false).ToList();
var sql113333 = fsql.Select <TableAllType>().Where(a => !inarray.Contains(a.Int)).ToList();
var inarray2n = Enumerable.Range(1, 3333).ToArray();
var sql1111111 = fsql.Select <TableAllType>().Where(a => inarray2n.Contains(a.Int)).ToList();
var sql1122222 = fsql.Select <TableAllType>().Where(a => inarray2n.Contains(a.Int) == false).ToList();
var sql1133333 = fsql.Select <TableAllType>().Where(a => !inarray2n.Contains(a.Int)).ToList();
}
}
public void SubSelectUseGenerateCommandParameterWithLambda()
{
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseConnectionString(DataType.SqlServer, "Data Source=.;Integrated Security=True;Initial Catalog=issues684;Pooling=true;Max Pool Size=3;TrustServerCertificate=true")
.UseGenerateCommandParameterWithLambda(true)
.UseAutoSyncStructure(true)
.UseMonitorCommand(null, (cmd, log) => Trace.WriteLine(log))
.Build())
{
var guidval = Guid.NewGuid();
var strval = "nameval";
var timeval = DateTime.Now;
var decval1 = 1.1M;
var decval2 = 2.2M;
var subselect = fsql.Select <ssugcpwl01>();
var sql = subselect.ToSql(a => new
{
a.id, a.name, a.createTime,
sum1 = fsql.Select <TableAllType>().Where(b => b.Guid == guidval).Sum(b => b.Int),
sum2 = fsql.Select <TableAllType>().Where(b => b.String == strval).Sum(b => b.Long),
sum3 = fsql.Select <TableAllType>().Where(b => b.DateTime == timeval).Sum(b => b.Decimal),
sum4 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval1).Sum(b => b.Decimal),
sum5 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval2).Sum(b => b.Decimal),
});
var subselect0 = subselect as Select0Provider;
Assert.Equal(5, subselect0._params.Count);
Assert.Equal("@exp_0", subselect0._params[0].ParameterName);
Assert.Equal("@exp_1", subselect0._params[1].ParameterName);
Assert.Equal("@exp_2", subselect0._params[2].ParameterName);
Assert.Equal("@exp_3", subselect0._params[3].ParameterName);
Assert.Equal("@exp_4", subselect0._params[4].ParameterName);
Assert.Equal(@"SELECT a.[id] as1, a.[name] as2, a.[createTime] as3, isnull((SELECT sum(b.[Int])
FROM [tb_alltype] b
WHERE (b.[Guid] = @exp_0)), 0) as4, isnull((SELECT sum(b.[Long])
FROM [tb_alltype] b
WHERE (b.[String] = @exp_1)), 0) as5, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[DateTime] = @exp_2)), 0) as6, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_3)), 0) as7, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_4)), 0) as8
FROM [ssugcpwl01] a", sql);
var groupselect = fsql.Select <ssugcpwl01>().GroupBy(a => a.name);
sql = groupselect.ToSql(a => new
{
a.Key,
sum1 = fsql.Select <TableAllType>().Where(b => b.Guid == guidval).Sum(b => b.Int),
sum2 = fsql.Select <TableAllType>().Where(b => b.String == strval).Sum(b => b.Long),
sum3 = fsql.Select <TableAllType>().Where(b => b.DateTime == timeval).Sum(b => b.Decimal),
sum4 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval1).Sum(b => b.Decimal),
sum5 = fsql.Select <TableAllType>().Where(b => b.Decimal == decval2).Sum(b => b.Decimal),
});
var groupselect0 = groupselect as SelectGroupingProvider;
Assert.Equal(5, groupselect0._select._params.Count);
Assert.Equal("@exp_0", groupselect0._select._params[0].ParameterName);
Assert.Equal("@exp_1", groupselect0._select._params[1].ParameterName);
Assert.Equal("@exp_2", groupselect0._select._params[2].ParameterName);
Assert.Equal("@exp_3", groupselect0._select._params[3].ParameterName);
Assert.Equal("@exp_4", groupselect0._select._params[4].ParameterName);
Assert.Equal(@"SELECT a.[name] as1, isnull((SELECT sum(b.[Int])
FROM [tb_alltype] b
WHERE (b.[Guid] = @exp_0)), 0) as2, isnull((SELECT sum(b.[Long])
FROM [tb_alltype] b
WHERE (b.[String] = @exp_1)), 0) as3, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[DateTime] = @exp_2)), 0) as4, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_3)), 0) as5, isnull((SELECT sum(b.[Decimal])
FROM [tb_alltype] b
WHERE (b.[Decimal] = @exp_4)), 0) as6
FROM [ssugcpwl01] a
GROUP BY a.[name]", sql);
}
}
/// <summary>
/// 注册MySql工作单元服务
/// </summary>
/// <typeparam name="TUnitOfWOrk">工作单元接口类型</typeparam>
/// <typeparam name="TUnitOfWorkImplementation">工作单元实现类型</typeparam>
/// <param name="services">服务集合</param>
/// <param name="connection">连接字符串</param>
/// <param name="setupAction">配置操作</param>
/// <param name="freeSqlSetupAction">FreeSql配置操作</param>
public static IServiceCollection AddMySqlUnitOfWork <TUnitOfWOrk, TUnitOfWorkImplementation>(this IServiceCollection services
, string connection
, Action <FreeSqlBuilder> setupAction = null
, Action <IFreeSql> freeSqlSetupAction = null)
where TUnitOfWOrk : class, IUnitOfWork
where TUnitOfWorkImplementation : UnitOfWorkBase, TUnitOfWOrk
{
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(DataType.MySql, connection)
.UseLazyLoading(false);
setupAction?.Invoke(freeSqlBuilder);
var freeSql = freeSqlBuilder.Build();
freeSqlSetupAction?.Invoke(freeSql);
freeSql.Aop.AuditValue += (s, e) =>
{
// 乐观锁
if (e.AuditValueType == AuditValueType.Insert || e.AuditValueType == AuditValueType.Update)
{
if (e.Property.Name == AuditedPropertyConst.Version)
{
if (e.Value is byte[] bytes && bytes.Length == 0)
{
e.Value = Encoding.UTF8.GetBytes(Guid.NewGuid().ToString());
}
}
}
// 时间
if (e.AuditValueType == AuditValueType.Insert)
{
if (e.Property.Name == AuditedPropertyConst.CreationTime ||
e.Property.Name == AuditedPropertyConst.ModificationTime)
{
e.Value = DateTime.Now;
}
}
if (e.AuditValueType == AuditValueType.Update)
{
if (e.Property.Name == AuditedPropertyConst.ModificationTime)
{
e.Value = DateTime.Now;
}
}
// 用户
if (e.Property.Name == AuditedPropertyConst.Creator ||
e.Property.Name == AuditedPropertyConst.Modifier ||
e.Property.Name == AuditedPropertyConst.CreatorId ||
e.Property.Name == AuditedPropertyConst.ModifierId)
{
var currentUser = ServiceLocator.Instance?.GetService <ICurrentUser>();
if (currentUser == null || currentUser.UserId.IsEmpty())
{
return;
}
if (e.AuditValueType == AuditValueType.Insert)
{
if (e.Property.Name == AuditedPropertyConst.Creator ||
e.Property.Name == AuditedPropertyConst.Modifier)
{
e.Value = currentUser.GetFullName() ?? currentUser.GetUserName();
}
if (e.Property.Name == AuditedPropertyConst.CreatorId ||
e.Property.Name == AuditedPropertyConst.ModifierId)
{
e.Value = currentUser.GetUserId();
}
}
if (e.AuditValueType == AuditValueType.Update)
{
if (e.Property.Name == AuditedPropertyConst.Modifier)
{
e.Value = currentUser.GetFullName() ?? currentUser.GetUserName();
}
if (e.Property.Name == AuditedPropertyConst.ModifierId)
{
e.Value = currentUser.GetUserId();
}
}
}
};
var freeSqlWrapper = new FreeSqlWrapper {
Orm = freeSql
};
freeSql.GlobalFilter.Apply <ISoftDelete>("SoftDelete", x => x.IsDeleted == false);
services.AddSingleton(freeSqlWrapper);
services.AddScoped <TUnitOfWOrk, TUnitOfWorkImplementation>();
return(services);
}
/// <summary>
/// 添加数据库
/// </summary>
/// <param name="services"></param>
/// <param name="env"></param>
public async static Task AddDbAsync(this IServiceCollection services, IHostEnvironment env)
{
services.AddScoped <MyUnitOfWorkManager>();
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", env.EnvironmentName);
//创建数据库
if (dbConfig.CreateDb)
{
await DbHelper.CreateDatabaseAsync(dbConfig);
}
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(false)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
//Console.WriteLine($"{cmd.CommandText}\n{traceLog}\r\n");
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion 监听所有命令
var fsql = freeSqlBuilder.Build();
//配置实体
var appConfig = new ConfigHelper().Get <AppConfig>("appconfig", env.EnvironmentName);
DbHelper.ConfigEntity(fsql, appConfig);
#region 初始化数据库
//同步结构
if (dbConfig.SyncStructure)
{
DbHelper.SyncStructure(fsql, dbConfig: dbConfig, appConfig: appConfig);
}
#region 审计数据
//计算服务器时间
var serverTime = fsql.Select <DualEntity>().Limit(1).First(a => DateTime.UtcNow);
var timeOffset = DateTime.UtcNow.Subtract(serverTime);
var user = services.BuildServiceProvider().GetService <IUser>();
DbHelper.TimeOffset = timeOffset;
fsql.Aop.AuditValue += (s, e) =>
{
DbHelper.AuditValue(e, timeOffset, user);
};
#endregion 审计数据
//同步数据
if (dbConfig.SyncData)
{
await DbHelper.SyncDataAsync(fsql, dbConfig, appConfig);
}
#endregion 初始化数据库
//生成数据包
if (dbConfig.GenerateData && !dbConfig.CreateDb && !dbConfig.SyncData)
{
await DbHelper.GenerateSimpleJsonDataAsync(fsql, appConfig);
}
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
#endregion 监听Curd操作
if (appConfig.Tenant)
{
fsql.GlobalFilter.Apply <ITenant>("Tenant", a => a.TenantId == user.TenantId);
}
#endregion FreeSql
services.AddSingleton(fsql);
//导入多数据库
if (null != dbConfig.Dbs)
{
foreach (var multiDb in dbConfig.Dbs)
{
switch (multiDb.Name)
{
case nameof(MySqlDb):
var mdb = CreateMultiDbBuilder(multiDb).Build <MySqlDb>();
services.AddSingleton(mdb);
break;
default:
break;
}
}
}
}
/// <summary>
/// FreeSql
/// </summary>
/// <param name="services"></param>
public static void AddContext(this IServiceCollection services, IConfiguration configuration)
{
IConfigurationSection configurationSection = configuration.GetSection("ConnectionStrings:MySql");
IFreeSql fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.MySql, configurationSection.Value)
.UseNameConvert(NameConvertType.PascalCaseToUnderscoreWithLower)
.UseAutoSyncStructure(true)
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd =>
{
Trace.WriteLine(cmd.CommandText + ";");
}
)
.Build()
.SetDbContextOptions(opt => opt.EnableAddOrUpdateNavigateList = true);//联级保存功能开启（默认为关闭）
fsql.Aop.CurdAfter += (s, e) =>
{
Log.Debug($"ManagedThreadId:{Thread.CurrentThread.ManagedThreadId}: FullName:{e.EntityType.FullName}" +
$" ElapsedMilliseconds:{e.ElapsedMilliseconds}ms, {e.Sql}");
if (e.ElapsedMilliseconds > 200)
{
//记录日志
//发送短信给负责人
}
};
//敏感词处理
if (configuration["AuditValue:Enable"].ToBoolean())
{
IllegalWordsSearch illegalWords = ToolGoodUtils.GetIllegalWordsSearch();
fsql.Aop.AuditValue += (s, e) =>
{
if (e.Column.CsType == typeof(string) && e.Value != null)
{
string oldVal = (string)e.Value;
string newVal = illegalWords.Replace(oldVal);
//第二种处理敏感词的方式
//string newVal = oldVal.ReplaceStopWords();
if (newVal != oldVal)
{
e.Value = newVal;
}
}
};
}
services.AddSingleton(fsql);
services.AddScoped <UnitOfWorkManager>();
fsql.GlobalFilter.Apply <IDeleteAduitEntity>("IsDeleted", a => a.IsDeleted == false);
//在运行时直接生成表结构
try
{
fsql.CodeFirst.SyncStructure(ReflexHelper.GetEntityTypes(typeof(IEntity)));
}
catch (Exception e)
{
Log.Logger.Error(e + e.StackTrace + e.Message + e.InnerException);
}
services.AddFreeRepository();
}
/// <summary>
/// 添加租户数据库
/// </summary>
/// <param name="services"></param>
/// <param name="env"></param>
public static void AddTenantDb(this IServiceCollection services, IHostEnvironment env)
{
services.AddScoped <MyUnitOfWorkManager>();
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", env.EnvironmentName);
var appConfig = new ConfigHelper().Get <AppConfig>("appconfig", env.EnvironmentName);
var user = services.BuildServiceProvider().GetService <IUser>();
int idleTime = dbConfig.IdleTime > 0 ? dbConfig.IdleTime : 10;
IdleBus <IFreeSql> ib = new IdleBus <IFreeSql>(TimeSpan.FromMinutes(idleTime));
ib.TryRegister("tenant_" + user.TenantId.ToString(), () =>
{
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
fsql.GlobalFilter.Apply <IEntitySoftDelete>("SoftDelete", a => a.IsDeleted == false);
//共享数据库
if (appConfig.TenantType == TenantType.Share)
{
fsql.GlobalFilter.ApplyIf <ITenant>("Tenant", () => user.TenantId > 0, a => a.TenantId == user.TenantId);
}
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
#endregion
#region 审计数据
fsql.Aop.AuditValue += (s, e) =>
{
if (user == null || user.Id <= 0)
{
return;
}
if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert)
{
switch (e.Property.Name)
{
case "CreatedUserId":
e.Value = user.Id;
break;
case "CreatedUserName":
e.Value = user.Name;
break;
case "TenantId":
e.Value = user.TenantId;
break;
}
}
else if (e.AuditValueType == FreeSql.Aop.AuditValueType.Update)
{
switch (e.Property.Name)
{
case "ModifiedUserId":
e.Value = user.Id;
break;
case "ModifiedUserName":
e.Value = user.Name;
break;
}
}
};
#endregion
#endregion
return(fsql);
});
services.AddSingleton(ib);
}
public void IncludeLevelTest()
{
using (var freeSql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "Data Source=:memory:;")
.UseAutoSyncStructure(true)
.Build())
{
freeSql.Aop.CurdBefore += (s, e) =>
{
Trace.WriteLine(e.Sql);
};
var company = new Company {
Id = Guid.NewGuid(), Code = "CO001"
};
var department = new Department {
Id = Guid.NewGuid(), Code = "D001", CompanyId = company.Id
};
var orgnization = new Orgnization {
Code = "C001", CompanyId = company.Id
};
freeSql.Insert(company).ExecuteAffrows();
freeSql.Insert(orgnization).ExecuteAffrows();
freeSql.Insert(department).ExecuteAffrows();
var materials = new[]
{
new Material {
Code = "TEST1", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
},
new Material {
Code = "TEST2", Units = new List <Unit> {
new Unit {
Code = "KG"
}
}
}
};
var repo1 = freeSql.GetGuidRepository <Material>();
repo1.DbContextOptions.EnableCascadeSave = true;
repo1.Insert(materials);
var order = new Order
{
Code = "X001",
OrgnizationId = orgnization.Id,
OrderItems = new List <OrderItem>
{
new OrderItem {
ItemCode = "01", MaterialId = materials[0].Id
},
new OrderItem {
ItemCode = "02", MaterialId = materials[1].Id
},
}
};
var repo2 = freeSql.GetGuidRepository <Order>();
repo2.DbContextOptions.EnableCascadeSave = true;
repo2.Insert(order);
// 可以完整加载数据
var list1 = freeSql.Select <Orgnization>().IncludeMany(t => t.Company.Departments).ToList();
// 只能查询到Orgnization
var list2 = freeSql.Select <Order>().IncludeMany(t => t.Orgnization.Company.Departments).ToList();
//freeSql.Select<Order>().IncludeMany(t => t.OrderItems, then => then.IncludeMany(t => t.Material.Units)).ToList().Dump();
// 使用扩展方法加载到指定层级
var list3 = freeSql.Select <Order>().IncludeLevel(3).ToList();
}
}
public void ListContains()
{
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(int);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.""Type"" as1
FROM ""User"" a
WHERE (((a.""Type"") in (1)))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(DataType.Sqlite, "data source=:memory:")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.ConfigEntity <User>(a => { });
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(string);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.""Type"" as1
FROM ""User"" a
WHERE (((a.""Type"") in ('Client')))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=5;Allow User Variables=True")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.Entity <User>(a => a.ToTable("issues1137_user"));
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.`Type` as1
FROM `issues1137_user` a
WHERE (((a.`Type`) in ('Client')))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=5;Allow User Variables=True")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.Entity <User>(a => a.ToTable("issues1137_user"));
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(int);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.`Type` as1
FROM `issues1137_user` a
WHERE (((a.`Type`) in (1)))", sql);
}
using (var fsql = new FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=5;Allow User Variables=True")
.UseGenerateCommandParameterWithLambda(true)
.Build())
{
fsql.CodeFirst.Entity <User>(a => a.ToTable("issues1137_user"));
fsql.Aop.ConfigEntityProperty += (s, e) =>
{
if (e.Property.PropertyType.IsEnum)
{
e.ModifyResult.MapType = typeof(string);
}
};
var listEnum = new List <UserType> {
UserType.Client
};
var sql = fsql.Select <User>().Where(a => listEnum.Contains(a.Type)).ToSql(a => a);
Assert.Equal(@"SELECT a.`Type` as1
FROM `issues1137_user` a
WHERE (((a.`Type`) in ('Client')))", sql);
}
}
/// <summary>
/// 添加数据库
/// </summary>
/// <param name="services"></param>
/// <param name="env"></param>
public async static Task AddDbAsync(this IServiceCollection services, IHostEnvironment env)
{
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", env.EnvironmentName);
//创建数据库
if (dbConfig.CreateDb)
{
await DbHelper.CreateDatabaseAsync(dbConfig);
}
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(false)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
//Console.WriteLine($"{cmd.CommandText}\n{traceLog}\r\n");
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
//配置实体
var appConfig = new ConfigHelper().Get <AppConfig>("appconfig", env.EnvironmentName);
DbHelper.ConfigEntity(fsql, appConfig);
#region 初始化数据库
//同步结构
if (dbConfig.SyncStructure)
{
DbHelper.SyncStructure(fsql, dbConfig: dbConfig, appConfig: appConfig);
}
//同步数据
if (dbConfig.SyncData)
{
await DbHelper.SyncDataAsync(fsql, dbConfig);
}
#endregion
//生成数据包
if (dbConfig.GenerateData && !dbConfig.CreateDb && !dbConfig.SyncData)
{
await DbHelper.GenerateSimpleJsonDataAsync(fsql);
}
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
#endregion
#region 审计数据
//计算服务器时间
//var serverTime = fsql.Select<T>().Limit(1).First(a => DateTime.local);
//var timeOffset = DateTime.UtcNow.Subtract(serverTime);
var user = services.BuildServiceProvider().GetService <IUser>();
fsql.Aop.AuditValue += (s, e) =>
{
if (user == null || user.Id <= 0)
{
return;
}
if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert)
{
switch (e.Property.Name)
{
case "CreatedUserId":
e.Value = user.Id;
break;
case "CreatedUserName":
e.Value = user.Name;
break;
case "TenantId":
e.Value = user.TenantId;
break;
//case "CreatedTime":
// e.Value = DateTime.Now.Subtract(timeOffset);
// break;
}
}
else if (e.AuditValueType == FreeSql.Aop.AuditValueType.Update)
{
switch (e.Property.Name)
{
case "ModifiedUserId":
e.Value = user.Id;
break;
case "ModifiedUserName":
e.Value = user.Name;
break;
//case "ModifiedTime":
// e.Value = DateTime.Now.Subtract(timeOffset);
// break;
}
}
};
#endregion
#endregion
//导入多数据库
if (null != dbConfig.Dbs)
{
foreach (var multiDb in dbConfig.Dbs)
{
switch (multiDb.Name)
{
case nameof(MySqlDb):
var mdb = CreateMultiDbBuilder(multiDb).Build <MySqlDb>();
services.AddSingleton(mdb);
break;
default:
break;
}
}
}
}
/// <summary>
/// 注入FreeSql
/// </summary>
/// <param name="services">IServiceCollection</param>
public static void AddFreeSql(this IServiceCollection services)
{
Log.Logger.Information("Initialize FreeSql Start;");
//获取数据库类型及其连接字符串
var dataTypeValue = NpsEnvironment.NPS_DB_DATETYPE;
var dataTypeConnectionString = NpsEnvironment.NPS_DB_MASTERCONNECTSTRING;
if (Enum.TryParse(dataTypeValue, out DataType dataType))
{
if (!Enum.IsDefined(typeof(DataType), dataType))
{
Log.Error($"数据库配置DataType:{dataType}无效");
}
if (dataTypeConnectionString.IsNullOrWhiteSpace())
{
Log.Error($"数据库配置ConnectionStrings:{dataType}连接字符串无效");
}
}
else
{
Log.Error($"数据库配置DataType:{dataTypeValue}无效");
}
//创建建造器
var builder = new FreeSqlBuilder()
.UseConnectionString(dataType, dataTypeConnectionString)
.UseNameConvert(NameConvertType.PascalCaseToUnderscoreWithLower)
//设置是否自动同步表结构，开发环境必备
.UseAutoSyncStructure(NpsEnvironment.NPS_DB_SYNCSTRUCTURE.ToBooleanOrDefault(false))
.UseNoneCommandParameter(true)
.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{ //监听所有命令
Log.Logger.Debug($"MonitorCommand:{traceLog};");
});
//生成数据库操作对象
IFreeSql freeSql = builder.Build();
//开启联级保存功能（默认为关闭）
freeSql.SetDbContextOptions(opts => opts.EnableAddOrUpdateNavigateList = true);
//加载过滤器，设置全局软删除为false
freeSql.GlobalFilter.Apply <ISoftDelete>("IsDeleted", a => a.IsDeleted == false);
//监听CURD操作
freeSql.Aop.CurdAfter += (s, e) =>
{
//Task.Run(() =>//写法一
Parallel.For(0, 1, a =>//写法二
{
//添加MiniProfiler监控SQL性能
MiniProfiler.Current.CustomTiming("SQL：", $"【SQL语句】：{e.Sql}，耗时：{e.ElapsedMilliseconds}毫秒");
//若实体中不含DisableSqlCurd标记，则将记录写入至数据库
if (e.EntityType.GetCustomAttribute <DisableSqlCurdAttribute>(false) == null)
{
//获取ISqlCurdService对象，在获取之前需要注入
var sqlCurdService = services.BuildServiceProvider().GetRequiredService <ISqlCurdService>();
sqlCurdService.Create(new SqlCurdAddInput
{
FullName = e.EntityType.FullName,
ExecuteMilliseconds = e.ElapsedMilliseconds,
Sql = e.Sql
});
}
});
};
//获取IdGenerator对象，在获取之前需要注入
var longGenerator = services.BuildServiceProvider().GetRequiredService <ILongIdGenerator>();
var guidGenerator = services.BuildServiceProvider().GetRequiredService <IGuidGenerator>();
//审计Curd
freeSql.Aop.AuditValue += (s, e) =>
{
if (e.Column.CsType == typeof(long))
{
if (e.Property.GetCustomAttribute <IdGeneratorAttribute>(false) != null)
{
if (e.Value?.ToString() == "0")
{//当主键为long类型且拥有Id自生成标记且值为0时，主键列不能设置自增属性
e.Value = longGenerator?.Create();
}
}
}
else if (e.Column.CsType == typeof(Guid))
{
if (e.Property.GetCustomAttribute <IdGeneratorAttribute>(false) != null)
{
if (e.Value?.ToString() == "00000000-0000-0000-0000-000000000000")
{//当主键为Guid类型且拥有Id自生成标记且值全为0时
e.Value = guidGenerator?.Create();
}
}
}
};
//注入IOC框架
services.AddSingleton(freeSql);
//注入仓储
services.AddFreeRepository();
//注入工作单元
services.AddScoped <UnitOfWorkManager>();
//注入SQLCurd语句写入服务
services.AddScoped <ISqlCurdService, SqlCurdService>();
//连接数据库
try
{
using var masterPool = freeSql.Ado.MasterPool.Get();
}
catch (Exception ex)
{
Log.Logger.Error(ex + ex.StackTrace + ex.Message + ex.InnerException);
return;
}
//同步表结构及初始化数据
try
{
//注意：只有当CURD到此表时，才会自动生成表结构。
//如需系统运行时迁移表结构，请使用SyncStructure方法
//在运行时直接生成表结构
if (NpsEnvironment.NPS_DB_SYNCSTRUCTURE.ToBooleanOrDefault(false))
{
freeSql.CodeFirst
.ConfigEntity()
.SeedData(NpsEnvironment.NPS_DB_SYNCDATA.ToBooleanOrDefault(false)) //初始化部分数据
.SyncStructure(FreeSqlEntitySyncStructure.FindIEntities(new string[] { "Nps.Data" }));
}
}
catch (Exception ex)
{
Log.Logger.Error(ex + ex.StackTrace + ex.Message + ex.InnerException);
return;
}
Log.Logger.Information("Initialize FreeSql End;");
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var freesql = new FreeSqlBuilder().UseAutoSyncStructure(true)
.UseConnectionString(DataType.SqlServer, CustomConfigManager.Default["ConnStr"])
.UseExitAutoDisposePool(true)
.Build();
services.AddSingleton <IFreeSql>(freesql);
//services.AddCors(options =>
//{
// // CorsPolicy 是自訂的 Policy 名稱
// options.AddPolicy("CorsPolicy", policy =>
// {
// policy.WithOrigins("http://localhost:5000")
// .AllowAnyHeader()
// .AllowAnyMethod()
// .AllowCredentials();
// });
//});
//services.AddScoped<IMessageService, MessageService>();
//services.AddScoped<ISessionService, SessionService>();
services.AddSingleton <IStorage>(x =>
{
var env = (IHostEnvironment)x.GetService(typeof(IHostEnvironment));
return(new LocalStorage(env.ContentRootPath));
});
services.AddSwaggerDocument(opt =>
{
opt.ApiGroupNames = new[] { "im" };
opt.DocumentName = "api";
opt.Title = "Api接口";
opt.UseJsonTemplate(typeof(Startup).Assembly);
opt.DocumentProcessors.Add(new SecurityDefinitionAppender("Authorization", new OpenApiSecurityScheme()
{
Type = OpenApiSecuritySchemeType.ApiKey,
Name = "Authorization",
In = OpenApiSecurityApiKeyLocation.Header,
Description = "授权token",
}));
});
services.AddCors(x =>
{
x.AddDefaultPolicy(y =>
y.AllowAnyHeader().AllowAnyMethod().AllowCredentials().SetIsOriginAllowed(_ => true));
});
services.Configure <KestrelServerOptions>(options =>
{
options.AllowSynchronousIO = true;
});
// If using IIS:
services.Configure <IISServerOptions>(options =>
{
options.AllowSynchronousIO = true;
});
services.AddControllers()
.AddNewtonsoftJson((options) =>
{
options.SerializerSettings.DateFormatString = "yyyy-MM-dd HH:mm:ss";
(options.SerializerSettings.ContractResolver as DefaultContractResolver).NamingStrategy = new CamelCaseNamingStrategy(true, true);
}).EnableJsonValueModelBinder();
services.AddAuthentication()
.AddWebJWT("im", new WebJWTOption()
{
Audience = "sdfs",
Issuer = "sss",
LoginService = new LoginService(),
//TokenFactory = (context)=>context.HttpContext.Request.Query["access_token"]
});
services.AddIM();
//services.AddSignalR(x =>
//{
// x.EnableDetailedErrors = true;
// x.KeepAliveInterval=TimeSpan.FromMinutes(2);
//});
}
public void ConfigureContainer(ContainerBuilder builder)
{
#region AutoFac IOC容器
try
{
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", _env.EnvironmentName);
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(dbConfig.SyncStructure)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
var fsql = freeSqlBuilder.Build();
builder.RegisterInstance(fsql).SingleInstance();
builder.RegisterType(typeof(UnitOfWorkManager)).InstancePerLifetimeScope();
builder.RegisterType <HttpContextAccessor>().As <IHttpContextAccessor>().SingleInstance();
builder.RegisterType <User>().As <IUser>().SingleInstance();
builder.RegisterType <UserToken>().As <IUserToken>().InstancePerLifetimeScope();
#region 缓存
var cacheConfig = _configHelper.Get <CacheConfig>("cacheconfig", _env.EnvironmentName);
if (cacheConfig.Type == CacheType.Redis)
{
var csredis = new CSRedis.CSRedisClient(cacheConfig.Redis.ConnectionString);
RedisHelper.Initialization(csredis);
builder.RegisterType <RedisCache>().As <ICache>().SingleInstance();
}
else
{
builder.RegisterType <MicrosoftMemoryCache.MemoryCache>().As <MicrosoftMemoryCache.IMemoryCache>().SingleInstance();
builder.RegisterType <MemoryCache>().As <ICache>().SingleInstance();
}
#endregion
#region SingleInstance
//无接口注入单例
var assemblyCore = Assembly.Load("Web.Core");
var assemblyCommon = Assembly.Load("Admin.Core.Common");
builder.RegisterAssemblyTypes(assemblyCore, assemblyCommon)
.Where(t => t.GetCustomAttribute <SingleInstanceAttribute>() != null)
.SingleInstance();
//有接口注入单例
builder.RegisterAssemblyTypes(assemblyCore, assemblyCommon)
.Where(t => t.GetCustomAttribute <SingleInstanceAttribute>() != null)
.AsImplementedInterfaces()
.SingleInstance();
#endregion
#region Aop
var interceptorServiceTypes = new List <Type>();
if (_appConfig.Aop.Transaction)
{
builder.RegisterType <TransactionInterceptor>();
interceptorServiceTypes.Add(typeof(TransactionInterceptor));
}
#endregion
#region Repository
var assemblyRepository = Assembly.Load("Admin.Core.Repository");
builder.RegisterAssemblyTypes(assemblyRepository)
.AsImplementedInterfaces()
.InstancePerDependency();
#endregion
#region Service
var assemblyServices = Assembly.Load("Admin.Core.Service");
builder.RegisterAssemblyTypes(assemblyServices)
.AsImplementedInterfaces()
.InstancePerDependency()
.EnableInterfaceInterceptors()
.InterceptedBy(interceptorServiceTypes.ToArray());
#endregion
}
catch (Exception ex)
{
throw new Exception(ex.Message + "\n" + ex.InnerException);
}
#endregion
}
/// <summary>
/// 添加租户数据库
/// </summary>
/// <param name="services"></param>
/// <param name="env"></param>
public static void AddTenantDb(this IServiceCollection services, IHostEnvironment env)
{
services.AddScoped <MyUnitOfWorkManager>();
var dbConfig = new ConfigHelper().Get <DbConfig>("dbconfig", env.EnvironmentName);
var appConfig = new ConfigHelper().Get <AppConfig>("appconfig", env.EnvironmentName);
var user = services.BuildServiceProvider().GetService <IUser>();
int idleTime = dbConfig.IdleTime > 0 ? dbConfig.IdleTime : 10;
IdleBus <IFreeSql> ib = new IdleBus <IFreeSql>(TimeSpan.FromMinutes(idleTime));
var tenantName = AdminConsts.TenantName;
if (appConfig.TenantType == TenantType.Own)
{
tenantName = "tenant_" + user.TenantId.ToString();
}
ib.TryRegister(tenantName, () =>
{
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
fsql.GlobalFilter.Apply <IEntitySoftDelete>("SoftDelete", a => a.IsDeleted == false);
//配置实体
DbHelper.ConfigEntity(fsql, appConfig);
//共享数据库
if (appConfig.TenantType == TenantType.Share)
{
fsql.GlobalFilter.ApplyIf <ITenant>("Tenant", () => user.TenantId > 0, a => a.TenantId == user.TenantId);
}
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
#endregion
#region 审计数据
//计算服务器时间
var serverTime = fsql.Select <DualEntity>().Limit(1).First(a => DateTime.UtcNow);
var timeOffset = DateTime.UtcNow.Subtract(serverTime);
fsql.Aop.AuditValue += (s, e) =>
{
if (user == null || user.Id <= 0)
{
return;
}
if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert)
{
switch (e.Property.Name)
{
case "CreatedUserId":
e.Value = user.Id;
break;
case "CreatedUserName":
e.Value = user.Name;
break;
case "TenantId":
e.Value = user.TenantId;
break;
}
if (e.Property.GetCustomAttribute <ServerTimeAttribute>(false) != null && (e.Column.CsType == typeof(DateTime) || e.Column.CsType == typeof(DateTime?)) &&
(e.Value == null || (DateTime)e.Value == default || (DateTime?)e.Value == default))
{
e.Value = DateTime.Now.Subtract(timeOffset);
}
}
/// <summary>
/// 添加数据库配置
/// </summary>
/// <param name="services"></param>
/// <param name="appConfig"></param>
public static void AddFreesql(this IServiceCollection services, AppConfig appConfig, DbConfig dbConfig)
{
_services = services;
//创建数据库
if (dbConfig.CreateDb)
{
//await DbHelper.CreateDatabase(dbConfig);
}
#region FreeSql
var freeSqlBuilder = new FreeSqlBuilder()
.UseConnectionString(dbConfig.Type, dbConfig.ConnectionString)
.UseAutoSyncStructure(dbConfig.SyncStructure)
.UseLazyLoading(false)
.UseNoneCommandParameter(true);
#region 监听所有命令
if (dbConfig.MonitorCommand)
{
freeSqlBuilder.UseMonitorCommand(cmd => { }, (cmd, traceLog) =>
{
//Console.WriteLine($"{cmd.CommandText}\n{traceLog}\r\n");
Console.WriteLine($"{cmd.CommandText}\r\n");
});
}
#endregion
var fsql = freeSqlBuilder.Build();
services.AddSingleton <IHttpContextAccessor, HttpContextAccessor>();
services.AddSingleton <IUser, User>();
services.AddFreeRepository(filter => filter.Apply <IEntitySoftDelete>("SoftDelete", a => a.IsDeleted == false));
services.AddScoped <UnitOfWorkManager>();
services.AddSingleton(fsql);
services.AddSingleton <ISysFileResponsitory, SysFileResponsitory>();
services.AddSingleton <ISysDicResponsitory, SysDicResponsitory>();
#region 初始化数据库
//同步数据
if (dbConfig.SyncData)
{
//await DbHelper.SyncData(fsql, dbConfig);
}
#endregion
//生成数据包
if (dbConfig.GenerateData && !dbConfig.CreateDb && !dbConfig.SyncData)
{
//await DbHelper.GenerateSimpleJsonData(fsql);
}
List <string> _md5s = new List <string>();
#region 监听Curd操作
if (dbConfig.Curd)
{
fsql.Aop.CurdBefore += (s, e) =>
{
Console.WriteLine($"{e.Sql}\r\n");
};
}
fsql.Aop.CurdAfter += (s, e) =>
{
if (e.CurdType == FreeSql.Aop.CurdType.Insert || e.CurdType == FreeSql.Aop.CurdType.Update)
{
//文件，更新为已使用
if (_md5s.Any() && e.EntityType != typeof(SysFile))
{
var file = services.BuildServiceProvider().GetService <ISysFileResponsitory>();
file.UpdateDiy.Set(p => p.Used, true).Where(p => _md5s.Contains(p.Md5) && p.Uploaded && !p.Used).ExecuteAffrows();
_md5s.Clear();
}
}
if (e.ElapsedMilliseconds > 3000)
{
//TODO 慢SQL提醒开发者
Console.WriteLine($"慢Sql:{e.Sql}\r\n");
}
};
#endregion
#region 审计数据
fsql.Aop.AuditValue += (s, e) =>
{
if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert || e.AuditValueType == FreeSql.Aop.AuditValueType.Update)
{
//更新文件为已使用
if (e.Property.GetCustomAttributes(typeof(FileAttribute), false).Any())
{
if (e.Value != null && !_md5s.Any(p => p == e.Value.ToString()))
{
_md5s.Add(e.Value.ToString());
}
}
var user = services.BuildServiceProvider().GetService <IUser>();
if (user == null || !(user.Id > 0))
{
return;
}
if (e.AuditValueType == FreeSql.Aop.AuditValueType.Insert)
{
switch (e.Property.Name)
{
case "CreatedUserId":
e.Value = user.Id;
break;
case "CreatedUserName":
e.Value = user.RealName;
break;
case "CreatedTime":
e.Value = DateTime.Now;
break;
}
}
switch (e.Property.Name)
{
case "ModifiedUserId":
e.Value = user.Id;
break;
case "ModifiedUserName":
e.Value = user.Name;
break;
case "ModifiedTime":
e.Value = DateTime.Now;
break;
}
}
};
#endregion
#endregion
}
