From 06a8b6963d6d4f229d4e196b4b09ce57e2cbf2dd Mon Sep 17 00:00:00 2001
From: 杨前锦 <1010338399@qq.com>
Date: 星期四, 24 七月 2025 17:21:40 +0800
Subject: [PATCH] 印尼佳通-GT服务开发
---
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)));
+ //浠ｇ爜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 鑾峰彇涓婂眰鏂规硶鐨勪俊鎭�+ }
+ //鐩稿綋浜嶦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)));
+ //浠ｇ爜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 鑾峰彇涓婂眰鏂规硶鐨勪俊鎭�+ }
+ //鐩稿綋浜嶦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