A Dictionary in a Dictionary

General
1

I am testing the use of nested dictionaries on a RowSet with table data. The key of the MainTableData dictionary is the ID of the row. The other column data are stored in the DetailTableData dictionary (key is name column, value is column value). This is my piece of code:

rs = db.SelectSQL(sql)
If rs <> Nil And rs.RowCount > 0 Then
  
  While Not rs.AfterLastRow
    DetailTableData.RemoveAll
    For c As Integer = 0 to rs.ColumnCount - 1
      if rs.ColumnAt(c).Name = "ID" Then
        id = rs.ColumnAt(c).Int64Value
      Else
        DetailTableData.Value(rs.ColumnAt(c).Name) = rs.ColumnAt(c).StringValue
      End If
    Next
    MainTableData.Value(id) = DetailTableData
    rs.MoveToNextRow
  Wend

End If

The id’s (keys) of MainTableData are correct. However, the DetailData is the same for all keys: the data of the last row in the database table. In the debugging process everything works fine for the first row of data. But DetailTableData.Removeall also removes the DetailTableData of the first key of MainTableData and DetailTableData of the first id is built up with the data of the second row in the RowSet. Why? How to solve this problem?

2

Make a new DetailTableData for each row and don’t use RemoveAll.

3

@Beatrix_Willius Thank you, Beatrix. I have no idea why this code is not working and producing such strange results. But I will try your proposal.

4

@Beatrix_Willius This is perfect: I created an array of DetailTableData and used for each row a new element of the array to store the the detailed data elements. Here is my code:

Var rs As RowSet
Var id As Int64
Var MainTableData As Dictionary = New Dictionary
Var DetailTableData() As Dictionary 
Var sql As String = "SELECT * FROM "+TableName+";"
Var t As Integer

Try
  rs = db.SelectSQL(sql)
  If rs <> Nil And rs.RowCount > 0 Then
    While Not rs.AfterLastRow
      DetailTableData.Add(New Dictionary)
      t = DetailTableData.Count-1
      For c As Integer = 0 to rs.ColumnCount - 1
        if rs.ColumnAt(c).Name = "ID" Then
          id = rs.ColumnAt(c).Int64Value
        Else
          DetailTableData(t).Value(rs.ColumnAt(c).Name) = rs.ColumnAt(c).StringValue
        End If
      Next
      MainTableData.Value(id) = DetailTableData(t)
      rs.MoveToNextRow
    Wend
  End If

Thank you. My question: what is the problem with the previous version? Is this an issue to be reported?

5

Beatrix stated it quite succinctly: you were not creating a New Dictionary inside the loop. Your new code does that. If you insert DetailTableData = New Dictionary in place of DetaiTableData.RemoveAll it would work.

The reason is that DetailTableData is an object reference. Unless you create a new instance every time, you wind up with all the MainTableData entries pointing to the same object, whose values get overwritten each time through the While loop.

2 Likes
6

Thank you, Tim. Great explanation! This helps me understand why the first example went wrong.