jQuery Mobile Cascaded Selects using MVC4 and KnockoutJs

This post documents building cascading select inputs using jQuery and Knockout for the user interface and MVC4 to serve the content.

Step 1

OPEN or CREATE the MvcMobileApp that includes the Chinook Models and Data Access class [chinook-asp-net-mvc-4-web-application]

View

CREATE a view that will be used to render the cascading selects to the browser. In the Solution Explorer, right click on the Views folder and create a new folder names Chinook. Then right click on the new Chinook folder and Add a new View named CascadeSelect.

Controller

CREATE a controller class for the cascading selects. In the Solution Explorer, right click on the Controllers folder and add a new empty MVC controller named ChinookController

Below the public ActionResult Index() method, add these methods as shown below:

  1. CascadeSelect() returns the CascadeSelect View
  2. GetArtists() returns an json array of artists
  3. GetAlbums() returns an json array of albums by artist id
  4. GetTracks() returns an json array of tracks by album id

MvcMobileApp/Controllers/ChinookController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcMobileApp.Controllers
{
    public class ChinookController : Controller
    {
        //
        // GET: /Chinook/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult CascadeSelect()
        {
            return View();
        }

        public ActionResult GetArtists()
        {
            var artistList = Chinook.GetArtists();
            return this.Json(artistList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetAlbums(string id)
        {
            var albumList = Chinook.GetAlbums(Convert.ToInt32(id));
            return this.Json(albumList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetTracks(string id)
        {
            var trackList = Chinook.GetTracks(Convert.ToInt32(id));
            return this.Json(trackList, JsonRequestBehavior.AllowGet);
        }

    }
}

EDIT Views/Shared/_Layout.cshtml

  1. move the client script bundle loaders into the document head
  2. add a link to the knockoutjs script in the document head
  3. in the document body, look for the opening div data-role=”page”, and add id=”page” to it as follows:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>@ViewBag.Title</title>
        <meta name="viewport" content="width=device-width" />
        <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" />
        @Styles.Render("~/Content/mobileCss", "~/Content/css")
        @Scripts.Render("~/bundles/modernizr")
        @Scripts.Render("~/bundles/jquery", "~/bundles/jquerymobile")
        @RenderSection("scripts", required: false)
        <script src="~/Scripts/knockout-2.1.0.js" type="text/javascript"></script>
    </head>
    <body>
        <div data-role="page" data-theme="b" id="page">
            <div data-role="header">
                @if (IsSectionDefined("Header")) {
                    @RenderSection("Header")
                } else {
                    <h1>@ViewBag.Title</h1>
                    @Html.Partial("_LoginPartial")
                }
            </div>
            <div data-role="content">
                @RenderBody()
            </div>
        </div>
    </body>
</html>

EDIT MvcMobileApp/Views/Chinook/CascadeSelect.cshtml, add this javascript to the bottom of the view.


<script type="text/javascript">
    //for jQueryMobile use this instead of document ready
    $("#page").live("pageinit", function (event) {

        getArtists();

    });

    var viewModel = function () {
        var self = this;
        self.artists = ko.observableArray([]);
        self.albums = ko.observableArray([]);
        self.tracks = ko.observableArray([]);

        //selected value observable and onchange subscription to fire data lookup for child select
        self.selectedAlbum = ko.observable();
        self.selectedAlbum.subscribe(function (value) {
            if (value) {
                getTracks(value);
            }
        });
        self.selectedArtist = ko.observable();
        self.selectedArtist.subscribe(function (value) {
            if (value) {
                getAlbums(value);
            }
        });
    };
    var viewModel = new viewModel();
    ko.applyBindings(viewModel);

    function getArtists() {
        $.getJSON("/Chinook/GetArtist", null, function (data) {
            viewModel.artists(data);
            // reset jQueryMobile selection label
            $("#select-artist").selectmenu("refresh");
        });
    }

    function getAlbums(artistId) {
        $.getJSON("/Chinook/GetAlbums/" + artistId, null, function (data) {
            viewModel.albums(data);
            // reset jQueryMobile selection label
            $("#select-album").selectmenu("refresh");
        });
    }

    function getTracks(albumId) {
        $.getJSON("/Chinook/GetTracks/" + albumId, null, function (data) {
            viewModel.tracks(data);
            // reset jQueryMobile selection label
            $("#select-track").selectmenu("refresh");
        });
    }
</script>
Insert this data bound markup above the javascript that was just added to CascadeSelect.cshtml
<label for="select-artist" class="ui-hidden-accessible">Artist</label>
<select name="select-artist" id="select-artist"
    data-bind="options: artists,
    optionsText: 'Name',
    optionsValue: 'ArtistId',
    optionsCaption: 'Select Artist',
    value: selectedArtist">
</select>

<div data-bind="visible: albums().length > 0">
    <label for="select-album" class="ui-hidden-accessible">Album</label>
    <select name="select-album" id="select-album"
        data-bind="options: albums,
        optionsText: 'Title',
        optionsValue: 'AlbumId',
        optionsCaption: 'Select Album',
        value: selectedAlbum">
    </select>
</div>

<div data-bind="visible: tracks().length > 0">
    <label for="select-track" class="ui-hidden-accessible">Track</label>
    <select name="select-track" id="select-track"
        data-bind="options: tracks,
        optionsText: 'Name',
        optionsValue: 'TrackId',
        optionsCaption: 'Select Track'">
    </select>
</div>

EDIT Views/Home/Index.cshtml and add this razor html helper code to the bottom that will render a link to the CascadeSelect view from the home view.

@Html.ActionLink("CascadeSelect", "CascadeSelect", "Chinook")

Run

Select F5 or one of the debug options to run the app.

Optimize

It is likely that on page load the first select does not populate before it can be accessed by the user. Therefore, we will use the server side view model data source for the select list when the view is returned to the page on load.

EDIT Controllers/ChinookController.cs

CHANGE
public ActionResult CascadeSelect()
{
    return View();
}
TO
public ActionResult CascadeSelect()
{
    //return model for first select
    var artistList = Chinook.GetArtists();
    return View(artistList);
}

EDIT Views/Chinook/CascadeSelect.cshtml

On the very first line, add
@model List

NEXT, REPLACE

<select name="select-artist" id="select-artist"
    data-bind="options: artists,
    optionsText: 'Name',
    optionsValue: 'ArtistId',
    optionsCaption: 'Select Artist',
    value: selectedArtist">
</select>
WITH
<select name="select-artist" id="select-artist"
    data-bind="value: selectedArtist">
        <option value="">Select Artist</option>
@{
    if (Model != null)
    {
        foreach (var item in Model)
        {
            <option value="@item.ArtistId">@item.Name</option>
        }
    }
}
</select>

NEXT, change the visible binding on the select-album parent div.
REPLACE

<div data-bind="visible: albums().length > 0">
WITH
<div data-bind="visible: selectedArtist">

NEXT, change the visible binding on the select-track parent div.
REPLACE

<div data-bind="visible: tracks().length > 0">
WITH
<div data-bind="visible: selectedArtist, visible: selectedAlbum">

NOTE: Bindings are applied in order from left to right. In the data-bind above, visible: selectedArtist is applied before visible: selectedAlbum.

In the javascript code block at the bottom of Views/Chinook/CascadeSelect.cshtml, DELETE these two lines of javascript:

getArtists();

self.artists = ko.observableArray([]);
AND

DELETE the function:

function getArtists() {
    $.getJSON("/Chinook/GetArtists", null, function (data) {
        viewModel.artists(data);
        // reset jQueryMobile selection label
        $("#select-artist").selectmenu("refresh");
    });
}

Review

After all of those changes, your Views/Chinook/CascadeSelect.cshtml view should like this:
@model List
@{
    ViewBag.Title = "CascadeSelect";
}

<h2>CascadeSelect</h2>

<label for="select-artist" class="ui-hidden-accessible">Artist</label>
<select name="select-artist" id="select1"
    data-bind="value: selectedArtist">
        <option value="">Select Artist</option>
@{
    if (Model != null)
    {
        foreach (var item in Model)
        {<a href="~/Scripts/">~/Scripts/</a>
            <option value="@item.ArtistId">@item.Name</option>
        }
    }
}
</select>

<div data-bind="visible: selectedArtist">
    <label for="select-album" class="ui-hidden-accessible">Album</label>
    <select name="select-album" id="select2"
        data-bind="options: albums,
        optionsText: 'Title',
        optionsValue: 'AlbumId',
        optionsCaption: 'Select Album',
        value: selectedAlbum">
    </select>
</div>

<div data-bind="visible: selectedArtist, visible: selectedAlbum">
    <label for="select-track" class="ui-hidden-accessible">Track</label>
    <select name="select-track" id="select3"
        data-bind="options: tracks,
        optionsText: 'Name',
        optionsValue: 'TrackId',
        optionsCaption: 'Select Track'">
    </select>
</div>

<script type="text/javascript">
    //for jQueryMobile use this instead of document ready
    $("#page").live("pageinit", function (event) {
        //getArtists();
    });

    var viewModel = function () {
        var self = this;
        self.albums = ko.observableArray([]);
        self.tracks = ko.observableArray([]);

        //selected value observable and onchange subscription to fire data lookup for child select
        self.selectedAlbum = ko.observable();
        self.selectedAlbum.subscribe(function (value) {
            if (value) {
                getTracks(value);
            }
        });
        self.selectedArtist = ko.observable();
        self.selectedArtist.subscribe(function (value) {
            if (value) {
                getAlbums(value);
            }
        });
    };
    var viewModel = new viewModel();
    ko.applyBindings(viewModel);

    function getAlbums(artistId) {
        $.getJSON("/Chinook/GetAlbums/" + artistId, null, function (data) {
            viewModel.albums(data);
            // reset jQueryMobile selection label
            $("#select-album").selectmenu("refresh");
        });
    }

    function getTracks(albumId) {
        $.getJSON("/Chinook/GetTracks/" + albumId, null, function (data) {
            viewModel.tracks(data);
            // reset jQueryMobile selection label
            $("#select-track").selectmenu("refresh");
        });
    }
</script>

Run

Select F5 or one of the debug options to run the app.

    References:
  • KnockoutJS Simplify dynamic JavaScript UIs by applying the Model-Vifew-View Model (MVVM) pattern.

Chinook ASP.NET MVC 4 Web Application

For this tutorial, you will need:

In Visual Studio, create a new C# ASP.NET MVC 4 Web Application:
Visual Studio 2012 New Project Dialog

Next, select the Mobile Application Project Template. This template includes jQuery, jQuery intellisense for Visual Studio, jQuery Mobile and Knockoutjs javascript files.
Visual Studio 2012 Project Template Dialog

If you haven’t done so yet, now would be a good time to install the Chinook database to your SQL Server.

Make sure you can Access your SQL Server [Configure Windows Firewall]

Open up the Web.config and replace

<connectionStrings>
 <add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-MvcMobileApp-20120922134208;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-MvcMobileApp-20120922134208.mdf" />
</connectionStrings>

With *

<connectionStrings>
 <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True" />
</connectionStrings>

* VGN-FW290 is the name of my computer. Replace this with your computer name

SQL Stored Procedures

Open up SQL Server Management Studio and connect to the server that is defined in your web.config connection string. In the Object Explorer(F8) Right click on the Stored Procedures node of the expanded Databases.Chinook tree and Select “New Stored Procedure”.

The first stored procedure you will create, GetArtists uses CTE (Common Table Expression) to handle the paging of the records. Especially in web applications, it is a best practice to utilize paging when returning records from large tables.

The next stored procedure you will create, GetAlbums is for selecting Album(s).

The third stored procedure you will create, GetTracks is for selecting Track(s).

SQL Function

New Scalar-value function

Create this user defined function to add a computed AlbumCount column to the Artist Table.

Now we need to bind the SQL Function to the Artist table. In the Object Explorer(F8), expand the Tables node and Artist table node. Right click on the Columns folder and select New Column from the context menu. Enter AlbumCount under Column Name and int under Data Type. Navigate to the Column Properties tab, expand the Computed Column Specification and enter “([dbo].[CountAlbums]([ArtistId]))” for the (Formula) value. Save the Artist table (Ctrl+S).

Data Models

Create a data model class for the Chinook data objects. In the Solution Explorer, right click on the Models folder and add a new file named ChinookModels.cs

MvcMobileApp/Models/ChinookModels.cs
using System;

namespace MvcMobileApp.Models
{
    public class AlbumModel
    {
        public int AlbumId { get; set; }
        public string Title { get; set; }
        public int ArtistId { get; set; }
    }

    public class ArtistModel
    {
        public int ArtistId { get; set; }
        public string Name { get; set; }
        public int AlbumCount { get; set; }
    }

    public class TrackModel
    {
        public int TrackId { get; set; }
        public string Name { get; set; }
        public int AlbumId { get; set; }
        public int MediaTypeId { get; set; }
        public int GenreId { get; set; }
        public string Composer { get; set; }
        public int Milliseconds { get; set; }
        public int Bytes { get; set; }
        public double UnitPrice { get; set; }
    }
}

Data Access

Create a data access class that can be used by any controller in our application to interface with the Chinook database. In the Solution Explorer, right click on the MvcMobileApp project and add a new class called Chinook. The result is a file named Chinook.cs in the root of our application.

MvcMobileApp/Chinook.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace MvcMobileApp
{
    using Models;

    public class Chinook
    {
        public static List GetAlbums(int artistId)
        {
            List albumList = new List();
            SqlConnection conn = new SqlConnection(Config.ChinookConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetAlbums", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ArtistId", artistId);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    albumList.Add(new AlbumModel()
                    {
                        AlbumId = Convert.ToInt32(dr["AlbumId"]),
                        Title = dr["Title"].ToString(),
                        ArtistId = Convert.ToInt32(dr["ArtistId"]),
                    });
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return albumList;
        }

        public static List GetArtists()
        {
            //get all artists
            List artistList = GetArtists(0,1);
            return artistList;
        }

        public static List GetArtists(int pageSize, int pageNumber)
        {
            List artistList = new List();
            SqlConnection conn = new SqlConnection(Config.ChinookConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetArtists", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@PageSize", pageSize);
                cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    artistList.Add(new ArtistModel()
                    {
                        ArtistId = Convert.ToInt32(dr["ArtistId"]),
                        Name = dr["Name"].ToString(),
                        AlbumCount = Convert.ToInt32(dr["AlbumCount"])
                    });
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return artistList;
        }

        public static List GetTracks(int albumId)
        {
            List trackList = new List();
            SqlConnection conn = new SqlConnection(Config.ChinookConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetTracks", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@AlbumId", albumId);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    trackList.Add(new TrackModel()
                    {
                        TrackId = Convert.ToInt32(dr["TrackId"]),
                        Name = dr["Name"].ToString(),
                        AlbumId = Convert.ToInt32(dr["AlbumId"]),
                        MediaTypeId = Convert.ToInt32(dr["MediaTypeId"]),
                        GenreId = Convert.ToInt32(dr["GenreId"]),
                        Composer = dr["Composer"].ToString(),
                        Milliseconds = Convert.ToInt32(dr["Milliseconds"]),
                        Bytes = Convert.ToInt32(dr["Bytes"]),
                        UnitPrice = Convert.ToDouble(dr["UnitPrice"]),
                    });
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return trackList;
        }

        private class Config
        {
            static public String ChinookConnection { get { return WebConfigurationManager.ConnectionStrings["ChinookConnection"].ConnectionString; } }
        }

    }

}

Controller

CREATE a controller class for the cascading selects. In the Solution Explorer, right click on the Controllers folder and add a new empty MVC controller named ChinookController

Below the public ActionResult Index() method, add these methods as shown below:

  1. GetArtists() returns an json array of artists
  2. GetAlbums() returns an json array of albums by artist id
  3. GetTracks() returns an json array of tracks by album id

MvcMobileApp/Controllers/ChinookController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcMobileApp.Controllers
{
    public class ChinookController : Controller
    {
        //
        // GET: /Chinook/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult GetArtists()
        {
            var artistList = Chinook.GetArtists();
            return this.Json(artistList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetAlbums(string id)
        {
            var albumList = Chinook.GetAlbums(Convert.ToInt32(id));
            return this.Json(albumList, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetTracks(string id)
        {
            var trackList = Chinook.GetTracks(Convert.ToInt32(id));
            return this.Json(trackList, JsonRequestBehavior.AllowGet);
        }

    }
}