public async Task <Transformer> LoadSingleAsync(WRLDCWarehouseDbContext _context, ILogger _log, TransformerForeign trForeign, EntityWriteOption opt) { // check if entity already exists Transformer existingTr = await _context.Transformers.SingleOrDefaultAsync(tr => tr.WebUatId == trForeign.WebUatId); // check if we should not modify existing entities if (opt == EntityWriteOption.DontReplace && existingTr != null) { return(existingTr); } // check if substation type is valid string ssTypeSubstation = "SubStation"; string ssTypeGenStation = "Generating Station"; if (!(trForeign.StationType == ssTypeSubstation || trForeign.StationType == ssTypeGenStation)) { _log.LogCritical($"substation type is not {ssTypeSubstation} or {ssTypeGenStation} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); return(null); } MajorSubstation hvSubstation = null; int hvSubstationWebUatId = -1; if (trForeign.StationType == ssTypeSubstation) { // The transformer is in Substation hvSubstationWebUatId = trForeign.HVStationWebUatId; hvSubstation = await _context.MajorSubstations.SingleOrDefaultAsync(hvss => hvss.WebUatId == hvSubstationWebUatId); if (hvSubstation == null) { _log.LogCritical($"Unable to find MajorSubstation with webUatId {hvSubstationWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); return(null); } } GeneratingStation hvGenStation = null; int hvGenstationWebUatId = -1; if (trForeign.StationType == ssTypeGenStation) { // The transformer is in GeneratingStation hvGenstationWebUatId = trForeign.HVStationWebUatId; hvGenStation = await _context.GeneratingStations.SingleOrDefaultAsync(hvgt => hvgt.WebUatId == hvGenstationWebUatId); if (hvGenStation == null) { _log.LogCritical($"Unable to find GeneratingStation with webUatId {hvGenstationWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); return(null); } } // find the HV Voltage of the Transformer via the Voltage WebUatId int hvVoltWebUatId = trForeign.HighVoltLevelWebUatId; VoltLevel hvVolt = await _context.VoltLevels.SingleOrDefaultAsync(vl => vl.WebUatId == hvVoltWebUatId); // if voltage level doesnot exist, skip the import. Ideally, there should not be such case if (hvVolt == null) { _log.LogCritical($"Unable to find HV VoltLevel with webUatId {hvVoltWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); return(null); } // find the LV Voltage of the Transformer via the Voltage WebUatId int lvVoltWebUatId = trForeign.LowVoltLevelWebUatId; VoltLevel lvVolt = await _context.VoltLevels.SingleOrDefaultAsync(vl => vl.WebUatId == lvVoltWebUatId); // if voltage level doesnot exist, skip the import. Ideally, there should not be such case if (lvVolt == null) { _log.LogCritical($"Unable to find LV VoltLevel with webUatId {lvVoltWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); // uncomment this after vendor complies for non null LV voltage types // return null; } // find the State of the substation via the State WebUatId int stateWebUatId = trForeign.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 Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); return(null); } // find the TransformerType of the Transformer via the TransformerTypeWebUatId int trTypeWebUatId = trForeign.TransTypeWebUatId; TransformerType trType = await _context.TransformerTypes.SingleOrDefaultAsync(trt => trt.WebUatId == trTypeWebUatId); // if TransformerType doesnot exist, skip the import. Ideally, there should not be such case if (trType == null) { _log.LogCritical($"Unable to find TransformerType with webUatId {trTypeWebUatId} while inserting Transformer with webUatId {trForeign.WebUatId} and name {trForeign.Name}"); return(null); } // check if we have to replace the entity completely if (opt == EntityWriteOption.Replace && existingTr != null) { _context.Transformers.Remove(existingTr); } // if entity is not present, then insert or check if we have to replace the entity completely if (existingTr == null || (opt == EntityWriteOption.Replace && existingTr != null)) { Transformer newTr = new Transformer(); newTr.Name = trForeign.Name; newTr.StationType = trForeign.StationType; newTr.HighVoltLevelId = hvVolt.VoltLevelId; if (lvVolt != null) { newTr.LowVoltLevelId = lvVolt.VoltLevelId; } newTr.TransformerNumber = trForeign.TransformerNumber; newTr.TransformerTypeId = trType.TransformerTypeId; newTr.StateId = state.StateId; newTr.MVACapacity = trForeign.MVACapacity; newTr.CodDate = trForeign.CodDate; newTr.CommDate = trForeign.CommDate; newTr.DecommDate = trForeign.DecommDate; newTr.WebUatId = trForeign.WebUatId; if (trForeign.StationType == ssTypeSubstation) { newTr.HvSubstationId = hvSubstation.MajorSubstationId; } else if (trForeign.StationType == ssTypeGenStation) { newTr.HvGeneratingStationId = hvGenStation.GeneratingStationId; } _context.Transformers.Add(newTr); await _context.SaveChangesAsync(); return(newTr); } // check if we have to modify the entity if (opt == EntityWriteOption.Modify && existingTr != null) { existingTr.Name = trForeign.Name; existingTr.StationType = trForeign.StationType; existingTr.HighVoltLevelId = hvVolt.VoltLevelId; if (lvVolt != null) { existingTr.LowVoltLevelId = lvVolt.VoltLevelId; } existingTr.TransformerNumber = trForeign.TransformerNumber; existingTr.TransformerTypeId = trType.TransformerTypeId; existingTr.StateId = state.StateId; existingTr.MVACapacity = trForeign.MVACapacity; existingTr.CodDate = trForeign.CodDate; existingTr.CommDate = trForeign.CommDate; existingTr.DecommDate = trForeign.DecommDate; if (trForeign.StationType == ssTypeSubstation) { existingTr.HvSubstationId = hvSubstation.MajorSubstationId; } else if (trForeign.StationType == ssTypeGenStation) { existingTr.HvGeneratingStationId = hvGenStation.GeneratingStationId; } await _context.SaveChangesAsync(); return(existingTr); } return(null); }
public List <TransformerForeign> ExtractTransformersForeign(string oracleConnString) { using (OracleConnection con = new OracleConnection(oracleConnString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; cmd.CommandText = @"select ID, TRANSFORMER_NAME, HV_STATION_ID, CIRCUIT_NUMBER, TYPE_GT_ICT, MVA_CAPACITY, DATETIME_OF_COMMISSIONING, DATETIME_OF_DECOMMISSIONING, DATETIME_OF_COD, LOCATION_ID, STATIONTYPE, HV_VOLTAGE_LEVEL, LV_VOLTAGE_LEVEL from TRANSFORMER where :id=1 AND TRANSFORMER_NAME IS NOT NULL and STATIONTYPE IS NOT NULL and HV_STATION_ID IS NOT NULL and CIRCUIT_NUMBER IS NOT NULL and TYPE_GT_ICT IS NOT NULL and LOCATION_ID IS NOT NULL AND HV_VOLTAGE_LEVEL IS NOT NULL"; // 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 <TransformerForeign> transformersForeign = new List <TransformerForeign>(); while (reader.Read()) { TransformerForeign transForeign = new TransformerForeign(); transForeign.WebUatId = reader.GetInt32(0); transForeign.Name = reader.GetString(1); transForeign.HVStationWebUatId = reader.GetInt32(2); transForeign.TransformerNumber = reader.GetInt32(3); transForeign.TransTypeWebUatId = reader.GetInt32(4); transForeign.MVACapacity = reader.GetDecimal(5); transForeign.CommDate = reader.GetDateTime(6); transForeign.DecommDate = reader.GetDateTime(7); transForeign.CodDate = reader.GetDateTime(8); transForeign.StateWebUatId = reader.GetInt32(9); transForeign.StationType = reader.GetString(10); transForeign.HighVoltLevelWebUatId = reader.GetInt32(11); // checking for null values due to vendor non compliance if (!reader.IsDBNull(12)) { transForeign.LowVoltLevelWebUatId = reader.GetInt32(12); } else { transForeign.LowVoltLevelWebUatId = -1; } transformersForeign.Add(transForeign); } reader.Dispose(); return(transformersForeign); } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } } } }