.NET SqlSugar 仓储、工作单元、服务层
<div><div dir="ltr">
<div>
<h1>1. 安装 NuGet</h1>
<div> </div>
<div>
<div dir="ltr">
<pre><code>SqlSugarCore</code></pre>
</div>
</div>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>2. SqlSugar 数据库上下文</h1>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>using SqlSugar;
namespace Demo;
public class SqlSugarContext
{
public ISqlSugarClient Db { get; }
public SqlSugarContext()
{
Db = new SqlSugarClient(new ConnectionConfig
{
ConnectionString = "Data Source=.;Database=DemoDb;UID=sa;PWD=123;",
DbType = DbType.SqlServer,
IsAutoCloseConnection = true
});
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>3. 实体</h1>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public int Stock { get; set; }
}
public class Order
{
public int Id { get; set; }
public int ProductId { get; set; }
public decimal Price { get; set; }
}
public class OrderDto
{
public int OrderId { get; set; }
public string ProductName { get; set; }
public int Stock { get; set; }
public decimal Price { get; set; }
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>4. 仓储</h1>
<div> </div>
<h2>接口</h2>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>public interface IRepository<T> where T : class, new()
{
// 查询
Task<T> GetByIdAsync(int id);
Task<List<T>> GetListAsync();
// ==========================================
// 1. 事务模式：AddQueue → 入队，不执行SQL
// ==========================================
void Insert(T entity);
void Update(T entity);
void Delete(T entity);
// ==========================================
// 2. 立即模式：ExecuteCommand → 直接执行SQL
// ==========================================
Task InsertImmediateAsync(T entity);
Task UpdateImmediateAsync(T entity);
Task DeleteImmediateAsync(T entity);
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<h2>实现</h2>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>using SqlSugar;
namespace Demo;
public class Repository<T> : IRepository<T> where T : class, new()
{
private readonly ISqlSugarClient _db;
public Repository(ISqlSugarClient db)
{
_db = db;
}
// ======================
// 查询（直接执行）
// ======================
public async Task<T> GetByIdAsync(int id)
=> await _db.Queryable<T>().InSingleAsync(id);
public async Task<List<T>> GetListAsync()
=> await _db.Queryable<T>().ToListAsync();
// ======================
// 事务模式：AddQueue
// 只入队，不执行SQL
// 必须等 SaveQueuesAsync 才执行
// ======================
public void Insert(T entity)
=> _db.Insertable(entity).AddQueue();
public void Update(T entity)
=> _db.Updateable(entity).AddQueue();
public void Delete(T entity)
=> _db.Deleteable(entity).AddQueue();
// ======================
// 立即模式：ExecuteCommand
// 直接执行SQL，不走队列
// 不需要 SaveChanges，无事务
// ======================
public async Task InsertImmediateAsync(T entity)
=> await _db.Insertable(entity).ExecuteCommandAsync();
public async Task UpdateImmediateAsync(T entity)
=> await _db.Updateable(entity).ExecuteCommandAsync();
public async Task DeleteImmediateAsync(T entity)
=> await _db.Deleteable(entity).ExecuteCommandAsync();
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>5. 工作单元 UnitOfWork</h1>
<div> </div>
<div>管理所有仓储 + 统一提交队列事务</div>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>namespace Demo;
public interface IUnitOfWork
{
IRepository<Product> Products { get; }
IRepository<Order> Orders { get; }
Task SaveChangesAsync();
}
public class UnitOfWork : IUnitOfWork
{
private readonly ISqlSugarClient _db;
public UnitOfWork(SqlSugarContext context)
{
_db = context.Db;
}
// 懒加载仓储
private IRepository<Product>? _productRepo;
public IRepository<Product> Products
=> _productRepo ??= new Repository<Product>(_db);
private IRepository<Order>? _orderRepo;
public IRepository<Order> Orders
=> _orderRepo ??= new Repository<Order>(_db);
// ======================
// 提交所有 AddQueue 操作
// 自动开启一个事务
// 全部成功/全部回滚
// ======================
public async Task SaveChangesAsync()
{
await _db.SaveQueuesAsync();
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>6. 服务层</h1>
<div> </div>
<h2>规则</h2>
<div> </div>
<div>1）单表 / 多表增删改
<div> </div>
→ 走 Repository + UnitOfWork
<div> </div>
→ 用 AddQueue
<div> </div>
→ SaveChangesAsync 统一事务，原子性</div>
<div> </div>
<div>2）多表联查 / 复杂查询 / 分页
<div> </div>
→ 直接用 ISqlSugarClient
<div> </div>
→ 不进仓储，不进 Uow</div>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>using SqlSugar;
namespace Demo;
public class OrderService
{
private readonly IUnitOfWork _uow;// 增删改 + 事务
private readonly ISqlSugarClient_db; // 查询/联表
public OrderService(IUnitOfWork uow, SqlSugarContext context)
{
_uow = uow;
_db= context.Db;
}
// ==================================================
// 1）增删改（单表/多表）→ 走 Uow + 事务 + 原子性
// ==================================================
public async Task CreateOrder(Order order)
{
// 1. 订单入队
_uow.Orders.Insert(order);
// 2. 扣库存入队
var product = await _uow.Products.GetByIdAsync(order.ProductId);
product.Stock--;
_uow.Products.Update(product);
// 3. 统一提交：开启一个事务，执行所有队列
await _uow.SaveChangesAsync();
}
// ==================================================
// 单表立即执行：不使用事务，直接插入
// ==================================================
public async Task AddProductImmediate(Product product)
{
// 直接执行SQL，无事务，不需要 SaveChanges
await _uow.Products.InsertImmediateAsync(product);
}
// ==================================================
// 2）多表联查 / 复杂查询 / 分页
// 直接用 _db，不走仓储，不走 Uow
// ==================================================
public async Task<List<OrderDto>> GetOrderList()
{
return await _db.Queryable<Order>()
.LeftJoin<Product>((o, p) => o.ProductId == p.Id)
.Select((o, p) => new OrderDto
{
OrderId = o.Id,
ProductName = p.Name,
Stock = p.Stock,
Price = o.Price
})
.ToListAsync();
}
public async Task<List<OrderDto>> GetPageList(int pageIndex, int pageSize)
{
return await _db.Queryable<Order>()
.LeftJoin<Product>((o, p) => o.ProductId == p.Id)
.Select((o, p) => new OrderDto
{
OrderId = o.Id,
ProductName = p.Name
})
.ToPageListAsync(pageIndex, pageSize);
}
}
</code></pre>
</div>
<div> </div>
</div>
</div>
<div> </div>
<hr>
<div> </div>
<h1>7. Program.cs 注册</h1>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSingleton<SqlSugarContext>();
builder.Services.AddScoped<IUnitOfWork, UnitOfWork>();
builder.Services.AddScoped<OrderService>();
var app = builder.Build();
app.Run();
</code></pre>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>8. 知识点总结</h1>
<div> </div>
<h2>① <code>AddQueue()</code> 是什么？</h2>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>_db.Insertable(entity).AddQueueAsync()
</code></pre>
</div>
<div> </div>
</div>
</div>
<ul>
<li>不执行 SQL</li>
<li>把操作加入事务队列</li>
<li>必须调用 <code>SaveQueuesAsync()</code> 才会真正执行</li>
<li>多个操作共用一个事务，保证原子性</li>
</ul>
<div> </div>
<h2>② <code>ExecuteCommandAsync()</code> 是什么？</h2>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>_db.Insertable(entity).ExecuteCommandAsync()
</code></pre>
</div>
<div> </div>
</div>
</div>
<ul>
<li>立即执行 SQL</li>
<li>不入队</li>
<li>不需要 <code>SaveChanges</code></li>
<li>无事务，执行完就落库</li>
</ul>
<div> </div>
<h2>③ 三层用法规则</h2>
<div> </div>
<h3>1）单表 / 多表增删改</h3>
<div> </div>
<div>→ 走 <code>Repository + UnitOfWork</code>
<div> </div>
→ 使用 <code>Add/Update/Delete</code>（AddQueue）
<div> </div>
→ 最后 <code>SaveChangesAsync</code>
<div> </div>
→ 一个事务，原子性，要么全成功要么全失败</div>
<div> </div>
<h3>2）多表联查 / 复杂查询 / 报表 / 分页</h3>
<div> </div>
<div>→ 直接使用 <code>ISqlSugarClient</code>
<div> </div>
→ 不进仓储，不进 UnitOfWork
<div> </div>
→ 自由 <code>Join / Where / OrderBy / Select / ToPageList</code></div>
<div> </div>
<h3>3）Service 结构</h3>
<div> </div>
<div>
<div dir="ltr">
<div>
<pre><code>Service
├─ 写操作（增删改）
│ → _uow.仓储.Add/Update/Delete
│ → _uow.SaveChangesAsync()
└─ 读操作（查询/联表/分页）
→ _db.Queryable<T>().Join<T2>().Select(...)
</code></pre>
</div>
<div> </div>
</div>
</div>
<hr>
<div> </div>
<h1>9. 总结</h1>
<div> </div>
<ul>
<li>AddQueue：先排队，最后统一事务执行</li>
<li>ExecuteCommand：立即执行，不排队，无事务</li>
<li>增删改走 Uow，查询直接用 SqlSugar</li>
<li>仓储只管单表，Uow 管事务，Service 管业务</li>
</ul><br><br>
来源：https://www.cnblogs.com/chuansheng/p/19915717
頁:
[1]