Saturday, March 16, 2013

Northwind-mania: CRUD operations against Web API Category table

Web API was introduced together with ASP.NET MVC 4. It is an ideal platform for building RESTful applications. In this tutorial, we will create a simple Web API server application using ASP.NET MVC 4. We will then consume the server application from a simple C# Command Line client application that executes CRUD operations against the Northwind Category table through Web API. The acronym CRUD stand for (C)reate (R)etrieve (U)date (D)elete.

Let us start by creating a Web API server web application using the famed Northwind database Category table.

Building the Web API server application

  • Start Visual Studio 2012
  • File >> New Project >> Visual C# >> Web >> ASP.NET MVC 4 Web Application
  • Name the Application NorthwindWebApi and click on OK
  • On the “Project Template” dialog, select the “Web API” template then click on OK
  • Right-click on the Models folder and choose: Add >> New Item >> ADO.NET Entity Data Model
  • Name the data model NorthwindModel.edmx
  • On the “Entity Data Model Wizard” dialog, select “Generate from database” then click Next
  • Click on “New Connection” and subsequently connect to the Northwind database that you have access to
  • When you are back at the “Entity Data Model Wizard” dialog, change the entity connection setting to NorthwindContext as this becomes the name of our Entity Framework context. Click on Next.
  • Select only the Categories table then click on Finish
  • Build once by hitting Ctrl-Shift-B on your keyboard so that the newly created classes are visible to your Visual Studio environment

Next we need to create a Web API controller for the Categories table. This is done very easily in ASP.NET MVC 4.0.

  • Right-click on the Controllers folder and choose: Add >> Controller
  • Make selections as shown in the dialog below:


  • When you are done, click on Add.
  • Have a look at the code in the CategoryController.cs class. You will notice that Visual Studio 2012 has conveniently produced for us all the action methods that cater for Post (Create); Get (Retrieve);  Put (Update); and Delete.

Now let us test to confirm that our Web API controller for categories actually works.

  • Hit Ctrl-F5 in Visual Studio to start your web application and view the home page in a browser.
  • Click on the API link on the top right-hand side. This takes you to the “ASP.NET Web API Help” page that shows you some documentation on how Web API works
  • Scroll down to the Category section. Click on “GET api/Category”. This shows you sample JSON and XML data that gets returned depending on the request header.
  • Click “Back” on the browser to return to the “ASP.NET Web API Help” page
  • In the address line, replace “Help” with api/Category. Your address URL should look similar to: http://localhost:14350/api/Category
  • If you are using IE, you will notice that a *.json file is being downloaded. If you open this .json file in Notepad you will notice that it contains all our categories serialized as JSON objects. We are now ready to build our C# command-line client.

Building the C# command-line Web-API client

  • In Visual Studio 2012 right-click on the solution and choose: Add >> New Project >> Visual C# >> Console Application
  • Name your console application WebApiClient
  • Let us first create a Category class on the client command-line application. This will be used to construct C# objects from de-serialized Category JSON objects. Create the following class:
public class Category {
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }

