/// <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);
}
public Startup(IConfiguration configuration)
{
Configuration = configuration;
//log4net日志配置
Log4NetUtil.InitLog4net();
Log4NetUtil.Info(typeof(Startup), "entConsole启动");
}
/// <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>
/// 批次处理通知事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BulkCopy_SqlRowsCopied_Notify(object sender, SqlRowsCopiedEventArgs e)
{
string strTableName = string.Empty;
SqlBulkCopy sqlBulkCopy = sender as SqlBulkCopy;
if (sqlBulkCopy != null)
{
strTableName = sqlBulkCopy.DestinationTableName;
}
Log4NetUtil.Info(this, strTableName + " 当前已复制行数：" + e.RowsCopied.ToString());
}
/// <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());
}
public HttpResponseMessage SetLogMessageTraceLevel(string level)
{
var log4NetLevel = Log4NetUtil.ParseLevel(level);
if (log4NetLevel == null)
{
return(Request.CreateResponse(HttpStatusCode.BadRequest));
}
ServiceLocator.ConfigService.SetLogMessageTraceLevel(log4NetLevel);
return(new HttpResponseMessage(HttpStatusCode.OK));
}
/// <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);
}
}
private static async Task WriteExceptionAsync(HttpContext context, Exception exception)
{
//记录日志
Log4NetUtil.LogError(exception.GetBaseException().ToString(), exception);
//返回友好的提示
var response = context.Response;
//状态码
if (exception is Exception)
{
response.StatusCode = (int)HttpStatusCode.BadRequest;
}
response.ContentType = "application/json";
await response.WriteAsync(JsonConvert.SerializeObject(new CustomExceptionResultModel(response.StatusCode, exception.GetBaseException()))).ConfigureAwait(false);
}
/// <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);
}
public void Test_Log4NetUtil_Init()
{
/*
* 【说明】
* 以下这句作用是添加配置文件，需放在 log4net.LogManager.GetLogger() 方法所在项目的 AssemblyInfo.cs 里
* ps:若放在其他项目的 AssmblyInfo.cs 里，配置文件将不起作用，例如本例，是放在Utlities项目的 AssemblyInfo.cs 里，因为 log4net.LogManager.GetLogger() 方法在 Log4NetUtil.cs
* [assembly: log4net.Config.XmlConfigurator(ConfigFile = @"..\..\..\Utilities\Xml\Log4Net.xml", Watch = true)]
* ConfigFile 路径是以启动项目为相对路径的
*/
//这个Demo，使用Log4NetUtil，结合Log4Net.xml的配置完成。
Log4NetUtil.Init("SmsSendLog", "SystemLog", "ErrorLog");
Log4NetUtil.SetConnString("AdoNetAppender_SQLServer", BaseSystemInfo.TestDbHelperConnectionString, "SmsSendLog");
Log4NetUtil.SetConnString("AdoNetAppender_SQLServer2", BaseSystemInfo.TestDbHelperConnectionString, "SystemLog");
Log4NetUtil.SetDefatultLog("SystemLog");
}
/// <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);
}
public static void RunWithArgs(ProjectRunnerArguments programArgs)
{
if (programArgs.OutputDirectory != null)
{
Paths.OutputDirectory = programArgs.OutputDirectory;
}
if (programArgs.InputDirectory != null)
{
Paths.InputDirectory = programArgs.InputDirectory;
}
Log4NetUtil.InitializeLoggers(programArgs.Debug);
var runner = programArgs.TestSource.GetProjectRunner(programArgs);
List <TestExecutionResults> results = runner.Run();
if (programArgs.TestSource == ProjectRunnerType.TestProject)
{
CheckResults(results, programArgs as TestProjectRunnerArguments);
}
}
public ActionResult HelloWorld(DemoForm demoForm)
{
ResponseResult response = new ResponseResult();
DemoModel demoModel = new DemoModel();
demoModel.UserName = demoForm.Message;
if (string.IsNullOrEmpty(demoForm.SerialNumber))
{
Log4NetUtil.LogWarn(demoForm.ToJson());
response.Code = ResponseResultType.ParamError;
response.Message = "参数错误";
return(Content(response.ToJson()));
}
response.SerialNumber = "2";
response.Code = ResponseResultType.Success;
response.Message = _env.EnvironmentName;
response.Data = demoModel;
return(Content(response.ToJson()));
}
public void OnException(ExceptionContext context)
{
try
{
Log4NetUtil.LogError("全局异常", context.Exception);
if (context.HttpContext.Request.IsHttps)
{
context.Result = new JsonResult(context.Exception.Message);
}
else
{
context.Result = new RedirectToActionResult("Error", "Home", new { });
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
context.ExceptionHandled = true; // 注意：如果不添加这句代码，程序不会自动断路，会继续向下进行。
}
/// <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);
}
}
public IActionResult Login([FromBody] UserInfoModels user)
{
var json = new JsonResultModels();
try
{
if (string.IsNullOrWhiteSpace(user.UserName) || string.IsNullOrWhiteSpace(user.Password))
{
json.Code = 201;
json.Msg = "用户名密码不能为空";
return(Ok(json));
}
else
{
var UserDate = _userBLL.CheckUser(user.UserName, user.Password);
if (UserDate == null)
{
json.Code = 400;
json.Msg = "登录失败:用户不存在!";
}
else
{
Dictionary <string, string> keyValues = new Dictionary <string, string> {
{ "UserName", UserDate.UserName }
};
json.Code = 200;
json.Msg = "登录成功";
json.TnToken = _tokenHelper.CreateToken(keyValues);
}
}
}
catch (Exception ex)
{
json.Code = 400;
json.Msg = "登录失败:" + ex.Message;
Log4NetUtil.LogError(json.Msg, ex);
}
Log4NetUtil.LogInfo(json.Msg);
return(Ok(json));
}
/// <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);
}
public async Task Invoke(HttpContext context)
{
try
{
await Next(context);
}
catch (Exception e)
{
context.Response.Clear();
context.Response.StatusCode = StatusCodes.Status200OK;
Log4NetUtil.LogError("全局异常", e);
if (context.Request.IsHttps)
{
context.Response.ContentType = "application/json";
await context.Response.WriteAsync(JsonConvert.SerializeObject(new { Code = 500, Message = e.Message }));
}
else
{
context.Response.Redirect("/Home/Error");
}
}
}
/// <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>
/// 批量插入(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);
}
}
private static void ConfigureLogging()
{
log4net.Config.XmlConfigurator.Configure();
Log.Info("*****Starting web app****************");
Log4NetUtil.ConfigureAdoNetAppender();
}
