In the July/August 2013 issue, I looked at creating a Single Page Application (SPA) from the client side. As promised, this article shows how to commit changes to a database using ASP.NET Web API as the service provider.

ASP.NET Web API Introduction

Microsoft ASP.NET Web API, initially known as WCF Web API, was officially released with ASP.NET MVC 4. The project originated in the WCF team's effort to provide support for RESTful services that were created with .NET code. The goal of Web API is to provide RESTful services that can be called from a variety of clients. Although released in conjunction with MVC 4, Web API is not limited to Web pages. Web API can be hosted in an MVC project, or in any other .NET project, even a console application.

Web API is intended to be a light-weight HTTP message handler that allows the developer to easily publish their service end points. By default, it supports JSON and XML, but you can write your own message-handling system. It's essentially a REST-specific MVC controller, which means that developers already using ASP.NET MVC will feel comfortable developing Web API controllers. Web API also supports many of the MVC Controller features, such as ASP.NET Routing, Model Binding, Filters, and content negotiation.

Web API can work with a variety of data providers. For this example, I chose to use Entity Framework (EF) and Code First.

Customer Class

To get started, in the MVC project, add a new class called Customer in the Models folder. For each field used on the Web page, add a property to the new POCO (Plain Old CLR Object) class. Save the file and build the project so that the class is available to the controller wizard.

public class Customer
{
    public int Id { get; set; }
    public string Company { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime StartDate { get; set; }
}

Adding the Controller

Right-click on the Controller folder and select the Add > Controller menu items. Enter CustomerDataController as the Controller Name. Select API controller with read/write actions, using Entity Framework as the template. Drop down the selection for Model class and select the Customer class that you just created. For the final option, Data context class, select the option. When prompted, enter a name that makes sense for your project; I called mine SpaContext. After making all the selections, the wizard should look like Figure 1. Once you're satisfied with your selections, click the Add button.

Figure 1: Adding the new Web API controller.
Figure 1: Adding the new Web API controller.

This wizard creates three items: a controller, a database context, and a connection string. The new CustomerDataController is located in the Controllers folder. I will examine the contents of this file in a moment.

The database context class is located in the Models folder. It's responsible for connecting the POCO class with the database.

public class SpaContext : DbContext
{
    public SpaContext(): base("name=SpaContext")
    {
    }
    public DbSet<Customer> Customers { get; set; }
}

The connection string is added to the web.config file. The connection string is pretty standard fare for EF, and here defines a local SQL database connection. The wizard automatically generates a name for the database using the given context name appended with a current date representation. You can modify this name at this time if you want. If you do decide to change the database name, be sure to do so in both places.

<connectionStrings>
    <add name="SpaContext" connectionString="Data Source=(localdb)\v11.0;
     Initial Catalog=SpaContext-20130502193525;
     Integrated Security=True;
     MultipleActiveResultSets=True;
     AttachDbFilename=|DataDirectory|SpaContext-20130502193525"
     providerName="System.Data.SqlClient" />
</connectionStrings>

At this point, all the necessary pieces are in place for EF to work. The first time the database context is triggered, EF automatically creates a database to persist the data. The database location and type, SQL Server, SQL Express, or Local Db is determined by the connection string. EF also allows you to seed the database with default values. And, because it would be nice to have some records for the application to consume, add a data initializer to handle that task.

The first time the database context is triggered, EF automatically creates a database to persist the data.

Create a new folder called Classes and add a new class called CodeFirstDataInitializer.cs. This new class needs to inherit from the class DropCreateDatabaseIfModelChanges. The inheritance causes the database to be recreated each time you modify the Customer POCO class. You could optionally inherit from the DropCreateDatabaseAlways class if you wanted to start each session with a fresh set of data. Note that, as the name implies, the initializer drops the entire database. This setting should never be left in place when the application is deployed.

public class CodeFirstDataInitializer : 
                       DropCreateDatabaseIfModelChanges<SpaContext>
{
    protected override void Seed(SpaContext context)
    {
        context.Customers.Add(new Customer
        {
            Company = "EPS Software",
            FirstName = "Jeff",
            LastName = "Etter",
            StartDate = DateTime.Parse("2/27/2012")
        });
    }
}

Override the Seed method to provide default records. The Seed method is run when the database is created and allows you to add records to the tables. In this case, there is only a single table so the code is basic; simply create a new Customer object and add it to the Customers table that is exposed by the context. (Note that EF pluralizes the table names for you.) If the database were more complex, you could add records to any tables created by the POCO classes.

The final step to seed the database is to call the initializer method. Open the Global.asax file and add a call to the SetInitializer method and pass a new instance of the CodeFirstDataInitializer method.

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    ...
    System.Data.Entity.Database.SetInitializer(new CodeFirstDataInitializer());
}

