Create a WCF RESTFul Service for the Northwind Database

This website is no longer actively supported

Written by John DeVight on 02 Aug 2012 18:19

Download Northwind WCF Service Source Code

Summary

Create a WCF RESTFul Service that accesses the Northwind Database using the ADO.NET Entity Framework. The WCF RESTFul Service sends and receives data as JSON so that it can be used by an HTML application.

Overview

I have been asked to speak at the TechGate 2012 Conference and I decided to do a session on developing HTML Metro applications for Windows 8. Visual Studio 2012 for Windows 8 allows developers to create Metro applications using HTML or XAML. Since 2011 I have been developing ASP.NET MVC applications with a focus on creating a rich user experience with CSS and JavaScript using third party libraries build on top of jQuery. So, HTML made the most sense for me and I discovered how easy and how much fun it was to create a Metro application. But I'm getting ahead of myself. Building the Windows 8 Metro application is in the next article. Before I created the Metro application, I needed to create a service that would allow me to access a database. I chose the well known Northwind Sample Database as my backend database. I decided to make the service a WCF RESTful service. I found a great article that demonstrated how to create a WCF RESTful service at Create and Consume RESTFul Service in .NET Framework 4.0. The two problems that I had are:

1. The client was written as a C# console application. It is a great demonstration of communicating with a WCF RESTful service from a C# client, but I needed to tweak the project to get it to work with a Windows 8 HTML Metro application.
2. Since I am working with the ADO.NET Entity Framework, I had problems returning model objects defined in the ADO.NET Entity Data Model. I needed to find a different way of sending and receiving model objects.

Creating the Northwind Service Solution

Creating the WCF Service Application

To create the Northwind Service, launch Visual Studio 2010 (for this article I used Microsoft Visual Web Developer 2010 Express). Create a new "WCF Service Application" and call it NorthwindService.

Creating the NorthwindData Data Library

I like to separate the data layer from the interface layer. Add a new "Class Library" project to the solution and call it NorthwindData.

In the NorthwindData class library, delete Class1.cs.

In the NorthwindService, add a .NET reference to "System.ServiceModel.Activation" and "System.Data.Entity". The reference to "System.ServiceModel.Activation" is so that the WCF Service can behave as a RESTFul Service. The reference to "System.Data.Entity" is so that the NorthwindService can use model objects defined in the ADO.NET Entity Data Model. Also add a Projects reference to the NorthwindData class library.

Configuring the WCF Service as a RESTFul Service

In the NorthwindService, delete IService1.cs and Service1.svc from the project. Now add a new WCF Service to the NorthwindService project and call it RestService.svc. Right click on the RestService.svc select "View Markup" from the popup menu. Add a Factory attribute set to "System.ServiceModel.Activation.WebServiceHostFactory". The RestService.svc Markup should look like this:

<%@ ServiceHost Language="C#" Debug="true" Service="NorthwindService.RestService" CodeBehind="RestService.svc.cs" Factory="System.ServiceModel.Activation.WebServiceHostFactory" %>

In the NorthwindService, add a "Global Application Class" (Global.asax) to the project. In Global.asax.cs, add the "System.Web.Routing" and "System.ServiceModel.Activation" namespaces. Then add the following line to Application_Start:

RouteTable.Routes.Add(new ServiceRoute("RestService", new WebServiceHostFactory(), typeof(RestService)));

The beginning of your Global.asax.cs file should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.SessionState;
using System.Web.Routing;
using System.ServiceModel.Activation;
 
namespace NorthwindService
{
    public class Global : System.Web.HttpApplication
    {
 
        protected void Application_Start(object sender, EventArgs e)
        {
            RouteTable.Routes.Add(new ServiceRoute("RestService", new WebServiceHostFactory(), typeof(RestService)));
        }
 