public override string ToString() {
string str = string.Format("ID={0}; Name={1}; Description={2}", CategoryId, CategoryName, Description);
return str;
We will need to use a NuGet package named “Microsoft ASP.NET Web API Client Libraries” for accessing Web API through HTTP.

  • Right-click on the command-line project “WebApiClient” and choose: Manage NuGet Packages. Enter “Microsoft ASP.NET Web API Client Libraries” in the search toolbox on the top right-hand side of the dialog. 

  • When the appropriate NuGet package appears, click on the Install button beside it.

  • After the package installs, click Close to close the dialog

  • Add a reference to the “System.Net.Http” assembly

Client-side helper class

The CategoryWebApiHelper class will be used to simulate CRUD operations. In the command-line client application, create a class named CategoryWebApiHelper with the following instance variables and constructor:

public class CategoryWebApiHelper {
private string apiUrl = "";
HttpClient client = new HttpClient();
public CategoryWebApiHelper(string baseUrl, string apiUrl) {
this.apiUrl = apiUrl;
this.client.BaseAddress = new Uri(baseUrl);
// Add an Accept header for JSON format.
this.client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

  • You will need to resolve the HttpClient and MediaMediaTypeWithQualityHeaderValue classes

The GET helper methods for data retrieval

We will need two get methods. One to retrieve all categories and one to retrieve a specific category by ID. First, add the following Get() method to the CategoryWebApiHelper class that essentially reads in all the categories from the database:
public List<Category> Get() {
HttpResponseMessage response = client.GetAsync(this.apiUrl).Result;
if (response.IsSuccessStatusCode) {
var categories = response.Content.ReadAsAsync<IEnumerable<Category>>().Result;
return categories.ToList();
return null;

Notice that async calls are being made to the server. The most interesting thing about the above code is that the ReadAsAsync method reads the response body and de-serializes it to a specified CLR type.

The HttpResponseMessage has a property IsSuccessStatusCode that indicates whether or not the response is OK.

Next, we will add a method that retrieves data for a Category object specified by the CategoryId.

public Category Get(int customerId) {
HttpResponseMessage response = client.GetAsync(this.apiUrl + customerId).Result;
if (response.IsSuccessStatusCode) {
var category = response.Content.ReadAsAsync<Category>().Result;
return category;
return null;

The POST helper method for data creation

Add the following method to create a Category record in the database:

public string PostInsert(Category category) {
HttpResponseMessage response = client.PostAsJsonAsync(apiUrl, category).Result;
if (response.IsSuccessStatusCode)
return string.Format("Category {0} inserted successfully", category.CategoryName);
return string.Format("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
Unlike with the get methods where we used client.GetAsync(), we use client.PostAsJsonAsync() in order to insert data. Notice that the PostAsJsonAsync() method takes two parameters – namely a relative URL and the Category object.

The PUT helper method for data updates

The helper method for updating category data is shown below:

public string PutUpdate(Category category) {
HttpResponseMessage response = client.PutAsJsonAsync(this.apiUrl + category.CategoryId, category).Result;
if (response.IsSuccessStatusCode)
return string.Format("Category {0} updated successfully", category.CategoryId);
return string.Format("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
The PutAsJsonAsync() method is used to update data. It takes two arguments. The first argument contains information about the CategoryId that is to be updated. The second argument is a Category object.

The DELETE helper method

Finally, the helper method for deleting data needs to know the primary key as shown below:
public string Delete(int categoryId) {
HttpResponseMessage response = client.DeleteAsync(this.apiUrl + categoryId).Result;
if (response.IsSuccessStatusCode)
return string.Format("Category {0} deleted successfully", categoryId);
return String.Format("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
The DeleteAsync() methods needs the CategoryId that is to be deleted.

Testing our Helper methods

In the Main() method of your Program.cs class, construct an instance of the CategoryWebApiHelper class as follows:
CategoryWebApiHelper helper = new CategoryWebApiHelper("http://localhost:14350/", "api/category/");
Adjust the above URL to suit your particular environment. Next, let’s just read all the items in the Categories table. Add the following code to the Main() method:
// get all Category items
List<Category> categories = helper.Get();
foreach (Category t in categories)

Run the command-line application by hitting Ctrl-F5 and you should see output similar to:

Next, lets retrieve data for a specific category, say ID=5. Add this code to your main method:

// get one Category
Category category = helper.Get(5);
if (category != null)

This will result in output like the following:


Similarly, you can test our other helper methods for adding, updating, and deleting data.

Adding data

// Add a Category
var newCategory = new Category() {
CategoryName = "Teas",
Description = "Darjeeling"

Updating data

// Update Category with ID=12
Category category = helper.Get(12);
category.Description = "Peppermint";

Deleting data

// Delete Category with ID=12

It is strange how it is always easiest to delete.

I hope you found this post useful.

No comments:

Post a Comment