RuoVea.ExSugar 10.0.0.5

📋 RuoVea.ExSqlSugar 组件概览

RuoVea.ExSqlSugar 是一个基于 SqlSugar 的 ORM 扩展类库，提供了企业级的数据库访问解决方案，支持多种数据库和丰富的功能特性。

🏗️ 核心架构

1. 两种使用方式

方式一：DbContext 模式

// 1. 定义 DbContext
public class DefultDbContext : DbContext
{
    public DefultDbContext(ConnectionConfig config) : base(config)
    {
    }
}

// 2. 注入服务
builder.Services.AddDbContextSetup(provider => 
    new DefultDbContext(AppSettings.GetSection("DefultDbConnString").Get<ConnectionConfig>()));

// 3. 在服务中使用
public class DemoService : IDemoService
{
    private readonly DefultDbContext _defultDbContext;
    
    public DemoService(DefultDbContext defultDbContext)
    {
        _defultDbContext = defultDbContext;
    }
}

方式二：Repository 模式

// 1. 注册服务
builder.Services.AddSqlSugarSetup();

// 2. 在服务中使用
public class DemoService : IDemoService
{
    private readonly SugarRepository<SysUser> _sysUserRepository;
    
    public DemoService(SugarRepository<SysUser> sysUserRepository)
    {
        _sysUserRepository = sysUserRepository;
    }
}

🔧 核心配置

1. 数据库连接配置

{
  "ConnectionConfigs": [
    {
      "ConfigId": "1300000000001",
      "DbType": "Sqlite",
      "ConnectionString": "DataSource=./ruovea.dll",
      "EnableUnderLine": false,
      "EnableDiffLog": false,
      "IsEncrypt": false,
      "DbSecurity": "",
      "IsDeleteFilter": true,
      "IsUserIdFilter": false,
      "IsTenantIdFilter": false,
      "CommandTimeOut": 30
    }
  ],
  "DataAuditing": {
    "CreateTime": "CreateTime",
    "ModifyTime": "ModifyTime", 
    "Creator": "Creator",
    "Modifier": "Modifier",
    "TenantId": "TenantId",
    "IsDelete": "IsDelete"
  },
  "SqlSugarConst": {
    "MainConfigId": 1300000000001, // 默认主数据库标识（默认租户）
    "LogConfigId": 1300000000002, // 默认日志数据库标识
    "PrimaryKey": "Id", //默认表主键
    "DefaultTenantId": 1300000000001 //默认租户
  }
}

2. 多数据库支持

支持以下数据库类型：

• Sqlite、MySQL、SqlServer、Oracle
• PostgreSQL、Dm（达梦）、Kdbndp（人大金仓）
• OpenGauss、ClickHouse、Access
• ODBC、Custom（自定义）

🚀 完整使用示例

1. 实体类定义

// 基础实体
[SugarTable("sys_user")]
public class SysUser : EntityBase
{
    [SugarColumn(ColumnName = "user_name", Length = 50)]
    public string UserName { get; set; }
    
    [SugarColumn(ColumnName = "email", Length = 100)]
    public string Email { get; set; }
    
    [SugarColumn(ColumnName = "phone", Length = 20)]
    public string Phone { get; set; }
    
    [SugarColumn(ColumnName = "status")]
    public UserStatus Status { get; set; }
}

// 租户实体
[SugarTable("sys_tenant")]
public class SysTenant : EntityTenant
{
    [SugarColumn(ColumnName = "tenant_name", Length = 100)]
    public string TenantName { get; set; }
    
    [SugarColumn(ColumnName = "contact_person", Length = 50)]
    public string ContactPerson { get; set; }
    
    [SugarColumn(ColumnName = "contact_phone", Length = 20)]
    public string ContactPhone { get; set; }
}

// 业务实体
[SugarTable("biz_order")]
public class BizOrder : EntityBase
{
    [SugarColumn(ColumnName = "order_no", Length = 50)]
    public string OrderNo { get; set; }
    
    [SugarColumn(ColumnName = "customer_id")]
    public long CustomerId { get; set; }
    
    [SugarColumn(ColumnName = "total_amount", DecimalDigits = 2)]
    public decimal TotalAmount { get; set; }
    
