public void ImportDataTable(Cells c, out int total) { DataTable datatable = ImportWorkSheet(c); total = datatable.Rows.Count; using (OracleConnection conn = new OracleConnection(Connectionstr)) { conn.Open(); OracleTransaction tran = conn.BeginTransaction(); try { foreach (DataRow dr in datatable.Rows) { string sql1 = CreateInsertSql(dr); using (OracleCommand cmd = new OracleCommand(sql1, conn)) { cmd.Transaction = tran; cmd.ExecuteNonQuery(); } } tran.Commit(); } catch (Exception) { tran.Rollback(); conn.Close(); throw; } } }
public void ExecuteMax() { try { using (conn = new OracleConnection(ConfigurationManager.AppSettings["mergeData"])) { conn.Open(); tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); using (OracleCommand cmd = new OracleCommand()) { cmd.Connection = conn; cmd.Transaction = tran; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "createMaxChemicalIndustryTable"; cmd.ExecuteNonQuery(); } using (OracleCommand cmd = new OracleCommand()) { cmd.Connection = conn; cmd.Transaction = tran; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "createMaxEnergyGasTable"; cmd.ExecuteNonQuery(); } using (OracleCommand cmd = new OracleCommand()) { cmd.Connection = conn; cmd.Transaction = tran; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "createMaxEnergyTable"; cmd.ExecuteNonQuery(); } using (OracleCommand cmd = new OracleCommand()) { cmd.Connection = conn; cmd.Transaction = tran; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "createMaxEnergyOilTable"; cmd.ExecuteNonQuery(); } tran.Commit(); } } catch (Exception e) { tran.Rollback(); } finally { conn.Close(); } }
public static void ExecCinema_MT(int attid) { Console.WriteLine(DateTime.Now.ToString(_formatString)); //OracleConnection conn, OracleTransaction tran string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl)));User Id=settle;Password=settle;"; OracleConnection conn = new OracleConnection(conStr); conn.Open(); OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); try { var paramArray = new[] { new OracleParameter("@p_attachmentId", OracleDbType.Int32) { Value = attid }, //new OracleParameter("@QueryDateType", OracleDbType.Varchar2) { Value = queryDateType }, //new OracleParameter("@OrgType", OracleDbType.Varchar2) { Value = orgType }, //new OracleParameter("@TrustType", OracleDbType.NVarchar2) { Value = trustType }, // new OracleParameter("@Total", OracleDbType.Int32,ParameterDirection.Output), //new OracleParameter("@cur",OracleDbType.RefCursor,ParameterDirection.Output) }; using (OracleCommand cmd = new OracleCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "SP_CHECKCinema_MT_BYattID"; cmd.CommandTimeout = 90; cmd.Parameters.AddRange(paramArray); cmd.Transaction = tran; cmd.ExecuteNonQuery(); tran.Commit(); } } catch (Exception ee) { tran.Rollback(); Console.WriteLine(ee.Message); } finally { conn.Close(); } Console.WriteLine(DateTime.Now.ToString(_formatString)); }
/// <summary> /// 執行多條SQL語句,實現數據庫事務。 /// </summary> /// <param name="SQLStringList">多條SQL語句</param> /// <param name="DBUrl">數據庫鏈接地址</param> public int GetNonQueryByManySQL(ArrayList SQLStringList, string DBUrl) { if (DBUrl.ToString().Trim() == "" || DBUrl == null) { throw new Exception("數據庫鏈接地址不能為空"); } using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl)) { conn.Open(); Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(); cmd.Connection = conn; Oracle.ManagedDataAccess.Client.OracleTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int num = 0; for (int i = 0; i < SQLStringList.Count; i++) { string SQL = SQLStringList[i].ToString();//獲取SQL語句 if (SQL.Trim().Length > 1) { cmd.CommandText = SQL; num = cmd.ExecuteNonQuery(); } tx.Commit(); //提交事務 cmd.Dispose(); //釋放資源 conn.Dispose(); //釋放資源 conn.Close(); //關閉 } return(num); //返回執行結果數量 } catch (Oracle.ManagedDataAccess.Client.OracleException E) { tx.Rollback();//事務回滾 throw new Exception(E.Message); } } }
protected override void ExecuteInternal(JobExecutionContext context) { string connStr = ConfigurationManager.AppSettings["mergeData"]; OracleConnection conn = new OracleConnection(connStr); conn.Open(); OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); strInfo.AppendFormat("Source [Email:{0},Title:{1}]\n", ConfigurationManager.AppSettings["LongZhongSenderFilter"], ConfigurationManager.AppSettings["LongZhongTitleFilter"]); strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr); var lastSyncTime = Convert.ToDateTime("2016-11-16"); using (var cneEntities = new CnEEntities()) { var date = cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { //ToGMT8 lastSyncTime = date.Value.AddHours(8); } } try { #region 执行数据同步程序 var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"]; int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]); bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]); string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"]; string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"]; string attFetcherSavingPath = ConfigurationManager.AppSettings["LongZhongSavingPath"]; var attFetcher = new IMAP4AttFetcher(attFetcherSever, attFetcherPort, attFetcherUsingSsl, attFetcherUserName, attFetcherPassWord, attFetcherSavingPath); var attFetcherSenderFilter = new SenderFilter(); /*Filter example. If there are more filters , please write like below.*/ string[] senders = ConfigurationManager.AppSettings["LongZhongSenderFilter"].Split(';'); foreach (var sender in senders) { var senderRegex = new Regex(sender, RegexOptions.Compiled); attFetcherSenderFilter.SetRule(senderRegex); } attFetcher.AppendFilter(attFetcherSenderFilter); var attFetcherTitleFilter = new TitleFilter(); var titleRegex = new Regex(ConfigurationManager.AppSettings["LongZhongTitleFilter"], RegexOptions.Compiled); attFetcherTitleFilter.SetRule(titleRegex); attFetcher.AppendFilter(attFetcherTitleFilter); attFetcher.Execute(lastSyncTime); /*Get the saved attachment names.*/ var attachmentFileNames = attFetcher.GetAttachmentFileNames(); //List<string> attachmentFileNames = Directory.GetFiles(@"C:\DataFeedApp\Scheduler\CnE\LongZhong").ToList<string>(); foreach (var tempattachname in attachmentFileNames) { LongZhongExcelManager manager = new LongZhongExcelManager(); manager.GetCellsByFirstSheet(tempattachname, strInfo); } var fold = new DirectoryInfo(attFetcherSavingPath); if (fold.Exists) { FileInfo[] files = fold.GetFiles(); foreach (FileInfo f in files)//删除目录下所有文件 { f.Delete(); } } if (attachmentFileNames.Count > 0) { MergeData merge = new MergeData(); merge.ExecuteLongZhongExcel(conn, tran); tran.Commit(); strInfo.Append("Execute Procedure : CREATEMAXENERGYYIELDTABLE, CREATEMAXCHEMISTRYOUTPUTTABLE \r\n"); strInfo.Append("Insert Into Table : GDT_CHEMISTRYOUTPUTMAX, GDT_ENERGYYIELDMAX \r\n"); } else { strInfo.Append("No files found\r\n"); } #endregion var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); //merge data from temp to persistence } catch (Exception exception) { tran.Rollback(); logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1); logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } finally { conn.Close(); conn.Dispose(); } }
protected override void ExecuteInternal(JobExecutionContext context) { var strInfo = new StringBuilder(); #region 执行数据同步程序 if (DateTime.Now.Hour <= 15 || DateTime.Now.Hour >= 22) { strInfo.Append("Only this time(15:00Am-10:00Pm) point synchronous;"); return; } #endregion string connStr = ConfigurationManager.AppSettings["mergeData"].ToString(); OracleConnection conn = new OracleConnection(connStr); conn.Open(); OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n" + "\r\n", connStr); var lastSyncTime = new DateTime(2016, 9, 02); using (var cneEntities = new CnEEntities()) { var date = cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { //ToGMT8 lastSyncTime = date.Value.AddHours(8); } } try { #region 执行数据同步程序 var ch = new ChinaJci(); var sbSync = new StringBuilder(); ch.SyncChinaJciData(ref sbSync, lastSyncTime.ToString("yyyy-MM-dd")); strInfo.AppendFormat(sbSync.ToString()); #endregion MergeData merge = new MergeData(); merge.Execute(conn, tran); tran.Commit(); var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { tran.Rollback(); logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1); logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } finally { conn.Close(); } }
private void IQCReCheck(DataTable dtpub) { int m = 0; string states = ""; if (checktype == "NGToOK") { states = "OK"; } else if (checktype == "OKToNG") { states = "NG"; } else if (checktype == "NGTo特采") { states = "OK"; } string remarks = "", noteid = ""; if (checktype == "NGTo特采") { remarks = txtnoteid.Text + checktype + txtreason.Text; noteid = txtnoteid.Text; } else { remarks = checktype + txtreason.Text; } DataTable dt = dtpub; DataTable dtalready = new DataTable(); SqlConnection conn = new SqlConnection(DbAccess.connSql); //OracleConnection orac = new OracleConnection(DbAccess.connOral); Oracle.ManagedDataAccess.Client.OracleConnection orac = new Oracle.ManagedDataAccess.Client.OracleConnection(DbAccess.connOral); if (conn.State == ConnectionState.Closed) { conn.Open(); } if (orac.State == ConnectionState.Closed) { orac.Open(); } SqlTransaction tran1 = conn.BeginTransaction(); //OracleTransaction oratran = orac.BeginTransaction(); Oracle.ManagedDataAccess.Client.OracleTransaction oratran = orac.BeginTransaction(); try { Int32 org_id = 0, item_id = 0, tran_id = 0; Int64 recepid = 0; string org_idnew = ""; int instockqty = 0; int restenableqty = int.Parse(txtqty.Text); string msg = "", barcodemsg = "", msgcurrent = "", barcodemsgcurrent = ""; for (int j = 0; j < dt.Rows.Count; j++) { int alreadyqty = 0; org_idnew = dt.Rows[j]["ORGANIZATION_CODE"].ToString(); org_id = Int32.Parse(dt.Rows[j]["ship_to_org_id"].ToString()); item_id = Int32.Parse(dt.Rows[j]["INVENTORY_ITEM_ID"].ToString()); tran_id = Int32.Parse(dt.Rows[j]["transaction_id"].ToString()); //recepid = Int32.Parse(dt.Rows[0]["receipt_num"].ToString()); recepid = Int64.Parse(dt.Rows[0]["receipt_num"].ToString()); //判断当前行可检验数量大于界面录入数量,则把所有数量录入到第一笔. if (int.Parse(dt.Rows[j]["primary_quantity"].ToString()) >= restenableqty) { //则把用户输入的检验数量作为此行的检验数 instockqty = restenableqty; //20150312新增 //x表示还在接口表中的数量 //int x = ERPinterface(tran_id.ToString()); //用临时变量记录此次操作数量 int x = alreadyqty; //y表示还可处理的数量 int y = int.Parse(dt.Rows[j]["primary_quantity"].ToString()) - x; if (y >= restenableqty) { instockqty = restenableqty; } else if (y > 0 && y < restenableqty) { instockqty = y; } else { continue; } //20150312新增 barcodemsg = barcodemsg + insertBarCode(org_idnew.ToString(), item_id.ToString(), tran_id.ToString(), instockqty, Int32.Parse(dt.Rows[j]["primary_quantity"].ToString()), Login.username, recepid.ToString(), dt.Rows[j]["item_number"].ToString(), txtlotno.Text, "checkagain", remarks, conn, tran1); if (barcodemsg.IndexOf("成功") >= 0) { if (states == "OK") { msgcurrent = insertOracle(tran_id, instockqty, Login.username, noteid + txtreason.Text, orac, oratran); if (msgcurrent.IndexOf("Error") >= 0) { msg = msg + msgcurrent; break; } else { msg = msg + msgcurrent; txtqty.Text = "0"; break; } } else if (states == "NG") { msgcurrent = insertOracleReject(tran_id, instockqty, "BAD", 31, txtreason.Text, Login.username, orac, oratran); if (msgcurrent.IndexOf("Error") >= 0) { msg = msg + msgcurrent; break; } else { msg = msg + msgcurrent; txtqty.Text = "0"; break; } } } else { //防止第一笔已入库(一批次分多次入库问题),要继续循环下一笔 continue; } } //如果当前行的数量小于录入数量,则把当前行先进行检验通过该行的数量,同时把总共要检验的数量-第一笔已录的=作为剩下的可录入数量,再进行下一次循环 else { instockqty = int.Parse(dt.Rows[j]["primary_quantity"].ToString()); //20150312新增 //x表示还在接口表中的数量 //int x = ERPinterface(tran_id.ToString()); //用临时变量记录此次操作数量 try { DataRow[] rwalready = dtalready.Select(" id='" + tran_id + "'"); if (rwalready.Length > 0) { for (int z = 0; z < rwalready.Length; z++) { alreadyqty = alreadyqty + int.Parse(rwalready[z]["qty"].ToString()); } } } catch { dtalready.Columns.Add("id", typeof(string)); dtalready.Columns.Add("qty", typeof(int)); } int x = alreadyqty; //y表示还可处理的数量 int y = int.Parse(dt.Rows[j]["primary_quantity"].ToString()) - x; if (y >= restenableqty) { instockqty = restenableqty; } else if (y > 0 && y < restenableqty) { instockqty = y; } else { continue; } //20150312新增 barcodemsgcurrent = insertBarCode(org_idnew.ToString(), item_id.ToString(), tran_id.ToString(), instockqty, instockqty, Login.username, recepid.ToString(), dt.Rows[j]["item_number"].ToString(), txtlotno.Text, "checkagain", remarks, conn, tran1); barcodemsg = barcodemsg + barcodemsgcurrent; if (barcodemsgcurrent.IndexOf("成功") >= 0) { if (states == "OK") { msgcurrent = insertOracle(tran_id, instockqty, Login.username, noteid + txtreason.Text, orac, oratran); if (msgcurrent.IndexOf("Error") >= 0) { msg = msg + msgcurrent; break; } msg = msg + msgcurrent; restenableqty = restenableqty - instockqty; txtqty.Text = restenableqty.ToString(); DataRow newRow; newRow = dtalready.NewRow(); newRow["id"] = tran_id; newRow["qty"] = instockqty.ToString(); dtalready.Rows.Add(newRow); continue; } else if (states == "NG") { msgcurrent = insertOracleReject(tran_id, instockqty, "BAD", 31, txtreason.Text, Login.username, orac, oratran); if (msgcurrent.IndexOf("Error") >= 0) { msg = msg + msgcurrent; break; } msg = msg + msgcurrent; restenableqty = restenableqty - instockqty; txtqty.Text = restenableqty.ToString(); DataRow newRow; newRow = dtalready.NewRow(); newRow["id"] = tran_id; newRow["qty"] = instockqty.ToString(); dtalready.Rows.Add(newRow); continue; } } else { //防止第一笔已入库(一批次分多次入库问题),要继续循环下一笔 continue; } } } if (msg.IndexOf("Error") >= 0) { this.lblinfo.ForeColor = Color.Red; lblinfo.Text = msg + ";ERP写入失败!"; tran1.Rollback(); oratran.Rollback(); } else if (barcodemsg.IndexOf("失败") >= 0) { lblinfo.ForeColor = Color.Red; lblinfo.Text = barcodemsg + ";ERP写入失败!"; tran1.Rollback(); oratran.Rollback(); } else { lblinfo.ForeColor = Color.Blue; lblinfo.Text = "改判成功" + barcodemsg + ";" + msg; txtqty.Text = ""; this.txtreason.Text = ""; tran1.Commit(); oratran.Commit(); } } catch (Exception ex) { lblinfo.Text = ex.ToString(); tran1.Rollback(); oratran.Rollback(); } }
protected override void ExecuteInternal(JobExecutionContext context) { var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; string connStr = ConfigurationManager.AppSettings["mergeData"]; var conn = new OracleConnection(connStr); conn.Open(); var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); var strInfo = new StringBuilder(); string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"config\MySteel-data-sync.xml"); var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath)); // Set Connection string var destinationDbConn = string.Empty; settingManager.Init(destinationDbConn);//初始化 mapping strInfo.AppendFormat("Source [Type: {0} Address: {1}]\n", settingManager.SourceDb.Type, settingManager.SourceDb.Conn); strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", settingManager.DestinationDb.Type, settingManager.DestinationDb.Conn); DateTime lastSyncTime; var currentSyncTime = DateTime.Now; using (var cmd = new OracleCommand("SELECT max(dLastAccess) FROM RTMS_TABLEDATA ", conn)) { object obj = cmd.ExecuteScalar(); lastSyncTime = Convert.ToDateTime(obj.ToString()); strInfo.AppendFormat("Last successfully sync time : {0}.\n", obj); } try { using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, currentSyncTime)) { dataSync.TableSynched += (sender, e) => strInfo.AppendFormat ("{0} rows have been synchronized from {1} table in SYNC_REUTERS DB to {2} table in CnE DB.\n", e.NumOfRowsSynched, e.Source, e.Dest); dataSync.Init(); var sourceTable = settingManager.SourceTableName.Split(','); dataSync.Sync(sourceTable); } strInfo.AppendFormat("{0} table(s) be synchronized.\n", settingManager.TableMappings.Count()); var merge = new MergeData(); merge.ExecuteMetals(conn, tran); tran.Commit(); var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { tran.Rollback(); logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1); logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } }
protected override void ExecuteInternal(JobExecutionContext context) { var connStr = ConfigurationManager.AppSettings["mergeData"]; var conn = new OracleConnection(connStr); conn.Open(); OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); strInfo.AppendFormat("Source [Email:{0}]\n", ConfigurationManager.AppSettings["CoffedSenderFilter"]); strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr); strInfo.AppendFormat("Destination Table:Cofeed\n" + "\r\n"); var lastSyncTime = Convert.ToDateTime("2016-09-03"); using (var cneEntities = new CnEEntities()) { var date = cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { //ToGMT8 lastSyncTime = date.Value.AddHours(8); } } try { StringBuilder sb = new StringBuilder(); #region 执行数据同步程序 var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"]; int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]); bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]); string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"]; string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"]; string attFetcherSavingPath = ConfigurationManager.AppSettings["CoffedSavingPath"]; var attFetcher = new IMAP4AttFetcher(attFetcherSever, attFetcherPort, attFetcherUsingSsl, attFetcherUserName, attFetcherPassWord, attFetcherSavingPath); var attFetcherSenderFilter = new SenderFilter(); string[] senders = ConfigurationManager.AppSettings["CoffedSenderFilter"].Split(';'); foreach (var sender in senders) { var senderRegex = new Regex(sender, RegexOptions.Compiled); attFetcherSenderFilter.SetRule(senderRegex); } attFetcher.AppendFilter(attFetcherSenderFilter); var attFetcherTitleFilter = new TitleFilter(); var titleRegex = new Regex(ConfigurationManager.AppSettings["CoffedTitleFilter"], RegexOptions.Compiled); attFetcherTitleFilter.SetRule(titleRegex); attFetcher.AppendFilter(attFetcherTitleFilter); attFetcher.Execute(lastSyncTime); var attachmentFileNames = attFetcher.GetAttachmentFileNames(); foreach (var tempattachname in attachmentFileNames) { CofeedManager manager = new CofeedManager(); manager.GetCellsByFirstSheet(tempattachname, sb); } strInfo.Append(sb); var fold = new DirectoryInfo(attFetcherSavingPath); if (fold.Exists) { FileInfo[] files = fold.GetFiles(); foreach (FileInfo f in files)//删除目录下所有文件 { f.Delete(); } } #endregion if (attachmentFileNames.Count > 0) { MergeData merge = new MergeData(); merge.Execute(conn, tran); tran.Commit(); strInfo.Append("Execute Procedure : createMaxAgricultrueTable \r\n"); strInfo.Append("Insert Into Table : GDT_AgricultureMax \r\n"); } else { strInfo.Append("No files found\r\n"); } var settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"config\Ftp-Cofeed-data-sync.xml"); var settingManager = new FtpSyncXmlManager(File.ReadAllText(settingFilePath), lastSyncTime, startTime.AddHours(8)); settingManager.Init(); var ftpSync = new FtpSyncLoad(settingManager, strInfo); ftpSync.Excute(); var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { tran.Rollback(); logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1); logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } finally { conn.Close(); } }
/// <summary> /// Executes the command. /// </summary> /// <param name="dbCommand">The current sql command.</param> /// <param name="commandText">The command text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>-1 if command execution failed.</returns> public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. dbCommand = null; Int32 returnValue = -1; OracleClient.OracleConnection orlConnection = null; OracleClient.OracleTransaction orlTransaction = null; try { // Create a new connection. using (orlConnection = new OracleClient.OracleConnection(connectionString)) { // Open the connection. orlConnection.Open(); // Start a new transaction. orlTransaction = orlConnection.BeginTransaction(); // Create the command and assign any parameters. dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection); dbCommand.CommandType = commandType; dbCommand.Transaction = orlTransaction; if (values != null) { foreach (OracleClient.OracleParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Execute the command. returnValue = dbCommand.ExecuteNonQuery(); // Commit the transaction. orlTransaction.Commit(); // Close the database connection. orlConnection.Close(); } // Return true. return(returnValue); } catch (Exception ex) { try { // Attempt to roll back the transaction. if (orlTransaction != null) { orlTransaction.Rollback(); } } catch { } // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (orlConnection != null) { orlConnection.Close(); } } }
protected override void ExecuteInternal(Quartz.JobExecutionContext context) { string connStr = ConfigurationManager.AppSettings["mergeData"].ToString(); OracleConnection conn = new OracleConnection(connStr); conn.Open(); OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); strInfo.AppendFormat("Source [Email:{0}]\n", ConfigurationManager.AppSettings["CoffedProductSenderFilter"]); strInfo.AppendFormat("Destination [Type: Oracle DB, Address: {0}]\n", connStr); var lastSyncTime = Convert.ToDateTime("2016-11-14"); using (var cneEntities = new CnEEntities()) { var date = cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { lastSyncTime = date.Value.AddMinutes(-10); } } try { #region 执行数据同步程序 var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"]; int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]); bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]); string attFetcherUserName = ConfigurationManager.AppSettings["CnEUserName"]; string attFetcherPassWord = ConfigurationManager.AppSettings["CnEPassWord"]; string attFetcherSavingPath = ConfigurationManager.AppSettings["CoffedProductSavingPath"]; var attFetcher = new IMAP4AttFetcher(attFetcherSever, attFetcherPort, attFetcherUsingSsl, attFetcherUserName, attFetcherPassWord, attFetcherSavingPath); var attFetcherSenderFilter = new SenderFilter(); string[] senders = ConfigurationManager.AppSettings["CoffedProductSenderFilter"].Split(';'); foreach (var sender in senders) { var senderRegex = new Regex(sender, RegexOptions.Compiled); attFetcherSenderFilter.SetRule(senderRegex); } attFetcher.AppendFilter(attFetcherSenderFilter); attFetcher.Execute(lastSyncTime); var attachmentFileNames = attFetcher.GetAttachmentFileNames(); // List<string> attachmentFileNames = Directory.GetFiles(@"C:\DataFeedApp\Scheduler\CnE\CoffedProductData").ToList<string>(); for (int i = attachmentFileNames.Count - 1; i >= 0; i--) { var tempattachname = attachmentFileNames[i]; if (tempattachname.IndexOf("Productinventory") != -1) { CofeedProductManager c = new CofeedProductManager(); c.GetCellsByFirstSheet(tempattachname, strInfo); } else if (tempattachname.IndexOf("week") != -1) { CoffedWeekManager c = new CoffedWeekManager(); c.GetCellsByFirstSheet(tempattachname, strInfo); } } var fold = new DirectoryInfo(attFetcherSavingPath); if (fold.Exists) { FileInfo[] files = fold.GetFiles(); foreach (FileInfo f in files)//删除目录下所有文件 { f.Delete(); } } if (attachmentFileNames.Count > 0) { MergeData m = new MergeData(); m.ExecuteCoffedAndMetalMax(conn, tran); strInfo.Append("Execute Procedure : CREATEMAXAGRICULTRUEOUTPUT\r\n"); strInfo.Append("Insert Into Table : GDT_AGRICULTRUEOUTPUTMAX, GDT_ENERGYYIELDMAX \r\n"); //生成MAX表; //ExecuteCoffedAndMetalMax tran.Commit(); } else { strInfo.Append("No files found\r\n"); } ; #endregion var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { tran.Rollback(); logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1); logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } }
public static void ExcuteMailSync(string jobType, DateTime syncStartTime, string attFetcherSavingPath, string senderFilter, string titleFilter, string userName, string password , Action <SCHEDULERLOG> saveLog , Action <List <string>, StringBuilder> syncCallback , Action <OracleConnection, OracleTransaction> mergeCallback ) { string connStr = ConfigurationManager.AppSettings["mergeData"]; OracleConnection conn = new OracleConnection(connStr); conn.Open(); OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); var lastSyncTime = syncStartTime; using (var cneEntities = new CnEEntities()) { var date = cneEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == jobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { lastSyncTime = date.Value; } } try { #region 执行数据同步程序 strInfo.AppendFormat("Source [Type: Email , Sender: {0} ,Title {1} ]\n", senderFilter, titleFilter); strInfo.AppendFormat("Destination [Type: {0} Address: {1}]\n", "Oracle", connStr); var attFetcherSever = ConfigurationManager.AppSettings["CnEServer"]; int attFetcherPort = int.Parse(ConfigurationManager.AppSettings["CnEPort"]); bool attFetcherUsingSsl = Convert.ToBoolean(ConfigurationManager.AppSettings["CnEUsingSsl"]); string attFetcherUserName = userName; string attFetcherPassWord = password; var attFetcher = new IMAP4AttFetcher(attFetcherSever, attFetcherPort, attFetcherUsingSsl, attFetcherUserName, attFetcherPassWord, attFetcherSavingPath); var attFetcherSenderFilter = new SenderFilter(); /*Filter example. If there are more filters , please write like below.*/ string[] senders = senderFilter.Split(';'); foreach (var sender in senders) { var senderRegex = new Regex(sender, RegexOptions.Compiled); attFetcherSenderFilter.SetRule(senderRegex); } attFetcher.AppendFilter(attFetcherSenderFilter); if (!string.IsNullOrEmpty(titleFilter)) { var attFetcherTitleFilter = new TitleFilter(); var titleRegex = new Regex(titleFilter, RegexOptions.Compiled); attFetcherTitleFilter.SetRule(titleRegex); attFetcher.AppendFilter(attFetcherTitleFilter); } attFetcher.Execute(lastSyncTime); /*Get the saved attachment names.*/ var attachmentFileNames = attFetcher.GetAttachmentFileNames(); //var attachmentFileNames=new List<string>(); //attachmentFileNames.Add(@"C:\DataFeedApp\Scheduler\CnE\路透数据20161109.xls"); StringBuilder sb = new StringBuilder(); //var attachmentFileNames = Directory.GetFiles(@"C:\xx\troil").ToList<string>(); syncCallback(attachmentFileNames, sb); var fold = new DirectoryInfo(attFetcherSavingPath); if (fold.Exists) { FileInfo[] files = fold.GetFiles(); foreach (FileInfo f in files)//删除目录下所有文件 { f.Delete(); } } if (attachmentFileNames.Count == 0) { strInfo.Append("No find files .\r\n"); } #endregion var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo + sb.ToString(); saveLog(logEntity); //merge data from temp to persistence if (mergeCallback != null) { mergeCallback(conn, tran); } tran.Commit(); } catch (Exception exception) { tran.Rollback(); logEntity.ENDTIME = DateTime.UtcNow.AddDays(-1); logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; saveLog(logEntity); } finally { conn.Close(); conn.Dispose(); } }