How to delete multiple rows in a DataTable?
Solution 1
It depends on what you mean by 'delete'.
If you mean mark them as deleted, just call the Delete()
method on each row as you visit it in your loop. You then need to call AcceptChanges()
on the data table to finalize the delete - presumably after you update your database (if one is involved).
foreach( DataRow row in someTable.Rows )
{
if( /* your condition here */ )
row.Delete();
}
someTable.AcceptChanges();
If you mean remove it from the DataTable, then you need to do so in two passes:
List<DataRow> rowsToDelete = new List<DataRow>();
foreach( DataRow row in someTable.Rows )
{
if( /* your condition here */ )
{
rowsToDelete.Add( row );
}
}
foreach( DataRow row in rowsToDelete )
{
someTable.Rows.Remove( row );
}
It's worth pointing out that you can always use the first method to remove rows - since marking rows as Deleted
and then accepting changes will automatically remove them from the table. But, sometimes it is more clear and efficient to simply remove the DataRow
objects from the Rows
collection.
Solution 2
Try something like this example
DataTable table = new DataTable();
table.Columns.Add("Foo",typeof(int));
for (int i = 0; i < 10; i++)
table.Rows.Add(i);
for (int i = table.Rows.Count -1; i >=0; i--)
{
// sample removes all even foos
if ((int)table.Rows[i]["Foo"] % 2 == 0)
table.Rows.RemoveAt(i);
}
Solution 3
If you want a shorter solution than those proposed above, try looping over the list of results, and using a lambda like sub(x)
to remove each of those rows.
dt.Select("Column1 > 0").ToList.ForEach(Sub(x) dt.Rows.Remove(x))
Solution 4
The other way is
DataRow[] DrArrCheck = DataTableName.Select("ID > 0");
foreach(DataRow DrCheck in DrArrCheck)
{
DataTableName.Rows.Remove(DrCheck);
}
Solution 5
To delete multiple rows (for instance 50,000 out of 100,000) it is much quicker to copy the database than to do either datatable.Rows.Remove(row) or row.Delete(). For instance:
DataRow[] rowsToKeep = datatable.Select("ID > 50000");
DataTable tempDataTable= rowsToKeep.CopyToDataTable;
dataTable.Clear();
dataTable.Merge(tempDataTable);
tempDataTable.Dispose();
vel
Updated on May 27, 2021Comments
-
vel almost 3 years
How can I delete specific DataRows within a loop of a DataTable rows which meet a custom condition -lets say the rows having an index of even number-? (Without using LINQ)
Thanks
-
CuppM almost 14 yearsCould also do a forward loop (0 -> Count-1) if you move the
i++
inside the for loop and only increment when not deleting.if (...) remove() else i++
-
Ryan Rodemoyer over 12 yearsYou cannot modify a collection while iterating through it with a foreach loop.
-
Szjdw over 11 yearsIf you do the same as what LBushkin said,you will see as follow: Collection was modified; enumeration operation might not execute. so it is wrong.
-
Soenhay almost 11 yearsAs long as you call .AcceptChanges() prior to marking rows as deleted (before the foreach loop) then you will not get the "Collection was modified; enumeration operation might not execute" error. The error appears because there are pending changes that need to be accepted. If you use a for loop then it won't matter but in that case it would be best to iterate backwards.
-
Albert Laure over 10 yearsthe first method outputs what Szjdw said but the second one works like magic, thanks Bush!
-
Mstislav Toivonen about 9 yearsUsing of
Remove()
is not recommended in aforeach
loop since the function changes the state of the collection. -
Mufaddal about 5 yearsThe second option works like a charm, the first one will give an error...