public List <AcTransmissionLineForeign> ExtractAcTransLineForeign(string oracleConnString)
{
using (OracleConnection con = new OracleConnection(oracleConnString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = "select ID, LINE_NAME, VOLTAGE_LEVEL, FROM_SUB_ID, TO_SUB_ID from AC_TRANS_LINE_MASTER where :id=1 and LINE_NAME IS NOT NULL and ID IS NOT NULL and VOLTAGE_LEVEL IS NOT NULL and FROM_SUB_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 <AcTransmissionLineForeign> acTranLinesForeign = new List <AcTransmissionLineForeign>();
while (reader.Read())
{
AcTransmissionLineForeign acTransLineForeign = new AcTransmissionLineForeign();
acTransLineForeign.WebUatId = reader.GetInt32(0);
acTransLineForeign.Name = reader.GetString(1);
acTransLineForeign.VoltLevelWebUatId = reader.GetInt32(2);
acTransLineForeign.FromSSWebUatId = reader.GetInt32(3);
acTransLineForeign.ToSSWebUatId = reader.GetInt32(3);
acTranLinesForeign.Add(acTransLineForeign);
}
reader.Dispose();
return(acTranLinesForeign);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
}
}
public async Task <AcTransmissionLine> LoadSingleAsync(WRLDCWarehouseDbContext _context, ILogger _log, AcTransmissionLineForeign acTrForeign, EntityWriteOption opt)
{
// check if entity already exists
AcTransmissionLine existingAcTrLine = await _context.AcTransmissionLines.SingleOrDefaultAsync(acTr => acTr.WebUatId == acTrForeign.WebUatId);
// check if we should not modify existing entity
if (opt == EntityWriteOption.DontReplace && existingAcTrLine != null)
{
return(existingAcTrLine);
}
// find the from Substation via FromSubstattion WebUatId
int fromSSebUatId = acTrForeign.FromSSWebUatId;
Substation fromSS = await _context.Substations.SingleOrDefaultAsync(ss => ss.WebUatId == fromSSebUatId);
// if FromSubstation doesnot exist, skip the import. Ideally, there should not be such case
if (fromSS == null)
{
_log.LogCritical($"Unable to find FromSubstation with webUatId {fromSSebUatId} while inserting AcTransmissionLine with webUatId {acTrForeign.WebUatId} and name {acTrForeign.Name}");
return(null);
}
// find the To Substation via ToSubstattion WebUatId
int toSSebUatId = acTrForeign.ToSSWebUatId;
Substation toSS = await _context.Substations.SingleOrDefaultAsync(ss => ss.WebUatId == toSSebUatId);
// if ToSubstation doesnot exist, skip the import. Ideally, there should not be such case
if (toSS == null)
{
_log.LogCritical($"Unable to find ToSubstation with webUatId {toSSebUatId} while inserting AcTransmissionLine with webUatId {acTrForeign.WebUatId} and name {acTrForeign.Name}");
return(null);
}
// find the Voltage of the substation via the Voltage WebUatId
int voltWebUatId = acTrForeign.VoltLevelWebUatId;
VoltLevel voltLevel = await _context.VoltLevels.SingleOrDefaultAsync(vl => vl.WebUatId == voltWebUatId);
// if voltage level doesnot exist, skip the import. Ideally, there should not be such case
if (voltLevel == null)
{
_log.LogCritical($"Unable to find VoltLevel with webUatId {voltWebUatId} while inserting AcTransmissionLine with webUatId {acTrForeign.WebUatId} and name {acTrForeign.Name}");
return(null);
}
// check if we have to replace the entity completely
if (opt == EntityWriteOption.Replace && existingAcTrLine != null)
{
_context.AcTransmissionLines.Remove(existingAcTrLine);
}
// if entity is not present, then insert or check if we have to replace the entity completely
if (existingAcTrLine == null || (opt == EntityWriteOption.Replace && existingAcTrLine != null))
{
AcTransmissionLine newAcTrLine = new AcTransmissionLine();
newAcTrLine.Name = acTrForeign.Name;
newAcTrLine.VoltLevelId = voltLevel.VoltLevelId;
newAcTrLine.FromSubstationId = fromSS.SubstationId;
newAcTrLine.ToSubstationId = toSS.SubstationId;
newAcTrLine.WebUatId = acTrForeign.WebUatId;
_context.AcTransmissionLines.Add(newAcTrLine);
await _context.SaveChangesAsync();
return(newAcTrLine);
}
// check if we have to modify the entity
if (opt == EntityWriteOption.Modify && existingAcTrLine != null)
{
existingAcTrLine.Name = acTrForeign.Name;
existingAcTrLine.VoltLevelId = voltLevel.VoltLevelId;
existingAcTrLine.FromSubstationId = fromSS.SubstationId;
existingAcTrLine.ToSubstationId = toSS.SubstationId;
await _context.SaveChangesAsync();
return(existingAcTrLine);
}
return(null);
}
