C#: Bind Dictionary> to DataTable

Question

I have one dictionary Like

Dictionary<string, List<string>> first=new Dictionary<string, List<string>>();

I want to bind this dictionary to data table such that data table ColumnName should be key of the dictionary and respective columns should contain their dictionary values. What I tried:

Dictionary<string, List<string>> some= new Dictionary<string, List<string>>();
 System.Data.DataTable dt = new System.Data.DataTable();
            foreach (var entry in some)
            {
                if (entry.Value.Count > 0)
                {
                    dt.Columns.Add(entry.Key); 
                    //entry.Value.count is not same for all entry.Key                 
                    foreach (var value in entry.Value)
                    {
                        DataRow row = dt.NewRow();
                        row[entry.Key] = value;
                        dt.Rows.Add(row);
                    }
                }              
            }

Surely I know, above code is having some errors to achieve following result DesirrdResultImage any suggestions?

for every column, data is adding at next to the previous column's last row. @Haytam — Naira, Feb 24 '18 at 13:10
I am dealing with a lot of sheets of excel data to get user desired results on button click events of WinForms.I got the above situation, where I struck. @BKSpurgeon Tq — Naira, Feb 24 '18 at 14:03

Haytam · Accepted Answer · 2018-02-24T13:50:31.857

Here's a possible solution (note that I don't think this is the best way to do this, but I hope it'll help guide you):

Dictionary<string, List<string>> some = new Dictionary<string, List<string>>
{
    { "Key1", new List<string>
        {
            "Val1_1",
            "Val2_1",
            "Val3_1"
        }
    },
    { "Key2", new List<string>
        {
            "Val1_2",
            "Val2_2",
            "Val3_2"
        }
    }
};

DataTable dt = new DataTable();
var keys = some.Keys;

// Add all the columns from the beginning
dt.Columns.AddRange(keys.Select(key => new DataColumn(key)).ToArray());
// Get the rows number using the Max count of the lists (assuming the length of the lists might change, otherwise just use some.Values[0].Count)
int rowsNumber = some.Values.Max(s => s.Count);

for (int i = 0; i < rowsNumber; i++)
{
    var row = dt.NewRow();

    // Set all the values depending on the keys
    foreach (var key in keys)
    {
        if (some[key].count <= i)
            break;

        row[key] = some[key][i];
    }

    dt.Rows.Add(row);
}

dataGridView1.DataSource = dt;

The result is:

'row[key] = some[key][i] ' will get index out of range exception if all dictionaries having different count. plz edit. — Naira, Feb 24 '18 at 13:42
Add if (some[key].count<=) before row[key]=some[key][i]. @Haytam — Naira, Feb 24 '18 at 13:48

Zahid Tanveer · Answer 2 · 2018-02-24T14:42:58.447

Check if No row or less than Values than add Value to New row else Add value to existing row

        DataTable dt = new DataTable();
        int i = 0;
        foreach (var entry in some)
        {
            if (entry.Value.Count > 0)
            {
                dt.Columns.Add(entry.Key);
                DataRow row;
                //entry.Value.count is not same for all entry.Key                 
                foreach (var value in entry.Value)
                {

                    int ValueCount = entry.Value.Count();
                    if (dt.Rows.Count <= ValueCount)
                    {
                        row = dt.NewRow();
                        row[entry.Key] = value;
                        dt.Rows.Add(row);
                    }
                    else
                    {
                        row = dt.Rows[i];
                        row[entry.Key] = value;
                        i++;
                    }

                }

            }
        }

C#: Bind Dictionary> to DataTable

2 Answers2

Linked