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

Published by

Jim Frenette

Web Developer - views here are my own except those taken from people more clever than me.

Loading Disqus Comments ...
Loading Facebook Comments ...