福建佳通-流程优化

杨前锦

2025-07-01 a93b0e99036c24b9bd58c79bf5e7364b1ba28bae

 HH.WCS.Mobox3/HH.WCS.Mobox3.FJJT/util/SqlHelper.cs

New file
@@ -0,0 +1,288 @@
using Newtonsoft.Json;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Linq.Expressions;

namespace HH.WCS.Mobox3.FJJT.util
{
    //https://www.donet5.com/Home/Doc
    public class SqlHelper<T> where T : class, new()
    {
        public bool ExecuteSql(string sql) {
            try {
                var code = GetInstance().Ado.ExecuteCommand(sql);
                return code > 1;
            }
            catch (Exception ex) {
                return false;
            }
        }

        public bool Update(T model, string[] cols) {
            var db = GetInstance();
            return db.Updateable<T>(model).UpdateColumns(cols).ExecuteCommand() > 0;
        }
        public List<T> GetList(Expression<Func<T, bool>> where = null) {
            var db = GetInstance();
            if (where == null) {
                return db.Queryable<T>().ToList();
            }
            else {
                return db.Queryable<T>().Where(where).ToList();
            }
        }
        public T Get(Expression<Func<T, bool>> where, Expression<Func<T, object>> order, bool asc = false) {
            SqlSugarClient db = GetInstance();
            if (order == null) {
                return db.Queryable<T>().Where(where).Single();
            }
            else {
                return db.Queryable<T>().Where(where).OrderBy(order, asc ? OrderByType.Asc : OrderByType.Desc).First();
            }
        }
        public T Get(Expression<Func<T, bool>> where, Expression<Func<T, object>> orderBy = null) {
            try {
                var db = GetInstance();
                T model = null;
                if (orderBy != null) {
                    model = db.Queryable<T>().Where(where).OrderBy(orderBy, OrderByType.Desc).First();//查询表的所有
                }
                else {
                    model = db.Queryable<T>().Where(where).First();
                }
                return model;
            }
            catch (Exception ex) {
                Console.WriteLine(ex.Message);
                return default(T);
            }
        }
        public bool Update(T model) {
            var db = GetInstance();
            return db.Updateable<T>(model).ExecuteCommand() > 0;
        }
        public bool Insert(T model) {
            try {
                var db = GetInstance();
                return db.Insertable<T>(model).ExecuteCommand() > 0;
            }
            catch (Exception ex) {
                return false;
            }
        }

        //删除指定条件数据
        public bool Deleteable(Expression<Func<T, bool>> where) {
            try {
                var db = GetInstance();
                return db.Deleteable<T>().Where(where).ExecuteCommand() > 0;
            }
            catch (Exception ex) {
                return false;
            }
        }


        //创建SqlSugarClient 
        public SqlSugarClient GetInstance(string url = "") {
            //创建数据库对象
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {
                ConnectionString = string.IsNullOrEmpty(url) ? Settings.OracleServer : url,
                //ConnectionString = @"Data Source=192.168.1.198\sql2008;Initial Catalog=OIMobox;User ID=sa;Password=sa@2015",
               /* DbType = DbType.SqlServer,*/
                //ConnectionString = @"localhost/OIMobox;User ID=system;Password=Am123123",
                //ConnectionString = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OIMobox)));User Id=system;Password=Am123123;",
                DbType = DbType.Oracle,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
            });
            //
            //db.MappingTables.Add("","");

            //监控所有超过1秒的Sql
            db.Aop.OnLogExecuted = (sql, p) => {
                //执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1) {
                    Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
                }
                //相当于EF的 PrintToMiniProfiler
            };
            db.Aop.DataExecuted = (value, entity) => {
                entity.EntityColumnInfos.ToList().ForEach(a => {
                    var pvalue = entity.GetValue(a.PropertyName);
                    if (pvalue != null && pvalue.GetType() == typeof(String)) {
                        entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
                    }
                });
            };

            /* db.Aop.OnLogExecuting = (sql, pars) =>
             {
                 Console.WriteLine(sql);
                 foreach (var param in pars)
                 {
                     Console.WriteLine($"{param.ParameterName}: {param.Value}");
                 }
             };*/
            //据转换 (ExecuteCommand才会拦截，查询不行)
            //db.Aop.DataExecuting = (value, entity) => {
            //    //var val=entity.EntityColumnInfo
            //    Console.WriteLine(entity.EntityName);
            //};

