SKMC.API-v3/SKMC.API/Common/DB/SqlSugarTool.cs

using SKMC.Api.Common.File;
using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using DbType = SqlSugar.DbType;
using SKMC.Api.Common.Logger;

namespace SKMC.Api.Common.DB
{
    /// <summary>
    /// SqlSugar的工具类
    /// </summary>
    public class SqlSugarTool
    {
        private static readonly ILogger log = LogFactory.Get();

        private static readonly AppConfig appConfig = AppConfig.Instance();

        /// <summary>
        /// 从配置库文件获取数据库实例
        /// </summary>
        /// <returns></returns>
        public static SqlSugarClient GetConfigDB() => GetDB("DB_CONFIG");

        /// <summary>
        /// 从运行库文件获取数据库实例
        /// </summary>
        /// <returns></returns>
        public static SqlSugarClient GetRuntimeDB() => GetDB("DB_RUNDATA");

        private static SqlSugarClient GetDB(string dbName)
        {
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = String.Format(appConfig.ReadStringValue(dbName, "connection.datasource"), System.Environment.CurrentDirectory),
                DbType = DbType.Sqlite,
                // 自动释放数据务，如果存在事务，在事务结束后释放
                IsAutoCloseConnection = appConfig.ReadBoolValue(dbName, "connection.autoclosed"),
                // 从数据库系统表读取主键信息中（InitKeyType.Attribute从实体特性中读取主键自增列信息）
                InitKeyType = InitKeyType.Attribute
                // 暂不设置数据库缓存
            });

            //  调试SQL，AOP ,日志
            db.Aop.OnLogExecuted = (sql, pars) => // SQL执行完事件
            {
                // 获取执行后的总毫秒数
                double sqlExecutionTotalMilliseconds = db.Ado.SqlExecutionTime.TotalMilliseconds;
            };
            db.Aop.OnLogExecuting = (sql, pars) => // SQL执行前事件。可在这里查看生成的sql
            {
                string tempSQL = LookSQL(sql, pars);
            };
            db.Aop.OnError = (exp) =>// 执行SQL 错误事件
            {
                // exp.sql exp.parameters 可以拿到参数和错误Sql  
                StringBuilder sb_SugarParameterStr = new StringBuilder("###SugarParameter参数为:");
                var parametres = exp.Parametres as SugarParameter[];
                if (parametres != null)
                {
                    sb_SugarParameterStr.Append(JsonConvert.SerializeObject(parametres));
                }

                StringBuilder sb_error = new StringBuilder();
                sb_error.AppendLine("SqlSugarClient执行sql异常信息:" + exp.Message);
                sb_error.AppendLine("###赋值后sql:" + LookSQL(exp.Sql, parametres));
                sb_error.AppendLine("###带参数的sql:" + exp.Sql);
                sb_error.AppendLine("###参数信息:" + sb_SugarParameterStr.ToString());
                sb_error.AppendLine("###StackTrace信息:" + exp.StackTrace);

                log.Error(sb_error.ToString());
            };

            db.Aop.OnExecutingChangeSql = (sql, pars) => // SQL执行前 可以修改SQL
            {
                // 判断update或delete方法是否有where条件。如果真的想删除所有数据，请where(p=>true)或where(p=>p.id>0)
                if (sql.TrimStart().IndexOf("delete ", StringComparison.CurrentCultureIgnoreCase) == 0)
                {
                    if (sql.IndexOf("where", StringComparison.CurrentCultureIgnoreCase) <= 0)
                    {
                        throw new Exception("delete删除方法需要有where条件！！");
                    }
                }
                else if (sql.TrimStart().IndexOf("update ", StringComparison.CurrentCultureIgnoreCase) == 0)
                {
                    if (sql.IndexOf("where", StringComparison.CurrentCultureIgnoreCase) <= 0)
                    {
                        throw new Exception("update更新方法需要有where条件！！");
                    }
                }

                return new KeyValuePair<string, SugarParameter[]>(sql, pars);
            };

