Dictionary Of Dictionaries in C#

Question

I am reading an SQL table from SQl SERVER using C# code.

My data looks like this:

ZOO   Animal    Mn Tu
NDS     BAT     2   0
GOR     BAT     3   1
VTZ     BAT     1   2
MAS     BAT     0   0
CST     BAT     0   0
NDS     CAT     4   0
GOR     CAT     4   0
VTZ     CAT     2   0
MAS     CAT     7   0
CST     CAT     1   0
NDS     DOG     3   0
GOR     DOG     2   0
VTZ     DOG     1   0
MAS     DOG     3   0
CST     DOG     0   1
NDS     EGG     7   0
GOR     EGG     2   0
VTZ     EGG     0   0
MAS     EGG     0   0
CST     EGG     4   0

I wrote an sql reader command and it goes

sql = "SELECT * FROM table";

OleDbCommand cmd = new OleDbCommand(sql, conn);
        cmd.CommandTimeout = 600;
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {


            while (reader.Read())
            {
                var location = reader.GetString(0).Trim();
                var animal= reader.GetString(1).Trim();
                var Monday= reader.GetValue(1).ToString().Trim();
                var Tuesday= reader.GetValue(3).ToString().Trim();

             }
        }

I created a Dictionary as follows:

 private Dictionary<string, Dictionary<string, Dictionary<string, string>>> zooDict;
 zooDict= new Dictionary<string, Dictionary<string, Dictionary<string, string>>>();

I would ideally like to have a structure like this:

{
NDS:
    {
     BAT:
        {Mn: 2,
         Tu: 2},
     CAT:
        {
        MN: 4,
        Tu: 0
        }
     DOG:
        {
        MN: 3,
        Tu: 0
        }

     EGG:
        {
        MN: 7,
        Tu: 0
        }


    }
    GOR:
        .
        .
        .
        .
        .

}

I am hoping to convert this dictionary to a json file but I am unable to add keys and values to the empty dictionary. I come from python background where i can use defaultdict to create dictionary of dictionaries without running to the key errors.

Any tips to create the dictionary will be hugely appreciated.

---

if (!ZooDict.ContainsKey(location))
{
    var catDict = new Dictionary<string, string>();
    catDict.Add("Mon", Monday);
    catDict.Add("Tue", Tuesday);
    var AnimalDict = new Dictionary<string, Dictionary<string, string>>();
    AnimalDict.Add(animal, catDict);
    waitlistDict.Add(location, AnimalDict);
}
else
{
    if (!waitlistDict[location].ContainsKey(animal))
    {
        var catDict = new Dictionary<string, string>();
        catDict.Add("Mon", Monday);
        catDict.Add("Tue", Tuesday);
        var AnimalDict = new Dictionary<string, Dictionary<string, string>>();
        AnimalDict.Add(animal, catDict);
        waitlistDict[location] = AnimalDict;
    }
    else
    {
        if (waitlistDict[location][animal].ContainsKey("Mon"))
        {
            waitlistDict[location][animal]["Mon"] = Monday;
        }
        else
        {
            waitlistDict[location][animal]["Mon"] = Monday;
        }
        if (waitlistDict[location][animal].ContainsKey("Tue"))
        {
            waitlistDict[location][animal]["Tue"] = Tuesday;
        }
        else
        {
            waitlistDict[location][animal]["Tue"] = Tuesday;
        }
    }
}

Answer 1

public class DataLine
{
    public string Zoo { get; set; }
    public string Animal { get; set; }
    public int Mn { get; set; }
    public int Tu { get; set; }
}

public IEnumerable<DataLine> ReadAllLines()
{
    ...
    using (OleDbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            yield return new DataLine() { Zoo = reader.GetString(0).Trim(), Animal = reader.GetString(1).Trim(), Mn = reader.GetInt32(2), Tu = reader.GetInt32(3) };
        }
    }
}

var dictionary = ReadAllLines()
    .GroupBy(line => line.Zoo)
    .ToDictionary(zoo => zoo.Key,
                  zoo => zoo.ToDictionary(animal => animal.Animal,
                                          animal => new { animal.Mn, animal.Tu }));

var sb = new StringBuilder();

using (var sw = new System.IO.StringWriter(sb))
{
    new Newtonsoft.Json.JsonSerializer().Serialize(sw, dictionary);
}

Console.WriteLine(sb.ToString());

Answer 2

You can fill the dictionary directly in your loop

private Dictionary<string, Dictionary<string, Dictionary<string, string>>> zooDict =
     new Dictionary<string, Dictionary<string, Dictionary<string, string>>>();

while (reader.Read())
{
       var location = reader.GetString(0).Trim();
       var animal= reader.GetString(1).Trim();
       var Monday= reader.GetValue(1).ToString().Trim();
       var Tuesday= reader.GetValue(3).ToString().Trim();

       if(!zooDict.ContainsKey(location))
            zooDict[location] = new Dictionary<string, Dictionary<string, string>>();

       zooDict[location][animal] = new Dictionary<string, string>() 
       {
             { "MN", Monday },
             { "Tu", Tuesday }
       };
}

As a side note, you might want to consider creating classes to represent your objects, add them to a dictionary or list, and use Newtonsoft Json.net (http://www.newtonsoft.com/json) to serialize them instead of using dictionaries for everything. For example, you can create a class:

public class Animal
{
     public string Name { get; set; }
     public int Monday { get; set; }
     public int Tuesday { get; set; }
}

var zooDict = new Dictionary<string, List<Animal>>();

// then in your while loop you do this 
if(!zooDict.ContainsKey(location))
    zooDict[location] = new List<Animal>();
zooDict[location].Add(new Animal() { /* fill Monday and Tuesday properties */ });

Of course, you can take this one step further and create a class with properties string Location and List<Animal> Animals, and eliminate dictionaries altogether. It depends on your use case(s) I guess