How to delete all rows from datatable

Question

I want to delete all rows from datatable with rowstate property value Deleted.

DataTable dt;
dt.Clear(); // this will not set rowstate property to delete.

Currently I am iterating through all rows and deleting each row.

Is there any efficient way? I don't want to delete in SQL Server I want to use DataTable method.

---

Solution 1

We are using this way:

for(int i = table.Rows.Count - 1; i >= 0; i--) {
    DataRow row = table.Rows[i];
    if ( row.RowState == DataRowState.Deleted ) { table.Rows.RemoveAt(i); }
}

Solution 2

This will satisfy any FK cascade relationships, like 'delete' (that DataTable.Clear() will not):

DataTable dt = ...;
// Remove all
while(dt.Count > 0)
{
     dt.Rows[0].Delete();
}

Solution 3

dt.Rows.Clear();
dt.Columns.Clear();   //warning: All Columns delete
dt.Dispose();

Comments

[meetjaydeep]

I think this is the only better way foreach (DataRow row in dt.Rows) { row.Delete(); }

[meetjaydeep]

I want to delete from DataTable and it should update the rowstate.

[TcKs]

@meetjaydeep: If you do foreach, you can not change the collection. But RemeoveAt() changes collection, so foreach will be throw exception. So it's not solution.

[ykatchou]

it's a bad idea to do it using dynamic sql. sommarskog.se/dynamic_sql.html#good_practices

[Yetti]

I may have +1'ed too soon. This does not always work. This is vulnerable to an infinite loop. It seems like .Delete() doesn't always get rid of a row. It seems to merely mark a row's RowState as Deleted in some cases, without ever actually deleting them. This appears to be related to whether the row was created directly or had been added through a database call.