SqlSugard封装

4、实现仓储 BaseRepository.cs

using BoZhon.Data.SqlSugar.DbContext;
using BoZhon.Util.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace BoZhon.Data.SqlSugar.Repository
{
    public class BaseRepository : IBaseRepository where T : class, new()
    {
        public readonly IDbContext context = new AppDbContext();

        #region 新增

        /// 

        /// 增加单条数据
        /// 
        /// 实体对象
        /// 操作是否成功
        public async Task<bool> Add(T model)
        {
            return await context.db.Insertable(model).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 增加多条数据
        /// 
        /// 实体集合
        /// 操作是否成功
        public async Task<bool> AddRange(List list)
        {
            return await context.db.Insertable(list).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 添加单条数据，并返回 自增列
        /// 
        /// 实体对象
        /// 
        public async Task<bool> AddReturnIdentity(T model)
        {
            return await context.db.Insertable(model).ExecuteReturnIdentityAsync() > 0;//ExecuteReturnBigIdentity();//4.5.0.2 +  long
        }

        /// 

        /// 增加单条数据 ，并返回 实体
        /// 
        /// 实体对象
        /// 
        public async Task AddReturnEntity(T model)
        {
            return await context.db.Insertable(model).ExecuteReturnEntityAsync();
        }

        /// 

        /// 只添加指定列
        /// 
        /// 实体对象
        /// 指定要添加的列
        /// 
        public async Task<bool> AddColumns(T model, params string[] columns)
        {
            return await context.db.Insertable(model).InsertColumns(columns).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 不插入指定列
        /// 
        /// 实体对象
        /// 要忽略的列
        /// 
        public async Task<bool> AddColumnsByIgnoreColumns(T model, params string[] IgnoreColumns)
        {
            return await context.db.Insertable(model)
                .IgnoreColumns(IgnoreColumns).ExecuteCommandAsync() > 0;
        }

        #endregion

        #region 删除

        /// 

        /// 根据主键删除，并返回操作是否成功
        /// 
        /// 主键的类型
        /// 主键
        /// 
        public async Task<bool> Delete(S key)
        {
            return await context.db.Deleteable().In(key).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键删除，并返回操作是否成功
        /// 
        /// 主键类型
        /// 主键
        /// 
        public async Task<bool> DeleteRange(params S[] keys)
        {
            return await context.db.Deleteable().In(keys).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据条件删除，并返回操作是否成功
        /// 
        /// 条件
        /// 
        public async Task<bool> DeleteWhere(Expressionbool>> where)
        {
            return await context.db.Deleteable().Where(where).ExecuteCommandAsync() > 0;
        }

        #endregion

        #region 修改

        /// 

        /// 根据主键更新 ，返回操作是否成功
        /// 
        /// 
        /// 
        public async Task<bool> Update(T model)
        {
            return await context.db.Updateable(model).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键更新，返回操作是否成功
        /// 
        /// 实体集合
        /// 
        public async Task<bool> UpdateRange(List list)
        {
            return await context.db.Updateable(list).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据指定 列条件 更新 ，并返回操作是否成功
        /// 
        /// 实体对象
        /// 列条件 例如： t=>t.id>5 
        public async Task<bool> Update(T model, Expressionobject>> expression)
        {
            return await context.db.Updateable(model).WhereColumns(expression).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键，更新指定列,返回操作是否成功
        /// 
        /// 实体对象
        /// 要更新的列
        /// 
        public async Task<bool> UpdateColumns(T model, params string[] columns)
        {
            return await context.db.Updateable(model).UpdateColumns(columns).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键，更新指定列,返回操作是否成功
        /// 
        /// 实体对象
        /// 要更新的列
        /// 
        public async Task<bool> UpdateColumns(T model, Expressionobject>> columns)
        {
            return await context.db.Updateable(model).UpdateColumns(columns).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键 ， 忽略更新指定列，返回操作是否成功
        /// 
        /// 实体对象
        /// 不更新的 忽略列
        /// 
        public async Task<bool> UpdateColumnsByIgnoreColumns(T model, params string[] columns)
        {
            return await context.db.Updateable(model).IgnoreColumns(columns).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键 ， 忽略更新指定列，返回操作是否成功
        /// 
        /// 实体对象
        /// 不更新的 忽略列
        /// 
        public async Task<bool> UpdateColumnsByIgnoreColumns(T model, Expressionobject>> columns)
        {
            return await context.db.Updateable(model).IgnoreColumns(columns).ExecuteCommandAsync() > 0;
        }

        /// 

        /// 根据主键更新 列
        /// 
        /// 实体对象
        /// 是否 NULL的列不更新
        /// 是否忽略 自增列
        /// 是否 忽略默认值列
        /// 
        public async Task<bool> UpdateNotNullColumns(T model, bool ignoreAllNullColumns, bool isOffIdentity = false, bool ignoreAllDefaultValue = false)
        {
            return await context.db.Updateable(model)
                .IgnoreColumns(ignoreAllNullColumns: true, isOffIdentity: false, ignoreAllDefaultValue: false)
                .ExecuteCommandAsync() > 0;
        }

        //4.6.0.7 联表更新

        /// 

        /// //根据不同条件执行更新不同的列 
        /// 
        /// 实体对象
        /// 条件（key：要更新的列，value：条件是否更新此列）
        /// 
        public async Task<bool> UpdateIF(T model, Dictionaryobject>>, bool> dic)
        {
            var able = context.db.Updateable(model);
            foreach (var item in dic)
            {
                able.UpdateColumnsIF(item.Value, item.Key);// s=>s.name  ture
            }

            return await able.ExecuteCommandAsync() > 0;
        }

        #endregion

        #region 查询

        /// 

        /// 查询所有数据
        /// 
        /// 
        public async Task> getAll(bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 取前 num 条数据
        /// 
        /// 取前几条
        /// 
        public async Task> getTakeList(int num, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().OrderByIF(isOrderBy, orderBy, orderByType).Take(num).ToListAsync();
        }

        /// 

        /// 获取单表 分页数据
        /// 
        /// 跳过几条
        /// 取几条
        /// 跳过几条
        /// 排序条件
        /// 排序类型（Asc、Desc）
        /// 
        public async Task> getPageList(int skip, int take, Expressionbool>> whereExp, Expressionobject>> orderBy, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().Skip(skip).Take(take).OrderBy(orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 获取符合条件的前 num 条数据
        /// 
        /// 条件
        /// 取前几条
        ///  
        public async Task> getTakeList(Expressionbool>> where, int num, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().Where(where).OrderByIF(isOrderBy, orderBy, orderByType).Take(num).ToListAsync();
        }

        /// 

        /// 根据主键查询
        /// 
        /// 主键
        /// 
        public async Task getByPrimaryKey(object pkValue)
        {
            return await context.db.Queryable().InSingleAsync(pkValue);
        }

        /// 

        /// 根据条件获取 单条数据 
        /// 
        /// 条件
        /// 
        public async Task getFirstOrDefault(Expressionbool>> where)
        {
            return await context.db.Queryable().FirstAsync(where);//查询单条没有数据返回NULL, Single超过1条会报错，First不会
        }

        /// 

        /// 根据主键 In  查询
        /// 
        /// 主键的类型
        /// 主键 In 操作的结果集
        /// 
        public async Task> getByIn(List list, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().In(list).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 根据指定列 In 查询
        /// 
        /// 指定列的类型
        /// 指定列
        /// 指定列 In 操作 的结果集
        /// 
        public async Task> getByIn(Expressionobject>> column, List list, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().In(column, list).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 根据指定列 Not In (!Contain)查询
        /// 
        /// 指定列类型
        /// Not In的结果集
        /// 指定列
        /// 
        public async Task> getByNotIn(List list, object field, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().Where(t => !SqlFunc.ContainsArray(list, field)).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 根据条件 查询
        /// 
        /// 条件
        /// 
        public async Task> getByWhere(Expressionbool>> where, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().Where(where).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 单个条件 根据 isWhere 判断 是否使用此条件进行查询
        /// 
        /// 判断是否使用此查询条件的条件
        /// 查询条件
        /// 
        public async Task> getByWhereIF(bool isWhere, Expressionbool>> where, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().WhereIF(isWhere, where).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 多个条件 根据 wheres.value  判断是否使用 此 wheres.key 的条件
        /// 
        /// 查询条件
        /// 
        public async Task> getByWhereIF(Dictionarybool>>, bool> wheres, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            var able = context.db.Queryable();
            foreach (var item in wheres)
            {
                able.WhereIF(item.Value, item.Key);
            }
            return await able.OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 查询 指定列的值 在 start至end 之间的数据
        /// 
        /// 指定类
        /// 开始
        /// 结束
        /// 
        public async Task> getByBetween(object value, object start, object end, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable().Where(it => SqlFunc.Between(value, start, end)).OrderByIF(isOrderBy, orderBy, orderByType).ToListAsync();
        }

        /// 

        /// 判断是否存在这条记录
        /// 
        /// 条件
        /// 
        public async Task<bool> getIsAny(Expressionbool>> where)
        {
            return await context.db.Queryable().AnyAsync(where);
        }

        /// 

        /// 单表分页查询
        /// 
        /// 要查询的表
        /// 分页参数
        /// 是否需要条件查询
        /// 查询条件
        /// 是否需要排序条件
        /// 排序条件
        /// 排序类型（Asc、Desc）
        /// 
        public async Task> getPageList(Pagination pagination, bool isWhere = false, Expressionbool>> whereExp = null, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            RefAsync<int> totalNumber = 0;
            var result = await context.db.Queryable().WhereIF(isWhere, whereExp).OrderByIF(isOrderBy, orderBy, orderByType).ToPageListAsync(pagination.PageIndex, pagination.PageSize, totalNumber);
            pagination.TotalCount = totalNumber;
            return result;
        }

        /// 

        /// 两表查询，
        /// 
        /// 左表
        /// 右表
        /// 联表方式，联表字段（主外键关系）
        /// 联表查询的结果
        /// 是否需要查询条件
        /// 条件查询
        /// 是否需要排序
        /// 排序条件
        /// 排序类型（Asc、Desc）
        /// 
        public async Task<dynamic> getJoinList(Expression> joinExp, Expressiondynamic>> selectExp, bool isWhere = false, Expressionbool>> whereExp = null, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {

            return await context.db.Queryable(joinExp).WhereIF(isWhere, whereExp)
                .OrderByIF(isOrderBy, orderBy, orderByType).Select(selectExp).ToListAsync();
        }

        /// 

        /// 两表 分页查询，
        /// 
        /// 左表
        /// 右表
        /// 分页参数
        /// 联表方式，联表字段（主外键关系）
        /// 联表查询的结果
        /// 是否需要查询条件
        /// 条件查询
        /// 是否需要排序
        /// 排序条件
        /// 排序类型（Asc、Desc）
        /// 
        public async Task<dynamic> getJoinPageList(Pagination pagination, Expression> joinExp, Expressiondynamic>> selectExp, bool isWhere = false, Expressionbool>> whereExp = null, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {

            return await context.db.Queryable(joinExp).WhereIF(isWhere, whereExp)
                .OrderByIF(isOrderBy, orderBy, orderByType).Select(selectExp).ToPageListAsync(pagination.PageIndex, pagination.PageSize, pagination.TotalCount);
        }

        /// 

        /// 三表连接 查询
        /// 
        /// 表1
        /// 表2
        /// 表3
        /// 联表方式，联表字段（主外键关系）
        /// 联表查询的结果
        /// 是否需要查询条件
        /// 查询条件
        /// 是否需要排序条件
        /// 排序条件
        /// 排序类型（Asc、Desc）
        /// 
        public async Task<dynamic> getJoinList(Expression> joinExp, Expressiondynamic>> selectExp, bool isWhere = false, Expressionbool>> whereExp = null, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable(joinExp).WhereIF(isWhere, whereExp).OrderByIF(isOrderBy, orderBy, orderByType).Select(selectExp).ToListAsync();
        }

        /// 

        /// 三表连接 分页 查询
        /// 
        /// 表1
        /// 表2
        /// 表3
        /// 分页参数
        /// 联表方式，联表字段（主外键关系）
        /// 联表查询的结果
        /// 是否需要查询条件
        /// 查询条件
        /// 是否需要排序条件
        /// 排序条件
        /// 排序类型（Asc、Desc）
        /// 
        public async Task<dynamic> getJoinList(Pagination pagination, Expression> joinExp, Expressiondynamic>> selectExp, bool isWhere = false, Expressionbool>> whereExp = null, bool isOrderBy = false, Expressionobject>> orderBy = null, OrderByType orderByType = OrderByType.Asc)
        {
            return await context.db.Queryable(joinExp).WhereIF(isWhere, whereExp).OrderByIF(isOrderBy, orderBy, orderByType)
                .Select(selectExp).ToPageListAsync(pagination.PageIndex, pagination.PageSize, pagination.TotalCount);
        }

        /// 

        /// 执行查询sql语句 ，返回数据集
        /// 
        /// sql语句
        /// 
        public async Task> getListBySql(string sql)
        {
            return await context.db.SqlQueryable(sql).ToListAsync();
        }

        /// 

        /// 执行非查询sql语句，返回操作是否成功
        /// 
        /// sql语句
        /// 参数化
        /// 
        public async Task<bool> ExecuteCommandSql(string sql, List parameters)
        {
            return await context.db.Ado.ExecuteCommandAsync(sql, parameters) > 0;
        }

        /// 

        /// 执行查询sql语句，返回查询的结果集
        /// 
        /// sql语句
        /// 参数化
        /// 
        public async Task> getListBySqlQuery(string sql, List parameters)
        {
            return await context.db.Ado.SqlQueryAsync(sql, parameters);
        }


        /// 

        ///  执行查询sql语句，返回查询的DataTable
        /// 
        /// sql语句
        /// 参数化
        /// 
        public async Task getDataTable(string sql, List parameters)
        {
            return await context.db.Ado.GetDataTableAsync(sql, parameters);
        }

        /// 

        /// 执行查询sql语句，返回 第一行第一列
        /// 
        /// sql语句
        /// 参数化
        /// 
        public async Task<object> getScalar(string sql, List parameters)
        {
            return await context.db.Ado.GetScalarAsync(sql, parameters);
        }





        #endregion

        #region 其他


        /// 

        /// 使用存储过程，返回结果集
        /// 
        /// 存储过程名称 
        /// 参数，支持 output
        /// 
        public async Task UseStoredProcedure(string procedureName, List parameters)
        {
            return await context.db.Ado.UseStoredProcedure().GetDataTableAsync(procedureName, parameters);
        }

        /// 

        /// 使用事务 ，无返回值
        /// 
        /// 执行动作
        /// 错误回调
        public async Taskbool>> UseTran(Func func, Action errorCallBack)
        {
            return await context.db.Ado.UseTranAsync(func, errorCallBack); ;
        }

        /// 

        /// 使用事务，有返回值
        /// 
        /// 返回值类型
        /// 执行动作
        /// 错误回调
        public async Task> UseTran(Func> func, Action errorCallBack)
        {
            return await context.db.Ado.UseTranAsync(func, errorCallBack);
        }

        #endregion
    }

}

using BoZhon.Data.SqlSugar.Repository;
using BoZhon.Entity.BaseManage;
using BoZhon.Model.Param.BaseManage;
using BoZhon.Util.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BoZhon.IService.BaseManage
{
    ///

    ///系统日志表
    ///
    public interface base_logIServer : IBaseRepository
    {
        /// 

        /// 根据主键获取数据
        /// 
        /// 
        /// 
        Task FindEntity(string Id);

        /// 

        /// 根据条件获取信息
        /// 
        /// 
        /// 
        Task> FindList(base_logParam param);


        /// 

        /// 根据条件获取分页信息
        /// 
        /// 
        /// 
        /// 
        Task> FindPageList(base_logParam param, Pagination pagination);

        /// 

        /// 新增数据
        /// 
        /// 
        /// 
        Task<bool> InsetEntity(base_logEntity entity);

        /// 

        /// 修改数据
        /// 
        /// 
        /// 
        Task<bool> UpdateEntity(base_logEntity entity);

        /// 

        /// 根据主键删除信息
        /// 
        /// 
        /// 
        Task<bool> DeleteEntityById(string Id);

        /// 

        /// 根据多个主键删除信息
        /// 
        /// 
        /// 
        Task<bool> DeleteEntityByIds(List<string> Ids);
    }

using BoZhon.Data.SqlSugar.DbContext;
using BoZhon.Data.SqlSugar.Repository;
using BoZhon.Entity.BaseManage;
using BoZhon.IService.BaseManage;
using BoZhon.Model.Param.BaseManage;
using BoZhon.Util.Extension;
using BoZhon.Util.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace BoZhon.Service.BaseManage
{
    ///

    ///系统日志表
    ///
    public class base_logServer : BaseRepository, base_logIServer
    {
        #region 获取数据

        /// 

        /// 根据主键获取信息
        /// 
        /// 
        /// 
        public async Task FindEntity(string Id)
        {
            return await this.getByPrimaryKey(Id);
        }

        /// 

        /// 根据条件查询信息
        /// 
        /// 条件实体
        /// 
        public async Task> FindList(base_logParam param)
        {
            var expression = ListFilter(param);
            var list = await this.getByWhere(expression);
            return list;
        }

        /// 

        /// 根据条件查询实体数据信息
        /// 
        /// 条件实体
        /// 分页信息
        /// 
        public async Task> FindPageList(base_logParam param, Pagination pagination)
        {
            var expression = ListFilter(param);
            var list = await this.getPageList(pagination, true, expression);
            return list;
        }

        #endregion

        #region 提交数据

        /// 

        /// 根据主键删除数据
        /// 
        /// 
        /// 
        public async Task<bool> DeleteEntityById(string Id)
        {
            return await this.Delete(Id);
        }

        /// 

        /// 根据多个主键删除数据
        /// 
        /// 
        /// 
        public async Task<bool> DeleteEntityByIds(List<string> ids)
        {
            string[] idsArray = ids.ToArray();
            return await this.Delete(idsArray);
        }

        /// 

        /// 新增数据
        /// 
        /// 
        /// 
        public async Task<bool> InsetEntity(base_logEntity entity)
        {
            return await this.Add(entity);
        }

        /// 

        /// 修改数据
        /// 
        /// 
        /// 
        public async Task<bool> UpdateEntity(base_logEntity entity)
        {
            return await this.UpdateNotNullColumns(entity, true, false, false);
        }

        #endregion

        #region 私有方法

        /// 

        /// 查询条件组合
        /// 
        /// 
        /// 
        private Expressionbool>> ListFilter(base_logParam param)
        {
            var expression = LinqExtensions.True();
            if (param != null)
            {
                if (!param.LogID.IsEmpty())
                {
                    expression = expression.And(t => t.LogID.Equals(param.LogID));
                }
                if (!param.FunctionName.IsEmpty())
                {
                    expression = expression.And(t => t.FunctionName.Equals(param.FunctionName));
                }
                if (!param.ParamText.IsEmpty())
                {
                    expression = expression.And(t => t.ParamText.Equals(param.ParamText));
                }
                if (!param.CreateUserId.IsEmpty())
                {
                    expression = expression.And(t => t.CreateUserId.Equals(param.CreateUserId));
                }
                if (!param.LocalIP.IsEmpty())
                {
                    expression = expression.And(t => t.LocalIP.Equals(param.LocalIP));
                }
                if (!param.ClassName.IsEmpty())
                {
                    expression = expression.And(t => t.ClassName.Equals(param.ClassName));
                }
            }
            return expression;
        }

        #endregion


    }
}