simbada simbada - 1 month ago 7
C# Question

Continue insert if error occurs in foreach

I have a nested list and I am iterating over it and inserting data into my database, only if all child list items are successfully inserted in database.

You can see my code I am using

try catch
for nested iteration and as well as for outer iteration and whole code is wrapped inside
BeginTransaction.


This code works fine as long as I provide valid data.

But, if nested iteration failed due to some validation issue
(i.e. SubjectCategoryId not provided )
it exit the inner foreach after doing all iterations, increment the outer foreach, try to perform
SaveChanges
but fails by saying Object Null reference error.

Feel free to ask me if you need to more info. I feel I am missing some minor point especially when dealing with nested foreach inside
BeginTransaction
.

Do you see any issue with my code structure? I am using EF 5

My code:

foreach (var student in Student)
{
using (var transaction = _context.Database.BeginTransaction())
{
try
{
Student student = new Student
{
StudentNo = item.Stu_No,
Name = item.FullName,
Description = item.Description,
Phone = item.ContactNo,
Address = item.Location
Status = item.status
};

_context.Student.Add(student);
_context.SaveChanges(); // Get Object Null reference error for second iteration if child iteration fails. But works fine if no data issue.

var subjectsInfo = student.Subject_Info.GroupBy(pt => new { pt.Subject_Id, pt.SubjectDesc }).ToList();
SubjectInfo subInfo = new SubjectInfo();
SubjectCategory subCat = new SubjectCategory();

foreach(var subjectInfo in subjectsInfo)
{
try
{
subInfo.Subject_Id = subjectInfo.Id;
subInfo.Description = subjectInfo.Description;
subInfo.Id = student.Id;

_context.SubjectInfo.Add(subInfo);
_context.saveChanges();

subCat.SubjectCategoryId = subjectInfo.SubjectCategoryId;
subCat.Status = subjectInfo.Status;

_context.SubjectCategory.Add(subCat);
_context.saveChanges();
}
Catch(Exception ex)
{
// catch exception
}
}
}
Catch(Exception ex)
{
}
}
}


My data population:

List<Student> Student= new List<Student>();

Student.Add(new Student
{
BasicInfo = new BasicInfo
{
Stu_No = "1",
FullName = "Steve Adam",
ContactNo = "12345",
Location = "XYZ",
Status = "Active",
},

Subject_Info = new List<Subject_Info>
{
new Subject_Info() {
Subject_Id = "1",
SubjectDesc = "Math",

SubjectCategoryId = "",
Status = active
},

new Subject_Info() {
Subject_Id = "2",
SubjectDesc = "Physics",

SubjectCategoryId = "",
Status = active
},
}
});

Answer

You are overwriting your loop variable when you create a new student record:

foreach (var student in Student)
{
    using (var transaction = _context.Database.BeginTransaction())
    {
        try
        {
            Student student = new Student...

Use a different variable name.

If you

In your inner, subject info, foreach loop you'll need to count the number of successes:

foreach (var student in Student)
{
    using (var transaction = _context.Database.BeginTransaction())
    {
        try
        {
            Student student = new Student...

            int numSuccesses = 0;
             foreach(var subjectInfo in subjectsInfo)
             {
                 try
                 {
                   ... code omitted for clarity

                   numSuccesses++; // The very last thing you do
                 }     
                 Catch(Exception ex)
                 {
                  // catch exception
                 }
             }
        }

        Catch(Exception ex)
        {

        }
    } 
}

As you only really want save the records if at least one subject gets inserted you'll need to roll back the insertion of the student record if there were no successes after the subjectsInfo loop:

if (numSucesses == 0)
{
    // Roll back the insertion of the student record
}

Additionally you should only trap those exceptions you can recover from and log the error. Having a general exception handler is usually the sign of a bad design.