Read SQL Table into Dictionary>

Question

I have a SQL table that looks like this:

AAA, Amanda, Anthony
AAA, Anna, Andrew
BBB, Boris, Benji
BBB, Blondie, Bobby

I'm trying to use a SQL data reader to read the data then insert the results into a Dictionary<string, List<string[]>>

The expected result is:

[Key]
   "AAA"
[Value]
   "Amanda", "Anthony"
   "Anna", "Andrew"
[Key]
   "BBB"
[Value]
   "Boris", "Benji"
   "Blondie", "Bobby"

Please help:

using (SqlConnection cnn = new SqlConnection("connection"))
{
   using (SqlCommand cmd = new SqlCommand("command", cnn))
   {
      using (SqlDataReader rdr = cmd.ExecuteReader())
      {
         while (rdr.Read())
            {
               ... ?
            }
      {
   {
}

I would give some serious thought to whether you really want to put multiple values in one column as you appear to be doing. Usually there comes a point down the road where you will regret doing this... — Abe Miessler, Oct 23 '12 at 20:20
@RBarryYoung: What do you mean? I'm asking for help inserting records from a SQL table into a Dictionary. Why the -1? — Administrateur, Oct 23 '12 at 20:21
@AbeMiessler: "Usually there comes a point down the road where you will regret doing this..." _for example, when? — Administrateur, Oct 23 '12 at 20:22
When you want to treat the implied parent child relationship you have created as an actual parent child relationship. An answer with examples is too large to include in a comment. That might actually make a good SO question on it's own... — Abe Miessler, Oct 23 '12 at 20:24
@AbeMiessler: Thanks for the tip. For what I need right now, the suggestions below will work. — Administrateur, Oct 23 '12 at 21:00

score 1 · Answer 1 · answered Oct 23 '12 at 20:24

This is pretty simple, I think. Read through your data table, and for each row, check to see if [key] exists in your dictionary. If not, add it with a new List containing your [value]; otherwise, add the [value] to the List at position [key].

If you sort the data table, you can speed this up using a classic control break technique -- for each new [key], start accumulating a List until the [key] changes, then add the [key] and List to your dictionary.

score 1 · Answer 2 · answered Oct 23 '12 at 20:32

while (rdr.Read())
{
    if (dictionary.ContainsKey((string)rdr["column1"]){
        dictionary[(string)rdr["column1"]].Value.Add(new string[]{(string)rdr["column2"], (string)rdr["column3"]});
    } else {
        dictionary.Add((string)rdr["column1"]), new List<string>());
        dictionary[(string)rdr["column1"]].Value.Add(new string[]{(string)rdr["column2"], (string)rdr["column3"]});
    }
}

This should work.

score 0 · Accepted Answer · answered Oct 23 '12 at 20:26

Something similar to this:

 var dict = new Dictionary<string, List<string[]>>();
 while (rdr.Read())
 {
     // TODO: extract field1, field2, field3
     if(dict.ContainsKey(field1))
     {
         // Add the values to the existing list
         dict[field1].Add(new string [] {field2 , field3});
     }
     else
     {
         //Create a new list and set the initial value
         dict[field1] = new List<string[]> { new string[] { field2 , field3 } };
     }
 }

score 0 · Answer 4 · answered Oct 23 '12 at 20:27

0

Something like this maybe?

myDict.Add(rdr["key"].ToString(), rdr["value"].ToString().Split(",").ToList());

Untested, but it should get you going in the right direction...

answered Oct 23 '12 at 20:27

Abe Miessler

82,532
99
305
486

Read SQL Table into Dictionary>

4 Answers4