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.

You need to provide more details; what database do you use, and what language? — CharlesB, Apr 19 '11 at 12:05

score 5 · Answer 1 · answered Apr 19 '11 at 12:04

5

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); }
}

answered Apr 19 '11 at 12:04

TcKs

25,849
11
66
104

2

I think this is the only better way foreach (DataRow row in dt.Rows) { row.Delete(); } – meetjaydeep Apr 19 '11 at 12:30
3

@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. – TcKs Apr 19 '11 at 15:29

score 3 · Answer 2 · answered Jan 20 '12 at 17:00

3

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();
}

answered Jan 20 '12 at 17:00

Jorge

31
1

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. – Yetti Mar 26 '12 at 16:31

score 2 · Answer 3 · answered Aug 20 '15 at 14:02

2

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

answered Aug 20 '15 at 14:02

user3779649

21
3

score 1 · Answer 4 · edited Nov 30 '22 at 08:08

1

I typically execute the following SQL command:

DELETE FROM TABLE WHERE ID>0

edited Nov 30 '22 at 08:08

Bozhinovski

2,496
3
20
38

answered Apr 21 '11 at 09:36

andrew0007

1,265
4
18
32

score 0 · Answer 5 · answered Apr 19 '11 at 13:14

0

Since you're using an SQL Server database, I would advocate simply executing the SQL command "DELETE FROM " + dt.TableName.

answered Apr 19 '11 at 13:14

Roman Starkov

59,298
38
251
324

I want to delete from DataTable and it should update the rowstate. – meetjaydeep Apr 19 '11 at 14:45
it's a bad idea to do it using dynamic sql. http://www.sommarskog.se/dynamic_sql.html#good_practices – ykatchou Apr 21 '11 at 17:59

score 0 · Answer 6 · answered Apr 19 '11 at 14:31

0

I would drop the table, fastest way to delete everything. Then recreate the table.

answered Apr 19 '11 at 14:31

UnixShadow

1,222
8
12

score 0 · Answer 7 · answered Apr 19 '11 at 15:04

0

You could create a stored procedure on the SQL Server db that deletes all the rows in the table, execute it from your C# code, then requery the datatable.

answered Apr 19 '11 at 15:04

Beth

9,531
1
24
43

score 0 · Answer 8 · answered Mar 26 '12 at 16:46

Here is the solution that I settled on in my own code after searching for this question, taking inspiration from Jorge's answer.

DataTable RemoveRowsTable = ...;
int i=0;
//Remove All
while (i < RemoveRowsTable.Rows.Count)
{
     DataRow currentRow = RemoveRowsTable.Rows[i];
     if (currentRow.RowState != DataRowState.Deleted)
     {
         currentRow.Delete();
     }
     else
     {
         i++;
     }
}

This way, you ensure all rows either get deleted, or have their DataRowState set to Deleted.

Also, you won't get the InvalidOperationException due to modifying a collection while enumerating, because foreach isn't used. However, the infinite loop bug that Jorge's solution is vulnerable to isn't a problem here because the code will increment past a DataRow whose DataRowState has already been set to Deleted.

How to delete all rows from datatable

8 Answers8