一、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<List> ExecuteSql(string sql);/// /// 通过Ado方法执行SQL语句/// 支持任何SQL语句/// /// /// /// Task<List> 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<Func> whereExpression);/// /// 根据IDs查询数据/// /// /// Task<List> GetByIds(List lstIds);/// /// 根据条件查询一条数据/// /// /// Task GetSingleByFilter(Expression<Func> whereExpression);/// /// 查询所有数据/// /// Task<List> Get();/// /// 查询数据列表/// /// 条件表达式/// 数据列表Task<List> Get(Expression<Func> whereExpression);/// /// 查询数据列表/// /// 条件表达式/// 排序表达式/// 是否升序排序/// Task<List> Get(Expression<Func> whereExpression, Expression<Func> orderByExpression = null, bool isAsc = true);/// /// 分页查询/// /// /// /// /// /// /// Task<PaginatedViewModel> Get(Expression<Func> selector, Expression<Func> whereExpression, int intPageIndex, int intPageSize, Expression<Func> orderDescSelector = null);/// /// 分页查询/// /// /// /// /// /// /// Task<PaginatedViewModel> Get(Expression<Func> whereExpression, int intPageIndex, int intPageSize, Expression<Func> 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<List> ExecuteSql(string sql){return await Db.SqlQueryable(sql).ToListAsync();}/// /// 通过Ado方法执行SQL语句/// 支持任何SQL语句/// /// /// 参数/// public async Task<List> 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<List> 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<Func> whereExpression){return await Db.Queryable().AnyAsync(whereExpression);}/// /// 根据条件查询一条数据/// /// /// public async Task GetSingleByFilter(Expression<Func> whereExpression){return await Db.Queryable().FirstAsync(whereExpression);}/// /// 查询所有数据/// /// public async Task<List> Get(){return await Db.Queryable().ToListAsync();}/// /// 查询数据列表----按条件表达式/// /// 条件表达式/// 数据列表public async Task<List> Get(Expression<Func> whereExpression){return await Db.Queryable().WhereIF(whereExpression != null, whereExpression).ToListAsync();}/// /// 查询数据列表----按条件表达式、排序表达式/// /// 条件表达式/// 排序表达式/// 是否升序排序/// public async Task<List> Get(Expression<Func> whereExpression, Expression<Func> orderByExpression = null, bool isAsc = true){return await Db.Queryable().OrderByIF(orderByExpression != null, orderByExpression, isAsc " />/// 条件表达式/// 页码/// 页大小/// 排序字段/// public async Task<PaginatedViewModel> Get(Expression<Func> selector, Expression<Func> whereExpression, int intPageIndex, int intPageSize, Expression<Func> 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<PaginatedViewModel> Get(Expression<Func> whereExpression, int intPageIndex, int intPageSize, Expression<Func> 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);

其他函数的使用,按照接口规范要求使用即可,在这里就不过多赘叙了。

Copyright © maxssl.com 版权所有 浙ICP备2022011180号