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

Like what? Any other approach would eventually need to go over all your data c.q. loop over the records. I don't see the real problem that you are having. You dislike the above code? — Bazzz, Mar 08 '13 at 12:11
@Jodrell, Because DataTable can is not there in Seilverlight and sometimes not serializable while returning from service — Simsons, Mar 08 '13 at 12:16
but, if the thing that needs the `DataTable` worked with an `IEnumerable>` for instance ... — Jodrell, Mar 08 '13 at 12:20
What do you want to achieve? More speed? Reusability of code? Code elegance? For starters, don't use `Count()` extension method on `List`. Use `Count` property instead. — Nikola Radosavljević, Mar 08 '13 at 12:24
@NikolaRadosavljević, For now Speed and Elegance is what I'll say — Simsons, Mar 08 '13 at 12:25

John Kraft · Answer 1 · 2013-03-08T17:17:30.370

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

(facepalm) oops. you are right. I accidentally used the wrong iterator. Edited with correction. — John Kraft, Mar 08 '13 at 17:10
to populate the dataTable's Columns you cloud also use: foreach (KeyValuePair entry in myDictonary) { myTable.Columns.Add(entry.Key); } — philx_x, Jan 19 '15 at 16:29

score 7 · Answer 2 · answered Mar 08 '13 at 12:47

7

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;
}

answered Mar 08 '13 at 12:47

Nikola Radosavljević

6,871
32
44

you iterate the first row twice. – Jodrell Mar 08 '13 at 12:53
1

There is no fixed relationship between performance and readability. Fast code can be readable code. Ugliness is purely subjective. – Jodrell Mar 08 '13 at 17:15
Maybe I should have said "simpler". You can't say that implementation of quick sort is simpler than implementation of bubble sort. – Nikola Radosavljević Mar 08 '13 at 23:13

score 3 · Answer 3 · edited Dec 11 '20 at 03:33

3

Try this

List<Dictionary<string, object>> ListDic;

var stringListDic = JsonConvert.SerializeObject(ListDic);

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

edited Dec 11 '20 at 03:33

Bùi Đức Khánh

3,975
6
27
43

answered Dec 11 '20 at 02:24

liem xuan

31
1

Jodrell · Answer 4 · 2013-03-08T15:30:32.830

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;
    }
}

You're not disposing the enumerator. – Dustin Kingen Mar 08 '13 at 15:01 — Dustin Kingen, Mar 08 '13 at 15:01

score 1 · Answer 5 · answered Mar 08 '13 at 14:59

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;
    }

philx_x · Answer 6 · 2015-01-19T17:20:21.317

try my solution, seems very clean to me:

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

        foreach (Dictionary<string,string> dict in list)        //for every dictonary in the list ..
        {
            foreach (KeyValuePair<string,int> entry in dict) //for every entry in every dict
            {
                if (!myTable.Columns.Contains(entry.Key.ToString()))//if it doesn't exist yet
                {
                    myTable.Columns.Add(entry.Key);                 //add all it's keys as columns to the table
                }
            }
            table.Rows.Add(dict.Values.ToArray());              //add the the Values of every dict in the list as a new row
        }

        return table;
    }

Edit: Oh Snap, this works only for one Dictionary .. i didn't think it through. But maybie you can modify it to work for a List of Dictionarys ..

score 0 · Answer 7 · answered Aug 31 '16 at 21:56

Give this a try please

        DataTable table = new DataTable();

        foreach (IDictionary<string, object> row in DeviceTypeReport)
        {
            foreach (KeyValuePair<string, object> entry in row)
            {
                if (!table.Columns.Contains(entry.Key.ToString()))
                {
                    table.Columns.Add(entry.Key);
                }
            }
            table.Rows.Add(row.Values.ToArray());
        }

score 0 · Answer 8 · answered Aug 13 '19 at 18:01

private DataTable toDataTable(List<RetirementDiskModelDto> retirementDiskModelDtos)
        {
            DataTable result = new DataTable();
            foreach (var col in retirementDiskModelDtos.FirstOrDefault().Items)
                result.Columns.Add(col.Key);

            foreach (var row in retirementDiskModelDtos)
            {
                DataRow newrow = result.NewRow();
                foreach (var col in retirementDiskModelDtos.FirstOrDefault().Items)
                    newrow[col.Key] = col.Value;
                result.Rows.Add(newrow);
            }
            return result;
        }

Coverting List of Dictionary to DataTable

8 Answers8

Linked