At this point, the Web API is ready to run. Start the project and navigate to the URL api/CustomerData. The default behavior of the browser determines what happens when navigating to this URL. IE prompts you to open or save a file. Doing so results in a JSON-formatted string representing the serialized values of the record(s). Chrome displays an XML representation of the data as shown in Figure 2. This behavior occurs because of the request HTTP header's “Accept” default values, which sends a value indicating the default message format.

[{"Id":1,
  "Company":"EPS Software",
  "FirstName":"Jeff",
  "LastName":"Etter",
  "StartDate":"2012-02-27T00:00:00"
}]
Figure 2: JSON data's default format in Chrome
Figure 2: JSON data's default format in Chrome

Behind the scenes, this call to the Web API controller initiated a connection to the EF context. The first time a call to the EF object occurs, the data initialization code is triggered and a new database is created. Finally, the database initialization generates a new record, as dictated in the CodeFirstDataInitializer class. You can find the new database by clicking on the App_Data folder and selecting the Show All Files option on the Solution Explorer. You'll see a file with an extension of .mdf, indicating a local SQL database. The name of this file is determined by the connection string located in the web.config file.

Examining the Web API Controller

Open the CustomerDataController.cs file located in the Controllers folder. Because you selected the option to generate the controller with read/write actions, full create, read, update, and delete (CRUD) functionality is created for you.

private SpaContext db = new SpaContext();
// GET api/CustomerData
public IEnumerable<Customer> GetCustomers()...

// GET api/CustomerData/5
public Customer GetCustomer(int id)...

// PUT api/CustomerData/5
public HttpResponseMessage PutCustomer(int id, Customer customer)...

// POST api/CustomerData
public HttpResponseMessage PostCustomer(Customer customer)...

// DELETE api/CustomerData/5
public HttpResponseMessage DeleteCustomer(int id)...
protected override void Dispose(bool disposing)...

ASP.NET Web API implements a REST-like pattern, so it's no surprise to see that you have the four major verbs commonly used in a REST implementation: GET, PUT, POST, and DELETE. What's not apparent is that the prefix of the method name is the only part that's significant. The suffix (after the GET, PUT, POST, DELETE) isn't used to determine the method. In the REST pattern, the verb combined with the parameters that are passed to the endpoint determine the method used. In this case, the GetCustomers method is called any time the Web API is invoked with no parameters (/api/CustomerData). This behavior is exhibited because GetCusomters is the only GET method defined with no parameters. If you rename the GetCustomers method to GetFoo, or anything else, it still works. The suffix is dictated by convention and does not determine functionality.

In the REST pattern, the verb combined with the parameters that are passed to the endpoint determine which method is used.

The GetCustomers function doesn't do anything special; it simply returns the Customer table as an enumerable collection of the Customer type.

// GET api/CustomerData
public IEnumerable<Customer> GetCustomers()
{
    return db.Customers.AsEnumerable();
}

When you look at the GetCustomer method, you begin to see some things that are not so standard. It starts off by performing a typical database lookup based on the supplied ID, but if the record isn't found, you see a strange exception raised: the HttpResponseException is sent back to the client in the response header. Most C# developers would think it strange having production code throwing exceptions, as in the case of RESTful services, it's the appropriate convention. The status code is set to NotFound. The action taken is up to the client, and the jQuery AJAX calls that you will write later provide some error handling that make use of this status.

// GET api/CustomerData/5
public Customer GetCustomer(int id)
{
    Customer customer = db.Customers.Find(id);
    if (customer == null)
    {
        throw new HttpResponseException(
            Request.CreateResponse(
                HttpStatusCode.NotFound));
    }
    return customer;
}

The next method generated for you is PutCustomer. This method is called when an existing record is modified and the client generates a PUT request. This PUT request must pass the ID as a parameter on the URL, and the JSON representation of the Customer object as the payload. The first action performed is to determine if the state of the current model, in this case a Customer object, is valid. Web API uses the same Data Annotations used by a MVC Controller to determine the ModelState, so any validation attributes applied to the POCO class are utilized here. If the model state is determined to be invalid, an HTTP Response Message indicating a bad request is returned.

