DotNetNuke 7 Web Services Framework Web API Routes

When building the DotNetNuke Web Services API controllers, I want to use this REST convention for the endpoints to access a resource such as the Chinook database. Note: My DotNetNuke website is running under IIS 7.5 with "dnn7" as the host name binding. Only the request method, url, protocol, content-type and content-body are included in request examples below.

Return all Tracks:

GET http://dnn7/DesktopModules/Services/API/Tracks HTTP/1.1

Return a Track where ID = 1:

GET http://dnn7/DesktopModules/Services/API/Tracks/1 HTTP/1.1
CREATE a Track:
POST http://dnn7/DesktopModules/Services/API/Tracks HTTP/1.1

Content-Type: application/json

{
    "albumId": 1,
    "name": "For Those About To Rock (We Salute You)",
    "mediaTypeId": 1,
    "genreId": 1,
    "composer": "Angus Young, Malcolm Young, Brian Johnson",
    "milliseconds": 343719,
    "bytes": 11170334,
    "unitPrice": 0.99
}
UPDATE a Track:
PUT http://dnn7/DesktopModules/Services/API/Tracks/1 HTTP/1.1

Content-Type: application/json

{
    "albumId": 1,
    "name": "For Those About To Rock (We Salute You)",
    "mediaTypeId": 1,
    "genreId": 1,
    "composer": "Angus Young, Malcolm Young, Brian Johnson",
    "milliseconds": 343719,
    "bytes": 11170334,
    "unitPrice": 0.99
}
DELETE a Track where ID = 1:
DELETE http://dnn7/DesktopModules/Services/API/Tracks/1 HTTP/1.1
DELETE Tracks – body contains a json array of Track ID’s to delete:
DELETE http://dnn7/DesktopModules/Services/API/Tracks/Delete HTTP/1.1

Content-Type: application/json

{
    "ids": [
        6,
        7,
        8
    ]
}
RouteMapper.cs
using System;
using System.Web.Http;
using DotNetNuke.Web.Api;

namespace WebServices
{
    public class RouteMapper : IServiceRouteMapper
    {
        public void RegisterRoutes(IMapRoute mapRouteManager)
        {
            mapRouteManager.MapHttpRoute(
                moduleFolderName: "Services",
                routeName: "Default",
                url: "{controller}/{id}",
                defaults: new { id = RouteParameter.Optional },
                namespaces: new[] { "WebServices" }
            );
        }
    }
}

Json.NET

For the Delete method in the TracksController, we will use Json.NET to parse the track id’s from the json payload. To install Json.NET into the solution, I prefer NuGet. From the menu In Visual Studio, select Tools > Library Package Manager > Package Manager Console. Then run the following command:

PM> Install-Package Newtonsoft.Json
TracksController.cs
using System;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using DotNetNuke.Web.Api;
using Newtonsoft.Json.Linq;

namespace WebServices
{
    [AllowAnonymous]
    public class TracksController : DnnApiController
    {
        public HttpResponseMessage Get()
        {
            //put your code here for call to handle the get
            //for example
            //tracks = TracksService.Get();

            return Request.CreateResponse(HttpStatusCode.OK, tracks);
        }

        public HttpResponseMessage Get(int id)
        {
            //put your code here for call to handle the get
            //for example
            //track = TracksService.Get(id);

            return Request.CreateResponse(HttpStatusCode.OK, track);
        }

        [DnnAuthorize]
        public HttpResponseMessage Post(Track track)
        {
            //put your code here for call to handle the create
            //for example
            //TracksService.Create(track);

            return Request.CreateResponse(HttpStatusCode.OK);
        }

        [DnnAuthorize]
        public HttpResponseMessage Put(Track track)
        {
            //put your code here for call to handle the update
            //for example
            //TracksService.Update(track);

            return Request.CreateResponse(HttpStatusCode.OK);
        }

        [DnnAuthorize]
        public HttpResponseMessage Delete(int id)
        {
            //put your code here for call to handle the delete
            //for example
            //TracksService.Delete(id);

            return Request.CreateResponse(HttpStatusCode.OK);
        }