    [SugarColumn(ColumnName = "order_status")]
    public OrderStatus OrderStatus { get; set; }
    
    [SugarColumn(ColumnName = "order_time")]
    public DateTime OrderTime { get; set; }
}

public enum UserStatus
{
    Active = 1,
    Inactive = 0,
    Locked = 2
}

public enum OrderStatus
{
    Pending = 0,
    Confirmed = 1,
    Shipped = 2,
    Completed = 3,
    Cancelled = 4
}

2. Repository 模式使用

public class UserService
{
    private readonly SugarRepository<SysUser> _userRepository;
    private readonly SugarRepository<SysTenant> _tenantRepository;
    
    public UserService(
        SugarRepository<SysUser> userRepository,
        SugarRepository<SysTenant> tenantRepository)
    {
        _userRepository = userRepository;
        _tenantRepository = tenantRepository;
    }
    
    // 查询用户列表（分页）
    public async Task<PageResult<UserDto>> GetUserListAsync(UserQueryRequest request)
    {
        var query = _userRepository.AsQueryable()
            .WhereIF(!string.IsNullOrEmpty(request.UserName), u => u.UserName.Contains(request.UserName))
            .WhereIF(!string.IsNullOrEmpty(request.Email), u => u.Email.Contains(request.Email))
            .WhereIF(request.Status.HasValue, u => u.Status == request.Status.Value);
            
        return await query.ToPageAsync<SysUser, UserDto>(request.PageIndex, request.PageSize);
    }
    
    // 获取用户详情
    public async Task<UserDetailDto> GetUserDetailAsync(long userId)
    {
        var user = await _userRepository.GetByIdAsync(userId);
        if (user == null)
            throw new Exception("用户不存在");
            
        return user.Adapt<UserDetailDto>();
    }
    
    // 创建用户
    public async Task<long> CreateUserAsync(CreateUserRequest request)
    {
        // 检查用户名是否已存在
        if (await _userRepository.IsAnyAsync(u => u.UserName == request.UserName))
            throw new Exception("用户名已存在");
            
        var user = request.Adapt<SysUser>();
        user.CreateTime = DateTime.Now;
        user.IsDelete = IsDelete.NotDeleted;
        
        return await _userRepository.InsertReturnIdentityAsync(user);
    }
    
    // 更新用户
    public async Task<bool> UpdateUserAsync(UpdateUserRequest request)
    {
        var user = await _userRepository.GetByIdAsync(request.Id);
        if (user == null)
            throw new Exception("用户不存在");
            
        request.Adapt(user);
        user.ModifyTime = DateTime.Now;
        
        return await _userRepository.UpdateAsync(user) > 0;
    }
    
    // 删除用户（逻辑删除）
    public async Task<bool> DeleteUserAsync(long userId)
    {
        var user = await _userRepository.GetByIdAsync(userId);
        if (user == null)
            throw new Exception("用户不存在");
            
        return await _userRepository.FakeDeleteAsync(user) > 0;
    }
    
    // 批量更新用户状态
    public async Task<bool> BatchUpdateUserStatusAsync(List<long> userIds, UserStatus status)
    {
        return await _userRepository.UpdateAsync(
            u => userIds.Contains(u.Id), 
            u => new SysUser { Status = status, ModifyTime = DateTime.Now }) > 0;
    }
}

public class UserQueryRequest : PageParam
{
    public string UserName { get; set; }
    public string Email { get; set; }
    public UserStatus? Status { get; set; }
}

public class CreateUserRequest
{
    public string UserName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public UserStatus Status { get; set; }
}

public class UpdateUserRequest
{
    public long Id { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public UserStatus Status { get; set; }
}

3. DbContext 模式使用

public class OrderService
{
    private readonly DefultDbContext _dbContext;
    