The next step is to verify that the ID passed as a parameter is the same as the ID contained on the Customer object. Although not totally necessary, this step ensures that URL and the data passed match one another. Once these two validation steps are performed, the database is updated with the new values.

Committing the data is wrapped in a try/catch block, with the exception handler sending back the exception information and an HTTP Response Message indication that the record was not found. Finally if everything succeeds, an HTTP Response Message of OK is returned, letting the client know the record update was successful.

// PUT api/CustomerData/5
public HttpResponseMessage PutCustomer(int id, Customer customer)
{
    if (!ModelState.IsValid)
    {
        return Request.CreateErrorResponse(
            HttpStatusCode.BadRequest, ModelState);
    }
    if (id != customer.Id)
    {
        return Request.CreateResponse(HttpStatusCode.BadRequest);
    }
    db.Entry(customer).State = EntityState.Modified;
    try
    {
        db.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        return Request.CreateErrorResponse(
            HttpStatusCode.NotFound, ex);
    }
    return Request.CreateResponse(HttpStatusCode.OK);
}

When adding a new record, the client generates a POST message with the Customer object as the payload. This signature utilizes the PostCustomer method. The model is validated, and if not successful, a bad request HTTP Response Message is returned. If the model is valid, the Customer object is added to the database and a Created HTTP Response Message is generated. The response message also has a copy of the newly created customer object attached to it. This object can be used by the client as desired, but a typical use is to determine the unique identifier of the newly created object. In order to comply with the HTTP/1.1 protocol the server additionally includes a URI of the resource created.

// POST api/CustomerData
public HttpResponseMessage PostCustomer(Customer customer)
{
    if (ModelState.IsValid)
    {
        db.Customers.Add(customer);
        db.SaveChanges();
        HttpResponseMessage response = Request.CreateResponse(
            HttpStatusCode.Created, customer);
        response.Headers.Location = new Uri(Url.Link("DefaultApi",
            new { id = customer.Id }));
        return response;
    }
    else
    {
        return Request.CreateErrorResponse(
            HttpStatusCode.BadRequest, ModelState);
    }
}

When the client generates a DELETE message with a single parameter passed, the DeleteCustomer method is triggered. This method attempts to find a record that matches the identifier passed to the method. If the record is not found, an HTTP message of Not Found is returned. If a matching record is found, it's removed from the database and committed in a Try/Catch block. If an exception occurs, a Not Found status is returned with the exception details. If the removal is successfully committed, a standard OK status is returned.

// DELETE api/CustomerData/5
public HttpResponseMessage DeleteCustomer(int id)
{
    Customer customer = db.Customers.Find(id);
    if (customer == null)
    {
        return Request.CreateResponse(HttpStatusCode.NotFound);
    }
    db.Customers.Remove(customer);
    try
    {
        db.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
    }
    return Request.CreateResponse(HttpStatusCode.OK, customer);
}

The entire code for the Web API controller is shown in Listing 1.

Listing 1: Complete Web API Controller

public class CustomerDataController : ApiController
{
    private SpaContext db = new SpaContext();

    // GET api/CustomerData
    public IEnumerable<Customer> GetCustomers()
    {
        return db.Customers.AsEnumerable();
    }

    // GET api/CustomerData/5
    public Customer GetCustomer(int id)
    {
        Customer customer = db.Customers.Find(id);
        if (customer == null)
        {
            throw new HttpResponseException(
                Request.CreateResponse(
                    HttpStatusCode.NotFound));
        }

        return customer;
    }

    // PUT api/CustomerData/5
    public HttpResponseMessage PutCustomer(int id, Customer customer)
    {
        if (!ModelState.IsValid)
        {
            return Request.CreateErrorResponse(
                HttpStatusCode.BadRequest, ModelState);
        }

        if (id != customer.Id)
        {
            return Request.CreateResponse(HttpStatusCode.BadRequest);
        }

        db.Entry(customer).State = EntityState.Modified;

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            return Request.CreateErrorResponse(
                HttpStatusCode.NotFound, ex);
        }

