I have two tables such as DTTable1 and DTTable2. It has the following records.
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 records present in DTTable2(consider only ItemID) then remove the corresponding rows which has the ItemID same as DTTable1.
I have tried following codes but cant solve the problem.
Code snippet 1:
foreach (DataRow DR in DTTable2.Rows)
{
if (DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
{
DTTable2.Rows.Remove(DR);
}
}
DTTable2.AcceptChanges();
It showed the error, "Collection was modified; enumeration operation might not execute". So I used For Loop, It also not given the desired result.
Code Snippet 2:
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();
Code Snippet 3:
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);
Code Snippet 4:
DTTable2.Rows
.Where(DR => DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
.ToList()
.ForEach(r => DTTable2.Rows.Remove(r));
Code Snippet 5:
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);
Code Snippet 6:
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++;
}
Code Snippet 7:
var list = DTTable2.Rows.ToList();
foreach (DataRow DR in list)
{
DTTable2.Rows.Remove(DR);
}
Code Snippet 8:
var result = DTTable1.AsEnumerable()
.Where(row => !DTTable2.AsEnumerable()
.Select(r => r.Field<int>("ItemID"))
.Any(x => x == row.Field<int>("ItemID"))
).CopyToDataTable();
Code snippet 9:
for (int i = 0; i < DTTable2.Rows.Count; i++)
{
if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[i]["ItemID"].ToString())
{
cmd = new SqlCommand("DELETE FROM DetailsTB WHERE ItemID = '" + DTTable2.Rows[i]["ItemID"].ToString() + "' ", con1);
cmd.ExecuteNonQuery();
}
}
But sometimes, the second row doesn't remove 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 simplest method to do this?
All above code cant work.
some data are not deleted from DTTable2 that are also match with DTTable1.
These are only Few method here I write but i tried more than 20 method to do the same.