One of the common concerns that appears in every application is how to populate your select lists from a database. There are many “right” answers. The solution I’m about to show certainly isn’t any more “right” than any other solution out there. My solution does solve a few key problems that I’ve been having, and maybe this can work for some other readers.
Version #1: Add the select options to the model.
In this version, the model itself has the possible options as a property in the model.
public Controller EmployeesController
{
public ActionResult New()
{
Employee employee = new Employee();
employee.Departments = GetDepartments(); // method to fetch from DB.
return View(employee);
}
}
I don’t like this version because it’s just plain bad modeling. There is absolutely no reason that the Employee domain model should have a reference to the list of departments. I’m proud to say that I never wrote anything like this.
Version #2 Add the select options to a View Model.
This is generally the same thing, but now you’re using a view model. View models, or presenter models, are just that: they are used for the presentation tier. Presenters are generally a good thing, and the do things to data that may be outside the realm of the domain model. For example, you may store a DateTime property in the domain model, but present two fields of “Date” and “Time” to the user. The view model/presenter model will take care of breaking apart and joining the user data. I like that the name of the model indicates what it does.
public Controller EmployeesController
{
public ActionResult New()
{
EmployeeViewModel model = new EmployeeViewModel();
model.Departments = GetDepartments(); // method to fetch from DB.
return View(model);
}
}
Usage in views is relatively simple. Also nice: If you have a model, then you have the related drop down values.
@Html.DropDownListFor(x => x.DepartmentId, Model.Departments)
I don’t like that I now have 2x (or 3x, when creating separate “New” and “Edit” view models) the number of objects in my system. I also don’t like having to re-fetch the collection as in the following scenario.
public ActionResult New(EmployeeViewModel form)
{
if (!ModelState.IsValid)
{
model.Departments = GetDepartments(); // need to repopulate the collection.
return View(“New”, form);
}
}
If you don’t repopulate the collection, then you’ll get a Null Reference Exception when the page loads. Of course there is one situation where this is completely unnecessary: If your view model is aware of your data connection. Then you can have the default constructor populate the list. If your models are going to be aware of the database, then go ahead and use the Active Record pattern and save yourself the headache. If you’re using any Active Record implementation, then you don’t even need this article.
Version #3: Static Lookup class
This became my new mainstay for a few applications. In fact, I think that I did it this way in at least 3 apps.
@Html.DropDownListFor(x => x.DepartmentId, Lookup.Departments)
Again, nothing really wrong with this implementation. I really like the code reuse aspect: every place I need a list of departments, I can use the exact same call. Or at least I thought that there was nothing wrong with this until I started using NHibernate profiler. You get this nasty little error message that the view is querying the database. But how can that be? The Lookup static class was pulling the ISession object from a Common Service Locator, and that was calling a service layer. It was the service layer that was talking to the database. Surely, I was doing everything correctly. It turns out I wasn’t, and the profiler was smart enough to catch me on it. The original request was coming from the view; it was no longer in the hands of the controller. If you were tracking MVC lifecycle events, OnRequestedExecuted had already fired and OnResponseExecuting was well on its way. And, as it turns out, you’re in a race condition between when the database connection closes and that select query can make it back from the database.
So, this is good solution, except for a potentially difficult-to-debug race condition.
Version #4: MVC Dynamic Properties
This is really starting to get meaty now. I really like this one.
public Controller EmployeesController
{
public ActionResult New()
{
Employee employee = new Employee();
ViewModel.Departments = GetDepartments(); // ViewModel is a dynamic object.
return View(employee);
}
}
The nice part about this is that you can see all of the data needed by this action. As a bonus, there is no crossing of concerns. This version works whether you use your entity directly or if you use a view model object. Also, that model is only responsible for itself, not it’s related items collection. In your view, you can now use this.
@Html.DropDownListFor(x => x.DepartmentId, View.Departments)
Not bad. There’s only thing that I don’t like about it, and it’s the same thing that I pointed out in #2. If I have to redraw the form, I need to repopulate the dynamic property, or be faced yet again with the dreaded Null Reference Exception.
Version #5: jQuery Everything
There’s really only one problem left. Many applications require cascading drop downs. So all of my “static” drop downs are done one way, and all of my cascading drop downs are done another way. Isn’t that annoying? Yes, and it is not necessary. In all of these versions, we really have two problems that we need to solve.
- The code for fetching the collection from the database must be written.
- The code for wiring the collection to the UI must be written.
What’s left for the developer/architect is to decide where and how that code gets written. Doesn’t doing it exactly the same way every time make sense? So I suggest the following: Solve #1 by making the query a controller action that returns a JSON result. Solve #2 with this jQuery plugin.
(function ($) {
$.fn.buildSelectList = function (options) {
defaults = {
url: null,
data: null,
value: null,
valueProperty: "value",
textProperty: "text",
type: "GET",
emptyOption: true,
emptyOptionText: "",
spinner: null
};
var opts = $.extend(defaults, options);
return this.each(function () {
var $selectElement = $(this), $spinner = $(opts.spinner);
var sl = {
htmlOptions: null,
initialize: function () {
sl.buildSelectList();
sl.selectCurrent();
},
getJsonData: function () {
var jsonData;
$.ajax({
url: opts.url,
data: opts.data,
async: false,
dataType: "json",
type: opts.type,
success: function (json) {
jsonData = json;
}
});
return jsonData;
},
buildSelectList: function () {
if (sl.htmlOptions && !data) { return; }
sl.showSpinner();
var jsonData = sl.getJsonData();
sl.htmlOptions = "";
if (opts.emptyOption) {
sl.htmlOptions += "";
}
for (var i = 0; i < jsonData.length; i++) {
sl.htmlOptions += "<option value=\"" + jsonData[i][opts.valueProperty] + "\">" + jsonData[i][opts.textProperty] + "</option>";
}
$selectElement.html(sl.htmlOptions);
sl.hideSpinner();
},
selectCurrent: function () {
$selectElement.val(opts.value);
},
hideSpinner: function () {
if ($spinner) {
$spinner.hide();
}
},
showSpinner: function () {
if ($spinner) {
$spinner.show();
}
}
};
sl.hideSpinner();
sl.initialize();
});
};
})(jQuery);
When setting this up, we must give a URL. So we’ll do something like this in our view.
@Html.DropDownListFor(x => x.DepartmentId, new List()) <script type="text/javascript"> $(function () { var $departmentId = $("#DepartmentId"); $departmentId.buildSelectList({url: "@Url.Action("GetSelectList", "Departments")"}); }); </script>
The plugin is also set up to handle cascade events and pass the info to the controller action.
$(function () {
var $departmentId = $("#DepartmentId"), $myOtherProperty = $(“#MyOtherProperty”);
$myOtherProperty.change(function () {
$departmentId.buildSelectList({
url: "@Url.Action("GetSelectList", "Departments")",
data: { myOtherProperty: $myOtherProperty.val() }
});
});
});
In conclusion, we have a method of populating drop downs from a database that solves the following problems:
- No collection in the model.
- No having to remember to re-fetch if there’s a binding or model state error.
- No query from views.
- Both “static” and “dynamic” drop downs are treated the same way.
- The JSON result of the controller actions can be cached using the OutputCacheAttribute.
The gist is available here.