Read SQL Table into Dictionary<string, List<string[]>>

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())
            {
               ... ?
            }
      {
   {
}

Answer 1

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.