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

DataTable=Datatable.Clone;

That should do it. This will copy the table structure, but not the data. (Datatable.Copy would do that)

Answer 2

I'll just repost my comment here in case you want to close off this question, because I don't believe it's possible to "bulk delete" all the rows in a DataTable:

There's a difference between removing rows from a DataTable and deleting them. Deleting flags them as deleted so that when you apply changes to your database, the actual db rows are deleted. Removing them (with Clear()) just takes them out of the in-memory DataTable.

You'll have to iterate over the rows and delete them one by one. It's only a couple of lines of code.

Answer 3

'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++;
        }
    }