How modify datatable in C#

Question

I am new beginner in SSIS, I am taking data from excel source. Excel source has columns like A,B,C,D,E,F. I created datatable1 and datatable1 contains columns A,C,D. Now I want to create datatable2 from remaining columns from sheet which are not in datatable1, But it gives me error Collection was modified; enumeration operation may not execute


DataTable datatable1 = new DataTable();
DataTable datatable2 = new DataTable();
DataTable datatable3 = new DataTable();
foreach (DataColumn tw in dataTable2.Columns) <--error shows here
{
if (datatable1.Columns.Contains(tw.ToString()))
{
dataTable2.Columns.Remove(tw);
}
datatable3.merge(datatable2)
}

Based on this code, you haven't loaded anything into the data tables. — KeithL, Nov 09 '20 at 12:52

score 0 · Answer 1 · answered Nov 08 '20 at 19:21

As the error indicates, you can't modify the thing you are enumerating over.

Instead, enumerate over the master list of columns. I called my ref_data. For every column I found, I asked datatable1 whether it had a column of the same name. If it did, I used the Remove method against datatable2 to remove the column from the collection.

        DataTable ref_data = new DataTable();
        DataTable datatable1 = new DataTable();
        DataTable datatable2 = new DataTable();
        DataTable datatable3 = new DataTable();

        ref_data.Columns.Add(new DataColumn("A", System.Type.GetType("System.Int32")));
        ref_data.Columns.Add(new DataColumn("B", System.Type.GetType("System.Int32")));
        ref_data.Columns.Add(new DataColumn("C", System.Type.GetType("System.Int32")));
        ref_data.Columns.Add(new DataColumn("D", System.Type.GetType("System.Int32")));
        ref_data.Columns.Add(new DataColumn("E", System.Type.GetType("System.Int32")));
        ref_data.Columns.Add(new DataColumn("F", System.Type.GetType("System.Int32")));

        datatable1.Columns.Add(new DataColumn("A", System.Type.GetType("System.Int32")));
        datatable1.Columns.Add(new DataColumn("C", System.Type.GetType("System.Int32")));
        datatable1.Columns.Add(new DataColumn("D", System.Type.GetType("System.Int32")));

        // Duplicate datatable2 from ref_data
        // and then we'll remove what we don't need
        datatable2 = ref_data.Clone();

        // What columns exist in reference datatable that do not exist in datatable1?
        // Linq is probably more elegant but extensions are not working for me
        // var column_collection = ref_data.Columns.Where(x => x.)
        foreach (DataColumn item in ref_data.Columns)
        {
            // Build out datatable2 structure
            if (datatable1.Columns.Contains(item.ColumnName))
            {
                datatable2.Columns.Remove(item.ColumnName);
            }
        }

        // At this point, datatable 1 contains ACD, datatable2 contains BEF
        Console.WriteLine($"Dumping datatable1");
        foreach (DataColumn item in datatable1.Columns)
        {
            Console.WriteLine(item.ColumnName);
        }

        Console.WriteLine();
        Console.WriteLine($"Dumping datatable2");
        foreach (DataColumn item in datatable2.Columns)
        {
            Console.WriteLine(item.ColumnName);
        }

In order to use linq on a data table you need to add System.Data.DataSetExtensions (both in using and in reference) and then you can use AsEnumerable like dt.AsEnumerable.Select(row => row["A"],row["B"],row["D"]) — KeithL, Nov 09 '20 at 12:55

How modify datatable in C#

1 Answers1