Small method for saving student attendance

Question

var studentRepo = new StudentRepository();
int gradeParaleloId = Convert.ToInt32(cmbGradeParalelo.SelectedValue);
var students = studentRepo.FindAllStudentsFromGradeParalelo(gradeParaleloId);

int year = DateTime.Now.Year;
int month = Convert.ToInt32(cmbMes.SelectedIndex) + 1;
AttendanceRepository attendanceRepo = new AttendanceRepository();

for (int i = 0; i < students.Count; i++)
{
    for (int j = 1; j < dataGridView1.Columns.Count; j++)
    {
        DateTime date = new DateTime(year, month, j);

        var student = students[i];
        var attendance = attendanceRepo.FindAttendance(student.StudentId, date);

        if (attendance == null)
        {
            attendance = new Data.Repositories.Attendance();
            attendanceRepo.Add(attendance);
        }

        attendance.StudentId = student.StudentId;
        attendance.DateOfAttendance = date;
        attendance.Attended = dataGridView1[j, i].Value.ToString();
    }
}

attendanceRepo.SaveChanges();

My main thorn in the side is that for each day in the month, I have to query the database, and see if a record already exists in the DB for that day and student.

The upside is that I'm putting each attendance in the datacontext and only pushing the save at the very end as a transaction of sorts.

So I'm not inserting on every day for every student. Imagine for a class of 40 students. That's an easy \$40 * 30 = 1200\$ queries on the spot.

Is there some way for me to streamline this? I'm more concerned about this line:

var attendance = attendanceRepo.FindAttendance(student.StudentId, date);

Answer 1

First of all, minor beef. I'd reccommend that you change your index names to be more descriptive, particularly the inner one. For instance you could make your repo query

DateTime date = new DateTime(year, month, **day**);

Much more readable. When an index has a name, name it.

If you're having performance issues, try separating out the queries. In psuedocode:

foreach day in month
   get students_with_records from database

   foreach student in the_class
      if student NOT in students_with_records
         add record

This method will put the search through the student records on the client...it should be faster, since you will only have one query per day rather than one query per day per student. You really should profile the app and see if it's an issue, though.

Answer 2

Couple of things:

1. Although I can't tell for sure, it looks as if you're storing attendance if the student is present. Your database will grow very quickly that way. If possible, you might want to consider having a calendar table for the school year that designates school days. That way, you will only need to store if the student is absent or tardy. Present days will be assumed and can be calculated from the calendar table.

2. This is small, but the following code may not work either: int year = DateTime.Now.Year

Take, for example, a teacher that wants to go back and correct attendance in December.
It looks as if they will actually be selecting for December 2011, not 2010.