Multiple Dynamically Populated Drop Down Lists

Question

I've got a page which will have about ten drop down lists which are generated from my SQL database. What's the best way to populate them? I was going to use a stored procedure with LINQ to return multiple result sets, but it seems a bit overkill. Is there another way of doing it? Using HtmlHelpers or something like that?

Seeing as though everyone seems to be confused by this, I will elaborate.

• This isn't to do with caching, that's not what the problem states
• This isn't to do with ASP.NET controls such as DropDownList, I tagged it asp.net-mvc
• This isn't to do with code-behind models, I thought that was implicitly obvious by how I tagged this question originally as ASP.NET MVC

The problem is that multiple results sets are required on the page for drop down lists (think HTML!). So I have a drop down list for your favourite breed of badger, a drop down list for how many birthdays you've had, a drop down list for how many clouds are in the sky today. All of these are dynamically populated (please note, I am joking, this is a finance system I work on). I need all of them to be on my view page, but I'd rather NOT use the IMultipleResult return type in a LINQ stored procedure to bring back multiple result sets. It just gets messy.

So in basic, I want about 10 drop down lists on my view page, all of which are populated with data from a database (which constantly change). What is the best way to get them on the view?

Answer 1

This is really a question of data management, not anything to do with HtmlHelpers until you get to the very pointy end of things. Anyhow, the first thing I would ask myself is "how is this data updated and what kind of filtering do I need?"

If this list is pretty much constant, then you could go for a bit of caching.

If this list is pretty fluid, then just pull it out of the database as needed and be done with it. Worry about caching if your DB box starts melting.

Either way you probably want to wrap things up in some sort of LookupService class, but there isn't enough to go on to make any more specific recommendations.

Answer 2

I second Wyatt's recommendation about grabbing from the DB each time if the list is fluid.

However, I'm doing a similar thing (with 10+ dropdowns on each page, all very static... the values will rarely change).

The first version of the application had a helper method for each dropdown list (about 20 total by the end of the project) that grabbed from the respective tables and cached via another helper. Before I started caching, there was no db context available for the view (i created it in the controller and didn't pass it), each dropdown had to create a new connection. This got noticeably slow. Plus, I had some problems with my caching routines, and saving them with 20 magic strings in the cache, etc. Also, I had a separate querying object where I had to manually build the relationships, and having to create the 20 relationships for inner joins was a pain.

So... my new version:

I'm using a single "selectables" table. There's a PK, and a "selectable type" (which I have to admit is a string). There's a selectables enumerable, which makes things a bit cleaner. There's a main getAllSelectables() method that looks for the entire result set in the cach (and gets all rows from the db if its not in the cache) and returns it. Then there's a getSelectables(enum) that grabs out only the relevant values, and a third function getSelectListItems(enum) that calls getSelectables(enum) and returns a ienumerable for the mvc helper function.

Hope that helps, James

Answer 3

I would just pass the required data to the view, either in multiple ViewData dictionaries or as a special view model if you want strongly typed access. Then use HtmlHelper.DropDownList() to display the actual drop downs.

Weakly typed solution

Controller

ViewData["Data1"] = SomeRepository.GetList();
ViewData["Data2"] = SomeRepositoty.GetList();
return View();

View

<%= Html.DropDownList("Data1") %>
<%= Html.DropDownList("Data2") %>

Strongly typed solution

View model

public class DataViewModel
{
    public IEnumerable<string> Data1 { get; set; }
    public IEnumerable<string> Data2 { get; set;}
}

Controller

var model = SomeRepository.GetModel(); // returns an instance of DataViewModel
return View(model);

View

<%= Html.DropDownList("Data1", new SelectList(Model.Data1)) %>
<%= Html.DropDownList("Data2", new SelectList(Model.Data2)) %>

Answer 4

It depends on the data, if the data in the database is not updated that often, then you could have a process that creates XML files once a day. Then use the XML files as the source to the dropdowns, this would speed up the application and limit the calls to the database server.