Comparing and deleting data rows using C #:

Question

I have two tables, such as DTTable1 and DTTable2. He has the following entries.

DTTable1:

ItemID Specification Amount --------- --------------- --------- 1 A 10 1 B 20 1 C 30 

DTTable1:

  ItemID Specification Amount --------- --------------- --------- 1 A 10 1 B 20 1 C 30 2 A 10 2 B 20 3 A 10 3 B 20 

Here I want to compare these two tables. If DTTable1 entries are present in DTTable2 (consider only ItemID), delete the corresponding rows that have ItemID, such as DTTable1.

I tried foreach and forloop. Using ForEach:

  foreach (DataRow DR in DTTable2.Rows) { if (DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString()) { DTTable2.Rows.Remove(DR); } } DTTable2.AcceptChanges(); 

He showed the error, "The collection has been modified, the enumeration operation may not be performed." Therefore, I used For Loop, it also did not give the desired result.

Usage for the loop:

  for (int i = 0; i < DTTable2.Rows.Count; i++) { if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString()) { DTTable2.Rows.RemoveAt(i); } } DTTable2.AcceptChanges(); 

But sometimes the second row is not deleted from the table. I get the final DataTable as

  ItemID Specification Amount --------- --------------- --------- 1 B 20 2 A 10 2 B 20 3 A 10 3 B 20 

How to solve this? What is the easiest way to do this?

Answer 1

You cannot modify a collection while you list it, as in the first example. Instead, you should get a list of rows to delete, and then delete them.

 List<DataRow> rowsToDelete = new List<DataRow>(); foreach (DataRow DR in DTTable2.Rows) { if (DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString()) rowsToDelete.Add(DR); } foreach (var r in rowsToDelete) DTTable2.Rows.Remove(r); 

Or, built using linq:

 DTTable2.Rows .Where(DR => DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString()) .ToList() .ForEach(r => DTTable2.Rows.Remove(r)); 

---

The second example failed, because after deleting a row, the indices of subsequent rows change, but you continue to increment i , which actually skips the row immediately after the deleted row. There are two ways:

 for (int i = DTTable2.Rows.Count - 1; i >= 0; i--) if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString()) DTTable2.Rows.RemoveAt(i); 

or

 int i = 0; while (i < DTTable2.Rows.Count) { if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString()) DTTable2.Rows.RemoveAt(i); else i++; } 

---

Side note. I wonder if you really want to compare the data of line 0 with this description. Perhaps you wanted to compare all lines similar to the following (although not optimal)?

 if (DTTable1.Any(r => DTTable2.Rows[i]["ItemID"].ToString() == r["ItemID"].ToString())) 

Answer 2

Try using:

 var list = DTTable2.Rows.ToList();//create new list of rows foreach (DataRow DR in list) { //if bla bla bla DTTable2.Rows.Remove(DR); } 

Answer 3

You can use LINQ to DataTable:

 var result = DTTable1.AsEnumerable() .Where(row => !DTTable2.AsEnumerable() .Select(r => r.Field<int>("ItemID")) .Any(x => x == row.Field<int>("ItemID")) ).CopyToDataTable();