            db.Aop.OnExecutingChangeSql = (sql, pars) => //可以修改SQL和参数的值
            {
                if (pars != null && db.CurrentConnectionConfig.DbType == DbType.Oracle && pars.Any())
                {
                    var input = sql;
                    Stack<int> stack = new Stack<int>();
                    List<string> results = new List<string>();
                    for (int i = 0; i < input.Length; i++)
                    {
                        if (input[i] == '(')
                        {
                            stack.Push(i);
                        }
                        else if (input[i] == ')')
                        {
                            if (stack.Count > 0)
                            {
                                int start = stack.Pop();
                                string content = input.Substring(start + 1, i - start - 1);
                                results.Add(content);
                            }
                        }
                    }
                    foreach (var p in pars)
                    {
                        if (p.DbType == System.Data.DbType.String)
                        {
                            var result = results.Find(x => x.Contains(p.ParameterName) && x.Count(c => c == '=') == 1);
                            if (result != null && !result.Contains("trim("))
                            {
                                string[] parts = result.Split('=');
                                if (parts.Length > 1)
                                {
                                    string Field = parts[0];
                                    sql = sql.Replace(result, $"trim({Field}) = {p.ParameterName}");
                                }
                            }
                        }
                    }
                }
                return new KeyValuePair<string, SugarParameter[]>(sql, pars);
            };
            return db;
        }


        public SqlSugarClient GetOracleInstance(string url = "")
        {
            //创建数据库对象
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                //ConnectionString = string.IsNullOrEmpty(url) ? Settings.SqlServer : url,
                //ConnectionString = @"Data Source=192.168.1.198\sql2008;Initial Catalog=OIMobox;User ID=sa;Password=sa@2015",
                //DbType = DbType.SqlServer,
                //ConnectionString = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OIMobox)));User Id=system;Password=Am123123;",
               /* ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.3.18.224)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=db1.hec.prod)));User Id=WMS_X5;Password=WMS_X5_2617;",*/
                ConnectionString = @url,
                DbType = DbType.Oracle,
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
            });
            //
            //db.MappingTables.Add("","");

            //监控所有超过1秒的Sql
            db.Aop.OnLogExecuted = (sql, p) => {
                //执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
                {
                    Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
                }
                //相当于EF的 PrintToMiniProfiler
            };

            db.Aop.DataExecuted = (value, entity) => {
                entity.EntityColumnInfos.ToList().ForEach(a => {
                    var pvalue = entity.GetValue(a.PropertyName);
                    if (pvalue != null && pvalue.GetType() == typeof(String))
                    {
                        entity.SetValue(a.PropertyName, pvalue.ToString().Trim());
                    }
                });
            };

            //据转换 (ExecuteCommand才会拦截，查询不行)
            //db.Aop.DataExecuting = (value, entity) => {
            //    //var val=entity.EntityColumnInfo
            //    Console.WriteLine(entity.EntityName);
            //};
            db.Aop.OnExecutingChangeSql = (sql, pars) => //可以修改SQL和参数的值
            {
                if (pars.Any())
                {
                    int index = sql.IndexOf("WHERE") + 6;
                    var input = string.Join("", sql.Skip(index));
                    // 使用栈来匹配成对的括号
                    Stack<int> stack = new Stack<int>();
                    List<string> results = new List<string>();
                    for (int i = 0; i < input.Length; i++)
                    {
                        if (input[i] == '(')
                        {
                            stack.Push(i);
                        }
                        else if (input[i] == ')')
                        {
                            if (stack.Count > 0)
                            {
                                int start = stack.Pop();
                                string content = input.Substring(start + 1, i - start - 1);
                                results.Add(content);
                            }
                        }
                    }
                    foreach (var p in pars)
                    {
                        if (p.DbType == System.Data.DbType.String)
                        {
                            Console.WriteLine(p.ParameterName);
                            var result = results.Find(x => x.Contains(p.ParameterName) && x.Count(c => c == '=') == 1);
                            if (result != null && !result.Contains("trim("))
                            {
                                string[] parts = result.Split('"');
                                if (parts.Length > 1)
                                {
                                    string Field = parts[1];
                                    sql = sql.Replace(result, $"trim(\"{Field}\") = {p.ParameterName}");
                                }
                            }
                        }
                    }
                }
                return new KeyValuePair<string, SugarParameter[]>(sql, pars);
            };
            return db;
        }
    }

}