BaseDal.cs

using PT.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PT.DAL
{
    public class BaseDal<T, V> where T : class, new() where V : class, new()
    {

        #region 通用公共方法

        #region 查询

        /// <summary>
        /// 查询表所有数据
        /// </summary>
        /// <returns></returns>
        public virtual List<T> Queryable()
        {
            return SqlSugarHelper.Db.Queryable<T>().ToList();
        }


        /// <summary>
        /// 查询表未删除数据
        /// </summary>
        /// <returns></returns>
        public virtual List<T> QueryableNotDeleted()
        {
            return SqlSugarHelper.Db.Queryable<T>().Where("IsDelete=@IsDelete", new { IsDelete = 0 }).OrderBy("Id desc").ToList();
        }

        /// <summary>
        /// 以id数组集查询
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public virtual List<T> QueryableById(int[] ids)
        {
            return SqlSugarHelper.Db.Queryable<T>().Where("Id in(@ids)", new { ids = ids }).OrderBy("Id desc").ToList();
        }


        /// <summary>
        /// 查询表未删除数据  按条件查询
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual List<T> QueryableNotDeleted(T t)
        {
            return SqlSugarHelper.Db.Queryable<T>().Where("IsDelete=@IsDelete", new { IsDelete = 0 }).OrderBy("Id desc").ToList();
        }

        /// <summary>
        /// 按条件查询一行
        /// </summary>
        /// <param name="whereString"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual T QueryableWhereFirst(string whereString, object parameters)
        {
            //string whereString = "name=@name";
            //object parameters = new { name = "xx" };
            return SqlSugarHelper.Db.Queryable<T>().Where(whereString, parameters).First();
        }

        /// <summary>
        /// 单主键查询
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public virtual T QueryableInSingle(string Id)
        {
            //单主键查询
            return SqlSugarHelper.Db.Queryable<T>().InSingle(Id);
        }


        /// <summary>
        /// 查询单条  没有返回Null，如果结果大于1条会抛出错误 （根据业务重写）
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual UserList QueryableSingle(UserList t)
        {
            ////没有返回Null，如果结果大于1条会抛出错误
            return SqlSugarHelper.Db.Queryable<UserList>().Single(p => p.LoginID == t.LoginID && p.LoginPwd == t.LoginPwd && p.IsDelete == false);
        }


        /// <summary>
        /// 是否存在记录
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual bool IsExist(T t)
        {
            string whereString = "name=@name";
            object parameters = new { name = "xx" };
            return SqlSugarHelper.Db.Queryable<T>().Where(whereString, parameters).Any();
        }

        #endregion

        #region 插入


        /// <summary>
        /// 插入 忽略null列
        /// </summary>
        /// <param name="t"></param>
        /// <returns>返回自增id</returns>
        public virtual int InsertableIgnoreColumns(T t)
        {
            return SqlSugarHelper.Db.Insertable(t).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnIdentity();
        }


        /// <summary>
        /// 插入 忽略null列
        /// </summary>
        /// <param name="ts"></param>
        /// <returns>返回影响行数</returns>
        public virtual int InsertableIgnoreColumns(List<T> ts)
        {
            int n = 0;
            int Index = 0;
            //ignoreNullColumn 不支持批量操作   需要打包提交
            foreach (var item in ts)
            {
                SqlSugarHelper.Db.Insertable(item).IgnoreColumns(ignoreNullColumn: true).AddQueue();

                if (Index % 200 == 0 && Index > 1)
                {
                    n += SqlSugarHelper.Db.SaveQueues();
                }

                Index += 1;
            }
            n += SqlSugarHelper.Db.SaveQueues();
            return n;
        }

        #endregion

        #region 更新


        /// <summary>
        /// 更新 根据主键
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public virtual int Updateable(List<T> ts)
        {
            return SqlSugarHelper.Db.Updateable(ts).ExecuteCommand();

        }

        /// <summary>
        /// 更新 根据主键 忽略null列
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual int UpdateableIgnoreColumns(T t)
        {

            return SqlSugarHelper.Db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();

        }


        /// <summary>
        /// 批量更新 根据主键 忽略null列
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public virtual int UpdateableIgnoreColumns(List<T> ts)
        {
            int n = 0;
            int Index = 0;
            //ignoreNullColumn 不支持批量操作   需要打包提交
            foreach (var item in ts)
            {
                SqlSugarHelper.Db.Updateable(item).IgnoreColumns(ignoreAllNullColumns: true).AddQueue();
                if (Index % 200 == 0 && Index > 1)
                {
                    n += SqlSugarHelper.Db.SaveQueues();
                }

                Index += 1;
            }
            n += SqlSugarHelper.Db.SaveQueues();
            return n;

        }


        /// <summary>
        /// 更新 根据自定义条件 忽略null列  （根据业务重写）
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual int UpdateableIgnoreColumnsWhere(T t)
        {

            //return SqlSugarHelper.Db.Updateable(t).Where(p => p.xx == t.xx && p.IsDelete == false).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
            return 0;


        }


        /// <summary>
        /// 主键等于默认值插入否则更新  id=0插入 id不等于0更新
        /// </summary>
        /// <param name="list"></param>
        /// <returns>返回影响行数</returns>
        public virtual int InsertableDefaultAddElseUpdate(List<T> list)
        {

            //会更新所有字段 有的字段不需要更新的设置不了
            return SqlSugarHelper.Db.Storageable(list).DefaultAddElseUpdate().ExecuteCommand();
        }

        #endregion

        #region 删除

        /// <summary>
        /// 删除 根据主键
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual int Deleteable(T t)
        {

            return SqlSugarHelper.Db.Deleteable<T>().Where(t).ExecuteCommand();
        }



        /// <summary>
        /// 逻辑删除 根据主键
        /// </summary>
        /// <param name="ModifyBy">修改人</param>
        /// <param name="Ids">删除id集合</param>
        /// <returns></returns>
        public virtual int DeleteableLogic(string ModifyBy, List<int> Ids)
        {

            return SqlSugarHelper.Db.Deleteable<T>().In(Ids)
    .IsLogic()
    .ExecuteCommand("IsDelete", 1, "ModifyDate", "ModifyBy", ModifyBy);
        }

        #endregion

        #endregion

        #region 业务查询,根据业务需要重写方法



        /// <summary>
        /// 视图 多表联查 查询表未删除数据 （根据业务重写）
        /// </summary>
        /// <returns></returns>
        public virtual List<UserListV> QueryableNotDeletedV()
        {
            //Type t = typeof(T);

            var list = SqlSugarHelper.Db.Queryable<UserList>()
               .LeftJoin<GroupList>((a, b) => (a.GroupId == b.Id && b.IsDelete == false))
               .Where((a, b) => a.IsDelete == false)
               .OrderBy(a => a.Id, OrderByType.Desc)
               .Select((a, b) => new UserListV()
               {
                   Id = a.Id,
               },
               true)//true表示 其余字段自动映射，根据字段名字
              .ToList();
            return list;
        }


        /// <summary>
        /// 视图 多表联查 查询表未删除数据 按条件查询 （根据业务重写）
        /// </summary>
        /// <param name="v"></param>
        /// <returns></returns>
        public virtual List<IPListV> QueryableNotDeletedV(IPListV v)
        {
            //Type t = typeof(T);

            var exp = Expressionable.Create<IPList, PurposeList>()
                .And((a, b) => a.IsDelete == false)
             .AndIF(!string.IsNullOrWhiteSpace(v.Floor), (a, b) => a.Floor == v.Floor)
             .AndIF(!string.IsNullOrWhiteSpace(v.UserName), (a, b) => a.UserName.Contains(v.UserName))
             .AndIF(!string.IsNullOrWhiteSpace(v.AssgnName), (a, b) => a.Assgn.Contains(v.AssgnName))
             .AndIF(!string.IsNullOrWhiteSpace(v.IP), (a, b) => a.IP.Contains(v.IP))
             .ToExpression();//注意 这一句 不能少


            var list = SqlSugarHelper.Db.Queryable<IPList>()
   .LeftJoin<PurposeList>((a, b) => (a.PurposeId == b.PID && b.IsDelete == false))

   .Where(exp)
   .OrderBy(a => a.Id, OrderByType.Desc)
   .Select((a, b) => new IPListV()
   {
       Id = a.Id,
       UserName = a.UserName,
       PurposeName = b.Name,

   },
   true)//true表示 其余字段自动映射，根据字段名字
  .ToList();
            return list;
        }


        /// <summary>
        /// 视图 多表联查 分页查询 查询表未删除数据 按条件查询 （根据业务重写）
        /// </summary>
        /// <param name="where"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public virtual List<IPListV> QueryablePageList(IPListV where, int pageIndex, int pageSize, out int totalCount)
        {
            //Type t = typeof(T);

            totalCount = 0;

            var exp = Expressionable.Create<IPList, PurposeList>()
                .And((a, b) => a.IsDelete == false)
             .AndIF(!string.IsNullOrWhiteSpace(where.Floor), (a, b) => a.Floor == where.Floor)
             .AndIF(!string.IsNullOrWhiteSpace(where.IP), (a, b) => a.IP.Contains(where.IP))

             .AndIF(where.SearchType == "UserName" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.UserName.Contains(where.SearchVal))
             .AndIF(where.SearchType == "AssgnName" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.Assgn.Contains(where.SearchVal))
             .AndIF(where.SearchType == "IPAddress" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.IP == where.SearchVal.Trim())
             .AndIF(where.SearchType == "MacAddress" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.MacAddress.Contains(where.SearchVal))
             .AndIF(where.SearchType == "NodeName" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.NodeName.Contains(where.SearchVal))
             .ToExpression();//注意 这一句 不能少


            var list = SqlSugarHelper.Db.Queryable<IPList>()
   .LeftJoin<PurposeList>((a, b) => (a.PurposeId == b.PID && b.IsDelete == false))

   .Where(exp)
   //.OrderBy(a => a.Id, OrderByType.Desc)
   .Select((a, b) => new IPListV()
   {
       Id = a.Id,
       UserName = a.UserName,
       PurposeName = b.Name,

   },
   true)//true表示 其余字段自动映射，根据字段名字
  .ToPageList(pageIndex, pageSize, ref totalCount);
            return list;
        }

        #endregion

    }
}