From c8f338feee0b6003d8f069b1d37fd9b90dd1b7f4 Mon Sep 17 00:00:00 2001 From: 杨前锦 <1010338399@qq.com> Date: 星期一, 07 七月 2025 17:31:38 +0800 Subject: [PATCH] 印尼佳通优化 --- HH.WCS.Mobox3/HH.WCS.Mobox3.FJJT/util/SqlHelper.cs | 288 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 288 insertions(+), 0 deletions(-) diff --git a/HH.WCS.Mobox3/HH.WCS.Mobox3.FJJT/util/SqlHelper.cs b/HH.WCS.Mobox3/HH.WCS.Mobox3.FJJT/util/SqlHelper.cs new file mode 100644 index 0000000..2829ef5 --- /dev/null +++ b/HH.WCS.Mobox3/HH.WCS.Mobox3.FJJT/util/SqlHelper.cs @@ -0,0 +1,288 @@ +锘縰sing 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))); + //浠g爜CS鏂囦欢鍚�+ var fileName = db.Ado.SqlStackTrace.FirstFileName; + //浠g爜琛屾暟 + var fileLine = db.Ado.SqlStackTrace.FirstLine; + //鏂规硶鍚�+ var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName; + //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 鑾峰彇涓婂眰鏂规硶鐨勪俊鎭�+ } + //鐩稿綋浜嶦F鐨�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))); + //浠g爜CS鏂囦欢鍚�+ var fileName = db.Ado.SqlStackTrace.FirstFileName; + //浠g爜琛屾暟 + var fileLine = db.Ado.SqlStackTrace.FirstLine; + //鏂规硶鍚�+ var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName; + //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 鑾峰彇涓婂眰鏂规硶鐨勪俊鎭�+ } + //鐩稿綋浜嶦F鐨�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; + } + } + +} -- Gitblit v1.9.1