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<TEntity> : IBaseRepository where TEntity : class
{
    /// <summary>
    /// 
    /// </summary>
    ISqlSugarClient Db { get; }

    /// <summary>
    /// 执行查询SQL语句
    /// 只支持查询操作，并且支持拉姆达分页
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    Task<List<TEntity>> ExecuteSql(string sql);

    /// <summary>
    /// 通过Ado方法执行SQL语句
    /// 支持任何SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="whereObj"></param>
    /// <returns></returns>
    Task<List<TEntity>> ExecuteAllSql(string sql, object whereObj = null);

    /// <summary>
    /// 插入实体
    /// </summary>
    /// <param name="model"></param>
    /// <returns></returns>
    Task<int> Add(TEntity model);

    /// <summary>
    /// 批量插入实体
    /// </summary>
    /// <param name="listEntity"></param>
    /// <returns></returns>
    Task<int> Add(List<TEntity> listEntity);

    /// <summary>
    /// 根据实体删除数据
    /// </summary>
    /// <param name="model"></param>
    /// <returns></returns>
    Task<bool> Delete(TEntity model);

    /// <summary>
    /// 根据实体集合批量删除数据
    /// </summary>
    /// <param name="models"></param>
    /// <returns></returns>
    Task<bool> Delete(List<TEntity> models);

    /// <summary>
    /// 根据ID删除数据
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    Task<bool> DeleteById(object id);

    /// <summary>
    /// 根据IDs批量删除数据
    /// </summary>
    /// <param name="ids"></param>
    /// <returns></returns>
    Task<bool> DeleteByIds(List<object> ids);

    /// <summary>
    /// 更新实体
    /// </summary>
    /// <param name="model"></param>
    /// <returns></returns>
    Task<bool> Update(TEntity model);

    /// <summary>
    /// 批量更新实体
    /// </summary>
    /// <param name="listEntity"></param>
    /// <returns></returns>
    Task<int> Update(List<TEntity> listEntity);

    /// <summary>
    /// 根据ID查询一条数据
    /// </summary>
    /// <param name="objId"></param>
    /// <returns></returns>
    Task<TEntity> GetById(object objId);

    /// <summary>
    /// 根据条件查询数据是否存在
    /// </summary>
    /// <param name="whereExpression"></param>
    /// <returns></returns>
    Task<bool> GetAnyByFilter(Expression<Func<TEntity, bool>> whereExpression);

    /// <summary>
    /// 根据IDs查询数据
    /// </summary>
    /// <param name="lstIds"></param>
    /// <returns></returns>
    Task<List<TEntity>> GetByIds(List<object> lstIds);

    /// <summary>
    /// 根据条件查询一条数据
    /// </summary>
    /// <param name="whereExpression"></param>
    /// <returns></returns>
    Task<TEntity> GetSingleByFilter(Expression<Func<TEntity, bool>> whereExpression);

    /// <summary>
    /// 查询所有数据
    /// </summary>
    /// <returns></returns>
    Task<List<TEntity>> Get();

    /// <summary>
    /// 查询数据列表
    /// </summary>
    /// <param name="whereExpression">条件表达式</param>
    /// <returns>数据列表</returns>
    Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression);


    /// <summary>
    /// 查询数据列表
    /// </summary>
    /// <param name="whereExpression">条件表达式</param>
    /// <param name="orderByExpression">排序表达式</param>
    /// <param name="isAsc">是否升序排序</param>
    /// <returns></returns>
    Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression = null, bool isAsc = true);

    /// <summary>
    /// 分页查询
    /// </summary>
    /// <param name="selector"></param>
    /// <param name="whereExpression"></param>
    /// <param name="intPageIndex"></param>
    /// <param name="intPageSize"></param>
    /// <param name="orderDescSelector"></param>
    /// <returns></returns>
    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);


    /// <summary>
    /// 分页查询
    /// </summary>
    /// <param name="selector"></param>
    /// <param name="whereExpression"></param>
    /// <param name="intPageIndex"></param>
    /// <param name="intPageSize"></param>
    /// <param name="orderDescSelector"></param>
    /// <returns></returns>
    Task<PaginatedViewModel<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, int intPageIndex, int intPageSize, Expression<Func<TEntity, object>> orderDescSelector = null);
}

