xiaomiMes/MainForm/ClassFile/DbHelper/SqlHelper.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace MainForm.DbHelper
{
    /// <summary>
    /// 
    /// </summary>
    public class SqlHelper : PracticeContext
    {
        #region 查询

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <returns></returns>
        public List<T> Queryable<T>() where T : class, new()
        {
            return Db.Queryable<T>().ToList();
        }

        /// <summary>
        /// 根据表达式查询实体
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="expression">Where条件</param>
        /// <returns></returns>
        public List<T> Queryable<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            return Db.Queryable<T>().Where(expression).ToList();
        }

        /// <summary>
        /// 根据表达式查询第一条实体
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="expression">Where条件</param>
        /// <param name="orderFileds">排序字段</param>
        /// <returns></returns>
        public T QueryableAscFirst<T>(Expression<Func<T, bool>> expression, string orderFileds = null) where T : class, new()
        {
            if (orderFileds == null)
            {
                return Db.Queryable<T>().Where(expression).First();
            }
            return Db.Queryable<T>().Where(expression).OrderBy(orderFileds).First();
        }

        /// <summary>
        /// SQL语句查询
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="sql">SQL执行语句</param>
        /// <returns></returns>
        public List<T> SqlQueryable<T>(string sql) where T : class, new()
        {
            return Db.SqlQueryable<T>(sql).ToList();
        }

        /// <summary>
        /// SQL语句执行
        /// </summary>
        /// <param name="sql">SQL执行语句</param>
        /// <returns></returns>
        public bool SqlExecuteCommand(string sql)
        {
            if (string.IsNullOrEmpty(sql)) return true;

            try
            {
                int count = Db.Ado.ExecuteCommand(sql);

                return count > 0;
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        #endregion

        #region 新增

        /// <summary>
        /// 新增
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="insertObj">数据</param>
        /// <returns>状态</returns>
        public bool Insert<T>(T insertObj) where T : class, new()
        {
            if (insertObj == null) return true;

            try
            {
                return Db.Insertable(insertObj).ExecuteCommandIdentityIntoEntity();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="insertObjs">数据</param>
        /// <returns>状态</returns>
        public bool Insert<T>(List<T> insertObjs) where T : class, new()
        {
            if (!insertObjs.Any()) return true;

            try
            {
                //分页操作 ，如果不支持db.Fastest分页插入也是可以提升一下性能的
                Db.Utilities.PageEach(insertObjs, 100, pageList =>
                {
                    int result = Db.Insertable(pageList).ExecuteCommand();
                    if (result != pageList.Count)
                    {
                        throw new Exception($"批量新增失败，计划操作{pageList.Count}条，实际操作{result}条");
                    }
                });

                return true;
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        #endregion

        #region 修改

        /// <summary>
        /// 根据主键修改
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="UpdateObj">数据</param>
        /// <returns>状态</returns>
        public bool Update<T>(T UpdateObj) where T : class, new()
        {
            if (UpdateObj == null) return true;

            try
            {
                return Db.Updateable(UpdateObj).ExecuteCommandHasChange();
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        /// <summary>
        /// 根据主键修改实体指定列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="UpdateObj">数据</param>
        /// <param name="columns">需要更新的实体列</param>
        /// <returns></returns>
        public bool Update<T>(T UpdateObj, Expression<Func<T, object>> columns) where T : class, new()
        {
            if (UpdateObj == null) return true;

            try
            {
                return Db.Updateable(UpdateObj).UpdateColumns(columns).ExecuteCommandHasChange();
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        /// <summary>
        /// 根据主键批量修改
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="UpdateObjs">数据</param>
        /// <returns>状态</returns>
        public bool Update<T>(List<T> UpdateObjs) where T : class, new()
        {
            if (!UpdateObjs.Any()) return true;

            try
            {
                //分页操作 ，如果不支持db.Fastest分页插入也是可以提升一下性能的
                Db.Utilities.PageEach(UpdateObjs, 100, pageList =>
                {
                    int result = Db.Updateable(pageList).ExecuteCommand();
                    if (result != pageList.Count)
                    {
                        throw new Exception($"批量修改失败，计划操作{pageList.Count}条，实际操作{result}条");
                    }
                });

                return true;
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        #endregion

        #region 删除

        /// <summary>
        /// 根据主键删除
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <typeparam name="PkType">主键类型</typeparam>
        /// <param name="primaryKeyValue">主键</param>
        /// <returns>状态</returns>
        public bool Deleteable<T, PkType>(PkType primaryKeyValue) where T : class, new()
        {
            try
            {
                return Db.Deleteable<T>().In(primaryKeyValue).ExecuteCommandHasChange();
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        /// <summary>
        /// 根据表达式删除
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="expression">Where条件</param>
        /// <returns>状态</returns>
        public bool Deleteable<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            try
            {
                return Db.Deleteable<T>().Where(expression).ExecuteCommandHasChange();
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        /// <summary>
        /// 根据主键批量删除
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="UpdateObjs">数据</param>
        /// <returns>状态</returns>
        public bool Deleteable<T>(List<T> deleteObjs) where T : class, new()
        {
            if (!deleteObjs.Any()) return true;

            try
            {
                //分页操作 ，如果不支持db.Fastest分页插入也是可以提升一下性能的
                Db.Utilities.PageEach(deleteObjs, 100, pageList =>
                {
                    int result = Db.Deleteable(pageList).ExecuteCommand();
                    if (result != pageList.Count)
                    {
                        throw new Exception($"批量删除失败，计划操作{pageList.Count}条，实际操作{result}条");
                    }
                });

                return true;
            }
            catch (Exception e)
            {
                return ExceptionCapture(e);
            }
        }

        #endregion

        #region 事务  (private,后期扩展增删改方法直接把事务方法添加进去;或者根据需求public)

        /// <summary>
        /// 开始事务
        /// </summary>
        private void BeginTran()
        {
            Db.Ado.BeginTran();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        private void CommitTran()
        {
            Db.Ado.CommitTran();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        private void RollbackTran()
        {
            Db.Ado.RollbackTran();
        }

        #endregion

        #region 异常捕捉

        /// <summary>
        /// 异常捕捉
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        private bool ExceptionCapture(Exception ex)
        {
            Console.WriteLine("\n异常信息:\n{0}", ex.Message);

            return false;
        }

        #endregion
    }
}