Deleting rows from a datatable

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

I have 2 datatables called tableA and tableB. TableA and
TableB have a common field or column called IdentityID,
this field is not a primary key. I want find out if any
of the IdentityID's are not in tableB that are in tableA,
if they are then I want to delete the rows from tableA.

What is the best practice to do this?

I have tried doing a foreach loop through all the rows in
tableA, nesting another foreach loop inside the first to
check if the current row value for IdentityID is in
tableB. The problem is removing the rows because by doing
this I modify the collection and the foreach loop fails.
Even if I use the Delete method of the datarow which is
supposed to only mark the row for deletion this still
modifies the collection.

My work around has been to create a third table with only
the rows from tableA that are also in tableB but I don't
think this is the best way....?
 
Neil said:
I have 2 datatables called tableA and tableB. TableA and
TableB have a common field or column called IdentityID,
this field is not a primary key. I want find out if any
of the IdentityID's are not in tableB that are in tableA,
if they are then I want to delete the rows from tableA.

What is the best practice to do this?

I have tried doing a foreach loop through all the rows in
tableA, nesting another foreach loop inside the first to
check if the current row value for IdentityID is in
tableB. The problem is removing the rows because by doing
this I modify the collection and the foreach loop fails.
Even if I use the Delete method of the datarow which is
supposed to only mark the row for deletion this still
modifies the collection.

My work around has been to create a third table with only
the rows from tableA that are also in tableB but I don't
think this is the best way....?
Click to expand...

Why not just run this query:

DELETE
FROM TableA
WHERE EXISTS
(SELECT *
FROM TableB
WHERE IdentityID = TableA.IdentityID)
 
My datatables are in memory, I didn't think I could run
SQL statements against datatables in memory?
 
don't use a foreach. foreach uses an "IEnumerator" to iterate the
collection. IEnumerator does not support modifying contents of the
collection while it is being iterated.

Instead use a plain for loop. You can modify the collection all you want
while uses a plain for loop.

(C# code)
for (int i = tableA.Rows.Count-1; i >=0; i--)
{
DataRow drNextA = tableA.Rows;
for (int j = tableB.Rows.Count-1; j >=0; j--)
{
DataRow drNextB = tableB.Rows[j];
//compare and do as you wish. IE.
if (...whatever ...)
{
drNextB.Delete();
}
}
}

As you stated removing the DataRow is not what you want to do, that will not
cause it to be removed from the database tableB represents upon an update.

I always use a plain for loop instead of a foreach. I hate the IEnumerator.
Not being able to modify the collection as you iterate it almost makes the
loop useless.
 
Back
Top