C# datatable from Dictionary

Question

I have the following dictionary:

Dictionary messages = new Dictionary<int, dictionary<string, List<string>>>();

I need somehow to get the contents of this dictionary into a datagridview. The DGV should have 3 columns: ID, Message and Tags. The ID is the ID number of the message (the integer). The Message is, well, the message (the string). The list of tags is whatever tags are linked to this message, there could be everything from 1 to 6 different tags.

The dictionary is composed from the results of an SQL query. I thought of doing it this way, in order to remove duplicates of the messages. The SQL tables are made according to the accepted answer in this question: Building a relation between tables

Or am I perhaps thinking completely wrong? Could I use an SQL query to achieve what I want?

What I wish to achieve is: Getting the results from the query presented as:

| ID | Message1 | Tag1, tag2, tag3, tag4 |
| ID | Message2 | Tag1, tag2, tag3, tag4 |
| ID | Message3 | Tag1, tag2, tag3, tag4 |

Instead of as it is presented now:

| ID | Message1 | Tag1 |
| ID | Message1 | Tag2 |
| ID | Message1 | Tag3 |
| ID | Message2 | Tag1 |
| ID | Message2 | Tag2 |
| ID | Message2 | Tag3 |

The query I am using at the moment is:

SELECT t.message_ID, m.message, t.tagName FROM tbl_messages m JOIN tbl_messages_x_tbl_tags t ON m.message_ID = t.message_ID WHERE t.tagName='tag1' OR t.tagName='tag2'

Are you using an ORM such as dapper to query the database? https://github.com/StackExchange/Dapper also why a dictionary instead of a list of objects? — Matt Stannett, Jan 26 '18 at 08:52
If you want to display the data in simple rows, rather than a parent/child relationship then yes, just select the data in simple rows and set the DataSource accordingly. — Reinstate Monica Cellio, Jan 26 '18 at 08:52
@Rickard please review this answer: https://stackoverflow.com/questions/48310505/pivot-column-entries-into-a-comma-separated-list-in-a-single-row-for-distinct-ad/48311031#48311031 — Alexander I., Jan 26 '18 at 09:12
I have been looking now for some time, Alexander, but unfortunately I don't really understand. I don't understand the SQL query, and trying with the LINQ method I don't seem to be able to get the output I need. Either my query is wrong (same as described), or the code just doesn't work. I think the code is too much to add to the comment. Not sure how to show the code I am using in a good way.. — Rickard, Jan 26 '18 at 13:19

nvoigt · Answer 1 · 2018-01-26T09:04:39.133

1

This is how you can build a list of rows/columns from your data:

messages.SelectMany(outer => outer.Value.Select(inner => new
{
   Column1 = outer.Key,
   Column2 = inner.Key,
   Column3 = string.Join(", ", inner.Value) 
});

I have no idea how your datagridview looks like though.

edited Jan 26 '18 at 09:04

answered Jan 26 '18 at 08:54

nvoigt

75,013
26
93
142

My datagrid, so far, is just an added dgv in the UI, bound to the output of the following SQL query: SELECT message_ID, message FROM tbl_messages – Rickard Jan 26 '18 at 12:13
I tried this, declaring it a var and just added it as datasource to the DGV without any results. :/ – Rickard Jan 26 '18 at 13:21

score 0 · Answer 2 · answered Jan 29 '18 at 02:55

Here's how something like that will work:

You'll want to import system.linq first.

        System.Data.DataTable tbl = new System.Data.DataTable();
        tbl.Columns.Add("ID", typeof(Int32));
        tbl.Columns.Add("Message", typeof(String));
        tbl.Columns.Add("Tags", typeof(String));


        foreach (var kvp1 in messages)
        {
            foreach (var kvp2 in kvp1.Value)
            {

                tbl.Rows.Add(kvp1.Key, kvp2.Key, String.Join(", ", kvp2.Value.ToArray()));

            }
        }

C# datatable from Dictionary

2 Answers2