        ...

In the NorthwindService, open the web.config file. Replace the service.serviceModel element with the following:

<system.serviceModel>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true">
    </serviceHostingEnvironment>
    <standardEndpoints>
        <webHttpEndpoint>
            <standardEndpoint name="" helpEnabled="true" automaticFormatSelectionEnabled="true"></standardEndpoint>
        </webHttpEndpoint>
    </standardEndpoints>
</system.serviceModel>

Setting up the ADO.NET Entity Framework

Create the ADO.NET Entity Data Model

In the NorthwindData class library, add a new "ADO.NET Entity Data Model" and call it "NorthwindEntities.edmx".

In the "Choose Model Contents" dialog, select "Generate from Database". Then click "Next".

In the "Choose Your Data Connection" dialog, select the connection to the Northwind database. Verify that the "Save entity connection settings in App.Config as:" is checked and that the value is "NorthwindEntities". Then click "Next".

In the "Choose Your Database Objects" dialog, select all the "Tables" and "Views". Verify that the Model Namespace is "NorthwindModel" and then click "Finish".

Update the Web.Config File

In the NorthwindData class library, open the App.Config. Copy the connectionSettings element. In the NorthwindService, open the web.config and paste the connectionSettings as a child element of the configuration element. Here is what the web.config file should look like:

<?xml version="1.0"?>
<configuration>
    <connectionStrings>
        <add name="NorthwindEntities" connectionString="metadata=res://*/NorthwindEntities.csdl|res://*/NorthwindEntities.ssdl|res://*/NorthwindEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
    </connectionStrings>
 
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
 
    <system.serviceModel>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="true">
        </serviceHostingEnvironment>
        <standardEndpoints>
            <webHttpEndpoint>
                <standardEndpoint name="" helpEnabled="true" automaticFormatSelectionEnabled="true"></standardEndpoint>
            </webHttpEndpoint>
        </standardEndpoints>
    </system.serviceModel>
 
