DataTable, How to conditionally delete rows

Question

I'm engaged in a C# learning process and it is going well so far. I however just now hit my first "say what?" moment.

The DataTable offers random row access to its Rows collection, not only through typical collections behavior, but also through DataTable.Select. However I cannot seem to be able to tie this ability to DataRow.Delete. So far this is what it seems I need to do in order to conditionally delete one or more rows from a table.

int max = someDataTable.Rows.Count - 1;
for(int i = max; i >= 0; --i)
{
    if((int)someDataTable.Rows[i].ItemArray[0] == someValue)
    {
        someDataTable.Rows[i].BeginEdit();
        someDataTable.Rows[i].Delete();
    }
    else
        break;
}
someDataTable.AcceptChanges();

But I'm not happy with this code. Neither I'm convinced. I must be missing something. Am I really forced to hit the Rows collection sequentially if I need to delete one or more rows conditionally?

(don't mind the inverted for. I'm deleting from the end of the datatable. So it's ok)

score 84 · Accepted Answer · edited May 30 '22 at 15:15

84

You could query the dataset and then loop the selected rows to set them as delete.

var rows = dt.Select("col1 > 5");
foreach (var row in rows)
   { row.Delete(); }
   dt.AcceptChanges();

... and you could also create some extension methods to make it easier ...

myTable.Delete("col1 > 5");

public static DataTable Delete(this DataTable table, string filter)
{
    table.Select(filter).Delete();
    return table;
}
public static void Delete(this IEnumerable<DataRow> rows)
{
    foreach (var row in rows)
        row.Delete();
}

edited May 30 '22 at 15:15

Nitin Kabra

3,146
10
43
62

answered Oct 20 '09 at 00:19

Matthew Whited

22,160
4
52
69

Ah! Didn't occur to me Select() would return a reference to the datatable rows. I knew I had to be missing something. Thanks a bunch! – Alexandre Bell Oct 20 '09 at 01:37
Too good solution, this also solved my problem of datatable.select changing the order. – Signcodeindie Mar 30 '12 at 12:15
6

Finally You want to apply those change to dt table using below command.`dt.AcceptChanges()` – Damith Dec 24 '13 at 06:45
This is a useful approach, BUT, be careful if you need to check one column (rows values) with a variable instead of a simple number, the interpreter will try to find another column with the variable name. Eg: ' var rows = Dt.Select("Col1 < yourVariable); ' , code will try to find rows where [Column.Col1] is less than [Column.yourVariable] which will give you an error. – Lorenzo Bassetti Oct 27 '21 at 16:31

score 48 · Answer 2 · answered May 07 '12 at 19:38

48

Here's a one-liner using LINQ and avoiding any run-time evaluation of select strings:

someDataTable.Rows.Cast<DataRow>().Where(
    r => r.ItemArray[0] == someValue).ToList().ForEach(r => r.Delete());

answered May 07 '12 at 19:38

Alain

26,663
20
114
184

2

I really liked this solution- very nice - Thanks! – MDV2000 Jun 08 '12 at 14:55
3

Sometime, remember call function someDataTable.AcceptChanges(); after delete – Wolf Oct 12 '13 at 03:57
1

@GreyWolf if you want to write the changes back to the database, then do NOT call AcceptChanges! AcceptChanges will mark all new/updated rows as unchanged, thus the DataAdapter will think there are NO changes and won't write anything to the database on .Update(). – The Conspiracy Nov 10 '13 at 15:39
1

what is r.ItemArray[0] in this case, is it the first column of the row? please tag me when you reply. – user3281466 Jun 03 '15 at 17:16
Very nice and elegant code this is. I was searching for this trick since two days! And thank you so much @Alain to post this code 'coz you saved my day. – Rohan Rao Oct 21 '19 at 09:47

score 11 · Answer 3 · answered Oct 20 '09 at 00:23

11

I don't have a windows box handy to try this but I think you can use a DataView and do something like so:

DataView view = new DataView(ds.Tables["MyTable"]);
view.RowFilter = "MyValue = 42"; // MyValue here is a column name

// Delete these rows.
foreach (DataRowView row in view)
{
  row.Delete();
}

I haven't tested this, though. You might give it a try.

answered Oct 20 '09 at 00:23

itsmatt

31,265
10
100
164

I tried and it worked. I do prefer the Select approach though. But good thing to keep in mind. Thanks :) +1 – Alexandre Bell Oct 20 '09 at 01:41
This btw would leave the underlying DataTable object intact and "growing" with unused rows, hence, might be memory garbage. It is indeed useful if you want to retrive a different "view" of the DataTable, at a different moment or to a different object. Interesting approach! Thanks – Lorenzo Bassetti Oct 27 '21 at 08:37

score 1 · Answer 4 · answered Apr 08 '17 at 12:47

Extension method based on Linq

public static void DeleteRows(this DataTable dt, Func<DataRow, bool> predicate)
{
    foreach (var row in dt.Rows.Cast<DataRow>().Where(predicate).ToList())
        row.Delete();
}

Then use:

DataTable dt = GetSomeData();
dt.DeleteRows(r => r.Field<double>("Amount") > 123.12 && r.Field<string>("ABC") == "XYZ");

DataTable, How to conditionally delete rows

4 Answers4

Linked