Wednesday 29 April 2009

Delete rows from a DataTable

When I tried to delete DataRows from a DataTable based on some conditions, I wrote the code as below but receive an InvalidOperationException complaining {"Collection was modified; enumeration operation might not execute."}

DataColumn dc = dsData.Tables["tbl"].Columns["order_qty"];
foreach (DataRow dr in dsData.Tables["tbl"].Rows)
{
if (dr[dc].ToString() == "0")
dsData.Tables["tbl"].Rows.Remove(dr);
}

Then I changed my code to:

DataColumn dc = dsData.Tables["tbl"].Columns["order_qty"];
foreach (DataRow dr in dsData.Tables["tbl"].Rows)
{
if (dr[dc].ToString() == "0")
dr.Delete();
}

Everything works fine. Then I went to MSDN and found the explanation of difference between using Delete and Remove method on data table:

There are two methods you can use to delete a DataRow object from a DataTable object: the Remove method of the DataRowCollection object, and the Delete method of the DataRow object. While the Remove method deletes a DataRow from the DataRowCollection, the Delete method only marks the row for deletion. The actual removal occurs when the application calls the AcceptChanges method. By using Delete, you can programmatically check which rows are marked for deletion before actually removing them. When a row is marked for deletion, its RowState property is set to Deleted.

If a row is marked for deletion and you call the AcceptChanges method of the DataTable object, the row is removed from the DataTable. In contrast, if you call RejectChanges, the RowState of the row reverts to what it was before being marked as Deleted.

Obviously my first example won’t work because I used the Remove method which deletes a DataRow from the DataRowCollection within a foreach loop.

No comments:

Post a Comment