Deleting items while scanning collection

[AdeHall]

Hi

 

Could someone tell me what the best approach is to the following situation?

 

I am looping through the rows in a table within a dataset using a foreach statement. Depending on criteria I want to delete the current row and continue through the row colelction.

As soon as I delete the row I get an error.

 

foreach ( DataRow dr in dsMyDataSet.MyTable.Rows )

{

if ( shouldbedeleted )

dr.Delete();

}

 

I think I undersand why the error occurs but I don't know the proper way of achieving what I want.

 

Any suggestions please?

Ade

[ToniMontana]

Hi,

 

I am no expert on this, but would first

go through the collection only to mark

the items which should be deleted.

And then, in a second run, delete them.

You can easily put the references of

the datasets which should be deleted

in an arraylist.

 

Hope this helps,

[AdeHall]

Hi,

 

I am no expert on this, but would first

go through the collection only to mark

the items which should be deleted.

And then, in a second run, delete them.

You can easily put the references of

the datasets which should be deleted

in an arraylist.

 

Hope this helps,

 

Thanks Toni

 

I will try your suggestion.

I wish there was a slghtly neater solution though!

 

Regards

Ade

[Arch4ngel]

The better way... is a while loop.

 

// WARNING : I don't remember if the Count go down when you .Delete() ...

// if it don't... just remove the word "else" and you'll be fine.

 

int i = 0;

while( i < ds.MyTable.Rows.Count )

{

DataRow dr = ds.MyTable.Rows;

if( shouldbedeleted )

dr.Delete();

else

i++;

}

 

That is the best approach I learn. Remember something however... you can't modify the element in your foreach loop (dr in this eg. ).

 

But if you do a while or a for... you get around that.

 

Have fun.

[AdeHall]

Thanks - looks like a simple solution

 

Ade

[Arch4ngel]

Making 2 run make your processor doing 2 time the job... There's more simple solution out there.

[Nerseus]

Yes, yes... a much simpler solution!

First though - you can't modify a collection while inside a foreach loop - so says the MS help. That would include deleting items from the collection, adding items are reassigning the "pointer" to a new object.

 

To delete rows from a collection, just loop backwards in a for loop:

for(int i=dsMyDataSet.MyTable.Rows.Count-1; i>=0; i--)
{
   if ( shouldbedeleted )
   {
       dsMyDataSet.MyTable.Rows[i].Delete();
   }
}

 

You can, and probably should, encapsulate this in a reusable function as it comes up fairly often. Passing in a filter string is probably a good idea as well.

public static void DeleteRows(DataSet ds, string tableName, string filter)
{
   DataRow[] rows = ds.Tables[tableName].Select(filter);
   for(int i=rows.Length-1; i>=0; i--)
   {
       if ( shouldbedeleted )
       {
           rows[i].Delete();
       }
   }
}

(You can add your own error checking and such)

 

-Nerseus

[AdeHall]

Thanks for your reply.

 

I assume by looping backwards it stops the dsMyDataSet.MyTable.Rows.Count changing from confusing the value of i?

 

Ade

[PlausiblyDamp]

The count property will still change but as it's decreasing it will always be higher than your current position in the array.

[AdeHall]

Neat.

 

Thanks

Ade

[pelikan]

depending on your 'condition' for deleting rows, there might be a still simpler solution.

Use SQL. DELETE FROM table-name WHERE condition

 

make sure your where clause is correct first or you'll end up deleting rows you want to keep.

 

This would be the text of the OleDbCommand which is the DeleteCommand of the OleDbDataAdapter. You can set up parameters on the command to control what gets deleted dynamically.

 

eg. "DELETE FROM Authors WHERE (firstname = ?)