CODEWITHSUNDEEP
c#.netado.netdatatabledatarow
pencilCake
pencilCake

Reputation: 53243

How to delete multiple rows in a DataTable?

How can I delete specific DataRows within a loop of a DataTable rows which meet a custom condition -lets say the rows having an index of even number-? (Without using LINQ)

Thanks

Upvotes: 30

Views: 85857

Answers (11)

toly P
toly P

Reputation: 62

This is how I do it.

    for (int i = RowNumber.Count - 1; i >= 0; i--)
                        {
                            dt.Rows.RemoveAt(Int32.Parse(RowNumber[i]));
                        }

                        missingNotesGV.DataSource = dt;

                        missingNotesGV.DataBind();

Its important to do the for loop in reverse otherwise you get errors if you're removing rows at the end in addition to rows in other places.

Upvotes: 0

tval
tval

Reputation: 582

To delete multiple rows (for instance 50,000 out of 100,000) it is much quicker to copy the database than to do either datatable.Rows.Remove(row) or row.Delete(). For instance:

DataRow[] rowsToKeep = datatable.Select("ID > 50000");
DataTable tempDataTable= rowsToKeep.CopyToDataTable;
dataTable.Clear();
dataTable.Merge(tempDataTable);
tempDataTable.Dispose();

Upvotes: 2

Sergey
Sergey

Reputation: 179

If you want a shorter solution than those proposed above, try looping over the list of results, and using a lambda like sub(x) to remove each of those rows.

dt.Select("Column1 > 0").ToList.ForEach(Sub(x) dt.Rows.Remove(x))

Upvotes: 5

user2524845
user2524845

Reputation: 3

I always used LBushkin's "two-phase" approach and I finally decided it was worth it to write a function for it:

    public delegate bool DataRowComparer(DataRow dr);

    public static void RemoveDataRows(DataTable table, DataRowComparer drc)
    {
        List<DataRow> RowsToRemove = new List<DataRow>();
        foreach (DataRow dr in table.Rows)
            if (drc(dr))
                RowsToRemove.Add(dr);
        foreach (DataRow dr in RowsToRemove)
            table.Rows.Remove(dr);
    }

And now I can delete rows with one line of code (for example):

RemoveDataRows(dt, row => row["StringVal"].ToString() == "B" && (Int16)(row["NumberVal"]) >= 4);

In case this helps anyone...

(And any ways to further abbreviate are appreciated.)

Upvotes: 0

Jordan
Jordan

Reputation: 11

This is how I did it when I ran into this issue. 

Dim index As Integer = 0
Dim count As Integer = resultsDT.Rows.Count - 1
For i As Integer = 0 To count
    If resultsDT.Rows(index).Item("something") = "something" Then                               
        resultsDT.Rows(index).Delete()
        resultsDT.AcceptChanges()
        index = index - 1
    End If
    index = index + 1
    i = i + 1
Next

Upvotes: -1

Vin
Vin

Reputation: 922

 public static void DeleteRowsFromDataTable(DataTable dataTable, string columnName, string columnValue)
        {
            IEnumerable<DataRow> dataRows = (from t in dataTable.AsEnumerable()
                                             where t.Field<string>(columnName) == columnValue
                                             select t);
            foreach (DataRow row in dataRows)
                dataTable.Rows.Remove(row);
        }

Upvotes: 1

BharathiNathan
BharathiNathan

Reputation: 41

The other way is 

    DataRow[] DrArrCheck = DataTableName.Select("ID > 0");
    foreach(DataRow DrCheck in DrArrCheck)
    {
        DataTableName.Rows.Remove(DrCheck);
    }

Upvotes: 4

Johnny
Johnny

Reputation: 1575

try this

foreach(DataRow oRow in YourDataTable.Rows)
{
  if ("Check You Condition")
   {
      YourDataTable.Rows.Remove(oRow);
   }
}

Upvotes: -2

Kleinux
Kleinux

Reputation: 1541

try iterating over the result of Select(). This is pretty similar to other answers, but I find it the most direct

DataRow[] r = table.Select();
for (int i = 0; i < r.Length; i++)
{
    if (i % 2 == 0)
        r[i].Delete();
}

Upvotes: 0

LBushkin
LBushkin

Reputation: 131676

It depends on what you mean by 'delete'.

If you mean mark them as deleted, just call the Delete() method on each row as you visit it in your loop. You then need to call AcceptChanges() on the data table to finalize the delete - presumably after you update your database (if one is involved).

foreach( DataRow row in someTable.Rows )
{
    if( /* your condition here */ )
        row.Delete();
}
someTable.AcceptChanges();

If you mean remove it from the DataTable, then you need to do so in two passes:

List<DataRow> rowsToDelete = new List<DataRow>();
foreach( DataRow row in someTable.Rows )
{
    if( /* your condition here */ )
    {
        rowsToDelete.Add( row );
    }
}

foreach( DataRow row in rowsToDelete )
{
    someTable.Rows.Remove( row );
}

It's worth pointing out that you can always use the first method to remove rows - since marking rows as Deleted and then accepting changes will automatically remove them from the table. But, sometimes it is more clear and efficient to simply remove the DataRow objects from the Rows collection.

Upvotes: 63

Anthony Pegram
Anthony Pegram

Reputation: 126834

Try something like this example 

DataTable table = new DataTable();
table.Columns.Add("Foo",typeof(int));
for (int i = 0; i < 10; i++)
    table.Rows.Add(i);

for (int i = table.Rows.Count -1; i >=0; i--)
{
    // sample removes all even foos
    if ((int)table.Rows[i]["Foo"] % 2 == 0)
        table.Rows.RemoveAt(i);
}

Upvotes: 10

Related Questions