    public OrderService(DefultDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    
    // 复杂查询：联表查询
    public async Task<List<OrderDetailDto>> GetOrderDetailsAsync(long customerId)
    {
        return await _dbContext.Queryable<BizOrder>()
            .LeftJoin<SysUser>((o, u) => o.CustomerId == u.Id)
            .Where((o, u) => o.CustomerId == customerId && u.Status == UserStatus.Active)
            .Select((o, u) => new OrderDetailDto
            {
                OrderId = o.Id,
                OrderNo = o.OrderNo,
                CustomerName = u.UserName,
                TotalAmount = o.TotalAmount,
                OrderStatus = o.OrderStatus,
                OrderTime = o.OrderTime,
                CreateTime = o.CreateTime
            })
            .ToListAsync();
    }
    
    // 事务操作
    public async Task<bool> CreateOrderWithTransactionAsync(CreateOrderRequest request)
    {
        try
        {
            // 开启事务
            _dbContext.Ado.BeginTran();
            
            // 创建订单
            var order = request.Adapt<BizOrder>();
            order.OrderNo = GenerateOrderNo();
            order.OrderTime = DateTime.Now;
            order.CreateTime = DateTime.Now;
            order.IsDelete = IsDelete.NotDeleted;
            
            var orderId = await _dbContext.Insertable(order).ExecuteReturnIdentityAsync();
            
            // 创建订单明细
            var orderItems = request.Items.Select(item => new BizOrderItem
            {
                OrderId = orderId,
                ProductId = item.ProductId,
                Quantity = item.Quantity,
                UnitPrice = item.UnitPrice,
                CreateTime = DateTime.Now,
                IsDelete = IsDelete.NotDeleted
            }).ToList();
            
            await _dbContext.Insertable(orderItems).ExecuteCommandAsync();
            
            // 更新库存
            foreach (var item in request.Items)
            {
                await _dbContext.Updateable<BizProduct>()
                    .SetColumns(p => p.StockQuantity == p.StockQuantity - item.Quantity)
                    .Where(p => p.Id == item.ProductId && p.StockQuantity >= item.Quantity)
                    .ExecuteCommandAsync();
            }
            
            // 提交事务
            _dbContext.Ado.CommitTran();
            return true;
        }
        catch (Exception ex)
        {
            // 回滚事务
            _dbContext.Ado.RollbackTran();
            throw new Exception("创建订单失败", ex);
        }
    }
    
    // 存储过程调用
    public async Task<List<SalesReportDto>> GetSalesReportAsync(DateTime startDate, DateTime endDate)
    {
        var parameters = new[]
        {
            new SugarParameter("@StartDate", startDate),
            new SugarParameter("@EndDate", endDate),
            new SugarParameter("@TotalCount", 0, System.Data.DbType.Int32, true)
        };
        
        return await _dbContext.Ado.SqlQueryAsync<SalesReportDto>(
            "EXEC sp_GetSalesReport @StartDate, @EndDate, @TotalCount OUT", parameters);
    }
    
    private string GenerateOrderNo()
    {
        return $"ORD{DateTime.Now:yyyyMMddHHmmss}{new Random().Next(1000, 9999)}";
    }
}

4. 高级查询示例

public class AdvancedQueryService
{
    private readonly SugarRepository<BizOrder> _orderRepository;
    private readonly SugarRepository<SysUser> _userRepository;
    
    public AdvancedQueryService(
        SugarRepository<BizOrder> orderRepository,
        SugarRepository<SysUser> userRepository)
    {
        _orderRepository = orderRepository;
        _userRepository = userRepository;
    }
    
    // 动态条件查询
    public async Task<List<BizOrder>> SearchOrdersAsync(OrderSearchCriteria criteria)
    {
        var query = _orderRepository.AsQueryable()
            .WhereIF(criteria.StartTime.HasValue, o => o.OrderTime >= criteria.StartTime.Value)
            .WhereIF(criteria.EndTime.HasValue, o => o.OrderTime <= criteria.EndTime.Value)
            .WhereIF(criteria.MinAmount.HasValue, o => o.TotalAmount >= criteria.MinAmount.Value)
            .WhereIF(criteria.MaxAmount.HasValue, o => o.TotalAmount <= criteria.MaxAmount.Value)
            .WhereIF(criteria.Statuses?.Any() == true, o => criteria.Statuses.Contains(o.OrderStatus))
            .WhereIF(!string.IsNullOrEmpty(criteria.Keyword), o => 
                o.OrderNo.Contains(criteria.Keyword));
                
        if (!string.IsNullOrEmpty(criteria.SortField))
        {
            query = criteria.SortOrder?.ToLower() == "desc" 
                ? query.OrderBy($"{criteria.SortField} DESC")
                : query.OrderBy($"{criteria.SortField} ASC");
        }
        else
        {
            query = query.OrderBy(o => o.OrderTime, OrderByType.Desc);
        }
        
        return await query.ToListAsync();
    }
    
