YIHE_API/MadRunFabric.../Repositories/BaseSqlRepos...

using MadRunFabric.Common.DbContext;
using MadRunFabric.Common.IRepositories;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Common;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace MadRunFabric.Common.Repositories
{
    public class BaseSqlRepository<T> : BaseSqlContext, ISqlRepository<T> where T : class, new()
    {
        /// <summary>
        /// 获取数量
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public async Task<int> GetCount(Expression<Func<T, bool>> whereExpression = null)
        {
            if (whereExpression != null)
                return await Db.Queryable<T>().With(SqlWith.NoLock).CountAsync(whereExpression);
            else
                return await Db.Queryable<T>().With(SqlWith.NoLock).CountAsync();

        }

        /// <summary>
        /// 获取所有
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> GetList()
        {

            return await Db.Queryable<T>().With(SqlWith.NoLock).ToListAsync();
            //return CurrentCurrentDB.GetList();
        }
        public async Task<List<T>> GetListALL(Expression<Func<T, bool>> whereExpression)
        {
            return await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).ToListAsync();
        }

        public async Task<List<T>> GetListALL(List<IConditionalModel> conModels, string orderby)
        {
            return await Db.Queryable<T>().With(SqlWith.NoLock).Where(conModels).OrderBy(orderby).ToListAsync();
        }
        public async Task<List<T>> GetListALL(Expression<Func<T, bool>> whereExpression, Expression<Func<T, object>> orderExpression, OrderByType ordertype = OrderByType.Asc)
        {
            return await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).OrderBy(orderExpression, ordertype).ToListAsync();
        }

        /// <summary>
        /// 分页获取所有
        /// </summary>
        /// <param name="conModels"></param>
        /// <param name="pagemodel"></param>
        /// <returns></returns>
        public async Task<PageData<T>> GetListByPage(List<IConditionalModel> conModels, System.Common.MyPageModel pagemodel, string orderby)
        {
            var list = await Db.Queryable<T>().With(SqlWith.NoLock).Where(conModels).OrderBy(orderby).ToPageListAsync(pagemodel.PageIndex, pagemodel.PageSize, pagemodel.PageCount);
            PageData<T> pd = new PageData<T>();
            pd.Rows = list;
            pd.Totals = pagemodel.PageCount;
            return pd;// CurrentCurrentDB.GetPageList(conModels, pagemodel);
        }

        public async Task<PageData<T>> GetListByPage(List<IConditionalModel> conModels, MyPageModel pagemodel)
        {
            var list = await Db.Queryable<T>().With(SqlWith.NoLock).Where(conModels).ToPageListAsync(pagemodel.PageIndex, pagemodel.PageSize, pagemodel.PageCount);
            PageData<T> pd = new PageData<T>();
            pd.Rows = list;
            pd.Totals = pagemodel.PageCount;
            return pd;// CurrentCurrentDB.GetPageList(conModels, pagemodel);
        }

        public async Task<PageData<T>> GetListByPage(Expression<Func<T, bool>> whereExpression, MyPageModel pagemodel)
        {
            var list = await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).ToPageListAsync(pagemodel.PageIndex, pagemodel.PageSize, pagemodel.PageCount);
            PageData<T> pd = new PageData<T>();
            pd.Rows = list;
            pd.Totals = pagemodel.PageCount;
            return pd;// CurrentCurrentDB.GetPageList(conModels, pagemodel);
        }

        public async Task<PageData<T>> GetListByPage(Expression<Func<T, bool>> whereExpression, MyPageModel pagemodel, string orderby)
        {
            var list = await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).OrderBy(orderby).ToPageListAsync(pagemodel.PageIndex, pagemodel.PageSize, pagemodel.PageCount);
            PageData<T> pd = new PageData<T>();
            pd.Rows = list;
            pd.Totals = pagemodel.PageCount;
            return pd;// CurrentCurrentDB.GetPageList(conModels, pagemodel);

        }


        public async Task<T> GetSingle(Expression<Func<T, bool>> whereExpression)
        {
            //FirstAsync替换SingleAsync 保证正常运行，但是可能结果会不一样。
            return await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).FirstAsync();
            //return CurrentCurrentDB.GetSingle(whereExpression);
        }

        public async Task<T> GetFirst(Expression<Func<T, bool>> whereExpression, Expression<Func<T, object>> expression = null, OrderByType type = OrderByType.Asc)
        {
            //FirstAsync替换SingleAsync 保证正常运行，但是可能结果会不一样。
            if (expression == null)
                return await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).FirstAsync();
            else
                return await Db.Queryable<T>().With(SqlWith.NoLock).Where(whereExpression).OrderBy(expression, type).FirstAsync();
            //return CurrentCurrentDB.GetSingle(whereExpression);

        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public async Task<int> Add(T obj)
        {
            return await Db.Insertable<T>(obj).ExecuteReturnIdentityAsync();
            //return CurrentCurrentDB.InsertReturnIdentity(obj);
        }

        /// <summary>
        /// 插入并返回影响行数
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public async Task<bool> AddReturnCount(T obj)
        {
            int res = await Db.Insertable<T>(obj).ExecuteCommandAsync();
            return res > 0;
        }
        /// <summary>
        /// 插入并返回实体
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public async Task<T> AddAndUpdate(T obj, Expression<Func<T, object>> UpdateColumns = null)
        {
            if (UpdateColumns == null)
                return await Db.Saveable<T>(obj).ExecuteReturnEntityAsync();
            else
                return await Db.Saveable<T>(obj).UpdateColumns(UpdateColumns).ExecuteReturnEntityAsync();
        }

        /// <summary>
        /// 插入并返回实体
        /// </summary>
        /// <param name="obj">更新插入实体</param>
        /// <param name="UpdateColumns">更新字段  null全部</param>
        /// <returns></returns>
        public async Task<T> AddAndUpdateList(List<T> obj, Expression<Func<T, object>> UpdateColumns = null)
        {
            if (UpdateColumns == null)
                return await Db.Saveable<T>(obj).ExecuteReturnEntityAsync();
            else
                return await Db.Saveable<T>(obj).UpdateColumns(UpdateColumns).ExecuteReturnEntityAsync();
        }
        /// <summary>
        /// 添加排除某列
        /// </summary>

        /// <param name="obj"></param>
        /// <returns></returns>
        public async Task<int> AddIgnore(T obj, Expression<Func<T, object>> IgnoreColumns)
        {
            return await Db.Insertable<T>(obj).IgnoreColumns(IgnoreColumns).ExecuteReturnIdentityAsync();
            //return CurrentCurrentDB.InsertReturnIdentity(obj);
        }

        /// <summary>
        /// 强势插入，插入自增值 
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="isStrong">是否强势</param>
        /// <returns></returns>
        public async Task<int> AddStrong(T obj, bool isStrong = false)
        {
            return await Db.Insertable<T>(obj).IgnoreColumns(true, isStrong).ExecuteReturnIdentityAsync();
            //return CurrentCurrentDB.InsertReturnIdentity(obj);
        }
        /// <summary>
        /// 批量添加
        /// </summary>
        /// <param name="objlist"></param>
        /// <returns></returns>
        public async Task<bool> AddMany(List<T> objlist)
        {
            var res = await Db.Insertable<T>(objlist).ExecuteCommandAsync();
            return res > 0;
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <returns></returns>
        public async Task<bool> Delete(Expression<Func<T, bool>> whereExpression)
        {
            return await Db.Deleteable<T>(whereExpression).ExecuteCommandHasChangeAsync();
            //return CurrentCurrentDB.Delete(whereExpression);
        }

        /// <summary>
        /// 实体更新
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> Update(T obj)
        {
            return await Db.Updateable<T>(obj).ExecuteCommandHasChangeAsync();
            //return CurrentCurrentDB.Update(obj);
        }
        /// <summary>
        /// 条件更新  不传对象写法  表要有主键
        /// </summary>
        /// <param name="UpdateColumnsExpression"></param>
        /// <param name="whereExpression">【q=>q.Id='aaa'】成功  【q=>ids.Contains(q.Id)】接口工具测线上失败 线上直接调接口成功 ids=aaa,bbb  ids数组才可解析成in</param>
        /// <returns></returns>
        public async Task<bool> Update(Expression<Func<T, T>> UpdateColumnsExpression, Expression<Func<T, bool>> whereExpression)
        {
            //KeyValuePair<string,List<SugarParameter>> sql = Db.Updateable<T>().SetColumns(UpdateColumnsExpression).Where(whereExpression).ToSql();
            //string sqlkey = sql.Key;
            //List<SugarParameter> sqllist = sql.Value;
            //foreach (SugarParameter s in sqllist)
            //{
            //  string a=  s.Value.ToString();
            //    string b = s.ParameterName.ToString();
            //}
            return await Db.Updateable<T>().SetColumns(UpdateColumnsExpression).Where(whereExpression).ExecuteCommandHasChangeAsync();
            //return CurrentCurrentDB.Update(obj);
        }
        /// <summary>
        /// 批量整条更新
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public async Task<bool> UpdateList(List<T> obj)
        {
            return await Db.Updateable<T>(obj).ExecuteCommandHasChangeAsync();
            //return CurrentCurrentDB.Update(obj);
        }

        public async Task<System.Data.DataTable> GetTableSugar(string sql, SugarParameter[] sugarParameter = null)
        {
            if (sugarParameter == null)
            { return await Db.Ado.GetDataTableAsync(sql); }
            else
            { return await Db.Ado.GetDataTableAsync(sql, sugarParameter); }
        }

        public async Task<int> ExecuteCommandSugar(string sql, SugarParameter[] sugarParameter = null)
        {
            if (sugarParameter == null)
            { return await Db.Ado.ExecuteCommandAsync(sql); }
            else
            { return await Db.Ado.ExecuteCommandAsync(sql, sugarParameter); }
        }

        /// <summary>
        /// 批量指定字段更新  传对象写法
        /// </summary>
        /// <param name="objColumns">model => new { model.Name }</param>
        /// <returns></returns>
        [Obsolete]
        public async Task<bool> UpdateListToColumns(List<T> obj, Expression<Func<T, object>> objColumns)
        {
            return await Db.Updateable<T>(obj).UpdateColumns(objColumns).ExecuteCommandHasChangeAsync();
            //return CurrentCurrentDB.Update(obj);            
        }
    }
}