devbox
很楠不爱3
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

C# ORM模式之 SqlSugar使用_c# sqlsugar

作者：很楠不爱3 | 2024-02-23 07:53:23

赞

踩

c# 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接口封装

  1. public interface ISqlSugarRepository<TEntity> : IBaseRepository where TEntity : class
  2. {
  3.     /// <summary>
  4.     /// 
  5.     /// </summary>
  6.     ISqlSugarClient Db { get; }
  7.  
  8.     /// <summary>
  9.     /// 执行查询SQL语句
  10.     /// 只支持查询操作，并且支持拉姆达分页
  11.     /// </summary>
  12.     /// <param name="sql"></param>
  13.     /// <returns></returns>
  14.     Task<List<TEntity>> ExecuteSql(string sql);
  16.     /// <summary>
  17.     /// 通过Ado方法执行SQL语句
  18.     /// 支持任何SQL语句
  19.     /// </summary>
  20.     /// <param name="sql"></param>
  21.     /// <param name="whereObj"></param>
  22.     /// <returns></returns>
  23.     Task<List<TEntity>> ExecuteAllSql(string sql, object whereObj = null);
  25.     /// <summary>
  26.     /// 插入实体
  27.     /// </summary>
  28.     /// <param name="model"></param>
  29.     /// <returns></returns>
  30.     Task<int> Add(TEntity model);
  31.  
  32.     /// <summary>
  33.     /// 批量插入实体
  34.     /// </summary>
  35.     /// <param name="listEntity"></param>
  36.     /// <returns></returns>
  37.     Task<int> Add(List<TEntity> listEntity);
  38.  
  39.     /// <summary>
  40.     /// 根据实体删除数据
  41.     /// </summary>
  42.     /// <param name="model"></param>
  43.     /// <returns></returns>
  44.     Task<bool> Delete(TEntity model);
  45.  
  46.     /// <summary>
  47.     /// 根据实体集合批量删除数据
  48.     /// </summary>
  49.     /// <param name="models"></param>
  50.     /// <returns></returns>
  51.     Task<bool> Delete(List<TEntity> models);
  52.  
  53.     /// <summary>
  54.     /// 根据ID删除数据
  55.     /// </summary>
  56.     /// <param name="id"></param>
  57.     /// <returns></returns>
  58.     Task<bool> DeleteById(object id);
  59.  
  60.     /// <summary>
  61.     /// 根据IDs批量删除数据
  62.     /// </summary>
  63.     /// <param name="ids"></param>
  64.     /// <returns></returns>
  65.     Task<bool> DeleteByIds(List<object> ids);
  66.  
  67.     /// <summary>
  68.     /// 更新实体
  69.     /// </summary>
  70.     /// <param name="model"></param>
  71.     /// <returns></returns>
  72.     Task<bool> Update(TEntity model);
  73.  
  74.     /// <summary>
  75.     /// 批量更新实体
  76.     /// </summary>
  77.     /// <param name="listEntity"></param>
  78.     /// <returns></returns>
  79.     Task<int> Update(List<TEntity> listEntity);
  80.  
  81.     /// <summary>
  82.     /// 根据ID查询一条数据
  83.     /// </summary>
  84.     /// <param name="objId"></param>
  85.     /// <returns></returns>
  86.     Task<TEntity> GetById(object objId);
  87.  
  88.     /// <summary>
  89.     /// 根据条件查询数据是否存在
  90.     /// </summary>
  91.     /// <param name="whereExpression"></param>
  92.     /// <returns></returns>
  93.     Task<bool> GetAnyByFilter(Expression<Func<TEntity, bool>> whereExpression);
  95.     /// <summary>
  96.     /// 根据IDs查询数据
  97.     /// </summary>
  98.     /// <param name="lstIds"></param>
  99.     /// <returns></returns>
  100.     Task<List<TEntity>> GetByIds(List<object> lstIds);
  102.     /// <summary>
  103.     /// 根据条件查询一条数据
  104.     /// </summary>
  105.     /// <param name="whereExpression"></param>
  106.     /// <returns></returns>
  107.     Task<TEntity> GetSingleByFilter(Expression<Func<TEntity, bool>> whereExpression);
  109.     /// <summary>
  110.     /// 查询所有数据
  111.     /// </summary>
  112.     /// <returns></returns>
  113.     Task<List<TEntity>> Get();
  115.     /// <summary>
  116.     /// 查询数据列表
  117.     /// </summary>
  118.     /// <param name="whereExpression">条件表达式</param>
  119.     /// <returns>数据列表</returns>
  120.     Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression);
  122.  
  123.     /// <summary>
  124.     /// 查询数据列表
  125.     /// </summary>
  126.     /// <param name="whereExpression">条件表达式</param>
  127.     /// <param name="orderByExpression">排序表达式</param>
  128.     /// <param name="isAsc">是否升序排序</param>
  129.     /// <returns></returns>
  130.     Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression = null, bool isAsc = true);
  132.     /// <summary>
  133.     /// 分页查询
  134.     /// </summary>
  135.     /// <param name="selector"></param>
  136.     /// <param name="whereExpression"></param>
  137.     /// <param name="intPageIndex"></param>
  138.     /// <param name="intPageSize"></param>
  139.     /// <param name="orderDescSelector"></param>
  140.     /// <returns></returns>
  141.     Task<PaginatedViewModel<TResult>> Get<TResult>(Expression<Func<TEntity, TResult>> selector, Expression<Func<TEntity, bool>> whereExpression, int intPageIndex, int intPageSize, Expression<Func<TEntity, object>> orderDescSelector = null);
  143.  
  144.     /// <summary>
  145.     /// 分页查询
  146.     /// </summary>
  147.     /// <param name="selector"></param>
  148.     /// <param name="whereExpression"></param>
  149.     /// <param name="intPageIndex"></param>
  150.     /// <param name="intPageSize"></param>
  151.     /// <param name="orderDescSelector"></param>
  152.     /// <returns></returns>
  153.     Task<PaginatedViewModel<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, int intPageIndex, int intPageSize, Expression<Func<TEntity, object>> orderDescSelector = null);
  154. }
