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(); } } }