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.

Answer 2

If you wish to remove all the rows, you can use the Clear method on the datatable.

Answer 3

dt.Rows.RemoveAt(0)
dt.Rows.RemoveAt(1)

Answer 4

May be to use DataView would make a trick. For example, you can filter out rows you want to keep into DataView, convert view to table and dispose initial table. Then you have your table with rows you needed.

Dim view As DataView = YourTable.DefaultView            
view.RowFilter = "YourFilterColumn = 1259"
Dim tblNew as Datatable = view.ToTable
YourTable.Dispose

Let me know if it works for you.

Answer 5

I'm not sure if this will officially qualify as using a loop but here is a solution using LINQ:

dt.BeginLoadData();
( from row in dt.AsEnumerable()
  where row.Field<string>( "MyColumn"  ) == "DeleteValue"
  select row ).ToList().ForEach( row => row.Delete() );
dt.EndLoadData();
dt.AcceptChanges();

TBH, I'm not sure there is a way to do this without looping through the rows at some level. Either you loop through the rows deleting the ones you do not want, or create a new table filled with everything except the rows you do not want. However, it should be noted that even in the later case NET is probably looping through the rows to determine if the row should be included in the keeper table.

Answer 6

I think you should use LINQ for that. You will get datatable from the dataset and write a LINQ query to delete row matching your criteria.

So you don't need to loop for that.

Here are the some links that might help you.

• Creating a DataTable From a Query (LINQ to DataSet)
• LINQ query on a DataTable

Answer 7

Use a SQL statement within an ADO.NET command object. Obvoiusly the rows that you want to delete will have something in common.

Delete From MyTable where mycolumn='XYZ' and thisColumn='ABC'

Answer 8

You can call DeleteAllOnSubmit() if you're using LINQ to SQL. However, this will submit a DELETE statement for each entity being deleted, which is highly inefficient. You could always fork LINQ to SQL, or use a stored procedure.

BTW, you're question is very generic. My first inclination was to recommend using a WHERE clause.

Answer 9

We can always write a stored procedure to optimize ADO.NET entity-framework or LINQ to SQL roundtrips in some cases. The drawback is that that model starts looking a bit unconsistent. I too wonder if there is a better way :)

Answer 10

Thanks Bremer, this is the optimal code for delete rows in a datatable, for me is the fast method:

Public Sub BorrarFilasEnDatatable(ByRef dtDatos As DataTable, ByVal strWhere As String)
        Dim dtTemp As New DataTable
        Dim filas As DataRow()
        filas = dtDatos.Select("NOT(" & strWhere & ")")
        dtDatos.Clear()
        If filas.Count > 0 Then
            dtTemp = filas.CopyToDataTable
            dtDatos.Merge(dtTemp)
        End If
        dtTemp.Dispose()
End Sub

'call me method for delete rows
Me.BorrarFilasEnDatatable(dt1, "Id<10")