我将使用表联接。创建一个遍历结果的方法非常简单。即使查询多个窗口小部件及其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();
}
}
}