CODEWITHSUNDEEP
c#asp.netvisual-studio-2010linqdatatable
thevan
thevan

Reputation: 10364

Compare and delete datatable row using C#:

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 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();

It showed the error, "Collection was modified; enumeration operation might not execute". So I used For Loop, It also not given the desired result.

Using For 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 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?

Upvotes: 4

Views: 13487

Answers (4)

user1544428
user1544428

Reputation: 150

Well, guys. I had an almost identical situation and no matter how I tried to do it, I kept running into problems. My solution was not to try and loop through the DataTable at all. I made an array from the first DataTable (this contained the rows that needed to be deleted from the second DataTable). Loop through the Array and use a select to remove the row that matched in the DataTable. Since it wasn't a loop, and it could match any row in the DataTable, I quit getting errors.

Note: Original DataTable is a 1-column table that contains what you are trying to remove from the second DataTable by row (doesn't matter how many columns it has). Replace "LinkName=' w/ the column name for your 1-column table.

 public DataTable RemoveDuplicateRows(DataTable OriginalLinks, DataTable UpdatedLinks) // OriginalLinks is a 1-col dt to delete from new dt
    {

        ArrayList arrOriginalLinks = new ArrayList();

        if (OriginalLinks.Rows.Count > 0)
        {

            foreach (DataRow dr in OriginalLinks.Rows)
            {
                arrOriginalLinks.Add(dr["LinkName"]);
            }
        }


        if (OriginalLinks.Rows.Count == 0)
        {
            // do nothing
        }
        else
        {
            for (int i = OriginalLinks.Rows.Count - 1; i >= 0; i--)
            {
                string filter = "LinkName='" + arrOriginalLinks[i].ToString() + "'";

                UpdatedLinks.Select(filter).ToList().ForEach(r => r.Delete());


            }

        }

        return UpdatedLinks;

    }

This returns the second DataTable with just the rows left that didn't match a value found in the first DataTable.

Upvotes: 0

cuongle
cuongle

Reputation: 75316

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();

Upvotes: 4

lc.
lc.

Reputation: 116528

You can't modify a collection while you are enumerating it as in your first example. You should instead get a list of the 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, inline using linq:

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

Your second example fails because once a row is removed, the indexes of subsequent rows change but you continue to increment i, which effectively skips over the row immediately following the deleted row. There are two ways around this:

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 mean to always compare to row 0's data, given your description. Perhaps you meant to compare all rows like the following instead (although not optimal at all)?

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

Upvotes: 6

Rover
Rover

Reputation: 2230

Try to use:

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

Upvotes: 4

Related Questions