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

2. Select the 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

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

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[/caption]

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

This project is available for browsing and download at GitHub:

Resources