public List <StateForeign> ExtractStatesForeign(string oracleConnString)
{
using (OracleConnection con = new OracleConnection(oracleConnString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = "select ID, SHORT_NAME, FULL_NAME, REGION_ID from SRLDC_STATE_MASTER where :id=1 and SHORT_NAME IS NOT NULL and ID IS NOT NULL and FULL_NAME IS NOT NULL and REGION_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 <StateForeign> states = new List <StateForeign>();
while (reader.Read())
{
StateForeign state = new StateForeign();
state.WebUatId = reader.GetInt32(0);
state.ShortName = reader.GetString(1);
state.FullName = reader.GetString(2);
state.RegionWebUatId = reader.GetInt32(3);
states.Add(state);
}
reader.Dispose();
return(states);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
}
}
public async Task <State> LoadSingleAsync(WRLDCWarehouseDbContext _context, ILogger _log, StateForeign stateForeign, EntityWriteOption opt)
{
// check if entity already exists
State existingState = await _context.States.SingleOrDefaultAsync(r => r.WebUatId == stateForeign.WebUatId);
// check if we should not modify existing entities
if (opt == EntityWriteOption.DontReplace && existingState != null)
{
return(existingState);
}
// find the region of the state via the region WebUatId
int regionWebUatId = stateForeign.RegionWebUatId;
Region stateRegion = await _context.Regions.SingleOrDefaultAsync(r => r.WebUatId == regionWebUatId);
// if region doesnot exist, skip the import. Ideally, there should not be such case
if (stateRegion == null)
{
_log.LogCritical($"Unable to find region with webUatId {regionWebUatId} while inserting state with webUatId {stateForeign.WebUatId} and name {stateForeign.FullName}");
return(null);
}
// check if we have to replace the entity completely
if (opt == EntityWriteOption.Replace && existingState != null)
{
_context.States.Remove(existingState);
}
// if region is not present, then insert or check if we have to replace the entity completely
if (existingState == null || (opt == EntityWriteOption.Replace && existingState != null))
{
State newState = new State();
newState.FullName = stateForeign.FullName;
newState.ShortName = stateForeign.ShortName;
newState.RegionId = stateRegion.RegionId;
newState.WebUatId = stateForeign.WebUatId;
_context.States.Add(newState);
await _context.SaveChangesAsync();
return(newState);
}
// check if we have to modify the entity
if (opt == EntityWriteOption.Modify && existingState != null)
{
existingState.FullName = stateForeign.FullName;
existingState.ShortName = stateForeign.ShortName;
existingState.RegionId = stateRegion.RegionId;
await _context.SaveChangesAsync();
return(existingState);
}
return(null);
}
