//注册上下文:AOP里面可以获取IOC对象,如果有现成框架比如Furion可以不写这一行
services.AddHttpContextAccessor();
//注册SqlSugar
services.AddSingleton<ISqlSugarClient>(s =>
{
SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = _appConfiguration.GetConnectionString("Default"),//"Server=.\\SQL16; Database=MaikeABPStudy; Uid=sa; Pwd=123456;",
IsAutoCloseConnection = true,
},
db =>
{
//自动过滤软删除
db.QueryFilter.AddTableFilter<ISoftDelete>(it => it.IsDeleted == false);
//自动过滤租户
db.QueryFilter.AddTableFilter<IMustHaveTenant>(it =>
it.TenantId == IocManager.Instance.IocContainer.Resolve<IAbpSession>().TenantId);
db.Aop.DataExecuting = (_, entityInfo) =>
{
switch (entityInfo.OperationType)
{
//执行insert时
case DataFilterType.InsertByObject:
//自动设置主键: 雪花id,单号
if (entityInfo.EntityColumnInfo.IsPrimarykey)
{
//var obj = (IEntity)entityInfo.EntityValue;
//obj.Id = YitIdHelper.NextId();
}
//自动设置时间和操作人
if (entityInfo.EntityValue is ICreationAudited)
{
var session = IocManager.Instance.IocContainer.Resolve<IAbpSession>();
var obj = (ICreationAudited)entityInfo.EntityValue;
obj.CreationTime = DateTime.Now;
obj.CreatorUserId = session.UserId;
}
//自动设置租户
if (entityInfo.EntityValue is IMustHaveTenant)
{
var session = IocManager.Instance.IocContainer.Resolve<IAbpSession>();
var obj = (IMustHaveTenant)entityInfo.EntityValue;
obj.TenantId = session.TenantId.Value;
}
break;
case DataFilterType.UpdateByObject:
//自动设置时间和操作人
if (entityInfo.EntityValue is IModificationAudited)
{
var session = IocManager.Instance.IocContainer.Resolve<IAbpSession>();
var obj = (IModificationAudited)entityInfo.EntityValue;
obj.LastModificationTime = DateTime.Now;
obj.LastModifierUserId = session.UserId;
}
break;
case DataFilterType.DeleteByObject:
//自动设置时间和操作人
if (entityInfo.EntityValue is IDeletionAudited)
{
var session = IocManager.Instance.IocContainer.Resolve<IAbpSession>();
var obj = (IDeletionAudited)entityInfo.EntityValue;
obj.DeletionTime = DateTime.Now;
obj.DeleterUserId = session.UserId;
}
break;
}
};
db.Aop.OnLogExecuting = (sql, pars) =>
{
LogHelper.Logger.Fatal(UtilMethods.GetSqlString(db.CurrentConnectionConfig.DbType, sql, pars));
};
db.Aop.OnError = ex =>
{
if (ex.Parametres == null)
{
LogHelper.Logger.Fatal(ex.Sql);
}
else
{
LogHelper.Logger.Fatal(UtilMethods.GetSqlString(db.CurrentConnectionConfig.DbType, ex.Sql, (SugarParameter[])ex.Parametres));
}
};
});
return sqlSugar;
});
//注册SqlSugar仓储
services.AddScoped(typeof(ISqlSugarRepository<>), typeof(SqlSugarRepository<>));
在CORE层添加SqlSugar仓储接口和实现
接口
public interface ISqlSugarRepository<T> : ISimpleClient<T> where T : class, new()
{
ISqlSugarClient Context { get; set; }
ISugarQueryable<T> Queryable { get; }
}
实现
/// <summary>
/// 基础仓储类
/// </summary>
public class SqlSugarRepository<T> : SimpleClient<T>, ISqlSugarRepository<T> where T : class, new()
{
public SqlSugarRepository(ISqlSugarClient context = null) : base(context)
{
}
public ISugarQueryable<T> Queryable
{
get
{
return Context.Queryable<T>();
}
}
}
在应用层注入使用
private ISqlSugarRepository<XXXEntity> _sqlsugarRepository;
//使用
var obj = _sqlsugarRepository.Queryable.First() ;
备用代码:furion框架中sqlsugar的日志记录
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;
UtilMethods.GetSqlString(config.DbType, sql, pars).LogCritical();
//Console.WriteLine("【" + DateTime.Now + "——执行SQL】\r\n" + UtilMethods.GetSqlString(config.DbType, sql, pars) + "\r\n");
//Console.ForegroundColor = originColor;
//打印到swagger
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));
var log = "【SQL错误】\r\n" + UtilMethods.GetSqlString(config.DbType, ex.Sql, (SugarParameter[])ex.Parametres);
log += ex.ToString();
log.SetCategory<SqlSugarCache>().LogCritical();
//Console.WriteLine("【" + DateTime.Now + "——错误SQL】\r\n" + UtilMethods.GetSqlString(config.DbType, ex.Sql, (SugarParameter[])ex.Parametres) + "\r\n");
//Console.ForegroundColor = originColor;
//打印到swagger
App.PrintToMiniProfiler("SqlSugar", "Error", $"{ex.Message}{Environment.NewLine}{ex.Sql}{pars}{Environment.NewLine}");
};
db.Aop.OnLogExecuted = (sql, pars) =>
{
// 执行时间超过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 totalSeconds = db.Ado.SqlExecutionTime.TotalSeconds; // 方法名
var log = $"【所在文件名】:{fileName}\r\n【代码行数】:{fileLine}\r\n【方法名】:{firstMethodName}\r\n【执行时长】:{totalSeconds}\r\n" + $"【sql语句】:{UtilMethods.GetSqlString(config.DbType, sql, pars)}";
log.SetCategory<SqlSugarUnitOfWork>().LogInformation(); // 日志名字,借用SqlSugarUnitOfWork来标记执行时间过长的脚本
//var originColor = Console.ForegroundColor;
//Console.ForegroundColor = ConsoleColor.DarkYellow;
//Console.WriteLine(log);
//Console.ForegroundColor = originColor;
}
};
// 日志写入文件
if (App.GetConfig<bool>("Logging:File:Enabled"))
{
Array.ForEach(new[] { LogLevel.Information, LogLevel.Warning, LogLevel.Error, LogLevel.Critical }, logLevel =>
{
services.AddFileLogging("logs/{0:yyyy}{0:MM}{0:dd}/{1}-{0:HH}.log", options =>