|
using SqlSugar;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Diagnostics;
|
using System.Linq;
|
using System.Net;
|
using System.Text;
|
using System.Text.RegularExpressions;
|
|
namespace CommonUtil
|
{
|
/// <summary>
|
/// 数据库上下文
|
/// </summary>
|
public class DbContext
|
{
|
/// <summary>
|
/// 用来处理Db操作
|
/// </summary>
|
protected SqlSugarClient Db;
|
|
/// <summary>
|
/// 初始化数据库上下文实例
|
/// </summary>
|
public DbContext()
|
{
|
List<DbConnection> dbList = ListGetDbConnection();
|
|
if (dbList.Count == 0)
|
{
|
throw new ArgumentNullException("未配置数据库连接字符串");
|
}
|
|
List<ConnectionConfig> connectionList = ListGetConnectionConfig(dbList);
|
|
Db = new SqlSugarClient(connectionList);
|
}
|
|
/// <summary>
|
/// 获取配置文件的数据库集合
|
/// </summary>
|
/// <returns></returns>
|
private List<DbConnection> ListGetDbConnection()
|
{
|
List<DbConnection> dbList = new List<DbConnection>();
|
|
if (ConfigUtil.IsExist(DbConstants.PROJECT_DB_CONNECTION))
|
{
|
//其他数据库链接配置
|
var dbConfigList = ConfigUtil.GetChildren(DbConstants.PROJECT_DB_CONNECTION);
|
|
var dbConfigListor = dbConfigList.GetEnumerator();
|
|
while (dbConfigListor.MoveNext())
|
{
|
var item = dbConfigListor.Current;
|
|
var key = ConfigUtil.GetValue<string>($"{item.Path}:{DbConstants.PROJECT_DB_CONNECTION_KEY}");
|
var connectionString = ConfigUtil.GetValue<string>($"{item.Path}:{DbConstants.PROJECT_DB_CONNECTION_CONNECTIONSTRING}");
|
var dbType = ConfigUtil.GetValue<string>($"{item.Path}:{DbConstants.PROJECT_DB_CONNECTION_DB_TYPE}");
|
|
dbList.Add(new DbConnection
|
{
|
Key = key,
|
ConnectionString = DMEncrypt.Encrypt.DecryptDES(connectionString),
|
DbType = dbType
|
});
|
}
|
|
return dbList;
|
}
|
else
|
{
|
throw new ArgumentNullException("未在配置文件中找到数据库连接关键字!!!");
|
}
|
}
|
|
/// <summary>
|
/// 获取数据库链接集合
|
/// </summary>
|
/// <param name="dbList">数据库配置集合</param>
|
/// <returns></returns>
|
private List<ConnectionConfig> ListGetConnectionConfig(List<DbConnection> dbList)
|
{
|
List<ConnectionConfig> connectionList = new List<ConnectionConfig>();
|
|
foreach (DbConnection item in dbList)
|
{
|
ConnectionConfig connectionConfig = new ConnectionConfig
|
{
|
ConfigId = item.Key,
|
ConnectionString = item.ConnectionString,
|
DbType = GetDbType(item.DbType),
|
IsAutoCloseConnection = true,
|
AopEvents = GetAopEvents()
|
};
|
|
connectionList.Add(connectionConfig);
|
}
|
|
return connectionList;
|
}
|
|
/// <summary>
|
/// 获取数据库类型,默认MySql
|
/// </summary>
|
/// <param name="type">MySql/SqlServer/Sqlite/Oracle/PostgreSQL</param>
|
/// <returns></returns>
|
private SqlSugar.DbType GetDbType(string type)
|
{
|
return Enum.TryParse(type, out SqlSugar.DbType dbType) ? dbType : SqlSugar.DbType.MySql;
|
}
|
|
/// <summary>
|
/// 获取AOP事件
|
/// </summary>
|
/// <returns></returns>
|
private AopEvents GetAopEvents()
|
{
|
AopEvents events = new AopEvents
|
{
|
//执行SQL前触发
|
OnLogExecuting = (sql, pars) =>
|
{
|
},
|
|
//执行SQL完触发
|
OnLogExecuted = (sql, pars) =>
|
{
|
//SQL执行时间
|
// TimeSpan excuteTime = Db.Ado.SqlExecutionTime;
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.AppendLine($"SQL：{sql}");
|
sb.AppendLine($"SQL参数：{string.Join(",", pars?.Select(it => it.ParameterName + ":" + it.Value))}");
|
// sb.AppendLine($"SQL执行时间：{excuteTime}");
|
|
#if DEBUG
|
Debug.WriteLine(sb.ToString(), "SQL执行日志");
|
#else
|
LogUtil.Info(sb.ToString(), "SQL执行日志");
|
#endif
|
},
|
|
//执行SQL出错触发
|
OnError = (exp) =>
|
{
|
StringBuilder sb = new StringBuilder();
|
|
sb.AppendLine($"SQL：{exp.Sql}");
|
sb.AppendLine($"SQL参数：{exp.Parametres}");
|
sb.AppendLine($"异常信息：{exp.Message}");
|
|
#if DEBUG
|
Debug.WriteLine(sb.ToString(), "SQL执行错误日志");
|
#else
|
LogUtil.Error(sb.ToString(), "SQL执行错误日志", exp);
|
#endif
|
},
|
|
////数据变化事件触发
|
//OnDiffLogEvent = it =>
|
//{
|
// var editBeforeData = it.BeforeData;
|
// var editAfterData = it.AfterData;
|
// var sql = it.Sql;
|
// var parameter = it.Parameters;
|
// var businessData = it.BusinessData;
|
// var time = it.Time;
|
// //enum insert 、update and delete
|
// var diffType = it.DiffType;
|
|
// StringBuilder sb = new StringBuilder();
|
|
// sb.AppendLine($"事件业务参数：{businessData}");
|
// sb.AppendLine($"修改前值：{Db.Utilities.SerializeObject(editBeforeData)}");
|
// sb.AppendLine($"修改为值：{Db.Utilities.SerializeObject(editAfterData)}");
|
|
// LogUtil.Info(sb.ToString(), "SQL执行日志");
|
|
// //Console.WriteLine(businessData);
|
// //Console.WriteLine(editBeforeData[0].Columns[1].Value);
|
// //Console.WriteLine("to");
|
// //Console.WriteLine(editAfterData[0].Columns[1].Value);
|
// //Write logic
|
//}
|
};
|
|
return events;
|
}
|
|
private string GetIPAddress()
|
{
|
string ip = "";
|
try
|
{
|
WebClient MyWebClient = new WebClient();
|
MyWebClient.Credentials = CredentialCache.DefaultCredentials;//获取或设置用于向Internet资源的请求进行身份验证的网络凭据
|
|
Byte[] pageData = MyWebClient.DownloadData("http://www.net.cn/static/customercare/yourip.asp"); //从指定网站下载数据
|
|
string pageHtml = Encoding.Default.GetString(pageData); //如果获取网站页面采用的是GB2312，则使用这句
|
|
//string pageHtml = Encoding.UTF8.GetString(pageData); //如果获取网站页面采用的是UTF-8，则使用这句
|
|
Regex reg = new Regex(@"(?<=<h2>).+?(?=</h2>)");
|
MatchCollection mc = reg.Matches(pageHtml);
|
|
foreach (Match m in mc)
|
{
|
Regex rx = new Regex(@"((?:(?:25[0-5]|2[0-4]\d|((1\d{2})|([1-9]?\d)))\.){3}(?:25[0-5]|2[0-4]\d|((1\d{2})|([1-9]?\d))))");
|
if (rx.IsMatch(m.Groups[0].Value))
|
{
|
ip = m.Groups[0].Value;
|
}
|
}
|
}
|
catch
|
{
|
|
}
|
return ip;
|
}
|
|
|
/// <summary>
|
/// 根据key获取实际的业务库数据连接
|
/// </summary>
|
/// <param name="SQLString">查询连接</param>
|
/// <param name="ConnectionString">测试库还是正式库连接</param>
|
/// <returns></returns>
|
private List<DbConnection> GetDBConnectionString(string SQLString, string ConnectionString)
|
{
|
List<DbConnection> dbList = new List<DbConnection>();
|
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
|
{
|
//数据库链接
|
//Mysql数据库
|
ConnectionString = DMEncrypt.Encrypt.DecryptDES(ConnectionString),
|
|
//设置数据库类型（记得随着链接的数据库类型而改动）
|
DbType = SqlSugar.DbType.MySql,
|
//自动释放数据务，如果存在事务，在事务结束后释放
|
IsAutoCloseConnection = true,
|
});
|
|
var dt = db.Ado.GetDataTable(SQLString);
|
if (dt == null && dt.Rows.Count == 0)
|
{
|
throw new ArgumentNullException("业务数据库连接不存在");
|
}
|
else
|
{
|
foreach (DataRow dem in dt.Rows)
|
{
|
dbList.Add(new DbConnection
|
{
|
Key = dem["key_name"].ToString(),
|
ConnectionString = DMEncrypt.Encrypt.DecryptDES(dem["encrypt_pwd"].ToString()),
|
DbType = dem["db_type"].ToString()
|
});
|
}
|
|
|
return dbList;
|
}
|
}
|
}
|
}
|