    // 分组统计
    public async Task<List<OrderStatisticDto>> GetOrderStatisticsAsync(DateTime startDate, DateTime endDate)
    {
        return await _orderRepository.AsQueryable()
            .Where(o => o.OrderTime >= startDate && o.OrderTime <= endDate)
            .GroupBy(o => o.OrderStatus)
            .Select(g => new OrderStatisticDto
            {
                OrderStatus = g.Key,
                OrderCount = g.Count(),
                TotalAmount = g.Sum(o => o.TotalAmount),
                AvgAmount = g.Avg(o => o.TotalAmount)
            })
            .ToListAsync();
    }
    
    // 子查询
    public async Task<List<SysUser>> GetUsersWithOrdersAsync()
    {
        return await _userRepository.AsQueryable()
            .Where(u => SqlFunc.Subqueryable<BizOrder>()
                .Where(o => o.CustomerId == u.Id && o.OrderTime >= DateTime.Now.AddMonths(-1))
                .Count() > 0)
            .ToListAsync();
    }
    
    // 分页查询 with DTO 映射
    public async Task<PageResult<OrderListDto>> GetOrderListPagedAsync(OrderListQuery query)
    {
        var result = await _orderRepository.AsQueryable()
            .LeftJoin<SysUser>((o, u) => o.CustomerId == u.Id)
            .WhereIF(!string.IsNullOrEmpty(query.OrderNo), (o, u) => o.OrderNo.Contains(query.OrderNo))
            .WhereIF(query.CustomerId.HasValue, (o, u) => o.CustomerId == query.CustomerId.Value)
            .WhereIF(query.Status.HasValue, (o, u) => o.OrderStatus == query.Status.Value)
            .OrderBuilder(query, "OrderTime", true)
            .Select((o, u) => new OrderListDto
            {
                Id = o.Id,
                OrderNo = o.OrderNo,
                CustomerName = u.UserName,
                TotalAmount = o.TotalAmount,
                OrderStatus = o.OrderStatus,
                OrderTime = o.OrderTime,
                CreateTime = o.CreateTime
            })
            .ToPageAsync(query.PageIndex, query.PageSize);
            
        return result;
    }
}

public class OrderSearchCriteria
{
    public DateTime? StartTime { get; set; }
    public DateTime? EndTime { get; set; }
    public decimal? MinAmount { get; set; }
    public decimal? MaxAmount { get; set; }
    public List<OrderStatus> Statuses { get; set; }
    public string Keyword { get; set; }
    public string SortField { get; set; }
    public string SortOrder { get; set; }
}

public class OrderListQuery : PageParam
{
    public string OrderNo { get; set; }
    public long? CustomerId { get; set; }
    public OrderStatus? Status { get; set; }
}

5. 单元工作模式

[ApiController]
[Route("api/[controller]")]
[UnitOfWork] // 启用事务单元
public class OrderController : ControllerBase
{
    private readonly OrderService _orderService;
    private readonly UserService _userService;
    
    public OrderController(OrderService orderService, UserService userService)
    {
        _orderService = orderService;
        _userService = userService;
    }
    
