CODEWITHSUNDEEP
vb.netado.net
KuldipMCA
KuldipMCA

Reputation: 3149

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

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

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

Upvotes: 8

Views: 31605

Answers (10)

GabrielBlumen
GabrielBlumen

Reputation: 1

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")

Upvotes: 0

Andrew Florko
Andrew Florko

Reputation: 7750

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 :)

Upvotes: 0

Aaron Daniels
Aaron Daniels

Reputation: 9664

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.

Upvotes: 0

Nathan Fisher
Nathan Fisher

Reputation: 7941

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'

Upvotes: 0

Jalpesh Vadgama
Jalpesh Vadgama

Reputation: 14216

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.

Upvotes: 0

Thomas
Thomas

Reputation: 64645

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.

Upvotes: 0

Bremer
Bremer

Reputation: 407

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.

Upvotes: 12

Anvar
Anvar

Reputation: 439

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.

Upvotes: 0

tsilb
tsilb

Reputation: 8037

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

Upvotes: 0

Oded
Oded

Reputation: 498992

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

Upvotes: 0

Related Questions