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.

How are you determining which 1000 of the 4000 rows to delete? — JeffO, May 06 '10 at 12:13
That's my question is,is there any way to determine the rows for deletion ? — KuldipMCA, May 10 '10 at 09:45
Could you add to your question which .NET class you use to represent the data in memory? Is it DataSet or another class? Or I can reformulate the question: do you use SqlDataAdapter or LinkToSql Or Entity Framework to access the database? For every way one can write a solution, but I don't want to write different versions of the code if you need only one. A small example of database table (2-3 fields) could be also very good, then I will be use these field in my code example. — Oleg, May 10 '10 at 19:16
Why do you want to delete them without looping? Is it a performance issue? Cause it lasts only a few milliseconds to delete 1000 rows from a 4000 rows-datatable. — Tim Schmelter, May 12 '10 at 10:32
@KuldipMCA: Your reputation has been recalculated as requested. — Bill the Lizard, May 13 '10 at 10:51

score 12 · Accepted Answer · answered May 12 '10 at 18:23

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.

I have 40000 rows in a table and before merge you said i will clear that table then i lost all my rows. — KuldipMCA, May 13 '10 at 05:06
Yes, but before you cleared the table, you copied the records you want to keep a temp table. After the clear, you "merge" the records from the temp table (the 39000 you want to keep), back into the original (now empty) table. The end result is you have deleted the 1000 records without writing a loop. — Bremer, May 13 '10 at 14:55

score 0 · Answer 2 · answered May 06 '10 at 06:58

0

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

answered May 06 '10 at 06:58

Oded

489,969
99
883
1,009

i have table with 40000 rows i want to remove 1000 rows from datatable. – KuldipMCA May 06 '10 at 07:02
@KuldipMCA - Why can't you use looping? – Oded May 06 '10 at 07:15
3

@KuldipMCA - And why you can't loop through the rows of the table? – Oded May 06 '10 at 08:13

score 0 · Answer 3 · answered May 06 '10 at 06:59

0

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

answered May 06 '10 at 06:59

tsilb

7,977
13
71
98

i have table with 40000 rows i want to remove 1000 rows from datatable for that i need not to use loop. – KuldipMCA May 06 '10 at 07:03
1

These are details you should include in the original question. – tsilb May 07 '10 at 06:21

score 0 · Answer 4 · edited Oct 06 '10 at 13:22

0

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

edited Oct 06 '10 at 13:22

Peter Mortensen

30,738
21
105
131

answered May 06 '10 at 19:42

Andrew Florko

7,672
10
60
107

score 0 · Answer 5 · edited Oct 06 '10 at 13:18

0

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.

edited Oct 06 '10 at 13:18

Peter Mortensen

30,738
21
105
131

answered May 06 '10 at 19:48

Aaron Daniels

9,563
6
45
58

Anvar · Answer 6 · 2010-05-06T22:47:23.520

0

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.

edited May 06 '10 at 22:47

answered May 06 '10 at 22:33

Anvar

439
5
22

score 0 · Answer 7 · edited Oct 06 '10 at 13:15

0

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'

edited Oct 06 '10 at 13:15

Peter Mortensen

30,738
21
105
131

answered May 06 '10 at 22:40

Nathan Fisher

7,961
3
47
68

score 0 · Answer 8 · answered May 14 '10 at 06:11

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.

score 0 · Answer 9 · edited May 23 '17 at 12:02

0

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.

edited May 23 '17 at 12:02

Community

1
1

answered May 14 '10 at 09:30

Jalpesh Vadgama

13,653
19
72
94

GabrielBlumen · Answer 10 · 2017-04-03T21:45:05.170

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

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

10 Answers10