using Newtonsoft.Json;
|
using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Linq.Expressions;
|
|
namespace HH.WCS.Hexafluo.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;
|
}
|
}
|
|
public static List<ConnectionConfig> connD = new List<ConnectionConfig>();
|
//创建SqlSugarClient
|
public SqlSugarClient GetInstance(string url = null)
|
{
|
//创建数据库对象
|
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
|
{
|
ConnectionString = url ?? Settings.SqlServer,
|
DbType = DbType.SqlServer,
|
IsAutoCloseConnection = true,
|
MoreSettings = new ConnMoreSettings()
|
{
|
IsWithNoLockQuery = true,//主查询
|
IsWithNoLockSubquery = true //子查询
|
},
|
InitKeyType = InitKeyType.Attribute//从特性读取主键自增信息
|
});
|
//
|
//db.MappingTables.Add("","");
|
|
//监控所有超过1秒的Sql
|
db.Aop.OnLogExecuted = (sql, p) =>
|
{
|
// Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
|
LogHelper.Info("执行的SQL", "ID:" + JsonConvert.SerializeObject(db.ContextID) + " sql: " + UtilMethods.GetSqlString(DbType.SqlServer, sql, p));
|
//执行时间超过1秒
|
if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
|
{
|
//代码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);
|
//};
|
|
return db;
|
}
|
}
|
|
|
|
public class SqlSugarHelper //不能是泛型类
|
{
|
//多库情况下使用说明：
|
//如果是固定多库可以传 new SqlSugarScope(List<ConnectionConfig>,db=>{}) 文档：多租户
|
//如果是不固定多库 可以看文档Saas分库
|
public static List<ConnectionConfig> conn = new List<ConnectionConfig>() {
|
new ConnectionConfig(){ConfigId="1",
|
DbType=DbType.SqlServer,
|
ConnectionString=Settings.SqlServer,
|
IsAutoCloseConnection=true,
|
MoreSettings=new ConnMoreSettings() {
|
IsWithNoLockQuery= true,//主查询
|
//DisableWithNoLockWithTran = false,//事务禁用NoLock
|
IsWithNoLockSubquery=true //子查询
|
},
|
|
InitKeyType = InitKeyType.Attribute},
|
};
|
|
//用单例模式
|
/// <summary>
|
/// 连接池
|
/// </summary>
|
|
public static SqlSugarScope Db = new SqlSugarScope(conn,
|
db =>
|
{
|
|
//(A)全局生效配置点，一般AOP和程序启动的配置扔这里面 ，所有上下文生效
|
//调试SQL事件，可以删掉
|
db.Aop.OnLogExecuting = (sql, pars) =>
|
{
|
// Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响
|
|
//获取原生SQL推荐 5.1.4.63 性能OK
|
// UtilMethods.GetNativeSql(sql, pars);
|
//Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));
|
// LogHelper.Info("执行的SQL", UtilMethods.GetNativeSql(sql, pars));
|
LogHelper.Info("执行的SQL", "ID:" + JsonConvert.SerializeObject(db.ContextID) + " sql: " + UtilMethods.GetSqlString(DbType.SqlServer, sql, pars));
|
|
//获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
|
//UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
|
|
};
|
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.OnLogExecuted = (sql, p) =>
|
{
|
// Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
|
// LogHelper.Info("执行的SQL", sql + "\r\n" + db.Utilities.SerializeObject(p.ToDictionary(it => it.ParameterName, it => it.Value)));
|
|
//执行时间超过1秒
|
if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
|
{
|
//代码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.Ado.IsDisableMasterSlaveSeparation=true;
|
|
//注意多租户 有几个设置几个
|
//db.GetConnection(i).Aop
|
});
|
}
|
}
|