        [DnnAuthorize]
        public HttpResponseMessage Delete(JObject jObject)
        {
            var ids = new JArray();
            if (jObject["ids"].Type == JTokenType.String)
            {
                ids.Add(jObject["ids"]);
            }
            else
            {
                ids = (JArray)jObject["ids"];
            }
            long[] assetIds = ids.Select(jv => Convert.ToInt64((string)jv)).ToArray();
            
            //put your code here for call to handle the delete
            //for example
            //TracksService.Delete(assetIds);

            return Request.CreateResponse(HttpStatusCode.OK);
        }

    }

}

Resources

Chinook Web API Project

For this project, you will need to have access to the Chinook SQL Server Database.

1. Create a New Project

New ASP.NET MVC 4 Web Application - ChinookWebApi
New ASP.NET MVC 4 Web Application – ChinookWebApi

2. Select the Web API Project Template

New ASP.NET MVC 4 Project - Web API Project Template
New ASP.NET MVC 4 Project – Web API Project Template

Data Models

In Solution Explorer, right-click the Models folder and select Add > New Item (Ctrl+Shift+A) > Select Class and save it as Tracks.cs. Then add the data type objects to the Tracks class.

Add New Item - Tracks.cs
Add New Item – Tracks.cs

namespace ChinookWebApi.Models
{
    public class Tracks
    {
        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 Chinook class for database access. In the Solution Explorer, right click on the ChinookWebApi project and add a new class named Chinook.cs. To see the respective stored procedure SQL code, refer to Chinook SQL Server Database.

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

namespace ChinookWebApi
{
    using Models;

