static async Task <int> Main()
{
IConfiguration Config = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", false, true)
.AddJsonFile("appsettings.local.json", true, true)
.Build();
Log.Logger = new LoggerConfiguration()
.ReadFrom.Configuration(Config)
.MinimumLevel.Debug()
.WriteTo.Console(restrictedToMinimumLevel: LogEventLevel.Debug)
.CreateLogger();
DbContextOptionsBuilder <RecordContext> builder = new DbContextOptionsBuilder <RecordContext>()
.UseSqlServer(Config.GetConnectionString("ExcelUpdater"));
using RecordContext ctx = new RecordContext(builder.Options);
#region SET DEFAULT HTTP-REQUEST HEADERS
client.DefaultRequestHeaders.Add("Accept-Encoding", "gzip, deflate, br");
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
#endregion
#region SET LOGIN-FORM VALUES
KeyValuePair <string, string>[] credentials =
{
new KeyValuePair <string, string>("EmailAddress", Config["ClientCredential:Email"]),
new KeyValuePair <string, string>("Password", Config["ClientCredential:Password"])
};
#endregion
try
{
//AUTHENTICATE USER
await PostToPageAsync(new Uri(Config["LoginUrl"]), credentials);
//DOWNLOAD EXCEL TO LOCAL PATH
await DownloadFileAsync(new Uri(Config["ExcelUrl"]), Config["ExcelPath"]);
var excelStream = await GetStreamAsync(new Uri(Config["ExcelUrl"]));
Record latestRecord = await ctx.Records.OrderBy(x => x.My_Date).LastOrDefaultAsync();
ISheet sheet = new XSSFWorkbook(excelStream).GetSheetAt(0);
List <Record> recsToInsert = new List <Record>();
Log.Information("[Excel] Parsing .xlsx file..");
for (int i = 3; i < sheet.LastRowNum; i++)
{
var rowValues = new List <string>();
IRow row = sheet.GetRow(i);
if (DateTime.TryParseExact(row.GetCell(0).ToString().Trim(), "dd-MMM-yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var Date) && latestRecord != null)
{
if (Date < latestRecord.My_Date)
{
continue;
}
}
for (int j = 0; j < row.LastCellNum; j++)
{
rowValues.Add(row.GetCell(j).ToString().Trim());
}
Record current = Record.Create(rowValues);
recsToInsert.Add(current);
}
//UPDATE DATABASE
Log.Information(string.Format("[DB]Inserting {0} records in batches", recsToInsert.Count));
ctx.BulkInsertOrUpdate(recsToInsert);
Log.Information("[DB] Records Inserted");
}
catch (Exception e)
{
Log.Fatal(e, "[ERROR] Application failed");
throw;
}
finally
{
Log.CloseAndFlush();
}
return(0);
}
