C# looping through List> to populate a DataTable

Question

I need to loop through a List of Dictionaries

List<Dictionary<string,string>>

to populate a DataTable. Each Dictionary in the list has a Key, which needs to be the column name, and a Value which is what's in that column. The list contains 225 dictionaries (225 rows to the table).

List<Dictionary<string, string>> myList = 
       JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(jsonRep);
DataTable dt = new DataTable();

    //loop through list, loop through dictionaries, add keys as columns, 
    //values as rows.

so far, I have been trying..

//get max columns
int columns = myList[0].Count; <--gives me 13
//add columns
for (int i = 0; i < columns; i++)
   dt.Columns.Add(string myList[i].Keys);  <--somehow get to the key in dict to add as column names         
//add rows
foreach (var x in myList)
{
     dt.Rows.Add(x); <--not working
}
jsonReprValue = dt; <--save new DataTable to var jsonReprValue

How do I do this correctly? Thanks!

Are the columns the same for every row? – Steven Doggart Jun 13 '12 at 14:48 — Steven Doggart, Jun 13 '12 at 14:48

Seth Flowers · Accepted Answer · 2019-02-18T15:25:47.723

8

You have two problems. One is adding the columns, and the other is adding the rows.

Adding the columns

Assuming your list has items in it, and that all the dictionaries in the list have the same keys, then you only need to add columns from one of the dictionaries:

foreach(string column in myList[0].Keys)
{
    dt.Columns.Add(column);
}

Adding the Rows

Change this:

foreach (var x in myList)
{
    dt.Rows.Add(x); <--not working
}

To this:

foreach(Dictionary<string, string> dictionary in myList)
{
    DataRow dataRow = dt.NewRow();

    foreach(string column in dictionary.Keys)
    {
        dataRow[column] = dictionary[column];
    }

    dt.Rows.Add(dataRow);
}

See DataTable.NewRow.

edited Feb 18 '19 at 15:25

answered Jun 13 '12 at 14:52

Seth Flowers

8,990
2
29
42

Thanks for the quick reply! yes, the columns are the same for every row. Each dictionary contains the same keys, but different values. So, I don't need to do the first part at all..get max columns, add columns, etc?? I get an error when I comment that all out and just have this foreach loop, when it gets to dtrow[column] = dict[column]. says Column 'Date' does not belong to table. Date is the first key (column name). – Tonia Roddick Jun 13 '12 at 15:05
I updated my answer to address the "getting the columns" question. – Seth Flowers Jun 13 '12 at 15:06

score 0 · Answer 2 · answered Jun 13 '12 at 15:32

Reiview DataTable Class's example on how to work with a data table. But here is an example using Linq

List<Dictionary<string, string>> myList = new List<Dictionary<string, string>>()
                                                       { new Dictionary<string,string>() { { "ABC", "This" },
                                                                                           { "DEF", "is" },
                                                                                           { "GHI", "radio" },
                                                                                           { "JKL", "clash" } } };

DataTable dt = new DataTable();

// Add columns first
dt.Columns.AddRange( myList.First ()
                           .Select (kvp => new DataColumn() { ColumnName = kvp.Key, DataType = System.Type.GetType("System.String")} )
                           .AsEnumerable()
                           .ToArray()
                           );

// Now add the rows
myList.SelectMany (Dict => Dict.Select (kvp => new {
                                                    Row = dt.NewRow(),
                                                    Kvp = kvp
                                                    }))
      .ToList()
      .ForEach( rowItem => {
                              rowItem.Row[rowItem.Kvp.Key] = rowItem.Kvp.Value;
                              dt.Rows.Add( rowItem.Row );
                           }
             );
dt.Dump();

The result (Dump is LinqPad specific not .Net): enter image description here

C# looping through List> to populate a DataTable

2 Answers2

Linked

Related