    public class Chinook
    {
        public static void DeleteTrack(int trackId)
        {
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("DeleteTrack", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Id", trackId);
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        
        public static List Tracks(int trackId)
        {
            var trackList = new List();
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("GetTracks", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Id", trackId);
            var dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                trackList.Add(new Tracks
                {
                    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"]),
                });
            }
            dr.Close();
            conn.Close();

            return trackList;
        }

        public static List TracksByAlbum(int albumId)
        {
            var trackList = new List();
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("GetTracks", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@Id", albumId);
            var dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                trackList.Add(new Tracks
                {
                    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"]),
                });
            }
            dr.Close();
            conn.Close();

            return trackList;
        }

        public static Tracks UpsertTrack(Tracks tracks)
        {
            var conn = new SqlConnection(Config.ChinookConnection);
            conn.Open();
            var cmd = new SqlCommand("UpsertTrack", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            // Return value as parameter
            SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnValue);

            cmd.Parameters.AddWithValue("@AlbumId", tracks.AlbumId);
            cmd.Parameters.AddWithValue("@Bytes", tracks.Bytes);
            cmd.Parameters.AddWithValue("@Composer", tracks.Composer);
            cmd.Parameters.AddWithValue("@GenreId", tracks.GenreId);
            cmd.Parameters.AddWithValue("@MediaTypeId", tracks.MediaTypeId);
            cmd.Parameters.AddWithValue("@Milliseconds", tracks.Milliseconds);
            cmd.Parameters.AddWithValue("@Name", tracks.Name);
            cmd.Parameters.AddWithValue("@TrackId", tracks.TrackId);
            cmd.Parameters.AddWithValue("@UnitPrice", tracks.UnitPrice);
            cmd.ExecuteNonQuery();

            int id = Convert.ToInt32(returnValue.Value);
            tracks.TrackId = id;

            conn.Close();

            return tracks;
        }

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

Register Routes

In order to follow Web API design best practices, we need to edit the routes so action names are not required. Open ~\App_Start\RouteConfig.cs and add a route mapping above the existing Default mapping so your RegisterRoutes method looks like this.

RouteConfig.cs
public static void RegisterRoutes(RouteCollection routes)
{
    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(
        name: "Custom1",
        url: "{controller}/{id}",
        defaults: new { id = UrlParameter.Optional }
    );

    routes.MapRoute(
        name: "Default",
        url: "{controller}/{action}/{id}",
        defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
    );
}

Tracks Controller

In Solution Explorer, right-click on the Controllers folder select Add New. Under Scaffolding options Select the API controller with empty read/write actions Template. Name it TracksController.

Add Controller - TracksController API controller with empty read/write actions
Add Controller – TracksController API controller with empty read/write actions

TracksController.cs
using System;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace ChinookWebApi.Controllers
{
    using ChinookWebApi.Models;

    public class TracksController : ApiController
    {
        // GET api/tracks
        public HttpResponseMessage Get()
        {
            var value = Chinook.Tracks(0);
            return Request.CreateResponse(HttpStatusCode.OK, value);
        }

        public HttpResponseMessage Get(int id)
        {
            var value = Chinook.Tracks(id);
            return Request.CreateResponse(HttpStatusCode.OK, value);
        }

        public HttpResponseMessage Get(string albumId)
        {
            int id = Convert.ToInt32(albumId);
            var value = Chinook.TracksByAlbum(id);
            return Request.CreateResponse(HttpStatusCode.OK, value);
        }

        // POST api/tracks
        public HttpResponseMessage Post([FromBody]Tracks tracks)
        {
            var value = Chinook.UpsertTrack(tracks);
            return Request.CreateResponse(HttpStatusCode.OK, tracks);
        }

        // PUT api/tracks/5
        public HttpResponseMessage Put(int id, [FromBody]Tracks tracks)
        {
            tracks.TrackId = id;
            var value = Chinook.UpsertTrack(tracks);
            return Request.CreateResponse(HttpStatusCode.OK, tracks);
        }

        // DELETE api/tracks
        public HttpResponseMessage Delete([FromBody]int[] value)
        {
            foreach (int id in value)
            {
                Chinook.DeleteTrack(id);
            }
            return Request.CreateResponse(HttpStatusCode.OK);
        }

        // DELETE api/tracks/5
        public HttpResponseMessage Delete(int id)
        {
            Chinook.DeleteTrack(id);
            return Request.CreateResponse(HttpStatusCode.OK);
        }
    }
}

Now would be a good time to select F5 and debug the app. Load the url to request a track and see what the method we added returns, for example http://localhost:65374/api/tracks/1. If you want to return json, you could use the Advanced Rest Client Chrome App with the Content-type header set to application/json.

Advanced Rest Client - PUT Tracks Json Test
Advanced Rest Client – PUT Tracks Json Test
PUT Json Payload Example

Copy this example json payload into the Advanced Rest Client and set the request type to PUT to update a Track record. The key is making sure the HTTP request type is set for the type of CRUD operation you wish to perform.
C – POST create/insert
R – GET  read
U – PUT  update
D – DELETE

{
    "TrackId": 2918,
    "Name": "\"?\"",
    "AlbumId": 231,
    "MediaTypeId": 3,
    "GenreId": 19,
    "Composer": "",
    "Milliseconds": 2782333,
    "Bytes": 528227089,
    "UnitPrice": 1.99
}

You could also use a GET request: ~/api/tracks/2918 to retrieve Json to use.

Copy this example json payload into the Advanced Rest Client and set the request type to POST to create a new Track record. The new tracks object will be returned including the new TrackId.

POST Json Payload Example
{
    "Name": "\"_New Track\"",
    "AlbumId": 231,
    "MediaTypeId": 3,
    "GenreId": 19,
    "Composer": "",
    "Milliseconds": 3003222,
    "Bytes": 540772000,
    "UnitPrice": 0.99
}

With the HTTP request type set to DELETE, you can remove a single Track record with a request like:
~/api/tracks/2918
or to Delete multiple records, a request like this will work:
~/api/tracks
with a payload of TrackId’s to remove:

[ 2918, 2919, 2920 ]

Cross-Origin Resource Sharing (CORS)

To enable CORS in the Web API and allow JavaScript XMLHttpRequests from a browser in another domain, Carlos Figueira’s MSDN blog shows us how to create a global message handler for all controllers and actions in the application. Right-click the ChinookWebApi project and add a new folder named Handlers with a new CorsHandler class in it:

CorsHandler.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net.Http;
using System.Threading.Tasks;
using System.Threading;
using System.Net;

namespace ChinookWebApi.Handlers
{
    public class CorsHandler : DelegatingHandler
    {
        const string Origin = "Origin";
        const string AccessControlRequestMethod = "Access-Control-Request-Method";
        const string AccessControlRequestHeaders = "Access-Control-Request-Headers";
        const string AccessControlAllowOrigin = "Access-Control-Allow-Origin";
        const string AccessControlAllowMethods = "Access-Control-Allow-Methods";
        const string AccessControlAllowHeaders = "Access-Control-Allow-Headers";

        protected override Task SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
        {
            bool isCorsRequest = request.Headers.Contains(Origin);
            bool isPreflightRequest = request.Method == HttpMethod.Options;
            if (isCorsRequest)
            {
                if (isPreflightRequest)
                {
                    HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
                    response.Headers.Add(AccessControlAllowOrigin, request.Headers.GetValues(Origin).First());

                    string accessControlRequestMethod = request.Headers.GetValues(AccessControlRequestMethod).FirstOrDefault();
                    if (accessControlRequestMethod != null)
                    {
                        response.Headers.Add(AccessControlAllowMethods, accessControlRequestMethod);
                    }

                    string requestedHeaders = string.Join(", ", request.Headers.GetValues(AccessControlRequestHeaders));
                    if (!string.IsNullOrEmpty(requestedHeaders))
                    {
                        response.Headers.Add(AccessControlAllowHeaders, requestedHeaders);
                    }

                    TaskCompletionSource tcs = new TaskCompletionSource();
                    tcs.SetResult(response);
                    return tcs.Task;
                }
                else
                {
                    return base.SendAsync(request, cancellationToken).ContinueWith(t =>
                    {
                        HttpResponseMessage resp = t.Result;
                        resp.Headers.Add(AccessControlAllowOrigin, request.Headers.GetValues(Origin).First());
                        return resp;
                    });
                }
            }
            else
            {
                return base.SendAsync(request, cancellationToken);
            }
        }
    }
}

Register the CorsHandler in the Application_Start() method.

Global.asax.cs
using System;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

namespace ChinookWebApi
{
    using Handlers;

    public class WebApiApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();

            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            GlobalConfiguration.Configuration.MessageHandlers.Add(new CorsHandler()); 
        }
    }
}

To be continued … Validation and Error Handling

This project is available for browsing and download at GitHub:
https://github.com/jimfrenette/ChinookWebApi

Resources

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);
        }

    }
}

