ado.net - Delete all rows in a datatable

Question

I need to delete all the rows in a datatable (ADO.net). I dont want to use Foreach here.

In one single how can I delete all the rows.

Then I need to update the datatable to database.

Note: I have tried dataset.tables[0].rows.clear and dataset.clear but both are not working

Also I dont want to use sql delete query.

Post other than this if you able to provide answer.

Answer 1

'foreach' isn't such a simple answer to this question either -- you run into the problem where the enumerator is no longer valid for a changed collection.

The hassle here is that the Delete() behavior is different for a row in DataRowState.Added vs. Unchanged or Modified. If you Delete() an added row, it does just remove it from the collection since the data store presumably never knew about it anyway. Delete() on an unchanged or modified row simply marks it as deleted as others have indicated.

So I've ended up implementing an extension method like this to handle deleting all rows. If anyone has any better solutions, that would be great.

    public static void DeleteAllRows(this DataTable table)
    {
        int idx = 0;
        while (idx < table.Rows.Count)
        {
            int curCount = table.Rows.Count;
            table.Rows[idx].Delete();
            if (curCount == table.Rows.Count) idx++;
        }
    }