public List <BusForeign> ExtractBusesForeign(string oracleConnString)
{
using (OracleConnection con = new OracleConnection(oracleConnString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = "select ID, BUS_NAME, BUS_NUMBER, VOLTAGE, FK_SUBSTATION_ID from BUS where 1=1 and BUS_NAME IS NOT NULL and ID IS NOT NULL and BUS_NUMBER IS NOT NULL and VOLTAGE IS NOT NULL and FK_SUBSTATION_ID 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 <BusForeign> busesForeign = new List <BusForeign>();
while (reader.Read())
{
BusForeign busForeign = new BusForeign();
busForeign.WebUatId = reader.GetInt32(0);
busForeign.Name = reader.GetString(1);
busForeign.BusNumber = reader.GetInt32(2);
busForeign.VoltageWebUatId = reader.GetInt32(3);
busForeign.AssSubstationWebUatId = reader.GetInt32(4);
busesForeign.Add(busForeign);
}
reader.Dispose();
return(busesForeign);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
}
}
public async Task <Bus> LoadSingleAsync(WRLDCWarehouseDbContext _context, ILogger _log, BusForeign busForeign, EntityWriteOption opt)
{
// check if entity already exists
Bus existingBus = await _context.Buses.SingleOrDefaultAsync(b => b.WebUatId == busForeign.WebUatId);
// check if we should not modify existing entities
if (opt == EntityWriteOption.DontReplace && existingBus != null)
{
return(existingBus);
}
// find the substation of the bus via the AssSubstation WebUatId
int subWebUatId = busForeign.AssSubstationWebUatId;
Substation busSubstation = await _context.Substations.SingleOrDefaultAsync(s => s.WebUatId == subWebUatId);
// if Substation doesnot exist, skip the import. Ideally, there should not be such case
if (busSubstation == null)
{
_log.LogCritical($"Unable to find Substation with webUatId {subWebUatId} while inserting Bus with webUatId {busForeign.WebUatId} and name {busForeign.Name}");
return(null);
}
// find the voltage of the bus via the VoltLevel WebUatId
int voltLevelWebUatId = busForeign.VoltageWebUatId;
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 Bus with webUatId {busForeign.WebUatId} and name {busForeign.Name}");
return(null);
}
// check if we have to replace the entity completely
if (opt == EntityWriteOption.Replace && existingBus != null)
{
_context.Buses.Remove(existingBus);
}
// if entity is not present, then insert
if (existingBus == null || (opt == EntityWriteOption.Replace && existingBus != null))
{
Bus newBus = new Bus();
newBus.Name = busForeign.Name;
newBus.BusNumber = busForeign.BusNumber.ToString();
newBus.SubstationId = busSubstation.SubstationId;
newBus.VoltLevelId = voltLevel.VoltLevelId;
newBus.WebUatId = busForeign.WebUatId;
_context.Buses.Add(newBus);
await _context.SaveChangesAsync();
return(newBus);
}
// check if we have to modify the entity
if (opt == EntityWriteOption.Modify && existingBus != null)
{
existingBus.Name = busForeign.Name;
existingBus.BusNumber = busForeign.BusNumber.ToString();
existingBus.SubstationId = busSubstation.SubstationId;
existingBus.VoltLevelId = voltLevel.VoltLevelId;
await _context.SaveChangesAsync();
return(existingBus);
}
return(null);
}