public interface IBaseRepository { }

 事务接口：

  1. public interface IUnitOfWork
  2. {
  3.     /// <summary>
  4.     /// 
  5.     /// </summary>
  6.     /// <returns></returns>
  7.     SqlSugarClient GetDbClient();
  8.     /// <summary>
  9.     /// 
  10.     /// </summary>
  11.     void BeginTran();
  12.     /// <summary>
  13.     /// 
  14.     /// </summary>
  15.     void CommitTran();
  16.     /// <summary>
  17.     /// 
  18.     /// </summary>
  19.     void RollbackTran();
  20. }

2）、SqlSugarRepository接口实现

  1.  
  2. /// <summary>
  3. /// 
  4. /// </summary>
  5. /// <typeparam name="TEntity"></typeparam>
  6. public class SqlSugarRepository<TEntity> : ISqlSugarRepository<TEntity> where TEntity : class, new()
  7. {
  8.     private readonly SqlSugarClient _dbBase;
  9.  
  10.     public ISqlSugarClient Db
  11.     {
  12.         get { return _dbBase; }
  13.     }
  14.  
  15.     /// <summary>
  16.     /// 
  17.     /// </summary>
  18.     /// <param name="unitOfWork"></param>
  19.     public SqlSugarRepository(IUnitOfWork unitOfWork)
  20.     {
  21.         _dbBase = unitOfWork.GetDbClient();
  22.     }
  23.  
  24.     /// <summary>
  25.     /// 执行查询SQL语句
  26.     /// 只支持查询操作，并且支持拉姆达分页
  27.     /// </summary>
  28.     /// <param name="sql"></param>
  29.     /// <returns></returns>
  30.     public async Task<List<TEntity>> ExecuteSql(string sql)
  31.     {
  32.         return await Db.SqlQueryable<TEntity>(sql).ToListAsync();
  33.     }
  34.  
  35.  
  36.     /// <summary>
  37.     /// 通过Ado方法执行SQL语句
  38.     /// 支持任何SQL语句
  39.     /// </summary>
  40.     /// <param name="sql"></param>
  41.     /// <param name="whereObj">参数</param>
  42.     /// <returns></returns>
  43.     public async Task<List<TEntity>> ExecuteAllSql(string sql, object whereObj = null)
  44.     {
  45.         return await Task.Run(() => Db.Ado.SqlQuery<TEntity>(sql, whereObj));
  46.     }
  47.  
  48.     /// <summary>
  49.     /// 根据ID查询一条数据
  50.     /// </summary>
  51.     /// <param name="objId"></param>
  52.     /// <returns></returns>
  53.     public async Task<TEntity> GetById(object objId)
  54.     {
  55.         return await Db.Queryable<TEntity>().In(objId).SingleAsync();
  56.     }
  57.  
  58.     /// <summary>
  59.     /// 根据IDs查询数据
  60.     /// </summary>
  61.     /// <param name="lstIds">id列表</param>
  62.     /// <returns>数据实体列表</returns>
  63.     public async Task<List<TEntity>> GetByIds(List<object> lstIds)
  64.     {
  65.         return await Db.Queryable<TEntity>().In(lstIds).ToListAsync();
  66.     }
  67.  
  68.     /// <summary>
  69.     /// 插入实体
  70.     /// </summary>
  71.     /// <param name="entity">实体类</param>
  72.     /// <returns></returns>
  73.     public async Task<int> Add(TEntity entity)
  74.     {
  75.         var insert = Db.Insertable(entity);
  76.         return await insert.ExecuteCommandAsync();
  77.     }
  78.  
  79.     /// <summary>
  80.     /// 批量插入实体
  81.     /// </summary>
  82.     /// <param name="listEntity">实体集合</param>
  83.     /// <returns>影响行数</returns>
  84.     public async Task<int> Add(List<TEntity> listEntity)
  85.     {
  86.         return await Db.Insertable(listEntity.ToArray()).ExecuteCommandAsync();
  87.     }
  88.  
  89.     /// <summary>
  90.     /// 更新实体
  91.     /// </summary>
  92.     /// <param name="entity">实体类</param>
  93.     /// <returns></returns>
  94.     public async Task<bool> Update(TEntity entity)
  95.     {
  96.         return await Db.Updateable(entity).ExecuteCommandHasChangeAsync();
  97.     }
  98.  
  99.     /// <summary>
  100.     /// 批量更新实体
  101.     /// </summary>
  102.     /// <param name="listEntity">实体类</param>
  103.     /// <returns></returns>
  104.     public async Task<int> Update(List<TEntity> listEntity)
  105.     {
  106.         return await Db.Updateable(listEntity).ExecuteCommandAsync();
  107.     }
  108.  
  109.     /// <summary>
  110.     /// 根据实体删除数据
  111.     /// </summary>
  112.     /// <param name="entity">实体</param>
  113.     /// <returns></returns>
  114.     public async Task<bool> Delete(TEntity entity)
  115.     {
  116.         return await Db.Deleteable(entity).ExecuteCommandHasChangeAsync();
  117.     }
  118.  
  119.     /// <summary>
  120.     /// 根据实体集合批量删除数据
  121.     /// </summary>
  122.     /// <param name="models"></param>
  123.     /// <returns></returns>
  124.     public async Task<bool> Delete(List<TEntity> models)
  125.     {
  126.         return await Db.Deleteable(models).ExecuteCommandHasChangeAsync();
  127.     }
  128.  
  129.     /// <summary>
  130.     /// 根据ID删除数据
  131.     /// </summary>
  132.     /// <param name="id">ID</param>
  133.     /// <returns></returns>
  134.     public async Task<bool> DeleteById(object id)
  135.     {
  136.         return await Db.Deleteable<TEntity>(id).ExecuteCommandHasChangeAsync();
  137.     }
  138.  
  139.     /// <summary>
  140.     /// 根据IDs批量删除数据
  141.     /// </summary>
  142.     /// <param name="ids">ID集合</param>
  143.     /// <returns></returns>
  144.     public async Task<bool> DeleteByIds(List<object> ids)
  145.     {
  146.         return await Db.Deleteable<TEntity>().In(ids).ExecuteCommandHasChangeAsync();
  147.     }
  148.  
  149.     /// <summary>
  150.     /// 根据条件查询数据是否存在
  151.     /// </summary>
  152.     /// <param name="whereExpression"></param>
  153.     /// <returns></returns>
  154.     public async Task<bool> GetAnyByFilter(Expression<Func<TEntity, bool>> whereExpression)
  155.     {
  156.         return await Db.Queryable<TEntity>().AnyAsync(whereExpression);
  157.     }
  158.  
  159.     /// <summary>
  160.     /// 根据条件查询一条数据
  161.     /// </summary>
  162.     /// <param name="whereExpression"></param>
  163.     /// <returns></returns>
  164.     public async Task<TEntity> GetSingleByFilter(Expression<Func<TEntity, bool>> whereExpression)
  165.     {
  166.         return await Db.Queryable<TEntity>().FirstAsync(whereExpression);
  167.     }
  168.  
  169.     /// <summary>
  170.     /// 查询所有数据
  171.     /// </summary>
  172.     /// <returns></returns>
  173.     public async Task<List<TEntity>> Get()
  174.     {
  175.         return await Db.Queryable<TEntity>().ToListAsync();
  176.     }
  177.  
  178.     /// <summary>
  179.     /// 查询数据列表----按条件表达式
  180.     /// </summary>
  181.     /// <param name="whereExpression">条件表达式</param>
  182.     /// <returns>数据列表</returns>
  183.     public async Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression)
  184.     {
  185.         return await Db.Queryable<TEntity>().WhereIF(whereExpression != null, whereExpression).ToListAsync();
  186.     }
  187.  
  188.     /// <summary>
  189.     /// 查询数据列表----按条件表达式、排序表达式
  190.     /// </summary>
  191.     /// <param name="whereExpression">条件表达式</param>
  192.     /// <param name="orderByExpression">排序表达式</param>
  193.     /// <param name="isAsc">是否升序排序</param>
  194.     /// <returns></returns>
  195.     public async Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression = null, bool isAsc = true)
  196.     {
  197.         return await Db.Queryable<TEntity>().OrderByIF(orderByExpression != null, orderByExpression, isAsc ? OrderByType.Asc : OrderByType.Desc).WhereIF(whereExpression != null, whereExpression).ToListAsync();
  198.     }
  199.  
  200.     /// <summary>
  201.     /// 分页查询
  202.     /// </summary>
  203.     /// <param name="selector"></param>
  204.     /// <param name="whereExpression">条件表达式</param>
  205.     /// <param name="intPageIndex">页码</param>
  206.     /// <param name="intPageSize">页大小</param>
  207.     /// <param name="orderDescSelector">排序字段</param>
  208.     /// <returns></returns>
  209.     public async Task<PaginatedViewModel<TResult>> Get<TResult>(Expression<Func<TEntity, TResult>> selector, Expression<Func<TEntity, bool>> whereExpression, int intPageIndex, int intPageSize, Expression<Func<TEntity, object>> orderDescSelector = null)
  210.     {
  211.         var query = Db.Queryable<TEntity>().WhereIF(whereExpression != null, whereExpression);
  212.  
  213.         query = query.OrderByIF(orderDescSelector != null, orderDescSelector);
  214.  
  215.         var totalCount = 0;
  216.         var results = query.Select(selector).ToPageList(intPageIndex, intPageSize, ref totalCount).ToList();
  217.  
  218.         var basePage = new PaginatedViewModel<TResult>(intPageIndex, intPageSize, totalCount, results);
  219.  
  220.         return await Task.FromResult(basePage);
  221.     }
  222.  
  223.     /// <summary>
  224.     /// 分页查询
  225.     /// </summary>
  226.     /// <param name="selector"></param>
  227.     /// <param name="whereExpression">条件表达式</param>
  228.     /// <param name="intPageIndex">页码</param>
  229.     /// <param name="intPageSize">页大小</param>
  230.     /// <param name="orderDescSelector">排序字段</param>
  231.     /// <returns></returns>
  232.     public async Task<PaginatedViewModel<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, int intPageIndex, int intPageSize, Expression<Func<TEntity, object>> orderDescSelector = null)
  233.     {
  234.         var query = Db.Queryable<TEntity>().WhereIF(whereExpression != null, whereExpression);
  235.  
  236.         query = query.OrderByIF(orderDescSelector != null, orderDescSelector);
  237.  
  238.         var totalCount = 0;
  239.  
  240.         var results = query.ToPageList(intPageIndex, intPageSize, ref totalCount).ToList();
  241.  
  242.         var basePage = new PaginatedViewModel<TEntity>(intPageIndex, intPageSize, totalCount, results);
  243.  
  244.         return await Task.FromResult(basePage);
  245.     }
  246. }