public interface IBaseRepository { }

 事务接口：

public interface IUnitOfWork
{
    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    SqlSugarClient GetDbClient();
    /// <summary>
    /// 
    /// </summary>
    void BeginTran();
    /// <summary>
    /// 
    /// </summary>
    void CommitTran();
    /// <summary>
    /// 
    /// </summary>
    void RollbackTran();
}

2）、SqlSugarRepository接口实现

/// <summary>
/// 
/// </summary>
/// <typeparam name="TEntity"></typeparam>
public class SqlSugarRepository<TEntity> : ISqlSugarRepository<TEntity> where TEntity : class, new()
{
    private readonly SqlSugarClient _dbBase;

    public ISqlSugarClient Db
    {
        get { return _dbBase; }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="unitOfWork"></param>
    public SqlSugarRepository(IUnitOfWork unitOfWork)
    {
        _dbBase = unitOfWork.GetDbClient();
    }

    /// <summary>
    /// 执行查询SQL语句
    /// 只支持查询操作，并且支持拉姆达分页
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public async Task<List<TEntity>> ExecuteSql(string sql)
    {
        return await Db.SqlQueryable<TEntity>(sql).ToListAsync();
    }


    /// <summary>
    /// 通过Ado方法执行SQL语句
    /// 支持任何SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="whereObj">参数</param>
    /// <returns></returns>
    public async Task<List<TEntity>> ExecuteAllSql(string sql, object whereObj = null)
    {
        return await Task.Run(() => Db.Ado.SqlQuery<TEntity>(sql, whereObj));
    }

    /// <summary>
    /// 根据ID查询一条数据
    /// </summary>
    /// <param name="objId"></param>
    /// <returns></returns>
    public async Task<TEntity> GetById(object objId)
    {
        return await Db.Queryable<TEntity>().In(objId).SingleAsync();
    }

    /// <summary>
    /// 根据IDs查询数据
    /// </summary>
    /// <param name="lstIds">id列表</param>
    /// <returns>数据实体列表</returns>
    public async Task<List<TEntity>> GetByIds(List<object> lstIds)
    {
        return await Db.Queryable<TEntity>().In(lstIds).ToListAsync();
    }

    /// <summary>
    /// 插入实体
    /// </summary>
    /// <param name="entity">实体类</param>
    /// <returns></returns>
    public async Task<int> Add(TEntity entity)
    {
        var insert = Db.Insertable(entity);
        return await insert.ExecuteCommandAsync();
    }

    /// <summary>
    /// 批量插入实体
    /// </summary>
    /// <param name="listEntity">实体集合</param>
    /// <returns>影响行数</returns>
    public async Task<int> Add(List<TEntity> listEntity)
    {
        return await Db.Insertable(listEntity.ToArray()).ExecuteCommandAsync();
    }

    /// <summary>
    /// 更新实体
    /// </summary>
    /// <param name="entity">实体类</param>
    /// <returns></returns>
    public async Task<bool> Update(TEntity entity)
    {
        return await Db.Updateable(entity).ExecuteCommandHasChangeAsync();
    }

    /// <summary>
    /// 批量更新实体
    /// </summary>
    /// <param name="listEntity">实体类</param>
    /// <returns></returns>
    public async Task<int> Update(List<TEntity> listEntity)
    {
        return await Db.Updateable(listEntity).ExecuteCommandAsync();
    }

    /// <summary>
    /// 根据实体删除数据
    /// </summary>
    /// <param name="entity">实体</param>
    /// <returns></returns>
    public async Task<bool> Delete(TEntity entity)
    {
        return await Db.Deleteable(entity).ExecuteCommandHasChangeAsync();
    }

    /// <summary>
    /// 根据实体集合批量删除数据
    /// </summary>
    /// <param name="models"></param>
    /// <returns></returns>
    public async Task<bool> Delete(List<TEntity> models)
    {
        return await Db.Deleteable(models).ExecuteCommandHasChangeAsync();
    }

    /// <summary>
    /// 根据ID删除数据
    /// </summary>
    /// <param name="id">ID</param>
    /// <returns></returns>
    public async Task<bool> DeleteById(object id)
    {
        return await Db.Deleteable<TEntity>(id).ExecuteCommandHasChangeAsync();
    }

    /// <summary>
    /// 根据IDs批量删除数据
    /// </summary>
    /// <param name="ids">ID集合</param>
    /// <returns></returns>
    public async Task<bool> DeleteByIds(List<object> ids)
    {
        return await Db.Deleteable<TEntity>().In(ids).ExecuteCommandHasChangeAsync();
    }

    /// <summary>
    /// 根据条件查询数据是否存在
    /// </summary>
    /// <param name="whereExpression"></param>
    /// <returns></returns>
    public async Task<bool> GetAnyByFilter(Expression<Func<TEntity, bool>> whereExpression)
    {
        return await Db.Queryable<TEntity>().AnyAsync(whereExpression);
    }

    /// <summary>
    /// 根据条件查询一条数据
    /// </summary>
    /// <param name="whereExpression"></param>
    /// <returns></returns>
    public async Task<TEntity> GetSingleByFilter(Expression<Func<TEntity, bool>> whereExpression)
    {
        return await Db.Queryable<TEntity>().FirstAsync(whereExpression);
    }

    /// <summary>
    /// 查询所有数据
    /// </summary>
    /// <returns></returns>
    public async Task<List<TEntity>> Get()
    {
        return await Db.Queryable<TEntity>().ToListAsync();
    }

    /// <summary>
    /// 查询数据列表----按条件表达式
    /// </summary>
    /// <param name="whereExpression">条件表达式</param>
    /// <returns>数据列表</returns>
    public async Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression)
    {
        return await Db.Queryable<TEntity>().WhereIF(whereExpression != null, whereExpression).ToListAsync();
    }

