2

Im trying to delete rows from DataTable AllItems with rows from DataTables Items; The purpose of this to get items from DataTable AllItems which are not inside DataTable Items

All these rows Fiiled from same Excel file which contains several columns and are equal.

I have tried using foreach loop:

foreach(DataRow dr in AllItems.Rows)
{
if (Items.Contains(dr))
{
AllItems.Rows.Remove(dr);
}

But I get following error: Table doesn't have primary key.

Does anyone knows how i can delete these rows?

Eimantas Baigys
  • 147
  • 2
  • 4
  • 19
  • It's [best](http://msdn.microsoft.com/en-us/library/vstudio/ff926074.aspx) to use lowercase-first camel-case for `variableNames`. – Jonathon Reinhart May 15 '13 at 07:08
  • Vote to Close (spotted after adding an answer): http://stackoverflow.com/questions/4415519/best-way-to-remove-duplicate-entries-from-a-data-table - in particular, http://stackoverflow.com/a/15264638/1073107 gives a very succint answer for removing duplicate rows. – dash May 15 '13 at 07:28

2 Answers2

3

You have a few choices here:

1. Add a Primary Key

you can add a primary key to your data table when creating it.

Assuming you had a column called "Id" then you would do it this way:

AllItems.PrimaryKey = new DataColumn[] { workTable.Columns["Id"] };}

Or, for cases where your primary key is a composite key (multiple columns):

AllItems.PrimaryKey = new DataColumn[] { 
                             workTable.Columns["Id"], 
                             workTable.Columns["Name"] };}

This would then allow Contains to work correctly.

2. Use a DataView

You can use a DataView to filter out the distinct rows;

DataView view = new DataView(AllItems);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" , ..., "ColumnN");

3. Find Matching Rows using Select

Or you can rely on the Select method to test if a corresponding row exists in the Items DataTable based on a statement that's like a SQL WHEREclause:

List<DataRow> rowsToRemove = new List<DataRow>();

foreach(DataRow allItemRow in AllItems.Rows)
{
    if(Items.Select(String.Format("Id = {0}"), 
          allItemRow.Field<Int32>("Id")).Length == 0)
    {
        rowsToRemove.Add(allItemRow);
    }
}

rowsToRemove.ForEach(x => x.Delete());

AllItems.AcceptChanges();

Note that it's important NOT to remove rows while you are iterating the collection of Rows in AllItems - instead, collect these rows, and remove them afterwards.

4. Filter on the way in

Also note, and I haven't tried it, but, depending on how you are selecting the rows out of Excel, you may be able to use the SQL DISTINCT clause; if you are using ODBC to load data from Excel then you could try filtering at source.

dash
  • 89,546
  • 4
  • 51
  • 71
0

You may try this:

var exceptItems = AllItems.Rows.Cast<DataRow>()
    .Except(Items.Rows.Cast<DataRow>(), DataRowComparer.Default)
    .ToList();

As an alternative, if you want to keep working with the allItems data table after removing the items rows from it, you may try this (assuming that you have the column Id in both data tables, which uniquely identifies a row per data table):

var exceptItems = AllItems.Rows.Cast<DataRow>()
    .Select((i, index) => new { id = i["Id"], index })
    .Intersect(Items.Rows.Cast<DataRow>()
        .Select((i, index) => new { id = i["Id"], index }))
    .ToList();

for (int i = exceptItems.Count()-1; i >= 0; i--)
{
    AllItems.Rows.RemoveAt(exceptItems[i].index);
}

Here's a nicer arrangement of the last example above:

AllItems.Rows.Cast<DataRow>()
    .Select((i, index) => new { id = i["Id"], index })
    .Intersect(Items.Rows.Cast<DataRow>()
        .Select((i, index) => new { id = i["Id"], index }))
    .OrderByDescending(i => i.index)
    .ToList()
    .ForEach(i => AllItems.Rows.RemoveAt(i.index));
Alex Filipovici
  • 31,789
  • 6
  • 54
  • 78