        return Request.CreateResponse(HttpStatusCode.OK);
    }

    // POST api/CustomerData
    public HttpResponseMessage PostCustomer(Customer customer)
    {
        if (ModelState.IsValid)
        {
            db.Customers.Add(customer);
            db.SaveChanges();

            HttpResponseMessage response = Request.CreateResponse(
                HttpStatusCode.Created, customer);
            response.Headers.Location = new Uri(
                Url.Link("DefaultApi", 
                new { id = customer.Id }));
            return response;
        }
        else
        {
            return Request.CreateErrorResponse(
                HttpStatusCode.BadRequest, ModelState);
        }
    }

    // DELETE api/CustomerData/5
    public HttpResponseMessage DeleteCustomer(int id)
    {
        Customer customer = db.Customers.Find(id);
        if (customer == null)
        {
            return Request.CreateResponse(HttpStatusCode.NotFound);
        }

        db.Customers.Remove(customer);

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            return Request.CreateErrorResponse(
                HttpStatusCode.NotFound, ex);
        }

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

    protected override void Dispose(bool disposing)
    {
        db.Dispose();
        base.Dispose(disposing);
    }
}

Consuming the New Service

To consume the Web API services, some adjustments to the SPA are necessary. The overall process is simple: replace the static JSON customer list with a call to the Web API and update the methods on the view model to persist changes.

Getting the Data

Locate the variable called customerJSON that had the static JSON formatted customer list assigned to it. This variable is no longer necessary and can be removed.

Update the customers property of the view model from the existing customerJSON variable to a new KendoUI DataSource object.