    /// <summary>
    /// 查询数据列表----按条件表达式、排序表达式
    /// </summary>
    /// <param name="whereExpression">条件表达式</param>
    /// <param name="orderByExpression">排序表达式</param>
    /// <param name="isAsc">是否升序排序</param>
    /// <returns></returns>
    public async Task<List<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression = null, bool isAsc = true)
    {
        return await Db.Queryable<TEntity>().OrderByIF(orderByExpression != null, orderByExpression, isAsc ? OrderByType.Asc : OrderByType.Desc).WhereIF(whereExpression != null, whereExpression).ToListAsync();
    }

    /// <summary>
    /// 分页查询
    /// </summary>
    /// <param name="selector"></param>
    /// <param name="whereExpression">条件表达式</param>
    /// <param name="intPageIndex">页码</param>
    /// <param name="intPageSize">页大小</param>
    /// <param name="orderDescSelector">排序字段</param>
    /// <returns></returns>
    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)
    {
        var query = Db.Queryable<TEntity>().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<TResult>(intPageIndex, intPageSize, totalCount, results);

        return await Task.FromResult(basePage);
    }

    /// <summary>
    /// 分页查询
    /// </summary>
    /// <param name="selector"></param>
    /// <param name="whereExpression">条件表达式</param>
    /// <param name="intPageIndex">页码</param>
    /// <param name="intPageSize">页大小</param>
    /// <param name="orderDescSelector">排序字段</param>
    /// <returns></returns>
    public async Task<PaginatedViewModel<TEntity>> Get(Expression<Func<TEntity, bool>> whereExpression, int intPageIndex, int intPageSize, Expression<Func<TEntity, object>> orderDescSelector = null)
    {
        var query = Db.Queryable<TEntity>().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<TEntity>(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<T>
{
    public int PageIndex { get; private set; }

    public int PageSize { get; private set; }

    public long Count { get; private set; }

    public IEnumerable<T> Data { get; private set; }

    public PaginatedViewModel(int pageIndex, int pageSize, long count, IEnumerable<T> data)
    {
        PageIndex = pageIndex;
        PageSize = pageSize;
        Count = count;
        Data = data;
    }
}

3、SqlSugar在项目中的使用

1）、定义及初始化

private readonly ISqlSugarRepository<StatEquipmentInfo> _statEquipmentInfoRepository;

public GetStatusInfoHandler(ISqlSugarRepository<StatEquipmentInfo> statEquipmentInfoRepository)
{
   _statEquipmentInfoRepository = statEquipmentInfoRepository;
}

2)、使用

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

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