            // db.Aop.OnDiffLogEvent = it =>//  数据库操作前和操作后的数据变化。
            // {
            //     var editBeforeData = it.BeforeData;
            //     var editAfterData = it.AfterData;
            //     var sql = it.Sql;
            //     var parameter = it.Parameters;
            //     var data = it.BusinessData;
            //     var time = it.Time;
            //     var diffType = it.DiffType;// 枚举值 insert 、update 和 delete 用来作业务区分

            //     日志方法
            // };

            // db.Ado.CommandTimeOut // 设置sql执行超时等待时间(毫秒单位)
            // db.Ado.Connection.ConnectionTimeout // 设置数据库连接等待时间(毫秒单位)

            return db;
        }

        ///<summary>
        ///查看赋值后的sql
        ///</summary>
        ///<param name="sql"></param>
        ///<param name="pars">参数</param>
        ///<returns></returns>
        private static string LookSQL(string sql, SugarParameter[] pars)
        {
            if (pars == null || pars.Length == 0) return sql;

            StringBuilder sb_sql = new StringBuilder(sql);
            var tempOrderPars = pars.Where(p => p.Value != null).OrderByDescending(p => p.ParameterName.Length).ToList();// 防止 @par1错误替换@par12
            for (var index = 0; index < tempOrderPars.Count; index++)
            {
                sb_sql.Replace(tempOrderPars[index].ParameterName, "'" + tempOrderPars[index].Value.ToString() + "'");
            }

            return sb_sql.ToString();
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">主键</param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static T GetModel<T>(object id, SqlSugarClient db)
        {
            return db.Queryable<T>().InSingle(id);
        }

        /// <summary>
        /// 从缓存中获取一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">主键</param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static T GetModel_WithCache<T>(object id, SqlSugarClient db)
        {
            return db.Queryable<T>().WithCache().InSingle(id);
        }


        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增int类型id,实体需要设置主键特性)(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int InsertModel<T>(T t, SqlSugarClient db) where T : class, new()
        {
            // Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return db.Insertable(t).
                // IgnoreColumns(p=>p.Equals("id",StringComparison.InvariantCultureIgnoreCase)).
                IgnoreColumns(true, false).ExecuteReturnIdentity();
        }

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增long类型id,实体需要设置主键特性)(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static long InsertModel_BigIdentity<T>(T t, SqlSugarClient db) where T : class, new()
        {
            // Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return db.Insertable(t).
                // IgnoreColumns(p=>p.Equals("id",StringComparison.InvariantCultureIgnoreCase)).
                IgnoreColumns(true, false).ExecuteReturnBigIdentity();
        }

        /// <summary>
        /// 添加实体数据,删除缓存,并返回主键值(主键为自增int类型id,实体需要设置主键特性)(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int InsertModel_RemoveDataCache<T>(T t, SqlSugarClient db) where T : class, new()
        {
            // Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return db.Insertable(t).
                // IgnoreColumns(p=>p.Equals("id",StringComparison.InvariantCultureIgnoreCase)).
                IgnoreColumns(true, false).RemoveDataCache().ExecuteReturnIdentity();
        }

        /// <summary>
        /// 添加实体数据，并返回主键值(主键为自增long类型id,实体需要设置主键特性)(为null字段不更新,注意model有默认值的情况)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static long InsertModel_BigIdentity_RemoveDataCache<T>(T t, SqlSugarClient db) where T : class, new()
        {
            // Where(true/*不插入null值的列*/,false/*不插入主键值*/)
            return db.Insertable(t).
                // IgnoreColumns(p=>p.Equals("id",StringComparison.InvariantCultureIgnoreCase)).
                IgnoreColumns(true, false).RemoveDataCache().ExecuteReturnBigIdentity();
        }

        /// <summary>
        /// 根据主键更新实体，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModelByKey<T>(T t, SqlSugarClient db) where T : class, new()
        {
            // 字段null，不进行更新
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件表达式更新实体(为null字段不更新,注意model有默认值的情况)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="expressionWhere">条件表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModelsIgnoreNull<T>(T t, Expression<Func<T, bool>> expressionWhere, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).Where(expressionWhere).ExecuteCommand();
        }

        /// <summary>
        /// 根据条件表达式更新实体(指定要更新的列)，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <param name="expressionWhere">条件表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModels<T>(T t, Expression<Func<T, object>> columns, Expression<Func<T, bool>> expressionWhere, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable(t).UpdateColumns(columns).Where(expressionWhere).ExecuteCommand();
        }

        /// <summary>
        /// 动态更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns">要更新的字段</param>
        /// <param name="expressionWhere">where条件表达式</param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static int Update<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> expressionWhere, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable<T>().SetColumns(columns).
                // IgnoreColumns(ignoreAllNullColumns: true).// 不能加此方法，会有“CommandText 属性尚未初始化”异常
                Where(expressionWhere).ExecuteCommand();
            /* 调用示例
             Update<Entity.SysAdmin>(p => new Entity.SysAdmin { photo = photo, Password = newPwd }
                ,p => p.ID == sm.id && p.Password == oldPwd ,db) > 0;
             */
        }

        /// <summary>
        /// 根据主键更新实体，返回影响条数(实体字段要有主键特性)并删除缓存，返回影响条数(实体字段要有主键特性)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModelByKey_RemoveDataCache<T>(T t, SqlSugarClient db) where T : class, new()
        {
            // 字段null，不进行更新
            return db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).RemoveDataCache().ExecuteCommand();
        }

        /// <summary>
        /// 根据条件表达式更新实体(指定要更新的列)并删除缓存，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="columns"></param>
        /// <param name="expressionWhere">条件表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int UpdateModels_RemoveDataCache<T>(T t, Expression<Func<T, object>> columns, Expression<Func<T, bool>> expressionWhere, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable(t).UpdateColumns(columns).Where(expressionWhere).RemoveDataCache().ExecuteCommand();
        }

        /// <summary>
        /// 动态更新,并删除缓存
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columns">要更新的字段</param>
        /// <param name="expressionWhere">where条件表达式</param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static int Update_RemoveDataCache<T>(Expression<Func<T, T>> columns, Expression<Func<T, bool>> expressionWhere, SqlSugarClient db) where T : class, new()
        {
            return db.Updateable<T>().SetColumns(columns).
                // IgnoreColumns(ignoreAllNullColumns: true).// 不能加此方法，会有“CommandText 属性尚未初始化”异常
                Where(expressionWhere).RemoveDataCache().ExecuteCommand();
            /* 调用示例
             Update<Entity.SysAdmin>(p => new Entity.SysAdmin { photo = photo, Password = newPwd }
                ,p => p.ID == sm.id && p.Password == oldPwd ,db) > 0;
             */
        }

        /// <summary>
        /// 删除ids集合条件的字符串(高效率写法,注意防止注入攻击)
        /// </summary>
        /// <param name="ids">ids为字符串 "1,2,3"或"1" 形式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <param name="key">主键字段</param>
        /// <returns></returns>
        public static bool DeleteByWhereSql_ids<T>(string ids, SqlSugarClient db, string key = "id") where T : class, new()
        {
            return db.Deleteable<T>().Where(string.Format(" {0} IN ({1})", key, ids)).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 根据条件表达式删除，返回影响条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">表达式</param>
        /// <param name="db">(事务的db对象)</param>
        /// <returns></returns>
        public static int DeleteModels<T>(Expression<Func<T, bool>> expression, SqlSugarClient db) where T : class, new()
        {
            return db.Deleteable<T>().Where(expression).ExecuteCommand();
        }
    }
}