Chinook SQL Server Database

The Chinook Database is a sample database available for SQL Server, Oracle, MySQL, PostgreSQL, SQL Server Compact, SQLite and more. It is ideal for demos, application prototypes and perhaps even a digital media store application. It includes tables for artists, albums, media tracks, invoices and customers.

Chinook Database Download and Installation

Chinook Database
Chinook Database – Documentation

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

Connection String

<connectionStrings>
 <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True" />
 <!-- using account without SQL Express
 <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290;Initial Catalog=Chinook;Persist Security Info=True;User ID=sa;Password=***********" />
 -->
</connectionStrings>

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

SQL Stored Procedures

SQL Server 2012 Express Management Studio

Open up SQL Server Management Studio and connect to the server that is defined in the 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.

dbo.GetArtists

Create stored procedure GetAlbums for selecting Album(s).

dbo.GetAlbums

Create stored procedure GetTracks for selecting Track(s).

dbo.GetTracks

SQL Function

New Scalar-value function

An efficient way to return an Album Count column in each row is by using a function. This will come in handy when paginating Album records in the UI. Create this user defined function to add a computed AlbumCount column to the Artist Table.

dbo.CountAlbums

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).

Create stored procedure UpsertTrack for creating and updating track records.

dbo.UpsertTrack

Create stored procedure DeleteTrack for removing track records.

dbo.DeleteTrack