public void AddOrUpdate(NobelPrizeWinner nobelPrizeWinner)
{
using (this.unitOfWork)
{
var winner = this.nobelPrizeWinners.All()
.Where(w => w.Year == nobelPrizeWinner.Year &&
w.Name == nobelPrizeWinner.Name &&
w.PrizeName == nobelPrizeWinner.PrizeName)
.FirstOrDefault();
if (winner == null)
{
this.nobelPrizeWinners.Add(nobelPrizeWinner);
}
else
{
if (winner.CompareTo(nobelPrizeWinner) != 0)
{
winner.Category = nobelPrizeWinner.Category;
winner.BirthDate = nobelPrizeWinner.BirthDate;
winner.BirthPlace = nobelPrizeWinner.BirthPlace;
winner.Country = nobelPrizeWinner.Country;
winner.Residence = nobelPrizeWinner.Residence;
winner.FieldLanguage = nobelPrizeWinner.FieldLanguage;
winner.Motivation = nobelPrizeWinner.Motivation;
this.nobelPrizeWinners.Update(winner);
}
}
this.unitOfWork.Commit();
}
}
public IList <NobelPrizeWinner> Read(ExcelPackage excel)
{
ExcelWorksheet workSheet = excel.Workbook.Worksheets.First();
for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
{
var year = ushort.Parse(workSheet.Cells[rowNumber, 1].Text);
var category = (Category)Enum.Parse(typeof(Category), workSheet.Cells[rowNumber, 2].Text);
var name = workSheet.Cells[rowNumber, 3].Text;
var birthDate = workSheet.Cells[rowNumber, 4].Text;
var birthPlace = workSheet.Cells[rowNumber, 5].Text;
var country = workSheet.Cells[rowNumber, 6].Text;
var residence = workSheet.Cells[rowNumber, 7].Text;
var fieldLanguage = (workSheet.Cells[rowNumber, 8].Text);
var prizeName = workSheet.Cells[rowNumber, 9].Text;
var motivation = workSheet.Cells[rowNumber, 10].Text;
NobelPrizeWinner nobelPrizeWinner = new NobelPrizeWinner()
{
Year = year,
Category = category,
Name = name,
BirthDate = birthDate,
BirthPlace = birthPlace,
Country = country,
Residence = residence,
FieldLanguage = fieldLanguage,
PrizeName = prizeName,
Motivation = motivation
};
this.nobelPrizeWinners.Add(nobelPrizeWinner);
}
return(nobelPrizeWinners);
}
private void PopulateDatabase()
{
FileInfo file = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowsCount = worksheet.Dimension.Rows;
int colsCount = worksheet.Dimension.Columns;
for (int row = 2; row <= rowsCount; row++)
{
string rawText = string.Empty;
for (int col = 1; col <= colsCount; col++)
{
try
{
rawText += worksheet.Cells[row, col].Value.ToString() + Separator;
}
catch (NullReferenceException)
{
rawText += $"N/A{Separator}";
}
}
string[] winnerTokens = rawText.Split(Separator, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToArray();
string year = winnerTokens[0];
string category = winnerTokens[1];
string name = winnerTokens[2];
string birthdate = winnerTokens[3];
string birthPlace = winnerTokens[4];
string country = winnerTokens[5];
string residence = winnerTokens[6];
string fieldOrLanguage = winnerTokens[7];
string prizeName = winnerTokens[8];
string motivation = winnerTokens[9];
bool recordExists = this.RecordExists(year, category, name, birthdate, birthPlace, country, residence, fieldOrLanguage, prizeName, motivation);
if (recordExists)
{
continue;
}
NobelPrizeWinner winner = new NobelPrizeWinner
{
Year = year,
Category = category,
Name = name,
Birthdate = birthdate,
BirthPlace = birthPlace,
Country = country,
Residence = residence,
FieldOrLanguage = fieldOrLanguage,
PrizeName = prizeName,
Motivation = motivation
};
this.db.NobelPrizeWinners.Add(winner);
this.db.SaveChanges();
}
}
}
