private void RemoveLink() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = $@"DROP PUBLIC DATABASE LINK ""{AppConfig.OracleRemoteLinkName}""" }); }
private void CompilePkg() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = "ALTER PACKAGE ABT.PKG_UPDATE COMPILE SPECIFICATION" }); }
private void CompilePkgBody() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = "ALTER PACKAGE ABT.PKG_UPDATE COMPILE BODY" }); }
private void RemovePkg() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = "DROP PACKAGE ABT.PKG_UPDATE" }); }
void Process(string signature, string obj, string comment) { msg($"[{DateFormat( DateTime.Now )}] {obj} обрабатывается...", logList); var start_time = DateTime.Now; List <OracleCommand> sqls = new List <OracleCommand>(); sqls.Add(new OracleCommand() { CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" SET {obj}=0 WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'", }); sqls.Add(new OracleCommand() { CommandType = CommandType.StoredProcedure, CommandText = $"ABT.ABT_REPLICATION.PROCESS_" + (obj == "PRICES_CACHE" ? "PRICES" : obj) }); OracleDbService.ExecuteCommand(sqls); sqls.Clear(); var time = Math.Round((DateTime.Now - start_time).TotalDays * 24 * 60 * 60).ToString(); sqls.Add(new OracleCommand() { CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" " + $@"SET {obj}={time} WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'", }); OracleDbService.ExecuteCommand(sqls); msg($"[{DateFormat( DateTime.Now )}]{obj} обработано на {signature} за {time} сек", logList); progressBar.Invoke(new Action(() => { progressBar.Value += 1; })); }
void Download(string signature, string obj, string comment) { msg($"[{DateFormat( DateTime.Now )}] {obj} скачивается...", logList); var start_time = DateTime.Now; List <OracleCommand> sqls = new List <OracleCommand>(); sqls.Add(new OracleCommand() { CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" SET {obj}=0 WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'", }); sqls.Add(new OracleCommand() { CommandType = CommandType.StoredProcedure, CommandText = $"ABT.PKG_UPDATE.{obj}" }); OracleDbService.ExecuteCommand(sqls); sqls.Clear(); var time = Math.Round((DateTime.Now - start_time).TotalDays * 24 * 60 * 60).ToString(); sqls.Add(new OracleCommand() { CommandText = $@"UPDATE abt.update_log2@""{AppConfig.OracleRemoteLinkName}"" " + $@"SET {obj}={time} WHERE SIGNATURE='{signature}' and ""COMMENT""='{comment}'", }); OracleDbService.ExecuteCommand(sqls); msg($"[{DateFormat( DateTime.Now )}]{obj} скачалось за {time} сек", logList); progressBar.Invoke(new Action(() => { progressBar.Value += 1; })); }
private void CreateLink() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = $@"CREATE PUBLIC DATABASE LINK ""{AppConfig.OracleRemoteLinkName}"" CONNECT TO {AppConfig.OracleRemoteUser} IDENTIFIED BY ""{AppConfig.OracleRemotePassword}"" USING '{AppConfig.OracleRemoteLinkTNS}'" }); }
private void CreatePkg() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = "CREATE OR REPLACE PACKAGE ABT.pkg_update IS \n" + "\tPROCEDURE BAR_CODES; \n" + "\tPROCEDURE CONTRACTORS; \n" + "\tPROCEDURE COUNTRIES; \n" + "\tPROCEDURE CUSTOMERS; \n" + "\tPROCEDURE GOODS; \n" + "\tPROCEDURE GROUPS; \n" + "\tPROCEDURE GROUP_ITEMS; \n" + "\tPROCEDURE ITEMS; \n" + "\tPROCEDURE PRICES_CACHE; \n" + "END pkg_update; \n" }); }
private void CreatePkgBody() { OracleDbService.ExecuteCommand(new OracleCommand() { CommandText = "CREATE OR REPLACE PACKAGE BODY pkg_update IS \n" + "PROCEDURE BAR_CODES IS BEGIN\n" + " DELETE FROM ABT.IMPORT_BAR_CODES;\n" + $" INSERT INTO ABT.IMPORT_BAR_CODES SELECT * FROM ABT.UPDATE_BAR_CODES@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END BAR_CODES;\n" + "PROCEDURE CONTRACTORS IS BEGIN\n" + " DELETE FROM ABT.IMPORT_CONTRACTORS;\n" + $" INSERT INTO ABT.IMPORT_CONTRACTORS SELECT * FROM ABT.UPDATE_CONTRACTORS@\"{AppConfig.OracleRemoteLinkName}\" WHERE ID_DISTRICT IN (SELECT ID FROM REF_DISTRICTS RD); COMMIT;\n"+ "END CONTRACTORS;\n" + "PROCEDURE COUNTRIES IS BEGIN\n" + " DELETE FROM ABT.IMPORT_COUNTRIES;\n" + $" INSERT INTO ABT.IMPORT_COUNTRIES SELECT * FROM ABT.UPDATE_COUNTRIES@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END COUNTRIES;\n" + "PROCEDURE CUSTOMERS IS BEGIN\n" + " DELETE FROM ABT.IMPORT_CUSTOMERS;\n" + $" INSERT INTO ABT.IMPORT_CUSTOMERS SELECT * FROM ABT.UPDATE_CUSTOMERS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END CUSTOMERS;\n" + "PROCEDURE GOODS IS BEGIN\n" + " DELETE FROM ABT.IMPORT_GOODS;\n" + $" INSERT INTO ABT.IMPORT_GOODS SELECT * FROM ABT.UPDATE_GOODS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END GOODS;\n" + "PROCEDURE GROUPS IS BEGIN\n" + " DELETE FROM ABT.IMPORT_GROUPS;\n" + $" INSERT INTO ABT.IMPORT_GROUPS SELECT * FROM ABT.UPDATE_GROUPS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END GROUPS;\n" + "PROCEDURE GROUP_ITEMS IS BEGIN\n" + " DELETE FROM ABT.IMPORT_GROUP_ITEMS;\n" + $" INSERT INTO ABT.IMPORT_GROUP_ITEMS SELECT * FROM ABT.UPDATE_GROUP_ITEMS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END GROUP_ITEMS;\n" + "PROCEDURE ITEMS IS BEGIN\n" + " DELETE FROM ABT.IMPORT_ITEMS;\n" + $" INSERT INTO ABT.IMPORT_ITEMS SELECT * FROM ABT.UPDATE_ITEMS@\"{AppConfig.OracleRemoteLinkName}\"; COMMIT;\n"+ "END ITEMS;\n" + "PROCEDURE PRICES_CACHE IS BEGIN\n" + " DELETE FROM ABT.IMPORT_PRICES_CACHE;\n" + $" INSERT INTO ABT.IMPORT_PRICES_CACHE SELECT * FROM ABT.UPDATE_PRICES_CACHE@\"{AppConfig.OracleRemoteLinkName}\" WHERE ID_PRICE_TYPE IN (SELECT ID FROM REF_PRICE_TYPES); COMMIT;"+ "END PRICES_CACHE;\n" + "END pkg_update;" }); }