在 SQL 中基于一对多表关系填充对象

Question

我在 C# 中有一个像这样的对象：

private ClassWidget
{
    public int ID;
    public List<int> WidgetFavoriteNumbers;
}

假设我在 SQL 中有两个表，一个定义小部件属性，另一个保存单个小部件的许多记录，假设小部件最喜欢的数字：

widgets
-----------
id (int, not null)
// other properties ...

widget_nums
----------
widget_id (int, not null)
num (int)

我发现自己经常执行两个 SQL 查询即使我知道我可以连接表来创建一个查询，也要填充此对象。原因是，仅使用我需要的数据填充对象似乎比迭代具有大量重复数据的结果集更简单。当然，与真实场景相比，这个小部件示例要简化得多。这是示例：

int WidgetID = 8;
ClassWidget MyWidget = new ClassWidget();
using (SqlConnection conn = GetSQLConnection())
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = @"SELECT id FROM widgets WHERE id = @WidgetID;";
        cmd.Parameters.AddWithValue("WidgetID", WidgetID);
        using (SqlDataReader Reader = cmd.ExecuteReader())
        {
            if (Reader.HasRows)
                MyWidget.ID = GetDBInt("id", Reader); // custom method to read database result
        }
        cmd.CommandText = @"SELECT num FROM widget_nums WHERE widget_id = @WidgetID;";
        using (SqlDataReader Reader = cmd.ExecuteReader())
        {
            if (Reader.HasRows)
                while (Reader.Read())
                    MyWidget.WidgetFavoriteNumbers.Add(GetDBInt("num", Reader));
        }
        conn.Close();
    }
}

我的问题是我是否应该继续使用这种类型的方法，或者是否建议执行表连接。如果建议使用表连接，那么填充对象的最佳设计模式是什么？我的问题是，我必须创建一些逻辑来过滤掉重复的行，当我获取所有小部件而不仅仅是一个小部件时，这一点尤其复杂。

Answer 1

我会使用表连接。创建一个遍历结果的方法非常简单。即使在查询多个小部件及其 widget_nums 时，您也可以使用此方法

  private IEnumerable<ClassWidget> MapReaderToWidget(IDataReader reader) {
     var dict = new Dictionary<int, ClassWidget>();
     while (reader.Read()) {
        var id = (int)reader["id"];
        ClassWidget widget;
        if (!dict.TryGetValue(id, out widget)) {
            widget = new ClassWidget {
               ID = id,
               WidgetFavoriteNumbers = new List<int>();
            };
            dict.Add(id, widget);
        }
        widget.WidgetFavoriteNumbers.Add((int)reader["num"]);
     }
     return dict.Values;
  }

然后重写您的方法，如下所示：

using (SqlConnection conn = GetSQLConnection())
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = @"SELECT id FROM widgets INNER JOIN widget_nums on .... WHERE id = @WidgetID;";
        cmd.Parameters.AddWithValue("WidgetID", WidgetID);
        using (SqlDataReader Reader = cmd.ExecuteReader()) {
           return MapReaderToWidget(reader).FirstOrDefault();
        }
    }
}

Answer 2

使用表连接。它使用单个 SQL 查询，并且速度非常快（比您当前的方法快得多）。我想，对于过滤掉重复行的逻辑，您可以为此提出一个查询；花一些时间来开发一个查询，该查询可以从数据库中提供您想要的内容，您会对结果感到满意。

Answer 3

我认为您应该开始转向 Ado Entity Framework 或 LinQ to SQL 作为您的数据提供者，因为它将节省您大量时间，并且它将以有效的方式完全满足您的需求。