Sqlsugar标准的多库操作

1.appsetting文件

2.配置BaseLog类

 public abstract class BaseLog : RootEntityTkey<long>
    {
        [SplitField]
        public DateTime? DateTime { get; set; }

        [SugarColumn(IsNullable = true)]
        public string Level { get; set; }

        [SugarColumn(IsNullable = true, ColumnDataType = "longtext,text,clob")]
        public string Message { get; set; }

        [SugarColumn(IsNullable = true, ColumnDataType = "longtext,text,clob")]
        public string MessageTemplate { get; set; }

        [SugarColumn(IsNullable = true, ColumnDataType = "longtext,text,clob")]
        public string Properties { get; set; }
    }

3.配置AuditSqlLog类

[Tenant(configId: "log")]
[SugarTable("AuditSqlLog_20231201", "Sql审计日志")]
//[SplitTable(SplitType.Month)] //按月分表 （自带分表支持 年、季、月、周、日）
//[SugarTable($@"{nameof(AuditSqlLog)}_{{year}}{{month}}{{day}}")]
public class AuditSqlLog : BaseLog
{

}

4.配置AuditSqlLogVo类

public class AuditSqlLogVo
{
    public DateTime? DateTime { get; set; }
    public string Level { get; set; }
    public string Message { get; set; }
    public string MessageTemplate { get; set; }
    public string Properties { get; set; }
}

5.在CustomProfile中对AuditSqlLog和AuditSqlLogVo进行关系映射

// 日志
CreateMap<AuditSqlLog, AuditSqlLogVo>();
CreateMap<AuditSqlLogVo, AuditSqlLog>();

6.重新配置BaseRepository类，完成分库操作

public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class, new()
{
    private readonly SqlSugarScope _dbBase;
    private readonly IUnitOfWorkManage _unitOfWorkManage;
    public ISqlSugarClient Db => _db;

    private ISqlSugarClient _db
    {
        get
        {
            ISqlSugarClient db = _dbBase;

            //修改使用 model备注字段作为切换数据库条件，使用sqlsugar TenantAttribute存放数据库ConnId
            //参考 https://www.donet5.com/Home/Doc?typeId=2246
            var tenantAttr = typeof(TEntity).GetCustomAttribute<TenantAttribute>();
            if (tenantAttr != null)
            {
                //统一处理 configId 小写
                db = _dbBase.GetConnectionScope(tenantAttr.configId.ToString().ToLower());
                return db;
            }

            return db;
        }
    }


    public BaseRepository(IUnitOfWorkManage unitOfWorkManage)
    {
        _unitOfWorkManage = unitOfWorkManage;
        _dbBase = unitOfWorkManage.GetDbClient();
    }



    public async Task<List<TEntity>> Query()
    {
        await Console.Out.WriteLineAsync(Db.GetHashCode().ToString());

        return await _db.Queryable<TEntity>().ToListAsync();
    }


    public async Task<long> Add(TEntity entity)
    {
        var insert = _db.Insertable(entity);
        return await insert.ExecuteReturnSnowflakeIdAsync();
    }
}

7.在control层使用

![image](https://img2024.cnblogs.com/blog/2814984/202508/2814984-20250820134016386-1607376347.png)

private readonly IBaseService<AuditSqlLog, AuditSqlLogVo> _auditSqlLogService;

public WeatherForecastController(ILogger<WeatherForecastController> logger,
    IMapper mapper,
    IBaseService<Role, RoleVo> baseService,
    IOptions<RedisOptions> redisOptions,
    ICaching caching,
    IBaseService<AuditSqlLog, AuditSqlLogVo> auditSqlLogService)
{
    _logger = logger;
    _mapper = mapper;
    _baseService = baseService;
    _redisOptions = redisOptions;
    _caching = caching;
    _auditSqlLogService = auditSqlLogService;
}

[HttpGet("{id}", Name = "GetWeatherForecastById")] // or [HttpGet("one")]
public async Task<IActionResult> GetOne()
{
    var roleList = await _auditSqlLogService
        .Query();


    return Ok(roleList);
}