multiple lists stored to Dictionary<string, List<string>>

Question

I am trying to store an English dictionary (about 109,000 words in this particular dictionary) to a Dictionary> data structure and I'm having difficulty figuring out how to do it. My current approach idea is to store first character of the word as the key value and then store the word in a list ("wordlist"). When the key changes from 'a' to 'b', (or 'b' to 'c', etc.) is where I'm stuck, as am having trouble figuring out what to do with the list. Here's my effort to this point. Any help is highly appreciated.

public Dictionary<char, IEnumerable<string>> populateWordDictionary()
    {
        Dictionary<char, IEnumerable<string>> wordDictionary = new Dictionary<char, IEnumerable<string>>();
        //List<string> wordList;
        connect = new SqlConnection(connectionString);
        SqlCommand find = new SqlCommand("Select * FROM English order by Word", connect);

        // starting with first record, store each word into the List<string> wordlist
        SqlDataReader dr = null;
        try
        {
            connect.Open();
            dr = find.ExecuteReader();
            char key;
            char keyStartingPosition = 'a';
            List<string> wordList = new List<string>();
            while (dr.Read())
            {
                // if a word is present
                if (!dr.IsDBNull(0))
                {
                    // set Dictionary key value to the first letter in the word being evaluated
                    key = Convert.ToChar(dr[1].ToString().Substring(0, 1));

                    // if the key value is not the same as the starting position, clear the list
                    // i.e., we've advanced to the next letter in the alphabet
                    if (key != keyStartingPosition)
                    {   
                        // add the current list to the dictionary
                        wordDictionary.Add(key, wordList);
                        // advance key starting position to the new key value
                        keyStartingPosition = key;
                        // and clear current content of wordList
                        wordList.Clear();
                    }

                    // if the first letter of the word list hasn't advanced in the alphebet
                    // simply store the word to the current list.
                    if (key == keyStartingPosition)
                    {
                        wordList.Add(dr[1].ToString());
                    }
                }
            }
        }
        catch (Exception ex)
        {

        }
        finally
        {
            connect.Close();
        }

        return wordDictionary;

    }

Answer 1

This can be done much more simply using LINQ:

...
SqlCommand find = ...
var words = this.ToEnumerable(find);
// returns ILookup<char, string>
var wordDictionary = words.ToLookup(w => w[0]);
// wordDictionary['a'] gives an IEnumerable<string> for the words starting with a

// if you really want to use a dictionary, do:
var wordDictionary = works.GroupBy(w => w[0])
    .ToDictionary(g => g.Key, g => g.ToList());

// the ToEnumerable implementation
private IEnumerable<string> ToEnumerable(SqlCommand find)
{
    using (var reader = find.ExecuteReader) {
        while (reader.Read()) {
           if (!reader.IsDBNull(0)) { yield return reader[1].ToString(); }
        }
    }
}

Answer 2

Your general approach is fine, except for the wordList.Clear() part: you keep re-using the same list, and insert its copy at all keys. As the result, all keys end up with the same list of words as the last key.

To fix this problem, replace wordList.Clear() with

wordList = new List<string>();

and move the line ahead of the wordDictionary.Add line:

if (key != keyStartingPosition)
{   
    wordList = new List<string>();
    // add the current list to the dictionary
    wordDictionary.Add(key, wordList);
    // advance key starting position to the new key value
    keyStartingPosition = key;
}

Also note that since the addition happens upon encountering the first word in a given letter, you need to replace keyStartingPosition = 'a' with keyStartingPosition = '@' or another letter that cannot start a real word.