Populating an object based on a one-to-many table relationship in SQL

I have an object in C# like this:

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

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)

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.

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

Use the table join. It uses a single SQL query, and it's extremely fast (far faster than your current approach). And for logic to filter out duplicate rows, you can come up with a query for that, I'd imagine; take some time to develop a query that gives you what you want out of the database, and you'll be pleased with the results.

I think you should start moving to Ado Entity Framework or LinQ to SQL as you data provideer as it will save you a lot of time and it will do exactly what you want in an efficient way.