/// <summary>
/// 守护线程
/// </summary>
private void GuardThread()
{
while (isStart)
{
try
{
//启动获取数据线程
if (dataThread == null || !dataThread.IsAlive)
{
dataThread = new Thread(new ThreadStart(DataThread))
{
Name = "DataThread",
IsBackground = true
};
dataThread.Start();
}
}
catch (ThreadAbortException)
{
Log4NetUtil.Info("手动停止守护线程");
break;
}
catch (Exception ex)
{
Log4NetUtil.Error("守护线程运行错误，信息为：" + ex.Message);
}
finally
{
Thread.Sleep(50);
}
}
}
/// <summary>
/// 执行SQL语句，返回受影响的行数 (无事物)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns>受影响的行数</returns>
public int Execute(string sql, OracleParameter[] sqlParams, int timeOut, out string errorMsg)
{
int intResult = 0;
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (OracleParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
conn.Open();
intResult = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(intResult);
}
/// <summary>
/// 数据获取线程
/// </summary>
private void DataThread()
{
while (isStart)
{
try
{
if (CacheData.GetCount() > 0)
{
Command command = (Command)CacheData.GetDataFromQueue();
if (command != null)
{
DataForwardMethod(command);
}
}
}
catch (ThreadAbortException)
{
Log4NetUtil.Info("手动停止操作线程");
break;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Log4NetUtil.Error(ex.GetType().ToString() + ":" + ex.Message);
}
finally
{
Thread.Sleep(50);
}
}
}
/// <summary>
/// 执行查询操作，返回结果集的第一行第一列 (object类型)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns>object 对象</returns>
public object QueryObj(string sql, DbParameter[] sqlParams, int timeOut, out string errorMsg)
{
DbConnection conn = null;
DbCommand cmd = null;
object obj = null;
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (DbParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
conn.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(obj);
}
/// <summary>
/// 执行SQL语句，返回受影响的行数。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns></returns>
public int ExecuteTran(string sql, out string errorMsg)
{
int intResult = 0;
SQLiteConnection conn = null;
SQLiteCommand cmd = new SQLiteCommand();
SQLiteTransaction sqlTran = null;
errorMsg = string.Empty;
lock (padlock)
{
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
conn.Open();
sqlTran = conn.BeginTransaction();
cmd.Transaction = sqlTran;
intResult = cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception ex)
{
sqlTran.Rollback();
errorMsg = ex.ToString();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + " ConnStr:" + connStr + "|*|" + errorMsg);
}
return(intResult);
}
/// <summary>
/// 执行查询操作，返回 SqlDataReader 对象。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns>SqlDataReader 对象</returns>
public SqlDataReader QueryDr(string sql, SqlParameter[] sqlParams, int timeOut, out string errorMsg)
{
SqlConnection conn = null;
SqlCommand cmd = new SqlCommand();
SqlDataReader dr = null;
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (SqlParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(dr);
}
public IActionResult Index(string Msg)
{
var feature = HttpContext.Features.Get <IExceptionHandlerFeature>();
var error = feature?.Error;
//记录日志
Log4NetUtil.Error(this, Convert.ToString(error) + Msg);
//发送报警邮件
//MailHelper.SendAlertMail(_appConf.MailList.FirstOrDefault<MailStru>(), error.ToString());
//微信报警
//WeChatHelper.SendAlertWX(_appConf.WeChatList.FirstOrDefault<WeChatStru>());
ViewData["Msg"] = Msg;
return(View());
}
/// <summary>
/// 异常日志保存
/// </summary>
/// <param name="msg"></param>
private void ShowExceptionMsg(string msg, bool isShow)
{
Console.WriteLine(msg);
if (isShow)
{
Dispatcher.BeginInvoke(new Action(() =>
{
LogUtil.Error(msg, this);
}));
}
else
{
Log4NetUtil.Error(msg);
}
}
/// <summary>
/// 执行查询操作，查询结果保存至 DataTable 中。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns></returns>
public DataTable QueryDt(string sql, out string errorMsg)
{
SQLiteConnection conn = null;
SQLiteCommand cmd = new SQLiteCommand();
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
DataTable dt = new DataTable();
errorMsg = string.Empty;
lock (padlock)
{
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
adapter.SelectCommand = cmd;
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
adapter.Dispose();
}
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + " ConnStr:" + connStr + "|*|" + errorMsg);
}
return(dt);
}
/// <summary>
/// 执行查询操作，返回结果集的第一行第一列 (object类型)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns></returns>
public object QueryObj(string sql, out string errorMsg)
{
SQLiteConnection conn = null;
SQLiteCommand cmd = new SQLiteCommand();
object obj = null;
errorMsg = string.Empty;
lock (padlock)
{
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
conn.Open();
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + " ConnStr:" + connStr + "|*|" + errorMsg);
}
return(obj);
}
/// <summary>
/// 获取数据库链接
/// </summary>
/// <param name="connConfig">数据库链接配置结构</param>
/// <returns></returns>
public static IDbConnection GetConnection(DbConnStru connConfig)
{
IDbConnection conn = null;
if (string.IsNullOrEmpty(connConfig.connStr))
{
return(null);
}
try
{
switch (connConfig.dbType.ToLower().Trim())
{
case "sqlserver":
conn = new SqlConnection(connConfig.connStr);
break;
case "oracle":
conn = new OracleConnection(connConfig.connStr);
break;
case "mysql":
conn = new MySqlConnection(connConfig.connStr);
break;
case "sqlite":
conn = new SQLiteConnection(connConfig.connStr);
break;
default:
conn = new SqlConnection(connConfig.connStr);
break;
}
//打开数据库链接
conn.Open();
}
catch (Exception ex)
{
Log4NetUtil.Error(typeof(DapperAcc), "ConnStr:" + connConfig.connStr + " ErrorMsg:" + ex.ToString());
}
return(conn);
}
/// <summary>
/// ORM数据映射
/// </summary>
/// <param name="ID"></param>
/// <param name="connConfig"></param>
/// <returns></returns>
public IEnumerable <T> Select <T>(string sql, DbConnStru connConfig)
{
IEnumerable <T> list = null;
using (IDbConnection conn = DapperAcc.GetConnection(connConfig))
{
try
{
//传统sql in (1, 2, 3)写法
//conn.Query<TestTable>("SELECT * FROM TestTable WHERE id IN (@ids) ",new { ids = IDs.ToArray()})
list = conn.Query <T>(sql);
//list = conn.Query<DataInfoStru>(sql, new { id = ID });
}
catch (Exception ex)
{
Log4NetUtil.Error(this, "Select->" + ex.ToString());
}
}
return(list);
}
/// <summary>
/// 开始服务
/// </summary>
public void StartService()
{
try
{
isStart = true;
//启动获取数据守护线程
if (dataGuardThread == null || !dataGuardThread.IsAlive)
{
dataGuardThread = new Thread(new ThreadStart(GuardThread))
{
Name = "DataGuardThread",
IsBackground = true
};
dataGuardThread.Start();
}
}
catch (Exception e)
{
Log4NetUtil.Error("服务启动失败，原因：" + e.Message);
}
}
/// <summary>
/// 执行查询操作，返回 SQLiteDataReader 对象。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns></returns>
public SQLiteDataReader QueryDr(string sql, out string errorMsg)
{
SQLiteConnection conn = null;
SQLiteCommand cmd = new SQLiteCommand();
SQLiteDataReader dr = null;
errorMsg = string.Empty;
lock (padlock)
{
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
cmd.Dispose();
}
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + " ConnStr:" + connStr + "|*|" + errorMsg);
}
return(dr);
}
/// <summary>
/// Sqlite加载显示的数据
/// </summary>
/// <param name="options"></param>
/// <param name="pageclick">当前点击页</param>
/// <param name="sqlDic">过滤器条件</param>
/// <returns></returns>
public List <DownLoadAccountStru> GetXMLDataInfo(CommConf options, int pageClick, ref Dictionary <string, string> sqlDic)
{
XmlDocument xmlDoc = new XmlDocument();
XmlNodeList xmlNodeList = null;
XmlNode xmlNode = null;
string str_Temp = string.Empty;
//XML配置文件路径
string str_ConfigFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "config.xml");
//string str_ConfigFilePath = options.AttriList.FirstOrDefault(l => l.key == "XMLPath").value;
if (!File.Exists(str_ConfigFilePath))
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件不存在，str_ConfigFilePath=" + str_ConfigFilePath);
return(new List <DownLoadAccountStru>());
}
//XML配置部分
try
{
xmlDoc.Load(str_ConfigFilePath);
#region 加载 UserList
xmlNodeList = xmlDoc.SelectNodes("/Root/UserList/User");
if (xmlNodeList != null)
{
//List<DownLoadAccountStru> list_DownLoadAccount = new List<DownLoadAccountStru>();
foreach (XmlNode node in xmlNodeList)
{
//用户账号配置
DownLoadAccountStru userConfig = new DownLoadAccountStru();
userConfig.userName = node.SelectSingleNode("UserName").InnerText.Trim();
userConfig.Pwd = node.SelectSingleNode("Pwd").InnerText.Trim();
userConfig.pwdIsEncrypt = node.SelectSingleNode("IsEncrypt").InnerText.Trim().ToLower() == "true" ? true : false;
//解密
if (userConfig.pwdIsEncrypt)
{
userConfig.Pwd = EncryptUtil.DesDecrypt_Default(userConfig.Pwd);
}
//用户数据库链接配置
XmlNode connNode = node.SelectSingleNode("Conn");
if (connNode != null)
{
userConfig.Name = connNode.SelectSingleNode("Name").InnerText.Trim();
userConfig.DbType = connNode.SelectSingleNode("DbType").InnerText.Trim();
userConfig.ConnStr = connNode.SelectSingleNode("ConnStr").InnerText.Trim();
userConfig.connStrIsEncrypt = connNode.SelectSingleNode("IsEncrypt").InnerText.Trim().ToLower() == "true" ? true : false;
//解密
if (userConfig.connStrIsEncrypt)
{
userConfig.ConnStr = EncryptUtil.DesDecrypt_Default(userConfig.ConnStr);
}
if (node.SelectSingleNode("Encoding") != null)
{
userConfig.Encoding = node.SelectSingleNode("Encoding").InnerText.Trim();
}
}
else
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件格式异常,请检查节点:/Root/UserList/User/Conn");
continue;
}
if (!string.IsNullOrEmpty(userConfig.userName))
{
//UserName 唯一
if (dataInfo.FirstOrDefault <DownLoadAccountStru>(t => t.userName == userConfig.userName) == null)
{
dataInfo.Add(userConfig);
}
}
}
sqlDic["PageShowNum"] = options.AttriList.FirstOrDefault(o => o.key == "PageShowNum").value;
sqlDic["allowPageNum"] = options.AttriList.FirstOrDefault(l => l.key == "allowPageNum").value;
sqlDic["clickpagenow"] = pageClick.ToString();
return(dataInfo);
}
else
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件格式异常,请检查节点:/Root/UserList/User");
return(new List <DownLoadAccountStru>());
}
#endregion
}
catch (Exception ex)
{
Log4NetUtil.Error(this, "EntDtConf->加载XML配置文件异常:" + ex.ToString());
return(new List <DownLoadAccountStru>());
}
finally
{
xmlDoc = null;
xmlNodeList = null;
str_Temp = null;
}
}
/// <summary>
/// 批量删除（Delete）
/// </summary>
/// <param name="tableName">目标表名称</param>
/// <param name="dic_ColumnParaArry">列参数数组</param>
/// <param name="batchSize">批次中的数量</param>
/// <param name="timeOut">等待命令执行的时间</param>
/// <param name="errorMsg">返回的异常信息</param>
public int OracleBatchDel(string tableName, Dictionary <string, OrclDataStru> dic_ColumnParaArry, int batchSize, int timeOut, out string errorMsg)
{
errorMsg = string.Empty;
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction sqlTran = null;
StringBuilder sbCmdText = new StringBuilder();
//影响的行数（由于触发器的存在，不一定准确）
int intResult = 0;
//列数组
string[] arr_Columns = null;
//参数校验
if (string.IsNullOrEmpty(tableName))
{
errorMsg = "tableName 为空";
return(intResult);
}
if (dic_ColumnParaArry == null || dic_ColumnParaArry.Count < 1)
{
errorMsg = "dic_ColumnParaArry 为空";
return(intResult);
}
//取得列数组
arr_Columns = dic_ColumnParaArry.Keys.ToArray();
//准备Delete语句
sbCmdText.Append("DELETE FROM ");
sbCmdText.Append(tableName);
sbCmdText.Append(" WHERE ");
if (arr_Columns.Length == 1)
{
sbCmdText.Append(arr_Columns[0]);
sbCmdText.Append("=:");
sbCmdText.Append(arr_Columns[0]);
}
else
{
sbCmdText.Append("1=1");
foreach (string col in arr_Columns)
{
sbCmdText.Append(" AND ");
sbCmdText.Append(col);
sbCmdText.Append("=:");
sbCmdText.Append(col);
}
}
try
{
//取得数据库连接
conn = GetConnection();
//OracleCommand
cmd.Connection = conn;
//批次中的行数
if (batchSize > 0)
{
cmd.ArrayBindCount = batchSize;
}
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sbCmdText.ToString();
//等待命令执行的时间（以秒为单位）,默认值为 300 秒。
if (timeOut <= 300)
{
cmd.CommandTimeout = 300;
}
else
{
cmd.CommandTimeout = timeOut;
}
//创建参数
foreach (string colName in arr_Columns)
{
OracleDbType dbType = dic_ColumnParaArry[colName].dbType;
OracleParameter oraParam = new OracleParameter(colName, dbType);
oraParam.Direction = ParameterDirection.Input;
if (dic_ColumnParaArry[colName].arrParam != null)
{
oraParam.Value = dic_ColumnParaArry[colName].arrParam;
}
else if (dic_ColumnParaArry[colName].arryList != null)
{
oraParam.Value = dic_ColumnParaArry[colName].arryList.ToArray();
}
else
{
errorMsg = "ParaArry 为空";
return(intResult);
}
cmd.Parameters.Add(oraParam);
}
conn.Open();
sqlTran = conn.BeginTransaction();
cmd.Transaction = sqlTran;
intResult = cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception ex)
{
try
{
sqlTran.Rollback();
}
catch (Exception e)
{
Log4NetUtil.Error(this, "OracleBatchDel->回滚异常 SQL:" + sbCmdText.ToString() + "|*|" + e.ToString());
}
errorMsg = ex.ToString();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "OracleBatchDel->SQL:" + sbCmdText.ToString() + "|*|" + errorMsg);
}
return(intResult);
}
/// <summary>
/// 执行查询操作，查询结果保存至 DataTable 中。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
public DataTable QueryDt(string sql, OracleParameter[] sqlParams, int timeOut, out string errorMsg)
{
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleDataAdapter adapter = new OracleDataAdapter();
DataTable dt = new DataTable();
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (OracleParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
adapter.SelectCommand = cmd;
conn.Open();
adapter.Fill(dt);
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
adapter.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(dt);
}
/// <summary>
/// 批量执行语句
/// </summary>
/// <param name="strSql">Sql</param>
/// <param name="dic_ColumnParaArry">列参数数组</param>
/// <param name="batchSize">批次中的数量</param>
/// <param name="timeOut">等待命令执行的时间</param>
/// <param name="errorMsg">返回的异常信息</param>
public int OracleBatchExec(string strSql, Dictionary <string, OrclDataStru> dic_ColumnParaArry, int batchSize, int timeOut, out string errorMsg)
{
errorMsg = string.Empty;
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction sqlTran = null;
//影响的行数（由于触发器的存在，不一定准确）
int intResult = 0;
//列数组
string[] arr_Columns = null;
//参数校验
if (string.IsNullOrEmpty(strSql))
{
errorMsg = "Sql 为空";
return(intResult);
}
if (dic_ColumnParaArry == null || dic_ColumnParaArry.Count < 1)
{
errorMsg = "dic_ColumnParaArry 为空";
return(intResult);
}
//取得列数组
arr_Columns = dic_ColumnParaArry.Keys.ToArray();
//准备Insert语句
//sbCmdText.AppendFormat("INSERT INTO {0}(", tableName);
//sbCmdText.Append(string.Join(",", arr_Columns));
//sbCmdText.Append(") VALUES (");
//sbCmdText.Append(":" + string.Join(",:", arr_Columns));
//sbCmdText.Append(")");
try
{
//取得数据库连接
conn = GetConnection();
//OracleCommand
cmd.Connection = conn;
//批次中的行数
if (batchSize > 0)
{
cmd.ArrayBindCount = batchSize;
}
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSql;
//等待命令执行的时间（以秒为单位）,默认值为 300 秒。
if (timeOut <= 300)
{
cmd.CommandTimeout = 300;
}
else
{
cmd.CommandTimeout = timeOut;
}
//创建参数
foreach (string colName in arr_Columns)
{
OracleDbType dbType = dic_ColumnParaArry[colName].dbType;
OracleParameter oraParam = new OracleParameter(colName, dbType);
oraParam.Direction = ParameterDirection.Input;
if (dic_ColumnParaArry[colName].arrParam != null)
{
oraParam.Value = dic_ColumnParaArry[colName].arrParam;
}
else if (dic_ColumnParaArry[colName].arryList != null)
{
oraParam.Value = dic_ColumnParaArry[colName].arryList.ToArray();
}
else
{
errorMsg = "ParaArry 为空";
return(intResult);
}
cmd.Parameters.Add(oraParam);
}
conn.Open();
sqlTran = conn.BeginTransaction();
cmd.Transaction = sqlTran;
intResult = cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception ex)
{
try
{
sqlTran.Rollback();
}
catch (Exception e)
{
Log4NetUtil.Error(this, "OracleBatchExec->回滚异常 SQL:" + strSql + "|*|" + e.ToString());
}
errorMsg = ex.ToString();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "OracleBatchExec->SQL:" + strSql + "|*|" + errorMsg);
}
return(intResult);
}
/// <summary>
/// 执行查询操作，查询结果保存至 DataSet 中。
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="tableName">指定填充的TableName</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
public DataSet QueryDs(string sql, DbParameter[] sqlParams, string tableName, int timeOut, out string errorMsg)
{
DbConnection conn = null;
DbCommand cmd = null;
DbDataAdapter adapter = null;
DataSet ds = new DataSet();
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
//获取数据适配器
adapter = GetDataAdapter();
cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (DbParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
adapter.SelectCommand = cmd;
conn.Open();
if (!string.IsNullOrEmpty(tableName))
{
adapter.Fill(ds, tableName);
}
else
{
adapter.Fill(ds);
}
}
catch (Exception ex)
{
errorMsg = ex.ToString();
}
finally
{
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
adapter.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(ds);
}
/// <summary>
/// 批量插入(DataTable 源,带事务)
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="tableName">目标表名称</param>
/// <param name="dic_ColumnMapping">源和目标的列映射</param>
/// <param name="batchSize">每一提交批次中的行数</param>
/// <param name="timeOut">等待命令执行的时间</param>
/// <param name="isNotify">是否每批次通知</param>
/// <param name="errorMsg">返回的异常信息</param>
public void SqlBulkCopyInsert(DataTable dt, string tableName, Dictionary <string, string> dic_ColumnMapping, int batchSize, int timeOut, bool isNotify, out string errorMsg)
{
SqlConnection conn = null;
SqlBulkCopy sqlBulkCopy = null;
SqlTransaction sqlTran = null;
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
conn.Open();
sqlTran = conn.BeginTransaction();
//sqlBulkCopy = new SqlBulkCopy(conn);
sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, sqlTran);
//目标表名称
sqlBulkCopy.DestinationTableName = tableName.Trim();
//源和目标的列映射
if (dic_ColumnMapping != null && dic_ColumnMapping.Count > 0)
{
foreach (string strKey in dic_ColumnMapping.Keys)
{
sqlBulkCopy.ColumnMappings.Add(strKey, dic_ColumnMapping[strKey]);
}
}
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
}
//每一批次中的行数
if (batchSize > 1)
{
sqlBulkCopy.BatchSize = batchSize;
}
//等待命令执行的时间（以秒为单位）,默认值为 300 秒。
if (timeOut <= 300)
{
sqlBulkCopy.BulkCopyTimeout = 300;
}
else
{
sqlBulkCopy.BulkCopyTimeout = timeOut;
}
//是否按批次通知
if (isNotify)
{
sqlBulkCopy.NotifyAfter = sqlBulkCopy.BatchSize;
sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(BulkCopy_SqlRowsCopied_Notify);
}
if (dt != null && dt.Rows.Count != 0)
{
sqlBulkCopy.WriteToServer(dt);
sqlTran.Commit();
}
}
catch (Exception ex)
{
errorMsg = ex.ToString();
sqlTran.Rollback();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
if (sqlBulkCopy != null)
{
sqlBulkCopy.Close();
}
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SqlBulkCopyInsert->" + errorMsg);
}
}
/// <summary>
/// 错误
/// </summary>
/// <param name="log"></param>
/// <param name="main"></param>
public static void Error(string log, MainWindow main)
{
Log4NetUtil.Error(log);
LogBoxAppend(Colors.Red, "错误", log, main);
}
/// <summary>
/// 读取XML文件
/// </summary>
/// <param name="options"></param>
/// <param name="pageclick">当前点击页</param>
/// <param name="sqlDic">过滤器条件</param>
/// <returns></returns>
public List <CommonConfigStru> GetSftpDataInfo(CommConf options, int pageClick, ref Dictionary <string, string> sqlDic)
{
XmlDocument xmlDoc = new XmlDocument();
XmlNodeList xmlNodeList = null;
XmlNode xmlNode = null;
string str_Temp = string.Empty;
dataInfo = new List <CommonConfigStru>();
//XML配置文件路径
string str_ConfigFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "config.xml");
//string str_ConfigFilePath = options.AttriList.FirstOrDefault(l => l.key == "XMLPath").value;
if (!File.Exists(str_ConfigFilePath))
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件不存在，str_ConfigFilePath=" + str_ConfigFilePath);
return(new List <CommonConfigStru>());
}
//XML配置部分
try
{
xmlDoc.Load(str_ConfigFilePath);
CommonConfigStru ftpConfig = new CommonConfigStru();
#region 加载Ftp配置
xmlNode = xmlDoc.SelectSingleNode("/Root/CommConfig/Sftp");
if (xmlNode != null)
{
ftpConfig.Name = xmlNode.SelectSingleNode("Name").InnerText.Trim();
ftpConfig.ftpUri = xmlNode.SelectSingleNode("FtpUri").InnerText.Trim();
ftpConfig.ftpPort = ConvertUtil.ParseInt(xmlNode.SelectSingleNode("FtpPort").InnerText.Trim(), 22).ToString();
ftpConfig.ftpUserID = xmlNode.SelectSingleNode("FtpUserID").InnerText.Trim();
ftpConfig.ftpPassword = xmlNode.SelectSingleNode("FtpPassword").InnerText.Trim();
ftpConfig.isEncrypt = xmlNode.SelectSingleNode("IsEncrypt").InnerText.Trim().ToLower() == "true" ? true : false;
if (ftpConfig.isEncrypt)
{
//解密
ftpConfig.ftpPassword = EncryptUtil.DesDecrypt_Default(ftpConfig.ftpPassword);
}
}
else
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件格式异常,请检查节点:/Root/CommConfig/Sftp");
return(new List <CommonConfigStru>());
}
#endregion
#region 加载Ftp配置
xmlNode = xmlDoc.SelectSingleNode("/Root/CommConfig/HttpsAddr");
if (xmlNode != null)
{
CommonConfigStru httpsAddrConfig1 = new CommonConfigStru();
ftpConfig.httpsAddr = xmlNode.InnerText.Trim();
if (!string.IsNullOrEmpty(ftpConfig.httpsAddr))
{
//Name 唯一
if (dataInfo.FirstOrDefault <CommonConfigStru>(t => t.Name == ftpConfig.httpsAddr) == null)
{
dataInfo.Add(ftpConfig);
}
}
}
else
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件格式异常,请检查节点:/Root/CommConfig/Sftp");
return(new List <CommonConfigStru>());
}
#endregion
sqlDic["PageShowNum"] = options.AttriList.FirstOrDefault(o => o.key == "PageShowNum").value;
sqlDic["allowPageNum"] = options.AttriList.FirstOrDefault(l => l.key == "allowPageNum").value;
sqlDic["clickpagenow"] = pageClick.ToString();
return(dataInfo);
}
catch (Exception ex)
{
Log4NetUtil.Error(this, "EntDtConf->加载XML配置文件异常:" + ex.ToString());
return(new List <CommonConfigStru>());
}
finally
{
xmlDoc = null;
xmlNodeList = null;
str_Temp = null;
}
}
/// <summary>
/// 读取XML文件
/// </summary>
/// <param name="options"></param>
/// <param name="pageclick">当前点击页</param>
/// <param name="sqlDic">过滤器条件</param>
/// <returns></returns>
public List <CommonConfigStru> GetHttpsAddrDataInfo(CommConf options, int pageClick, ref Dictionary <string, string> sqlDic)
{
XmlDocument xmlDoc = new XmlDocument();
XmlNodeList xmlNodeList = null;
XmlNode xmlNode = null;
string str_Temp = string.Empty;
dataInfo = new List <CommonConfigStru>();
//XML配置文件路径
string configFilePath = options.AttriList.FirstOrDefault(l => l.key == "XMLPath").value;
if (!File.Exists(configFilePath))
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件不存在，str_ConfigFilePath=" + configFilePath);
return(new List <CommonConfigStru>());
}
//XML配置部分
try
{
xmlDoc.Load(configFilePath);
#region 加载Ftp配置
xmlNode = xmlDoc.SelectSingleNode("/Root/CommConfig/HttpsAddr");
if (xmlNode != null)
{
CommonConfigStru httpsAddrConfig = new CommonConfigStru();
httpsAddrConfig.httpsAddr = xmlNode.InnerText.Trim();
if (!string.IsNullOrEmpty(httpsAddrConfig.httpsAddr))
{
//Name 唯一
if (dataInfo.FirstOrDefault <CommonConfigStru>(t => t.Name == httpsAddrConfig.httpsAddr) == null)
{
dataInfo.Add(httpsAddrConfig);
}
}
sqlDic["PageShowNum"] = options.AttriList.FirstOrDefault(o => o.key == "PageShowNum").value;
sqlDic["allowPageNum"] = options.AttriList.FirstOrDefault(l => l.key == "allowPageNum").value;
sqlDic["clickpagenow"] = pageClick.ToString();
return(dataInfo);
}
else
{
Log4NetUtil.Error(this, "EntDtConf->config.xml 配置文件格式异常,请检查节点:/Root/CommConfig/Sftp");
return(new List <CommonConfigStru>());
}
#endregion
}
catch (Exception ex)
{
Log4NetUtil.Error(this, "EntDtConf->加载XML配置文件异常:" + ex.ToString());
return(new List <CommonConfigStru>());
}
finally
{
xmlDoc = null;
xmlNodeList = null;
str_Temp = null;
}
}
