public List <FilterBankForeign> ExtractFilterBanksForeign(string oracleConnString) { using (OracleConnection con = new OracleConnection(oracleConnString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; cmd.CommandText = @"select ID, REGION_ID, STATE_ID, SUBSTATION_ID, VOLTAGE_ID, MVAR, IS_SWITCHABLE, FILTERBANK_NUMBER, FILTERBANK_NAME from REPORTING_WEB_UI_UAT.FILTER_BANK where :id=1"; // Assign id parameter OracleParameter id = new OracleParameter("id", 1); cmd.Parameters.Add(id); //Execute the command and use DataReader to display the data OracleDataReader reader = cmd.ExecuteReader(); List <FilterBankForeign> fbsForeign = new List <FilterBankForeign>(); while (reader.Read()) { FilterBankForeign fbForeign = new FilterBankForeign(); fbForeign.WebUatId = reader.GetInt32(0); fbForeign.RegionWebUatId = reader.GetInt32(1); fbForeign.StateWebUatId = reader.GetInt32(2); fbForeign.SubstationWebUatId = reader.GetInt32(3); fbForeign.VoltLevelWebUatId = reader.GetInt32(4); fbForeign.MVARCapacity = reader.GetInt32(5); fbForeign.IsSwitchable = reader.GetInt32(6); fbForeign.FilterBankNumber = reader.GetInt32(7); if (!reader.IsDBNull(8)) { fbForeign.Name = reader.GetString(8); } fbsForeign.Add(fbForeign); } reader.Dispose(); return(fbsForeign); } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } } } }
public async Task <FilterBank> LoadSingleAsync(WRLDCWarehouseDbContext _context, ILogger _log, FilterBankForeign fbForeign, EntityWriteOption opt) { // check if entity already exists FilterBank existingFb = await _context.FilterBanks.SingleOrDefaultAsync(lr => lr.WebUatId == fbForeign.WebUatId); // check if we should not modify existing entities if (opt == EntityWriteOption.DontReplace && existingFb != null) { return(existingFb); } // find the region int regionWebUatId = fbForeign.RegionWebUatId; Region region = await _context.Regions.SingleOrDefaultAsync(r => r.WebUatId == regionWebUatId); // if region doesnot exist, skip the import. Ideally, there should not be such case if (region == null) { _log.LogCritical($"Unable to find region with webUatId {regionWebUatId} while inserting FilterBank with webUatId {fbForeign.WebUatId} and name {fbForeign.Name}"); return(null); } // find the State of the LineReactor via the State WebUatId int stateWebUatId = fbForeign.StateWebUatId; State state = await _context.States.SingleOrDefaultAsync(s => s.WebUatId == stateWebUatId); // if state doesnot exist, skip the import. Ideally, there should not be such case if (state == null) { _log.LogCritical($"Unable to find State with webUatId {stateWebUatId} while inserting FilterBank with webUatId {fbForeign.WebUatId} and name {fbForeign.Name}"); return(null); } // find the Substation of the LineReactor via the Substation WebUatId int ssWebUatId = fbForeign.SubstationWebUatId; Substation substation = await _context.Substations.SingleOrDefaultAsync(ss => ss.WebUatId == ssWebUatId); // if Substation doesnot exist, skip the import. Ideally, there should not be such case if (substation == null) { _log.LogCritical($"Unable to find Substation with webUatId {ssWebUatId} while inserting FilterBank with webUatId {fbForeign.WebUatId} and name {fbForeign.Name}"); return(null); } int voltLevelWebUatId = fbForeign.VoltLevelWebUatId; VoltLevel voltLevel = await _context.VoltLevels.SingleOrDefaultAsync(v => v.WebUatId == voltLevelWebUatId); // if voltLevel doesnot exist, skip the import. Ideally, there should not be such case if (voltLevel == null) { _log.LogCritical($"Unable to find VoltLevel with webUatId {voltLevelWebUatId} while inserting FilterBank with webUatId {fbForeign.WebUatId} and name {fbForeign.Name}"); return(null); } // check if we have to replace the entity completely if (opt == EntityWriteOption.Replace && existingFb != null) { _context.FilterBanks.Remove(existingFb); } // if entity is not present, then insert or check if we have to replace the entity completely if (existingFb == null || (opt == EntityWriteOption.Replace && existingFb != null)) { FilterBank newFb = new FilterBank(); newFb.Name = fbForeign.Name; newFb.RegionId = region.RegionId; newFb.StateId = state.StateId; newFb.SubstationId = substation.SubstationId; newFb.VoltLevelId = voltLevel.VoltLevelId; newFb.IsSwitchable = fbForeign.IsSwitchable == 0 ? false : true; newFb.FilterBankNumber = fbForeign.FilterBankNumber.ToString(); newFb.WebUatId = fbForeign.WebUatId; _context.FilterBanks.Add(newFb); await _context.SaveChangesAsync(); return(newFb); } // check if we have to modify the entity if (opt == EntityWriteOption.Modify && existingFb != null) { existingFb.Name = fbForeign.Name; existingFb.RegionId = region.RegionId; existingFb.StateId = state.StateId; existingFb.SubstationId = substation.SubstationId; existingFb.VoltLevelId = voltLevel.VoltLevelId; existingFb.IsSwitchable = fbForeign.IsSwitchable == 0 ? false : true; existingFb.FilterBankNumber = fbForeign.FilterBankNumber.ToString(); await _context.SaveChangesAsync(); return(existingFb); } return(null); }