What is the most efficient way to filter listboxes based on selections of another in c#?

Question

I have several listboxes that get each of their data from a separate stored procedure.

If the user selects an option in 1 listbox, it should filter the other listboxes.

I have done this before by adding logic to the stored procedure, but sometimes it seems to get very long.

Does anyone know of a better way to approach this?

The way I have it setup now is that for each ListBox, I have an ObjectDataSource which calls a method that calls a stored proc in the database to populate the listbox.

Answer 1

You can try changing the code so that instead of having the Listbox bind directly to an ADO.Net datatable, it binds to a DataView. DataViews can be sorted and filtered independently from the underlying DataTable they are based on...

Assume LBStates is state ListBox, and lbCities is City ListBox, and dtCities is form level DataTable variable with all cities in it and it has a State Column...

     DataView dvCities = dtCities.DefaultView; 
     dvCities.RowFilter = "State=" + lbStates.SelectedItem;
     lbCities.DataSource = dvCities;

Wire up a selectedIndexChanged event to the States ListBox (in initializtion code)

 lbStates.SelectedIndexChanged += lbStates_SelectedIndexChanged;

and In the States ListBox SelectedIndexChanged event, add the same code...

  private void lbStates_SelectedIndexChanged(object sender, event e)
  {
     DataView dvCities = dtCities.DefaultView; 
     dvCities.RowFilter = "State=" + lbStates.SelectedItem;
     lbCities.DataSource = dvCities;
  }

Answer 2

Listboxes are often used to display "lookup" data, which doesn't change often. Like a list of states or types of entities. So one thing to look into when trying to improve efficiency is caching. There's no reason for a round trip to the database every time you want to get a list of states.

Additionally, if you may want to return all listbox data in a single database call and store in a strongly-typed dataset. Then you can filter the dataset contents based on listbox selections and just rebind the dataset contents to other listboxes.