算法来填充词典<字符串，字典<字符串，字符串>>键入对象

Question

我的数据库表如下所示：

Room  Item  Description 
------------------------------------ 
Bedroom  Chair  Leather 
Bedroom  Bed   Comfortable 
Office  Desk  Very Small 
Office  Book Shelf Lot of Books 

我要填充这个数据库表分为以下字典类型对象

Dictionary<string, Dictionary<string,string> 

我该怎么做？

我开始编写代码如下，但我不能再继续，因为我不知道如何正确填充它。

Dictionary<string, Dictionary<string,string>> roomfurnitures= new Dictionary<string,Dictionary<string, string>>(); 

Dictionary<string, string> furniture= new Dictionary<string, string>(); 

      using (SqlDataReader reader = this.m_cmdGetFurnitureByRoom.ExecuteReader()) 
      { 
       while (reader.Read()) 
       { 
        string roomtype = reader.GetString(reader.GetOrdinal("Room")); 

        string item = reader.GetString(reader.GetOrdinal("Item")); 
        string description = reader.GetString(reader.GetOrdinal("Description")); 

        //I do not know how to populate the roomfurnitures dictionary poperly 
       } 
      } 

在roomfurnitures字典填充正确后，我希望它看起来像这样。请帮忙。

Bedroom  Chair   Leather     
       Bed    Comfortable 
Office   Desk   VerySmall 
       BookShelf  Lot of Books 

Answer 1

重要的是要记住的是，你第一次遇到一个新的房间，你需要实例化它的字典。加入这样的事情在您的评论的位置：

if (!roomfurnitures.ContainsKey(roomtype)) 
    roomfurnitures[roomtype] = new Dictionary<string, string>(); // the first time we've seen this 

// get the dictionary for the specific room 
var room = roomfurnitures[roomtype]; 

// now we can add the furniture to the room 
room[item] = description; 

Answer 2

你可以使用一个DataTable充满DataAdapter，然后用Linq-To-DataSet，Enumerable.GroupBy和Enumerable.ToDictionary：

var tblRooms = new DataTable(); 
using(var con = new SqlConnection(connectionString)) 
using (var da = new SqlDataAdapter(sql, con)) 
{ 
    da.Fill(tblRooms); 
} 
Dictionary<string, Dictionary<string,string>> roomGroups = tblRooms 
    .AsEnumerable() 
    .GroupBy(r => r.Field<string>("Room")) 
    .ToDictionary(g => g.Key, g => g.ToDictionary(
     r => r.Field<string>("Item"), 
     r => r.Field<string>("Description")));