C# ORM模式之 SqlSugar使用 |
您所在的位置：网站首页 › 初代本田飞度海报 › C# ORM模式之 SqlSugar使用 |
C# ORM模式之 SqlSugar使用
|
一、SqlSugar介绍及分析 SqlSugar是一款 老牌 .NET 开源ORM框架，连接DB特别方便 支持数据库：MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、人大金仓 官方文档：http://www.donet5.com/Home/Doc SqlSugar的优点： 1、高性能：不夸张的说，去掉Sql在数据库执行的时间，SqlSugar是EF数倍性能，另外在批量操作和一对多查询上也有不错的SQL优化； 2、高扩展性 ：支持自定义拉姆达函数解析、扩展数据类型、支持自定义实体特性，外部缓存等； 3、稳定性和技术支持： 虽然不是官方ORM, 但在稳定性上也是有着数年用户积累，如果遇到问题可以在GITHUB提出来，会根据紧急度定期解决； 4、功能全面：虽然SqlSugar小巧可功能并不逊色于EF框架 5、创新、持续更新 ，向下兼容 二、SqlSugar项目中的使用 1、包的引用：
2、全局引用：
3、接口中常用方法封装 1）、ISqlSugarRepository接口封装 public interface ISqlSugarRepository : IBaseRepository where TEntity : class { /// /// /// ISqlSugarClient Db { get; } /// /// 执行查询SQL语句 /// 只支持查询操作，并且支持拉姆达分页 /// /// /// Task ExecuteSql(string sql); /// /// 通过Ado方法执行SQL语句 /// 支持任何SQL语句 /// /// /// /// Task ExecuteAllSql(string sql, object whereObj = null); /// /// 插入实体 /// /// /// Task Add(TEntity model); /// /// 批量插入实体 /// /// /// Task Add(List listEntity); /// /// 根据实体删除数据 /// /// /// Task Delete(TEntity model); /// /// 根据实体集合批量删除数据 /// /// /// Task Delete(List models); /// /// 根据ID删除数据 /// /// /// Task DeleteById(object id); /// /// 根据IDs批量删除数据 /// /// /// Task DeleteByIds(List ids); /// /// 更新实体 /// /// /// Task Update(TEntity model); /// /// 批量更新实体 /// /// /// Task Update(List listEntity); /// /// 根据ID查询一条数据 /// /// /// Task GetById(object objId); /// /// 根据条件查询数据是否存在 /// /// /// Task GetAnyByFilter(Expression whereExpression); /// /// 根据IDs查询数据 /// /// /// Task GetByIds(List lstIds); /// /// 根据条件查询一条数据 /// /// /// Task GetSingleByFilter(Expression whereExpression); /// /// 查询所有数据 /// /// Task Get(); /// /// 查询数据列表 /// /// 条件表达式 /// 数据列表 Task Get(Expression whereExpression); /// /// 查询数据列表 /// /// 条件表达式 /// 排序表达式 /// 是否升序排序 /// Task Get(Expression whereExpression, Expression orderByExpression = null, bool isAsc = true); /// /// 分页查询 /// /// /// /// /// /// /// Task Get(Expression selector, Expression whereExpression, int intPageIndex, int intPageSize, Expression orderDescSelector = null); /// /// 分页查询 /// /// /// /// /// /// /// Task Get(Expression whereExpression, int intPageIndex, int intPageSize, Expression orderDescSelector = null); } public interface IBaseRepository { }事务接口： public interface IUnitOfWork { /// /// /// /// SqlSugarClient GetDbClient(); /// /// /// void BeginTran(); /// /// /// void CommitTran(); /// /// /// void RollbackTran(); }2）、SqlSugarRepository接口实现 /// /// /// /// public class SqlSugarRepository : ISqlSugarRepository where TEntity : class, new() { private readonly SqlSugarClient _dbBase; public ISqlSugarClient Db { get { return _dbBase; } } /// /// /// /// public SqlSugarRepository(IUnitOfWork unitOfWork) { _dbBase = unitOfWork.GetDbClient(); } /// /// 执行查询SQL语句 /// 只支持查询操作，并且支持拉姆达分页 /// /// /// public async Task ExecuteSql(string sql) { return await Db.SqlQueryable(sql).ToListAsync(); } /// /// 通过Ado方法执行SQL语句 /// 支持任何SQL语句 /// /// /// 参数 /// public async Task ExecuteAllSql(string sql, object whereObj = null) { return await Task.Run(() => Db.Ado.SqlQuery(sql, whereObj)); } /// /// 根据ID查询一条数据 /// /// /// public async Task GetById(object objId) { return await Db.Queryable().In(objId).SingleAsync(); } /// /// 根据IDs查询数据 /// /// id列表 /// 数据实体列表 public async Task GetByIds(List lstIds) { return await Db.Queryable().In(lstIds).ToListAsync(); } /// /// 插入实体 /// /// 实体类 /// public async Task Add(TEntity entity) { var insert = Db.Insertable(entity); return await insert.ExecuteCommandAsync(); } /// /// 批量插入实体 /// /// 实体集合 /// 影响行数 public async Task Add(List listEntity) { return await Db.Insertable(listEntity.ToArray()).ExecuteCommandAsync(); } /// /// 更新实体 /// /// 实体类 /// public async Task Update(TEntity entity) { return await Db.Updateable(entity).ExecuteCommandHasChangeAsync(); } /// /// 批量更新实体 /// /// 实体类 /// public async Task Update(List listEntity) { return await Db.Updateable(listEntity).ExecuteCommandAsync(); } /// /// 根据实体删除数据 /// /// 实体 /// public async Task Delete(TEntity entity) { return await Db.Deleteable(entity).ExecuteCommandHasChangeAsync(); } /// /// 根据实体集合批量删除数据 /// /// /// public async Task Delete(List models) { return await Db.Deleteable(models).ExecuteCommandHasChangeAsync(); } /// /// 根据ID删除数据 /// /// ID /// public async Task DeleteById(object id) { return await Db.Deleteable(id).ExecuteCommandHasChangeAsync(); } /// /// 根据IDs批量删除数据 /// /// ID集合 /// public async Task DeleteByIds(List ids) { return await Db.Deleteable().In(ids).ExecuteCommandHasChangeAsync(); } /// /// 根据条件查询数据是否存在 /// /// /// public async Task GetAnyByFilter(Expression whereExpression) { return await Db.Queryable().AnyAsync(whereExpression); } /// /// 根据条件查询一条数据 /// /// /// public async Task GetSingleByFilter(Expression whereExpression) { return await Db.Queryable().FirstAsync(whereExpression); } /// /// 查询所有数据 /// /// public async Task Get() { return await Db.Queryable().ToListAsync(); } /// /// 查询数据列表----按条件表达式 /// /// 条件表达式 /// 数据列表 public async Task Get(Expression whereExpression) { return await Db.Queryable().WhereIF(whereExpression != null, whereExpression).ToListAsync(); } /// /// 查询数据列表----按条件表达式、排序表达式 /// /// 条件表达式 /// 排序表达式 /// 是否升序排序 /// public async Task Get(Expression whereExpression, Expression orderByExpression = null, bool isAsc = true) { return await Db.Queryable().OrderByIF(orderByExpression != null, orderByExpression, isAsc ? OrderByType.Asc : OrderByType.Desc).WhereIF(whereExpression != null, whereExpression).ToListAsync(); } /// /// 分页查询 /// /// /// 条件表达式 /// 页码 /// 页大小 /// 排序字段 /// public async Task Get(Expression selector, Expression whereExpression, int intPageIndex, int intPageSize, Expression orderDescSelector = null) { var query = Db.Queryable().WhereIF(whereExpression != null, whereExpression); query = query.OrderByIF(orderDescSelector != null, orderDescSelector); var totalCount = 0; var results = query.Select(selector).ToPageList(intPageIndex, intPageSize, ref totalCount).ToList(); var basePage = new PaginatedViewModel(intPageIndex, intPageSize, totalCount, results); return await Task.FromResult(basePage); } /// /// 分页查询 /// /// /// 条件表达式 /// 页码 /// 页大小 /// 排序字段 /// public async Task Get(Expression whereExpression, int intPageIndex, int intPageSize, Expression orderDescSelector = null) { var query = Db.Queryable().WhereIF(whereExpression != null, whereExpression); query = query.OrderByIF(orderDescSelector != null, orderDescSelector); var totalCount = 0; var results = query.ToPageList(intPageIndex, intPageSize, ref totalCount).ToList(); var basePage = new PaginatedViewModel(intPageIndex, intPageSize, totalCount, results); return await Task.FromResult(basePage); } }事务接口实现： public class UnitOfWork : IUnitOfWork, IDisposable { private readonly ISqlSugarClient _sqlSugarClient; private bool _disposed = false; public UnitOfWork(ISqlSugarClient sqlSugarClient) { _sqlSugarClient = sqlSugarClient; } public SqlSugarClient GetDbClient() { return (SqlSugarClient)_sqlSugarClient; } public void BeginTran() { GetDbClient().BeginTran(); } public void CommitTran() { try { GetDbClient().CommitTran(); Dispose(); } catch { GetDbClient().RollbackTran(); Dispose(); } } public void RollbackTran() { GetDbClient().RollbackTran(); Dispose(); } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } public void Dispose(bool disposing) { if (_disposed) return; if (disposing) { GetDbClient()?.Dispose(); } _disposed = true; } ~UnitOfWork() => Dispose(false); }分页查询模型： public class PaginatedViewModel { public int PageIndex { get; private set; } public int PageSize { get; private set; } public long Count { get; private set; } public IEnumerable Data { get; private set; } public PaginatedViewModel(int pageIndex, int pageSize, long count, IEnumerable data) { PageIndex = pageIndex; PageSize = pageSize; Count = count; Data = data; } }3、SqlSugar在项目中的使用 1）、定义及初始化 private readonly ISqlSugarRepository _statEquipmentInfoRepository; public GetStatusInfoHandler(ISqlSugarRepository statEquipmentInfoRepository) { _statEquipmentInfoRepository = statEquipmentInfoRepository; }2)、使用 var infos = await _statEquipmentInfoRepository.GetSingleByFilter(s => s.EId == requestDtoModel.EId && s.StatDate == requestDtoModel.StatDate && s.ETypeId == requestDtoModel.ETypeId);其他函数的使用，按照接口规范要求使用即可，在这里就不过多赘叙了。 |
【本文地址】