Coverting List of Dictionary to DataTable

Question

Currently we are doing this by looping through each value of list and dictionary:

private DataTable ChangeToDictionary(List<Dictionary<string,int>> list)
       {
           DataTable datatTableReturn = new DataTable();

           if (list.Count() > 0)
           {
               Dictionary<string, int> haeders = list.ElementAt(0);
               foreach (var colHead in haeders)
               {
                   datatTableReturn.Columns.Add(colHead.Key);
               }
           }

           foreach (var row in list)
           {
               DataRow dataRow = datatTableReturn.NewRow();
               foreach (var col in row)
               {

                   dataRow[col.Key] = col.Value;
               }
               datatTableReturn.Rows.Add(dataRow);
           }
           return datatTableReturn;

       }

But is there a better way? Looping through so many times doesn't feel good

Solution 1

The answers above don't address the issue of the dictionary having more than 1 row. This solution addresses the issue.

static DataTable ToDataTable(List<Dictionary<string, int>> list)
{
    DataTable result = new DataTable();
    if (list.Count == 0)
        return result;

    var columnNames = list.SelectMany(dict=>dict.Keys).Distinct();
    result.Columns.AddRange(columnNames.Select(c=>new DataColumn(c)).ToArray());
    foreach (Dictionary<string,int> item in list)
    {
        var row = result.NewRow();
        foreach (var key in item.Keys)
        {
            row[key] = item[key];
        }

        result.Rows.Add(row);
    }

    return result;
}

static void Main(string[] args)
{
    List<Dictionary<string, int>> it = new List<Dictionary<string, int>>();
    Dictionary<string, int> dict = new Dictionary<string, int>();
    dict.Add("a", 1);
    dict.Add("b", 2);
    dict.Add("c", 3);
    it.Add(dict);
    dict = new Dictionary<string, int>();
    dict.Add("bob", 34);
    dict.Add("tom", 37);
    it.Add(dict);
    dict = new Dictionary<string, int>();
    dict.Add("Yip Yip", 8);
    dict.Add("Yap Yap", 9);
    it.Add(dict);

    DataTable table = ToDictionary(it);
    foreach (DataColumn col in table.Columns)
        Console.Write("{0}\t", col.ColumnName);
    Console.WriteLine();
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn column in table.Columns)
            Console.Write("{0}\t", row[column].ToString());
        Console.WriteLine();
    }
    Console.ReadLine();

}

And the output looks like...

a       b       c       bob     tom     Yip Yip Yap Yap
1       2       3
                        34      37
                                        8       9

Solution 2

Speed, elegance and reusability don't go together. You always choose more important one, and try to balance other two.

Faster the code, uglier it is. Prettier it is, less reusable it is.

Here's an example of "elegant" solution, but that goes with it not being very readable.

private static DataTable ToDictionary(List<Dictionary<string, int>> list)
{
    DataTable result = new DataTable();
    if (list.Count == 0)
        return result;

    result.Columns.AddRange(
        list.First().Select(r => new DataColumn(r.Key)).ToArray()
    );

    list.ForEach(r => result.Rows.Add(r.Select(c => c.Value).Cast<object>().ToArray()));

    return result;
}

Solution 3

Try this

List<Dictionary<string, object>> ListDic;

var stringListDic = JsonConvert.SerializeObject(ListDic);

var dataTable = JsonConvert.DeserializeObject<DataTable>(stringListDic);

Solution 4

How about something like the code below?

Good, because it iterates each row exactly once. It should be pretty quick, I've included obvious exceptions to make the code safer.

private static DataTable DictionariesToDataTable<T>(
        IEnumerable<IDictionary<string, T>> source)
{
    if (source == null)
    {
        return null;
    }

    var result = new DataTable();
    using (var e = source.GetEnumerator())
    {
        if (!e.MoveNext())
        {
            return result;
        }

        if (e.Current.Keys.Length == 0)
        {
            throw new InvalidOperationException();
        }

        var length = e.Current.Keys.Length;

        result.Columns.AddRange(
            e.Current.Keys.Select(k => new DataColumn(k, typeof(T))).ToArray());

        do
        {
            if (e.Current.Values.Length != length)
            {
                throw new InvalidOperationException();
            }

            result.Rows.Add(e.Current.Values);
        }
        while (e.MoveNext());

        return result;
    }
} 

Solution 5

Try this:

    private DataTable GetDataTableFromDictionaries<T>(List<Dictionary<string, T>> list)
    {
        DataTable dataTable = new DataTable();

        if (list == null || !list.Any()) return dataTable;

        foreach (var column in list.First().Select(c => new DataColumn(c.Key, typeof(T))))
        {
            dataTable.Columns.Add(column);
        }

        foreach (var row in list.Select(
            r =>
                {
                    var dataRow = dataTable.NewRow();
                    r.ToList().ForEach(c => dataRow.SetField(c.Key, c.Value));
                    return dataRow;
                }))
        {
            dataTable.Rows.Add(row);
        }

        return dataTable;
    }

View more solutions

Comments

[Jodrell]

you iterate the first row twice.

[Dustin Kingen]

You're not disposing the enumerator.

[Jodrell]

your output doesn't match your input.

[John Kraft]

(facepalm) oops. you are right. I accidentally used the wrong iterator. Edited with correction.

[Jodrell]

There is no fixed relationship between performance and readability. Fast code can be readable code. Ugliness is purely subjective.

[Nikola Radosavljević]

Maybe I should have said "simpler". You can't say that implementation of quick sort is simpler than implementation of bubble sort.

[Dragos Bobolea]

This was a time-saver. Kudos!

[philx_x]

to populate the dataTable's Columns you cloud also use: foreach (KeyValuePair<string,string> entry in myDictonary) { myTable.Columns.Add(entry.Key); }