using System;
|
using System.Linq;
|
|
using HH.WCS.Mobox3.AnGang.AppStart;
|
|
using SqlSugar;
|
|
using Task = System.Threading.Tasks.Task;
|
|
namespace HH.WCS.Mobox3.AnGang.Helpers {
|
// SqlSugar 教程：https://www.donet5.com/Home/Doc
|
|
/// <summary>
|
/// 数据库帮助类：包括单例模式和创建新连接的方法
|
/// </summary>
|
public static class DbHelper {
|
private static SqlSugarScope _sqlSugar;
|
|
static DbHelper() {
|
Init();
|
}
|
|
public static void Init() {
|
_sqlSugar = new SqlSugarScope(new ConnectionConfig() {
|
ConnectionString = Settings.Config.SqlServer,
|
DbType = DbType.SqlServer,
|
IsAutoCloseConnection = true,
|
InitKeyType = InitKeyType.Attribute // 从特性读取主键自增信息
|
},
|
db => {
|
// 监控所有超过1秒的Sql
|
db.Aop.OnLogExecuted = (sql, p) => {
|
// 执行时间超过1秒
|
if (db.Ado.SqlExecutionTime.TotalSeconds > 1) {
|
Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
|
// 代码CS文件名
|
var fileName = db.Ado.SqlStackTrace.FirstFileName;
|
// 代码行数
|
var fileLine = db.Ado.SqlStackTrace.FirstLine;
|
// 方法名
|
var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
|
}
|
// 相当于EF的 PrintToMiniProfiler
|
};
|
|
// 每次设置数值时都去除前导后导空格
|
db.Aop.DataExecuted = (value, entity) => {
|
entity.EntityColumnInfos.ToList().ForEach(a => {
|
var pvalue = entity.GetValue(a.PropertyName);
|
if (pvalue != null && pvalue.GetType() == typeof(String)) {
|
entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
|
}
|
});
|
};
|
|
// 配置AOP日志
|
//db.Aop.OnLogExecuting = (sql, pars) =>
|
//{
|
// Console.WriteLine(sql); // 输出纯SQL语句
|
//};
|
|
// 每N小时清理一次缓存
|
Task.Run(async () => {
|
while (true) {
|
await Task.Delay(TimeSpan.FromHours(2));
|
db.QueryFilter.Clear();
|
}
|
});
|
});
|
}
|
|
/// <summary>
|
/// 获取用于操控数据库的 SqlSugarClient 对象
|
/// </summary>
|
/// <returns></returns>
|
public static SqlSugarScope GetDbClient() {
|
return _sqlSugar;
|
}
|
|
/// <summary>
|
/// 生成用于操控数据库的 SqlSugarClient 对象
|
/// </summary>
|
/// <remarks>
|
/// 如果用 Oracle 数据需要包 Oracle.ManagedDataAccess/21.15.0，环境 netframework 4.62，太新了 4.8 有的服务器安装不上去
|
/// </remarks>
|
/// <param name="url"></param>
|
/// <returns></returns>
|
public static SqlSugarClient NewDbClient(string url = "") {
|
// 创建数据库对象
|
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {
|
ConnectionString = string.IsNullOrEmpty(url) ? Settings.Config.SqlServer : url,
|
DbType = DbType.SqlServer,
|
IsAutoCloseConnection = true,
|
InitKeyType = InitKeyType.Attribute // 从特性读取主键自增信息
|
});
|
|
// 监控所有超过1秒的Sql
|
db.Aop.OnLogExecuted = (sql, p) => {
|
// 执行时间超过1秒
|
if (db.Ado.SqlExecutionTime.TotalSeconds > 1) {
|
Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
|
// 代码CS文件名
|
var fileName = db.Ado.SqlStackTrace.FirstFileName;
|
// 代码行数
|
var fileLine = db.Ado.SqlStackTrace.FirstLine;
|
// 方法名
|
var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
|
}
|
// 相当于EF的 PrintToMiniProfiler
|
};
|
|
// 每次设置数值时都去除前导后导空格
|
db.Aop.DataExecuted = (value, entity) => {
|
entity.EntityColumnInfos.ToList().ForEach(a => {
|
var pvalue = entity.GetValue(a.PropertyName);
|
if (pvalue != null && pvalue.GetType() == typeof(String)) {
|
entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
|
}
|
});
|
};
|
|
// 据转换 (ExecuteCommand 才会拦截，查询不行)
|
//db.Aop.DataExecuting = (value, entity) => {
|
// //var val=entity.EntityColumnInfo
|
// Console.WriteLine(entity.EntityName);
|
//};
|
|
// 用于打印 SqlSugar 解码后的 SQL 语句
|
db.Aop.OnLogExecuting = (sql, pars) => {
|
Console.WriteLine(sql);
|
};
|
|
return db;
|
}
|
}
|
}
|
