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;
|
}
|
}
|
|
}
|
