Updating a datatable in c#

I have a datatable storing info about a student classroom. My table looks like this:

Student ID     Grade     Absence Count
00001           85             0
00002           95             7
00002           70             5
00003           35             1

Dont ask me why there are two id's that are the same... its just the way it is. Now i want to update the absence count for the 00002 id that has absence count of 7. At the same time, i want to delete the 00002 entry that doesnt have the absence count of 7 (in this case the one with count 5). Now i know how to query the table with a select statement and update the 00002 id student with count 7. How can i, at the same time, delete the other entry for the 00002 student? This is my code:

foreach(oldCount in absenceCount)
{
    DataRow[] dr = dt.Select("Student ID='" + ID + "' AND Absence Count='" + oldCount);
    dr[0]["Absence Count"] = newCount;
}

So here how can i tell the program that if there is another student id whose absence count isnt in the absenceCount 开发者_StackOverflowlist, delete it from the table?

Thanks

You can write

dr[1].Delete();

You could do the same select but with the opposite condition on the oldcount, like so

foreach(oldCount in absenceCount)
{
  DataRow[] dr = dt.Select("Student ID='" + ID + "' AND Absence Count != '" + oldCount);
  dr[0].Delete();
}

This will get you that student id but where the absence count is not equal to the old count.

You obviously still have a problem where you have, as Blindy suggested, 2 rows with the same student id and the same absence count.

I suppose you can select all the rows with same ID. Update the one you want to, and delete the rest of the rows. Something like

DataRow[] dr = dt.Select("Student ID='" + ID + "'");

and then updating the one that matches the Absence count, deleting the rest of them.

Regarding the comment: This is taken from Kenny's reply, but this is what i meant

foreach(DataRow row in dr)
{
  if (dr["Absence Count"] == absencCount)
      dr["Absence Count"] = newCount;
  else
      dr.Delete()
}

foreach(oldCount in absenceCount)
{
    DataRow[] dr = dt.Select("Student ID='" + ID);
    bool updated = false;
    foreach(DataRow row in dr)
    {
      if (!updated && dr["Absence Count"] == absenceCount)
      {
          dr["Absence Count"] = newCount;
          updated = true;
      }
      else
      {
          dr.Delete()
      }
    }
}