事务接口实现：

  1. public class UnitOfWork : IUnitOfWork, IDisposable
  2. {
  3.     private readonly ISqlSugarClient _sqlSugarClient;
  4.  
  5.     private bool _disposed = false;
  6.  
  7.     public UnitOfWork(ISqlSugarClient sqlSugarClient)
  8.     {
  9.         _sqlSugarClient = sqlSugarClient;
  10.     }
  11.  
  12.     public SqlSugarClient GetDbClient()
  13.     {
  14.         return (SqlSugarClient)_sqlSugarClient;
  15.     }
  16.  
  17.     public void BeginTran()
  18.     {
  19.         GetDbClient().BeginTran();
  20.     }
  21.  
  22.     public void CommitTran()
  23.     {
  24.         try
  25.         {
  26.             GetDbClient().CommitTran();
  27.             Dispose();
  28.         }
  29.         catch
  30.         {
  31.             GetDbClient().RollbackTran();
  32.             Dispose();
  33.         }
  34.     }
  35.  
  36.     public void RollbackTran()
  37.     {
  38.         GetDbClient().RollbackTran();
  39.         Dispose();
  40.     }
  41.  
  42.     public void Dispose()
  43.     {
  44.         Dispose(true);
  45.         GC.SuppressFinalize(this);
  46.     }
  47.  
  48.     public void Dispose(bool disposing)
  49.     {
  50.         if (_disposed) return;
  51.  
  52.         if (disposing)
  53.         {
  54.             GetDbClient()?.Dispose();
  55.         }
  56.  
  57.         _disposed = true;
  58.     }
  59.  
  60.     ~UnitOfWork() => Dispose(false);
  61. }

