How to delete multiple rows from datatable in VB.NET 2008?

Question

How to delete multiple rows from datatable in VB.NET 2008 without looping?

• I do not want to delete from the database.
• I want to delete from the local data table.
• I know the Select method and also Remove and remove at method too. But that needs looping to delete the rows from the data table.

I have 40000 rows and I want to delete selected 1000 rows from that data table.

Answer 1

I don’t know that this can be done in a straightforward way. There is no delete command on the datatable that will do this.

You could try something like this. You select the records you want to keep into a temp table, clear out the original table, and then merge the temp table back into the original.

Dim dtTemp As DataTable = ds.Tables("YourTable").Select("RecordsToKeep='This'").CopyToDataTable
ds.Tables("YourTable").Clear()
ds.Tables("YourTable").Merge(dtTemp)
dtTemp.Dispose()

That’s the best answer to the question I can think of. It seems like you may be using the datatable in an unusual way. You’re generally best off not populating the records to begin with, or filtering them out when you save the contents to it’s destination. Be it an XML file, SQL, or whatever.

Certainly, the loop method would be the most efficient. This is not likely to be the fastest method, but for only 4K rows, it's probably good enough.