CODEWITHSUNDEEP
c#
happysmile
happysmile

Reputation: 7777

How to remove a a particular row value from a datatable based on a condition

I have a DataTable like this

name     age
------------
kumar    27
kiran    29
anu      24
peter    34
tom      26
manu     35
sachin   37
geetha   23

Now I have another DataTable like this with one column:

name
----
manu
tom
anu

I need to compare the value in the column name here and remove all the rows that share the same name. Now the result output should be like this:

name     age
------------
kumar    27
kiran    29
peter    34
sachin   37
geetha   23

How can I achive this result?

Upvotes: 1

Views: 10058

Answers (4)

Andrew Leon
Andrew Leon

Reputation: 58

If you know the table that you wish to remove from you could do something like this.

foreach(DataRow rowTable2 in dataSet.Tables["Table2"].Rows)
{
    foreach(DataRow rowTable1 in dataSet.Tables["Table1"].Rows)
    {
         if(rowTable1["NameColumn"].ToString() == rowTable2["NameColumn"].ToString())
         {
             dataSet.Tables["Table1"].Rows.Remove(rowTable1);
         }
    }
}

That is a very simple easy way to get what you need done.

Upvotes: 0

Jerod Houghtelling
Jerod Houghtelling

Reputation: 4867

One way to do it is:

  1. Create a expression to match the rows; i.e. "name='manu' OR name='tom' OR name='anu'"
  2. Execute the expression against the original data table to select the rows to delete.
  3. Loop through and delete all of the matching rows.

Assumptions:table = original data table, table2 = filter data table.

string expression = string.Join( " OR ", table2.AsEnumerable().Select( 
     row => string.Format( "name='{0}'", row["name"] )).ToArray() );

foreach( DataRow row in table.Select( expression ) )
{
    row.Delete();
}

Upvotes: 1

Abel
Abel

Reputation: 57217

Assuming you mean .NET datatables and not SQL tables. One approach is the following. Convert your second datatable to a dictionary, something like this:

// create a lookup table to ease the process
Dictionary<string, string> dict = (from row in removeNames.AsEnumerable()
                                   select (string) row["name"])
                                  .ToDictionary(k => k);

You must loop from end to first, otherwise you get problems (cannot use foreach here, it'll throw an exception).

// loop top to bottom
for(var i = firstDT.Rows.Count - 1; i >= 0; i--)
{
    var row = firstDT.Rows[i];
    if(dict.Exists((string) row["name"]))
    {
        firstDT.Remove(row);
    }
}

Edit: better solution

Partially inspired by a little discussion with Jerod below, I figured there had to be a way to do this with a LINQ expression that's easy to read and apply. Previously, it didn't work, because I enumerated the DataRowView itself, which, when you try to remove something, will raise an InvalidOperationException, telling you that the collection was modified. Here's what I did, and it works because we use Select to create a copy of the row items:

// TESTED, WORKS
// assuming table contains {name, age} and removeNames contains {name}

// selecting everything that must be removed
var toBeRemoved = from row in table.Select()
                  join remove in removeNames.AsEnumerable()
                    on row["name"] equals remove["name"]
                  select row;

// marking the rows as deleted, this will not raise an InvalidOperationException
foreach (var row in toBeRemoved)
    row.Delete();

Upvotes: 1

J&#233;r&#233;mie
J&#233;r&#233;mie

Reputation: 1

Why don't you make a simple query : DELETE FROM table1 WHERE table1.name IN (SELECT name FROM table2)

Upvotes: 0

Related Questions