分页查询模型：

  1. public class PaginatedViewModel<T>
  2. {
  3.     public int PageIndex { get; private set; }
  4.  
  5.     public int PageSize { get; private set; }
  6.  
  7.     public long Count { get; private set; }
  8.  
  9.     public IEnumerable<T> Data { get; private set; }
  10.  
  11.     public PaginatedViewModel(int pageIndex, int pageSize, long count, IEnumerable<T> data)
  12.     {
  13.         PageIndex = pageIndex;
  14.         PageSize = pageSize;
  15.         Count = count;
  16.         Data = data;
  17.     }
  18. }

3、SqlSugar在项目中的使用

1）、定义及初始化

  1. private readonly ISqlSugarRepository<StatEquipmentInfo> _statEquipmentInfoRepository;
  2.  
  3. public GetStatusInfoHandler(ISqlSugarRepository<StatEquipmentInfo> statEquipmentInfoRepository)
  4. {
  5.    _statEquipmentInfoRepository = statEquipmentInfoRepository;
  6. }

2)、使用

var infos = await _statEquipmentInfoRepository.GetSingleByFilter(s => s.EId == requestDtoModel.EId && s.StatDate == requestDtoModel.StatDate && s.ETypeId == requestDtoModel.ETypeId);

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/很楠不爱3/article/detail/134841
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号