HMEFramework.SqlSugar 2.7.0

HMEFramework.SqlSugar

简介

HMEFramework.SqlSugar 是基于SqlSugar（ https://github.com/donet5/SqlSugar ） ORM的增强封装库，提供对MySql、SQLServer、SQLite、Oracle、PostgreSQL、Dameng、MariaDB、Tidb、Odbc With Doris数据库的便捷操作支持。采用分层架构设计（Context → Repository → Service），内置丰富的CRUD操作和高级查询功能。

核心特性

✔ **多数据库支持**  
  ✅ SQL Server 2008+  
  ✅ MySQL 5.7+  
  ✅ Other：SQLite、Oracle、PostgreSQL、Dameng、MariaDB、Tidb、Odbc  

✔ **企业级功能**  
  🔒 分布式事务支持  
  🔄 主从读写分离  
  💯 完善的批量操作  

✔ **开发效率**  
  🚀 全异步API设计  
  📊 内置分页查询组件  
  🧩 开箱即用的仓储模式  

运行环境

• 跨平台支持: Supports .NET Standard 2.1, .NET 6+.

安装

dotnet add package HMEFramework.SqlSugar

配置数据库连接

添加配置到 appsettings.json：

{
  "DataBaseConfig": {
    "ConnectionString": "Server=.;Database=DemoDB;Uid=sa;Pwd=123456;",
    "DbType": "SqlServer", // SqlServer/MySql
    "AutoClose": true,
    //读写分离配置
    "Slaves": [
      {
        "ConnectionString": "Server=slave1;Database=DemoDB;Uid=sa;Pwd=123456;",
        "HitRate": 10
      }
    ]
  }
}

服务注册

builder.Services
    .Configure<DataBaseConfig>(builder.Configuration.GetSection("DataBaseConfig"))
    .AddSqlSugarSetup(builder.Configuration.Get<DataBaseConfig>()!);

// 或使用 Action 配置方式
builder.Services.AddSqlSugarSetup(options =>
{
    options.ConnectionString = Configuration.GetConnectionString("DefaultConnection");
    options.DbType = DbType.SqlServer; // 或 DbType.MySql
    options.AutoClose = true;
    options.DisableNvarchar = false;
    // 其他配置...
});

基础使用

实体定义

[SugarTable("Users")]
public class User 
{
    [SugarColumn(IsPrimaryKey = true)]
    public string Id { get; set; }
    
    public string Name { get; set; }
    
    [SugarColumn(ColumnName = "create_time")]
    public DateTime CreateTime { get; set; }
}

仓储层实现

public class UserRepository : BaseSqlServerRepository<User>
{
    public UserRepository(IDbContext context) : base(context) 
    {
    }

    // 自定义查询方法
    public async Task<List<User>> GetActiveUsersAsync()
    {
        return await Db.Queryable<User>()
            .Where(u => u.CreateTime > DateTime.Now.AddMonths(-1))
            .ToListAsync();
    }
}

服务层实现

public class UserService : BaseSqlServerService<User>
{
    public UserService(ISqlServerCRUD<User> repository) : base(repository) 
    {
    }

    public async Task UpdateUserNameAsync(string userId, string newName)
    {
        await Db.Updateable<User>()
            .SetColumns(u => u.Name == newName)
            .Where(u => u.Id == userId)
            .ExecuteCommandAsync();
    }
}

核心功能示例

1. 分页查询

// 基础分页
var page = await Db.QueryPageAsync<User>(
    where: u => u.Name.Contains("张"),
    pageIndex: 1,
    pageSize: 20,
    orderByFileds: "Id DESC");

// 复杂分页
var page = await Db.QueryPageAsync<User, UserVO>(
    where: u => u.Age > 18,
    select: u => new UserVO { Name = u.Name, Age = u.Age },
    pageIndex: 1,
    pageSize: 20,
    orders: new Dictionary<Expression<Func<User, object>>, string> {
        { u => u.CreateTime, "DESC" },
        { u => u.Name, "ASC" }
    });

2. 事务处理

// 自动事务
var result = await Db.UseTranAsync(async () => 
{
    await userService.UpdateAsync(user);
    await orderService.CreateAsync(newOrder);
});

// 手动事务
try 
{
    Db.Ado.BeginTran();
    
    await userRepo.UpdateAsync(user);
    await logRepo.InsertAsync(log);
    
    Db.Ado.CommitTran();
}
catch 
{
    Db.Ado.RollbackTran();
    throw;
}

3. 批量操作

// 批量插入（10万条数据约2秒）
var data = Generate100KUsers();
await Db.BulkCopyAsync(data);

// 批量更新
await Db.BulkUpdateAsync(updatedUsers);

// 智能合并（存在更新，不存在插入）
await Db.BulkStorageAsync(users);

4. 多租户支持

// 租户过滤器
Db.QueryFilter.Add(new TableFilterItem<User>(
    query => query.Where(u => u.TenantId == currentTenantId)));

UnitOfWork 工作单元模式支持

案例1：基础事务操作

public class OrderService
{
    private readonly IUnitOfWork _uow;
    private readonly IRepository<Order> _orderRepo;
    private readonly IRepository<User> _userRepo;

    public OrderService(
        IUnitOfWork uow,
        IRepository<Order> orderRepo, 
        IRepository<User> userRepo)
    {
        _uow = uow;
        _orderRepo = orderRepo;
        _userRepo = userRepo;
    }

    public async Task CreateOrder(OrderCreateDto dto)
    {
        try
        {
            _uow.BeginTran();
            
            // 扣减用户余额
            await _userRepo.UpdateAsync(
                u => u.Id == dto.UserId,
                u => u.Balance == u.Balance - dto.TotalAmount);
            
            // 创建订单
            var order = new Order { /* 初始化订单 */ };
            await _orderRepo.InsertAsync(order);
            
            _uow.CommitTran();
        }
        catch
        {
            _uow.RollbackTran();
            throw;
        }
    }
}

案例2：仓储层实现

public class Repository<T> : IRepository<T> where T : class, new()
{
    protected readonly IUnitOfWork _uow;

    public Repository(IUnitOfWork uow)
    {
        _uow = uow;
    }

    protected ISqlSugarClient Db => _uow.Db;

    public async Task<int> InsertAsync(T entity)
    {
        return await Db.Insertable(entity).ExecuteCommandAsync();
    }

    // 其他CRUD方法...
}

// 自定义仓储示例
public interface ICustomOrderRepository : IRepository<Order>
{
    Task<List<Order>> GetRecentOrdersAsync(int days);
}

public class CustomOrderRepository : Repository<Order>, ICustomOrderRepository
{
    public CustomOrderRepository(IUnitOfWork uow) : base(uow) { }

    public async Task<List<Order>> GetRecentOrdersAsync(int days)
    {
        return await Db.Queryable<Order>()
            .Where(o => o.CreateTime >= DateTime.Now.AddDays(-days))
            .ToListAsync();
    }
}