    [HttpPost("create")]
    public async Task<IActionResult> CreateOrder([FromBody] CreateOrderRequest request)
    {
        // 这个方法中的所有数据库操作将在同一个事务中执行
        var result = await _orderService.CreateOrderWithTransactionAsync(request);
        
        if (result)
        {
            // 更新用户最后下单时间
            await _userService.UpdateUserLastOrderTimeAsync(request.CustomerId);
            
            return Ok(new { Success = true, Message = "订单创建成功" });
        }
        
        return BadRequest(new { Success = false, Message = "订单创建失败" });
    }
}

6. 种子数据初始化

public class SysUserSeedData : SeedDataEntity<SysUser>
{
    public override IList<SysUser> HasData()
    {
        return new List<SysUser>
        {
            new SysUser
            {
                Id = 1,
                UserName = "admin",
                Email = "admin@example.com",
                Phone = "13800138000",
                Status = UserStatus.Active,
                CreateTime = DateTime.Now,
                IsDelete = IsDelete.NotDeleted
            },
            new SysUser
            {
                Id = 2,
                UserName = "user01",
                Email = "user01@example.com",
                Phone = "13800138001",
                Status = UserStatus.Active,
                CreateTime = DateTime.Now,
                IsDelete = IsDelete.NotDeleted
            }
        };
    }
}

// 在启动时初始化种子数据
public class DataSeeder
{
    private readonly ISqlSugarClient _db;
    
    public DataSeeder(ISqlSugarClient db)
    {
        _db = db;
    }
    
    public async Task SeedAsync()
    {
        // 初始化用户数据
        var userSeedData = new SysUserSeedData();
        var users = userSeedData.HasData();
        
        if (!await _db.Queryable<SysUser>().AnyAsync())
        {
            await _db.Insertable(users).ExecuteCommandAsync();
        }
        
        // 初始化其他数据...
    }
}

7. 自定义过滤器

// 多租户数据过滤器
public class TenantDataFilter : ICustormerEntityFilter
{
    private readonly ICurrentUser _currentUser;
    
    public TenantDataFilter(ICurrentUser currentUser)
    {
        _currentUser = currentUser;
    }
    
    public void ApplyFilter(SqlSugarScopeProvider db)
    {
        // 自动过滤租户数据
        if (_currentUser.TenantId.HasValue)
        {
            db.QueryFilter.Add(new TableFilterItem<SysUser>(it => it.TenantId == _currentUser.TenantId.Value));
            db.QueryFilter.Add(new TableFilterItem<BizOrder>(it => it.TenantId == _currentUser.TenantId.Value));
            // 添加其他需要租户过滤的实体...
        }
    }
}

// 在配置中启用过滤器
public void ConfigureServices(IServiceCollection services)
{
    services.AddSqlSugarSetup(options =>
    {
        options.IsTenantIdFilter = true; // 启用租户过滤器
        options.IsDeleteFilter = true;   // 启用逻辑删除过滤器
        options.IsUserIdFilter = true;   // 启用用户数据过滤器
    });
}

8. SQL 日志和监控

public class SqlMonitorService
{
    private readonly IRestFulLog _restFulLog;
    
    public SqlMonitorService(IRestFulLog restFulLog)
    {
        _restFulLog = restFulLog;
    }
    
    // 记录 SQL 执行日志
    public void LogSqlExecution(string sql, SugarParameter[] parameters, long elapsedMilliseconds)
    {
        var formattedSql = SqlParameterFormatHelper.ParameterFormat(sql, parameters);
        
        _restFulLog.OperationLog(new OperationVo
        {
            RequestUrl = "SQL Execution",
            RequestMethod = "SQL",
            ExecutionTime = elapsedMilliseconds,
            Parameters = formattedSql,
            RequestTime = DateTime.Now
        });
        
        // 慢 SQL 告警
        if (elapsedMilliseconds > 1000) // 1秒以上认为是慢SQL
        {
            _restFulLog.ExceptionLog(new ExceptionVo
            {
                ExceptionMessage = $"慢SQL检测: {elapsedMilliseconds}ms",
                RequestUrl = "SQL Execution",
                OccurredTime = DateTime.Now
            });
        }
    }
}

🎯 核心特性

1. 自动审计字段

• 创建时间、修改时间自动填充
• 创建人、修改人自动记录
• 逻辑删除自动过滤

2. 内置过滤器

• 多租户数据隔离
• 用户数据权限控制
• 逻辑删除数据过滤

3. 事务管理

• 单元工作模式
• 手动事务控制
• 分布式事务支持

4. 性能优化

• 分页查询优化
• 批量操作支持
• SQL 执行监控

5. 开发友好

• 强类型查询
• Lambda 表达式支持
• 丰富的扩展方法

这个 ORM 组件为企业级应用提供了完整、高效、安全的数据库访问解决方案，大幅提升了开发效率和系统稳定性。