    <system.webServer>
        <modules runAllManagedModulesForAllRequests="true"/>
    </system.webServer>
 
</configuration>

I found that when I hosted the NorthwindService at Discount ASP.NET, I wasn't able to use Integrated Security. Instead, I used a user id and password to connect to the database. If you need to do the same, then you can change the connection string to look something like this (substituting myusername and mypassword for the username and password for your database):

<connectionStrings>
    <add name="NorthwindEntities" connectionString="metadata=res://*/NorthwindEntities.csdl|res://*/NorthwindEntities.ssdl|res://*/NorthwindEntities.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;User ID=myusername;Password=mypassword;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

Creating the CustomerRepository

In the NorthwindData class library, add a new class called CustomerRepository.cs. Add a new public static method to the CustomerRepository class called GetCustomers. that returns IList<Customer>. Next, add the code to get the list of customers from the database. The CustomerRepository.cs file should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace NorthwindData
{
    public class CustomerRepository
    {
        public static IList<Customer> GetCustomers()
        {
            IList<Customer> customers = null;
 
            using (NorthwindEntities entities = new NorthwindEntities())
            {
                customers = entities.Customers.ToList();
            }
            return customers;
        }
    }
}

Creating the GetCustomers Service Operation

Understanding WebGet and WebInvoke

The Create and Consume RESTFul Service in .NET Framework 4.0 article does a great job explaining the WebGet and WebInvoke operations, so I am going to just repeat what was said in the article:

WebGet

WebGet operation is logically receiving the information from a service operation & it can be called by the REST programming model. The WebGet attribute is applied to a service operation in addition to the OperationContract and associates the operation with a UriTemplate as well as the HTTP protocol Get verb.

WebInvoke

WebInvoke operation logically raises a service option & it can be called by the REST programming model. The WebInvoke attribute is applied to a service operation in addition to the OperationContract and associates the operation with a UriTemplate as well as an underlying transport verb that represents an invocation (for example, HTTP POST, PUT, or DELETE). WebInvoke has a property called Method, it allows specifying different types of HTTP methods (POST, PUT or DELETE), and by default Method is POST.

IRestService Interface

In the IRestService interface, add the "System.ServiceModel.Web" namespace. Next add the method GetCustomers that returns a string. Decorate the GetCustomers method with the OperationContract attribute. Also decorate the GetCustomers method with the WebGet and pass the UriTemplate and BodyStyle parameters into the constructor. Set the UriTemplate parameter to "GetCustomers" and the BodyStyle parameter to WebMessageBodyStyle.Bare. The IRestService.cs file should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Web;
 
namespace NorthwindService
{
    [ServiceContract]
    public interface IRestService
    {
        [OperationContract]
        [WebGet(UriTemplate = "GetCustomers", BodyStyle = WebMessageBodyStyle.Bare)]
        string GetCustomers();
    }
}

Referencing the JSON.NET Library

Since the data that will be sent and recieved from the Windows 8 Metro application will be in JSON format, the WCF RESTFul Service needs to be able to send and recieve JSON. The WebGet and WebInvode attributes take the constructor parameters "RequestFormat" and "ResponseFormat" that can be set to WebMessageFormat.Json. In my case, this didn't work. Since I am working with the ADO.NET Entity Framework, I discovered that when I tried sending or receiving model objects defined in the NorthiwndEntites ADO.NET Entity Data Model, I would get the error "Error 101 (net::ERR_CONNECTION_RESET): The connection was reset." My solution to this problem was to serialize/deserialize the model objects to and from JSON string using the JSON.NET library found on CodePlex. Once I started using JSON strings, everything worked great.

Download the Newtonsoft.Json.dll from JSON.NET and add a reference to it in the NorthwindService.

Adding Attributes to the RestService Class

For the RestService to work, it needs to be decorated with 2 attributes, AspNetCompatibilityRequirements and ServiceBehavior. The AspNetCompatibilityRequirements attribute requres the "System.ServiceModel.Activation" namespace be defined. The class should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using NorthwindData;
using Newtonsoft.Json;
using System.ServiceModel.Activation;
 
namespace NorthwindService
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
    public class RestService : IRestService
    {
    }
}

Creating the GetCustomers RestService Method

The GetCustomers method will call the CustomerRepository.GetCustomers() method. All the JSON libraries that I have found have been unsuccessful in serializing model objects defined in a ADO.NET Entity Data Model. It seems to have something to do with the foreign key references. To resolve this, I use a LINQ statement to select the model objects as a annonymous type objects and then serialize the annonymous type objects. Here is the GetCustomers method:

public string GetCustomers()
{
    IList<Customer> list = CustomerRepository.GetCustomers();
    string json = JsonConvert.SerializeObject(list.Select(m =>
        new
        {
            CustomerID = m.CustomerID,
            ContactName = m.ContactName,
            ContactTitle = m.ContactTitle,
            CompanyName = m.CompanyName,
            Address = m.Address,
            City = m.City,
            Region = m.Region,
            Country = m.Country,
            PostalCode = m.PostalCode,
            Fax = m.Fax,
            Phone = m.Phone
        }
    ));
    return json;
}

Testing the GetCustomers Method

I setup the NorthwindService in IIS, by viewing the properties on the NorthwindService, going to the Web tab and selecting Local IIS Web server. The Project Url is defined as: http://localhost/NorthwindService. I can now test the GetCustomer method by opening up a browser and typing the following into the URL: http://localhost/NorthwindService/RestService/GetCustomers. If you are using Chrome or FireFox, you will see the data displayed in the web page. If you are using Internet Explorer, you will need to "View Source" to see the data.

Creating the SaveCustomer Service Operation

Extending the Customer Class

The Customer class is defined by the ADO.NET Entity Data Model as a partial class. To add additional methods to it, a partial class with the same name and in the same namespace can be created. I extended the Customer class by adding a CopyTo method that copies the properties of the Customer instance to another Customer instance.

In the NorthwindData class library, add a new class file called Customer.cs. Define the Customer class as a public partial class. Add a public method called CopyTo that takes a Customer as a parameter. Set the properties of the Customer passed in with the properties of the current instance of Customer. The code should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace NorthwindData
{
    public partial class Customer
    {
        public void CopyTo(Customer dest)
        {
            dest.CompanyName = this.CompanyName;
            dest.ContactName = this.ContactName;
            dest.ContactTitle = this.ContactTitle;
            dest.Address = this.Address;
            dest.City = this.City;
            dest.Region = this.Region;
            dest.Country = this.Country;
            dest.PostalCode = this.PostalCode;
            dest.Fax = this.Fax;
            dest.Phone = this.Phone;
        }
    }
}

Adding the SaveCustomer method to the CustomerRepository

In the NorthwindData class library, open the CustomerRepository class and add the SaveCustomer method. The SaveCustomer method will retrieve the Customer to be updated from the database. Then the Customer retrieved from the database will be updated with the values in the Customer passed into the SaveCustomer method. Finally the Customer retrieved from the database will be saved back to the database. Here is the code:

public static bool SaveCustomer(Customer customer)
{
    bool success = false;
 
    using (NorthwindEntities entities = new NorthwindEntities())
    {
        Customer currentCustomer = entities.Customers.Where(c => c.CustomerID == customer.CustomerID).FirstOrDefault();
        customer.CopyTo(currentCustomer);
        success = entities.SaveChanges() == 1;
    }
 
    return success;
}

Adding the SaveCustomer Method to the IRestService Interface

When sending data to the WCF RESTFul Service, the method in the Service Contract Interface is decorated with the WebInvoke attribute and the Method parameter is passed to the constructor and set to "POST". Additionally, the UrlTemplate parameter is set in the WebInvoke constructor.

[OperationContract]
[WebInvoke(UriTemplate = "SaveCustomer", Method = "POST")]
string SaveCustomer(string data);

Creating the SaveCustomer RestService Method

The SaveCustomer method will call the CustomerRepository.SaveCustomer() method. The Customer is passed in as a JSON string. I use the JSON.NET JsonConvert.DeserializeObject method to deserialize the string into a Customer object. I them call the CustomerRepository.SaveCustomer method to save the Customer. I return a JSON object indicating whether the save was successful, and if it is not, a message as to why it was not successful. Here is the SaveCustomer method:

public string SaveCustomer(string data)
{
    string result = string.Empty;
 
    try
    {
        Customer customer = JsonConvert.DeserializeObject<Customer>(data);
        result = CustomerRepository.SaveCustomer(customer) ? "success" : "fail";
    }
    catch (Exception ex)
    {
        result = ex.Message;
    }
 
    return JsonConvert.SerializeObject(
        new
        {
            saved = result == "success",
            message = result
        });
}

Testing the SaveCustomer Method

To test the SaveCustomer method, I use Fiddler. On the Composer tab, I change the type of request to "POST". For the URL, I put the URL for SaveCustomer. In the Request Headers, I add "Content-Type: text/xml;". In the Request Body, I put the Customer JSON object as the text of a string xml element. Here is the Request Body:

<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">
{
  "CustomerID":"ALFKI",
  "ContactName":"Maria Anders",
  "ContactTitle":"Sales Representative",
  "CompanyName":"Alfreds Futterkiste",
  "Address":"Obere Str. 57",
  "City":"Berlin",
  "Region":"AA",
  "Country":"Germany",
  "PostalCode":"12209",
  "Fax":"030-0076545",
  "Phone":"030-0074321"
}
</string>

Here is a screenshot of Fiddler with the values entered:

Conclusion

Building a WCF RESTFul Service was easy to do. The difficulty that I experienced was having to figure out how to get the Windows 8 HTML Metro application to talk with the WCF RESTFul Service. I wasn't able to get the Windows 8 HTML Metro application to use JSON in it's WinJS.xhr function, but found that it worked well with XML. Getting the JSON string from the server as XML and also wrapping the JSON string in XML to post to the server is easy to do, but not as clean as I would have liked.

References

• Create and Consume RESTFul Service in .NET Framework 4.0
• Fiddler Web Debugging Proxy
• Northwind Sample Database
• JSON.NET

---