I want to import data from Excel to DataBase using EPPLUS. From here I took code: https://www.paragon-inc.com/resources/blogs-posts/easy_excel_interaction_pt6

            using (var db = new DbEntities())
                {
                    for (var row = 2; row <= lastRow; row++)
                    {
                        var newRecord = new DB_USER
                        {
                            ID = Int32.Parse(worksheet.Cells[idColumn + row].Value.ToString()),
                            FIRST_NAME = worksheet.Cells[firstNameColumn + row].Value.ToString(),
                            LAST_NAME = worksheet.Cells[lastNameColumn + row].Value.ToString(),                               
                        };

                        db.DB_USER.Add(newRecord);
                        try
                        {
                            db.SaveChanges();
                            totalImported++;
                        }
                        catch (Exception ex)
                        {
                            resultMessages.Add(string.Format("Error in line #{0}: {1}\n", row,
                                ex.Message));
                        }
                    }
                }

Everything works fine if data in excel are correct. Problem is if any record has invalid data. For example, we have 3 records in excel:

  • ID: 21 | FIRST NAME: John | LAST NAME: Cage
  • ID: 1 | FIRST NAME: Mei | LAST NAME: Blue
  • ID: 21 | FIRST NAME: Nick | LAST NAME: Siri

And in database is already record with ID = 1. So 1st and 3rd should save, but 2nd shouldn't. The problem is that only first record is saving, the rest (2nd and 3rd) will get an error. I don't know why? Maybe because it's one transaction or what? It's weird. Can anyone tell me what should I do to save 1st and 3rd record? Not only 1st in this case?

Related posts

Recent Viewed