Putting relational query results into a combo box

I have 3 tables with the following columns in my database:

tool: toolid, tool_name

facet: facetid, facet_name

tool_facet: tf_id, tf_toolid, tf_facetid

The third table contains a li开发者_StackOverflow中文版st of tools and their facets:

e.g.

1 tid2 fid3

2 tid2 fid4

3 tid3 fid2

where tid2 and fid3 are ids for tools and facets respectively.

In another combo box the user has chosen a specific tool. I want to now display the facets that apply to this tool in another combo box.

Any suggestions?

Thanks.

---

Surely you simply need to repopulate the second combo box's items when the user makes a selection in the first?

You likely don't want to requery the database each time the user makes a selection and so instead will want to hold a structure in memory of all valid facets keyed on toolid.

EDIT some further detail based on Dave's comment:

Assuming that you already have some code setup to query the database, you want to execute a query (or queries) that give you back the relevant data to hold in a cache and do this as part of your app's initialisation after connecting to the database. You could create two dictionaries to hold the tool and facet data. For example:

Dictionary<int, string> _tools = new Dictionary<int, string>();
Dictionary<int, string> _facets = new Dictionary<int, string>();

and then a third structure that basically holds the data in tool_facet:

Dictionary<int, List<int>> _tool_facets = new Dictionary<int, List<int>>();

these basically give you an in-memory copy of the tables that you can very quickly enumerate to populate combo boxes. You'll want to populate the combo boxes with something that holds the ID and a description, something like this will do:

class ComboBoxIntItem
{
  private readonly int _id;
  private readonly string _name;
  public ComboBoxIntItem(int id, string name)
  {
    _id = id;
    _name = name;
  }
  public override string ToString()
  {
    return _name;
  }
  public int GetId()
  {
    return _id;
  }
}

I'm assuming here that you need to know the tool and facet IDs that the user has selected. If you only care about the names then you could just use a single structure like this:

Dictionary<string, List<string>> _tool_facets = new Dictionary<string, List<string>>();

---

If everything in my other answer sounds like a lot of unnecessary work then you could also look at using the databinding capabilities that you get with the .NET controls. To be honest I'm not a fan and prefer to do it "by hand" as per my other answer. Why? Well, I feel you have more control and good separation of UI code from the database. Using the databinding approach is OK for small apps but in my experience it gets very messy for large ones.