public static void ExecNonQuerySp(string inName, OracleParameter[] inParms) { using (var vav = new VAVEntities()) { vav.Database.Connection.Open(); DbCommand cmd = vav.Database.Connection.CreateCommand(); cmd.CommandText = inName; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.AddRange(inParms); cmd.ExecuteNonQuery(); } }
/*empty table Iss_def */ public static string Delete_Iss_Def() { var result = "Success"; using (var Db = new VAVEntities()) { try { Db.Database.Connection.Open(); DbCommand cmd = Db.Database.Connection.CreateCommand(); cmd.CommandText = "vav.DeleteIssDef"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0; OracleParameter outPar = new OracleParameter(); outPar.ParameterName = "@Result"; outPar.DbType = DbType.String; outPar.Size = 400; outPar.Direction = ParameterDirection.Output; cmd.Parameters.Add(outPar); cmd.ExecuteNonQuery(); var ret = (string)cmd.Parameters["@Result"].Value; result = ret.Split('!').FirstOrDefault() == "Success" ? "Success" : "Fail"; } catch (Exception ex) { result = "Fail" + ex.Message; } finally { Db.Database.Connection.Close(); } return(result); } }
/// <summary> /// Sync data from GeniusDB and hosted in .144 which is the transfer DB /// </summary> /// <seealso cref="M:Spring.Scheduling.Quartz.QuartzJobObject.Execute(Quartz.JobExecutionContext)"/> protected override void ExecuteInternal(JobExecutionContext context) { var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"config\Sync-SdLocalRefinery.xml"); var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath)); // Set Connection string settingManager.Init(string.Empty);//初始化 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); var lastSyncTime = new DateTime(1900, 1, 1); var currentSyncTime = startTime.AddHours(8); using (var vavEntities = new VAVEntities()) { var date = vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { lastSyncTime = date.Value.AddHours(-0.5); } } try { using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, currentSyncTime)) { dataSync.PostTaskExecuted += (sender, e) => strInfo.AppendFormat ("Post sync task {0} is executed.\n", e.TaskName); dataSync.Init(); var sourceTable = settingManager.SourceTableName.Split(','); strInfo.Append(dataSync.SyncEachTableFromMaxMtime(sourceTable, "CreateDate")); //strInfo.Append(dataSync.Sync(sourceTable)); } var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { logEntity.ENDTIME = DateTime.UtcNow; logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } }
/// <summary> /// Sync china local data from sql server to oracle /// </summary> protected override void ExecuteInternal(JobExecutionContext context) { var startTime = DateTime.Now; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"config\China-local-sync.xml"); var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath)); // Set Connection string settingManager.Init(string.Empty);//初始化 mapping strInfo.AppendFormat("Bond-misc-sync.xml data Sync begin at {0}(System Time)\n", DateTime.Now); 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); var lastSyncTime = new DateTime(1900, 1, 1); var currentSyncTime = DateTime.Now; using (var vavEntities = new VAVEntities()) { var date = vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { lastSyncTime = date.Value.AddHours(-1.5); } } strInfo.AppendFormat("Last successfully sync time : {0}(System Time).\n", lastSyncTime); try { using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, currentSyncTime)) { dataSync.TableSynched += (sender, e) => strInfo.AppendFormat ("{0} rows have been synchronized from {1} to {2}.\n", e.NumOfRowsSynched, e.Source, e.Dest); dataSync.PostTaskExecuted += (sender, e) => strInfo.AppendFormat ("Post sync task {0} is executed.\n", e.TaskName); dataSync.Init(); var sourceTable = settingManager.SourceTableName.Split(','); dataSync.Sync(sourceTable); } strInfo.AppendFormat("{0} table(s) be synchronized.\n", settingManager.TableMappings.Count()); var endTime = DateTime.Now; strInfo.AppendFormat("Synchronization completed at {0}.\n", endTime); logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { logEntity.ENDTIME = DateTime.Now; logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } }
/// <summary> /// Sync data from GeniusDB and hosted in .144 which is the transfer DB /// </summary> /// <seealso cref="M:Spring.Scheduling.Quartz.QuartzJobObject.Execute(Quartz.JobExecutionContext)"/> protected override void ExecuteInternal(JobExecutionContext context) { if (DateTime.Now.Hour < 10 || DateTime.Now.Hour > 17) { return; } var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime }; var strInfo = new StringBuilder(); var lastSyncTime = new DateTime(2017, 06, 1); using (var vavEntities = new VAVEntities()) { var date = vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { lastSyncTime = date.Value.AddHours(8); } } try { string localBondCon = ConfigurationManager.AppSettings["143FileDB"]; var ds = new DataSet(); strInfo.Append("Query 143DB assertID\n"); using (OracleConnection conn = new OracleConnection(localBondCon)) { conn.Open(); OracleDataAdapter command = new OracleDataAdapter("SELECT ASSETID FROM BONDDELETE where ISVALID=1 and UPDATE_TIME>'" + lastSyncTime.ToString("dd-MMM-yyyy hh:mm:ss tt") + "'", conn); command.Fill(ds, "ds"); conn.Close(); if (ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0) { return; } strInfo.Append("Need to delete assertID:" + ds.Tables[0].Rows.Count + " \n"); } string proBondCon = ConfigurationManager.AppSettings["ProBondDB"]; using (OracleConnection conn = new OracleConnection(proBondCon)) { strInfo.Append("Delete 143DB assertID:\n"); conn.Open(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { strInfo.Append("ASSETID=" + ds.Tables[0].Rows[i][0] + ": \n"); int result; string sql = "delete GOVCORP_ASSET where ASSETID='" + ds.Tables[0].Rows[i][0] + "'"; using (OracleCommand cmd = new OracleCommand(sql, conn)) { result = cmd.ExecuteNonQuery(); } strInfo.Append("GOVCORP_ASSET " + result + " rows deleted. "); sql = "delete EJVBOND where ASSET_ID='" + ds.Tables[0].Rows[i][0] + "'"; using (OracleCommand cmd = new OracleCommand(sql, conn)) { result = cmd.ExecuteNonQuery(); } strInfo.Append("EJVBOND " + result + " rows deleted.\n\n"); } conn.Close(); strInfo.Append("Completed!\n"); } var endTime = DateTime.UtcNow; logEntity.ENDTIME = endTime; logEntity.JobStatus = JobStatus.Success; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { logEntity.ENDTIME = DateTime.UtcNow; logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } }
/// <summary> /// Execute the actual job. The job data map will already have been /// applied as object property values by execute. The contract is /// exactly the same as for the standard Quartz execute method. /// </summary> /// <seealso cref="M:Spring.Scheduling.Quartz.QuartzJobObject.Execute(Quartz.JobExecutionContext)"/> protected override void ExecuteInternal(JobExecutionContext context) { var startTime = DateTime.UtcNow; var logEntity = new SCHEDULERLOG { STARTTIME = startTime, JOBTYPE = JobType }; var strInfo = new StringBuilder(); string settingFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"config\dsos-new-increment-sync.xml"); var settingManager = new XmlSettingManager(File.ReadAllText(settingFilePath)); // Set Connection string //var entityConnectionString = // ConfigurationManager.ConnectionStrings["VAVEntities"].ConnectionString; //var connectionStringBuilder = new EntityConnectionStringBuilder(entityConnectionString); //var DestinationDbConn = connectionStringBuilder.ProviderConnectionString; settingManager.Init(string.Empty);//初始化 mapping strInfo.AppendFormat("DSOS Sync begin at {0}\n", startTime); 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); // Get Last successfully date time. var lastSyncTime = new DateTime(2015, 3, 1); using (var vavEntities = new VAVEntities()) { var date = vavEntities.SCHEDULERLOGs.Where(x => x.STATUS == 0 && x.JOBTYPE == this.JobType).Select( x => (DateTime?)x.STARTTIME).Max(); if (date != null) { lastSyncTime = date.Value; //Add Buffer lastSyncTime = lastSyncTime.AddHours(-1.5); } } strInfo.AppendFormat("Last successfully sync time : {0}(Eastern Standard Time).\n", startTime); try { List <string> tables; //if (hour >= 12 && hour <= 14) //sync iss_def //{ // //Delete iss_def // strInfo.AppendFormat("Delete iss_def at {0}.\n", DateTime.UtcNow.ToGMT8String()); // var re = SyncUtil.Delete_Iss_Def(); // strInfo.AppendFormat("Delete iss_def at {0}.\n Result: {1}", DateTime.UtcNow.ToGMT8String(), re); // tables = GetDsosSrcTablesToSync().ToList(); //} //else //{ // tables = GetDsosSrcTablesToSync().ToList(); // var s = tables.Single(t => t == "govcorp..iss_def"); // tables.Remove(s); //} tables = settingManager.TableMappings.Select(t => t.Source).ToList(); using (var dataSync = new DataSynchronizer(settingManager, lastSyncTime, startTime)) { dataSync.TableSynched += (sender, e) => strInfo.AppendFormat ("{0} rows have been synchronized from {1} view in DSOS to {2} table in VAV DB.\n", e.NumOfRowsSynched, e.Source, e.Dest); dataSync.PostTaskExecuted += (sender, e) => strInfo.AppendFormat ("Post sync task {0} is executed.\n", e.TaskName); dataSync.Init(); dataSync.Sync(tables); } strInfo.AppendFormat("{0} table(s) be synchronized.\n", settingManager.TableMappings.Count()); strInfo.AppendFormat("Synchronization completed at {0}.\n", DateTime.UtcNow.ToGMT8String()); //Update Bond Info strInfo.AppendFormat("Update Bond Info en&cn start at {0}.\n", DateTime.UtcNow.ToGMT8String()); //var result = SyncUtil.UpdateBondInfo(lastSyncTime, currentSyncTime); var endTime = DateTime.UtcNow; //strInfo.AppendFormat("Update Bond Info en&cn completed at {0}.\n Result: {1}", DateTime.UtcNow.ToGMT8String(), result); ////Rebuild Index //strInfo.AppendFormat("Rebuild Index at {0}.\n", DateTime.UtcNow.ToGMT8String()); //var result1 = SolrClient.RebuildIndex("full"); //strInfo.AppendFormat("Rebuild Index completed at {0}.\n Result: {1}", DateTime.UtcNow.ToGMT8String(), result1); logEntity.ENDTIME = endTime; //logEntity.JobStatus = (result == "Success" && result1 == "Success") ? JobStatus.Success : JobStatus.Fail; logEntity.RUNDETAIL = strInfo.ToString(); WriteLogEntity(logEntity); } catch (Exception exception) { logEntity.ENDTIME = DateTime.UtcNow; logEntity.JobStatus = JobStatus.Fail; logEntity.RUNDETAIL = strInfo + "\n" + exception; WriteLogEntity(logEntity); } }