Clean a DataTable in a loop moving used items to two other DataTables

Question

I have a main DataTable called dtMain and two child DataTables dtSuccess and dtFail. In a while loop that has a 10 second delay in each iteration some items in dtMain will be added to dtSuccess and dtFail. So in each iteration I would like my dtMain to be clean from everything that is in those other two lists.

I will put the code I have tried here but unfortunately it does not work.

while (dtMain.Rows.Count > 0)
{
   var query = dt.AsEnumerable().Except(dtSuccess.AsEnumerable(), DataRowComparer.Default)
                 .AsEnumerable().Except(dtFail.AsEnumerable(), DataRowComparer.Default);
   if (dtMain.AsEnumerable().Any())
        dtMain = query.CopyToDataTable();
   Thread.SLeep(10000);
}

I have received Exceptions such as:

Collection was modified; enumeration operation might not execute

And InvalidOperationException

The source contains no DataRows

Answer 1

Are you modifying the dtSuccess and dtFail datatables in another thread? You'll need to use a synchronization technique to only allow one thread in at a time.

To be honest, datatables aren't a very good mechanism for this. In fact, I rarely find datatables to be a good solution. One such solution might be to stop using the dtMain datatable, and convert it to a ConcurrentQueue<DataRow>. Have your other thread(s) pull from the Queue, and the main thread can just check to see if TryPeek on the queue fails. Or use a ReaderWriter lock to synchronize access to dtMain.

Answer 2

try looping on a variable (This will solve your collection modified problem) :

int i = dtMain.Rows.Count ;

while ( i > 0)
{
   var query = dt.AsEnumerable().Except(dtSuccess.AsEnumerable(), DataRowComparer.Default)
                 .AsEnumerable().Except(dtFail.AsEnumerable(), DataRowComparer.Default);
   if (dtMain.AsEnumerable().Any())
        dtMain = query.CopyToDataTable();
   Thread.SLeep(10000);
   i = dtMain.Rows.Count ;
}