在SQL中基于一对多表关系填充对象 [英] Populating an object based on a one-to-many table relationship in SQL

问题描述

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

I have an object in C# like this:

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

比方说，我在SQL中有两个表，一个表定义了窗口小部件属性，另一个表为单个窗口小部件保存了许多记录，让我们说窗口小部件的收藏夹编号:

Let's say I have two tables in SQL, one defines widget properties, and the other holds many records for a single widget, let's say the widget's favorite numbers:

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

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

我发现自己经常执行两个SQL查询来填充该对象，尽管我知道我可以联接表来创建一个查询.原因是只用我需要的数据填充对象似乎要比遍历具有大量重复数据的结果集更简单.当然，与实际情况相比，此小部件示例已大大简化.这是示例:

I find myself frequently executing two SQL queries to populate this object even though I know I can join the tables to create just one query. The reason is that it seems simpler to populate the object with just the data I need rather than iterating over result sets that have a lot of duplicate data. Of course this widget example is much simplified compared to the real scenario. Here's the example:

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();
    }
}

我的问题是我应该继续使用这种类型的方法，还是建议执行表联接.如果建议使用表连接，那么填充对象的最佳设计模式是什么?我的问题是我必须创建一些逻辑来过滤出重复的行，当我获得所有小部件而不是一个时，这特别复杂.

My question is whether I should continue using this type of approach, or if performing a table join would be recommended. If the table join is recommended, what is the best design pattern to populate the object? My problem is that I have to create some logic to filter out duplicate rows, and is especially complicated when I am getting all widgets rather than just one.

推荐答案

我将使用表联接.创建一个遍历结果的方法非常简单.即使查询多个窗口小部件及其及其widget_nums

I would use a table join. It is pretty simple to create a method which will traverse the results. You can use this method even when querying for multiple widgets and and their 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;
  }

然后将您的方法重写如下:

Then rewrite your method as following:

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();
        }
    }
}

这篇关于在SQL中基于一对多表关系填充对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！