var customerViewModel = kendo.observable({
    customers: customerJSON <-old value
    ...

The new assignment for the customers property is to a Kendo UI DataSource object. Add a new variable called jsonUrl that represents the URL of the Web API. Although this isn't required, it's very convenient to have all AJAX calls reference the same URL so it can be globally modified during testing and deployment.

var jsonUrl = "../api/CustomerData/";
var customerViewModel = kendo.observable({
    customers: new kendo.data.DataSource({
        transport: {
            read: {
                url: jsonUrl
            }
        }
    }),
    ...

The Kendo UI DataSource object has several options that it exposes. In this case, you're interested in the transport: read option. This property holds the URL that the DataSource object negotiates with to obtain data. In addition to read, you can also specify an update and destroy property. These options are convenient if you want to perform batch saving, where all changes are committed together. Although they're worth a mention, you won't use them in this application.

The original assignment to customers was a JavaScript array of JSON objects. The new assignment is to a Kendo UI DataSource object, which acts like a JavaScript function. These two objects are fundamentally different, so it's no great surprise that you need to adjust the manner by which you access the data.

Previously when you selected a customer for editing, you obtained a reference to the customer in the list by using the syntax of this.customers[index], which is standard syntax for accessing JavaScript arrays. Change the editCustomer function to access the data source object using the syntax this.customers.view()[index]. Note the addition of .view(), which is how you gain access to the data source object.

editCustomer: function (e) {
    this.set("addMode", false);
    this.set("editMode", true);
    var customerList = this.get("customers");
    var index = customerList.indexOf(e.dataItem);

    //this.set("selectedItem", this.customers[index]);
    this.set("selectedItem", this.customers.view()[index]);
}

The previous addCustomer was very simple; it added the new customer to the JavaScript array using the push() function. Update this function by changing the local collection access and adding a new AJAX call to the Web API to commit the changes.

addCustomer: function () {
    var customer = this.get("selectedItem");
    this.customers.push(customer);
}

Listing 2 shows the code for the updated addCustomer function.

Listing 2: Updated addCustomer function

addCustomer: function () {
    var customer = this.get("selectedItem");
    var customerList = this.customers;
    $.ajax({
        contentType: "application/json; charset=utf-8",
        type: "POST",
        url: jsonUrl,
        data: JSON.stringify(customer),
        success: function (data) {
            customerList.add(data);
        },
        error: function (jqXHR, textStatus, err) {
            alert('Error: ' + err);
        }
    });
}

Start by removing the this.customers.push(customer) line. Add a new line that creates a variable to hold a reference to the customers data source object, which is used if you're successful in calling the Web API. Add a jQuery AJAX call, ensuring that the type of call uses the POST verb and the url is set to the jsonURL variable you declared at the top of the script. Because the PostCustomer method on the Web API controller accepts a single parameter of a type of Customer, you need to set the data property of the AJAX call to a JSON representation of the customer variable. The easiest way to accomplish this is to use the JSON.stringify() helper method to serialize the customer variable.

The AJAX function now knows the service endpoint and the data to send, but isn't currently doing anything upon completion. There are several callback functions available; for now the ones you're interested in are success and error.

success: function (data) {
    customerList.add(data);
}

Upon successful completion of the AJAX call, the Web API controller sends back a JSON serialized object. This object is passed into the success function as a parameter, which can be used inside the function. Inside the function, add the line customerList.add(data), which will take the customer added by the Web API controller and add it to the local data source object. It's important to note that you're using the information returned by the Web API controller, not the local customer object that the form was bound to. If you were to use the local object, it wouldn't have the correct ID (the ID is generated on the server side) so future edits will fail to find the record to update.

If you were to use the local object, it wouldn't have the correct ID (the ID is generated on the server side) so future edits will fail to find the record to update.

error: function (jqXHR, textStatus, err) {
    alert('Error: ' + err);
}

If, for some reason, the AJAX call to the Web API controller fails, the error function alerts the user with the details. (See the sibebar The jQuery AJAX Function for further discussion on the parameters received by the error function.)

removeCustomer: function () {
    var customer = this.get("selectedItem");
    var customerList = this.get("customers");
    var index = customerList.indexOf(customer);
    this.customers.splice(index,1);
}

The previous removeCustomer function acted on the local JavaScript array, ultimately using the splice function to remove a specific item in the array. This needs to be updated to call the Web API method and access the local data source object properly. It will end up looking very similar to the addCustomer function, with a few distinct differences.

Remove the last three lines of the existing function. Add a new line that creates a variable to hold a reference to the customers data source object, which will be used if you are successful in calling the Web API. Add a jQuery AJAX call, but this time, the type is DELETE. The URL also uses the jsonUrl variable, but concatenates the identifier of the record to delete by adding + customer.Id. The delete method of the Web API controller only requires the ID, which is passed in the URL, so there is no need to use the data property as you did on the addCustomer function.

When removing the customer, the success function doesn't require information back from the Web API method; it only needs to know that it was successful. If it is, you can use the local reference to the customer object to identify the local record to remove. Add the line customerList.remove(customer) to remove the currently selected customer from the local list.

The error function of the removeCustomer is exactly the same as the addCustomer function. Your code should now look like Listing 3.

Listing 3: Updated removeCustomer function

removeCustomer: function () {
    var customer = this.get("selectedItem");
    var customerList = this.customers;
    $.ajax({
        contentType: "application/json; charset=utf-8",
        type: "DELETE",
        url: jsonUrl + customer.Id,
        success: function () {
            customerList.remove(customer);
        },
        error: function (jqXHR, textStatus, err) {
            alert('Error: ' + err);
        }
    });
}

Previously, the Done button had no function associated with it. Because the record being edited was bound to the observable collection, associating a function was unnecessary. However, you now need to commit the edit to the server. Add a new saveCustomer function to call the Web API. Listing 4 shows the new function, which looks almost identical to the removeCustomer function. In fact, the only difference is that you don't need to get a reference to the local list). Also the type is PUT, and in this method the success function has nothing to do.

Listing 4: Our new saveCustomer function

saveCustomer: function () {
    var customer = this.get("selectedItem");
    $.ajax({
        contentType: "application/json; charset=utf-8",
        type: "PUT",
        url: jsonUrl + customer.Id,
        data: JSON.stringify(customer),
        success: function () {
            //No action required on save...
        },
        error: function (jqXHR, textStatus, err) {
            alert('Error: ' + err);
        }
    });
}

Hook up the saveCustomer function by adding a click binding to the Done button.

<a data-role="backbutton" data-icon="reply"
    data-align="left"
    data-bind="visible: editMode">
        Done
</a>

Previously, the data-bind for the Done button only required a binding for visibility. Add an additional binding click: saveCustomer to wire up the new function.

<a data-role="backbutton" data-icon="reply"
    data-align="left"
    data-bind="click: saveCustomer, visible: editMode">
        Done
</a>

Running the application now, you can see no visible difference in the operation of the UI. However, now the data is persisted to the SQL local database file located in the project.

The application consumes the Web API service using a Web page and AJAX calls, but this same service endpoint could be called from any number of clients. You could, for example, call the API controller from an iOS or Android device. Web API is also an excellent choice for providing a service endpoint for Windows 8 applications. Although not as robust as a true WCF service, Web API provides a very quick and easy way to expose your data as JSON or XML.