Mikes Knowledge Base

WCF Web Services & iOS - Part 2

By Mike Gledhill

Pages:

So, we now have a very basic WCF Web Service which takes a string parameter, and returns it back, as a string in JSON format.

In this section, we're going to add some new services, which will return some data (in JSON format) from a SQL Server database, whilst walking through some of the problems you might encounter.

For this tutorial, you will need the following:

A copy of Microsoft Visual Studio
A copy of Microsoft SQL Server, with Admin rights
A copy of Microsoft's free Northwind sample database on your SQL Server.
You can download a SQL Server backup file (.bak) of the Northwind database from here, save it onto your PC, then run a script like this to restore it to your copy of SQL Server:

-- Restore a Northwind backup (which I've saved to the folder C:\SQL Server)
RESTORE DATABASE [Northwind]
FROM DISK = 'C:\SQL Server\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\SQL Server\Northwind.mdf',
MOVE 'Northwind_log' TO 'C:\SQL Server\Northwind_Log.mdf'

By the end of this chapter, we'll have a set of three web services which return JSON data (click on the links below):

http://www.iNorthwind.com/Service1.svc/getAllCustomers
http://www.iNorthwind.com/Service1.svc/getOrdersForCustomer/ANATR
http://www.iNorthwind.com/Service1.svc/getCustomerOrderHistory/ANTON

And actually, just with the first two of these web services, we'll have all the data we'd need to populate a Master-Detail view like this:

(You can click here to see how to create this example.).

Linking our web service to a SQL Server database

Make sure that you're not currently debugging, then follow these steps.

1. In Visual Studio's Solution Explorer window, right-click on the project name (JSONWebService), select Add \ New Item.

2. Select Data, then "LINQ to SQL Classes", change the name of the file to Northwind.dbml, then click on the Add button.

Your solution should now contain a .dbml file.

Note: yes, we could've used a Entity Framework data provider instead.

3. Double-click on the .dbml file, and click on View \ Server Explorer, to view a list of your current database connections.

4. You need to make sure your SQL Server database (in our case, Northwind) is listed in the Data Connections tree. If it's not listed, right-click on "Data Connections", select the "Add Connection" option, and fill in the details of your SQL Server's server name and database name.

5. Once you have your SQL Server connection setup, expand it in the Server Explorer window, and drag and drop the Customer and Order tables into the left blank area.

6. Compile your code (click on Build \ Build Solution) to make sure Visual Studio recognises the changes we've made to the .dbml file.

At this point, our LINQ connection knows about our two SQL Server tables, but the outside world doesn't.
We now need to define some new classes in our projects which describe their fields.

Adding the GetAllCustomers WCF Web Service, which returns JSON data

Back in Solution Explorer, right-click on the project name, JSONWebService, click on Add \ Class... set the filename to Customer.cs, and click on Add.

In the new Customer.cs file, add the following code, which adds a new class, wsCustomer.

This will be the Web Service (ws) version of our Customer class.
In other words, we define this class, so that we can create web services which can read/output sets of records of this type.

Now, there's nothing stopping us from creating our wsCustomer class and adding all of the fields in our SQL Server Customer table. But for this simple example, we're just going to return three of the fields.

Customer ID
Customer Name
City

Now, let's create a web service which can return a collection (List) of these records.

Let's start with the IService1.cs file. Add the following lines, at the top of the class:

Now add these lines, at the top of Service1.svc.cs :

This is pretty basic stuff, we just load the list of SQL Server Customer records, and turn them into wsCustomer records, which is what our web services have been setup to read and write.

public class Service1 : IService1
{ 
    public List<wscustomer> GetAllCustomers()
    {
        try
        {
            NorthwindDataContext dc = new NorthwindDataContext();
            List<wscustomer> results = new List<wscustomer>();
            foreach (Customer cust in dc.Customers)
            {
                results.Add(new wsCustomer() { 
                    CustomerID = cust.CustomerID,
                    CompanyName = cust.CompanyName,
                    City = cust.City
                });
            }
            return results;
        }
        catch (Exception ex)
        {
            //  Return any exception messages back to the Response header
            OutgoingWebResponseContext response = WebOperationContext.Current.OutgoingResponse;
            response.StatusCode = System.Net.HttpStatusCode.InternalServerError;
            response.StatusDescription = ex.Message.Replace("\r\n", "");
            return null;
        }
    }

I will explain the catch code a little later, as it's both important, and brilliant !
But for now, just build the solution, and run it.

The "WCF Test Client" should start up, but once again, we're going to ignore it, as it doesn't work with web services returning JSON data.

Open up a browser, and try to run the following URL (remembering to replace the Port number with whichever port number the "WCF Test Client" says it's connected to.

http://localhost:15021/Service1.svc/getAllCustomers

(I wonder if I'm the only person who went "Wow !" the first time I saw this work..).

Now, if you're using Google Chrome as your browser, I strongly recommend that you search for, and install, the free add-in called JSONView. It turns our one long line of JSON into something much more readable.

You see what I mean....!

And that's it. We have just created a WCF Web Service, which returns real SQL Server data, in JSON format.

JSON format

I'm repeating what I said on the previous page, but, looking at the JSON results above, I just wanted to point out that you don't have to have that GetAllCustomersResults wrapper around your JSON values.

Personally, I like it. My code can check that this JSON element exists, and be sure that it's called the correct service.
However, if you don't want this, simply remove the "BodyStyle = WebMessageBodyStyle.Wrapped" from the IService1.cs file:

Your service definition will then look like this:

[OperationContract]
[WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, UriTemplate = "getAllCustomers")]
List<wsCustomer> GetAllCustomers();

..and the service will return results like this:

DateTime values

One thing I don't like about WCF Services is the way it returns DateTime values in a completely non-generic format.

{
    Name: "Mike's gym appointment",
    StartTime: "/Date(1415612328877+0000)/",
    EndTime: "/Date(1415612264832+0000)/"
}

To get around this, you can follow my tips in this StackOverflow article:
Change default date serialization in WCF

Also, if you store your DateTime values in UTC format, you can use my following code to get your WCF services to return them in the user's local timezone:
How to get current user timezone in c#

Trust me, I've just saved you a day of head-scratching.

A quick word about JSON

A quick word about why we're bothering to load the data from SQL Server data but getting our web service to return it in JSON format.

The nice thing about JSON is that it's "highly portable". Because it's in a readable text format, any device can read this data, and they don't need Microsoft libraries to interpret it.

In this walkthrough, we're deliberately aiming to use this data on a completely non-Microsoft device. Alternatively, we could've written an HTML 5 app, and get our JavaScript to load this JSON data.

Getting our WCF Web Service to read/write data in JSON format keeps our options open, so, even if we ditch our iPads and move over to Android tablets, we won't need to rewrite our WCF Web Service.

Try Googling "Why use JSON" for lots of other (mostly similar) opinions on this subject.

Error handling in WCF Web Services

I also want to quickly mention the try..catch code which I included in the web service function above (and will be including in all of my other web services).

If you don't put a try...catch in your web services code, then, if something goes wrong, the service will fail.
You'll probably just get a HTTP Status Code of 400, with absolutely no indication of what went wrong.

A typical problem is when you have correctly working code, but then deploy to a live server, and suddenly the services stop working.
That "400 Bad Request" message won't give you any help as to what went wrong.

So, the problem is, how do you get WCF Services to handle errors ?

If you Google for "WCF services error handling", you'll find hundreds of people asking this question over the years. A common, but ugly, suggestion is to add an ErrorMessage string to each of your DataContracts, which you'd populate with error messages, if something goes wrong.

The method I've shown here is much easier, friendlier, and reuseable, without adding any extra fields.

catch (Exception ex)
{
    //  Return any exception messages back to the Response header
    OutgoingWebResponseContext response = WebOperationContext.Current.OutgoingResponse;
    response.StatusCode = System.Net.HttpStatusCode.InternalServerError;
    response.StatusDescription = ex.Message.Replace("\r\n", "");
    return null;
}

Basically, if an exception occurs, we return the exception message in the Response Header, along with a relevant HTTP Status Code (500).
It looks wrong, as the web service is returning null, but it actually works brilliantly.

With this try..catch in place, let's see what Google Chrome now shows in it's Network tab when I call a web service, but it can't connect to the database:

Cool, hey ?

Now, you just need to make sure the code which calls this web service will look out for HTTP Error codes coming back from the service, then check if the response had any statusText attached to it.

For example, here's how we'd call our getAllCustomers web service using Angular, and display any exception messages which occur:

$http.get('http://localhost:15021/Service1.svc/getAllCustomers')
    .then(function (data) {
        //  We successfully loaded the list of Customer names.
        $scope.ListOfCustomerNames = data.GetAllCustomerNamesResult;
    }, 
    function (errorResponse) {
        //  The WCF Web Service returned an error.  
        //  Let's display the HTTP Status Code, and any statusText which it returned.
        var HTTPErrorNumber = errorResponse.status;
        var HTTPErrorStatusText = errorResponse.statusText;
        alert("An error occurred whilst fetching Customer Names\r\nHTTP status code: " + HTTPErrorNumber + "\r\nError: " + HTTPErrorStatusText);
    });

I haven't seen any other tutorial offering this advice, and it's golden.
It will make your life (and early days, when you're learning to use web services) much easier.

So, bookmark this page, and cut'n'paste that try..catch code into every one of your web services.

Adding the GetOrdersForCustomer WCF Web Service, which returns JSON data

Let's go a step further and add a web service which takes a parameter.

In our URL, we will specify a particular [Customer] ID, and our web service will return a list of [Order] records for that customer. This is still going to be a "GET" web service (rather than a "POST" web service), so the steps are almost the same.

http://localhost:15021/Service1.svc/GetOrdersForCustomer/ANATR

Once again, stop Debugging your project, and follow these steps:

1. In Solution Explorer, right-click on the project name, JSONWebService, click on Add \ Class... change the filename to Order.cs, and click on Add.

2. In the new Order.cs file, add the following code for the web service (ws) version of our Order class, which we'll call wsOrder:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace JSONWebService
{
    [DataContract]
    public class wsOrder
    {
        [DataMember]
        public int OrderID { get; set; }

        [DataMember]
        public string OrderDate { get; set; }

        [DataMember]
        public string ShippedDate { get; set; }

        [DataMember]
        public string ShipName { get; set; }

        [DataMember]
        public string ShipAddress { get; set; }

        [DataMember]
        public string ShipCity { get; set; }

        [DataMember]
        public string ShipPostcode { get; set; }
    }
}

Now, let's create the web service which will return the list of Order records for a particular Customer.

3. In the IService1.cs file, add the following lines:

[ServiceContract]
  public interface IService1
  {
    [OperationContract]
    [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "getAllCustomers")]
    List<wsCustomer> GetAllCustomers();

    [OperationContract]
    [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "getOrdersForCustomer/{customerID}")]
    List<wsOrder> GetOrdersForCustomer(string customerID);

    ...

4. And finally, add these lines, in Service1.svc.cs, after our GetAllCustomers() function:

public List<wsorder> GetOrdersForCustomer(string customerID)
{
    try 
    {
        NorthwindDataContext dc = new NorthwindDataContext();
        List<wsorder> results = new List<wsorder>();
        System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.GetCultureInfo("en-US");
        foreach (Order order in dc.Orders.Where(s => s.CustomerID == customerID))
        {
            results.Add(new wsOrder()
            {
                OrderID = order.OrderID,
                OrderDate = (order.OrderDate == null) ? "" : order.OrderDate.Value.ToString("d", ci),
                ShipAddress = order.ShipAddress,
                ShipCity = order.ShipCity,
                ShipName = order.ShipName,
                ShipPostcode = order.ShipPostalCode,
                ShippedDate = (order.ShippedDate == null) ? "" : order.ShippedDate.Value.ToString("d", ci)
            });
        }
        return results;
    }
    catch (Exception ex)
    {
        //  Return any exception messages back to the Response header
        OutgoingWebResponseContext response = WebOperationContext.Current.OutgoingResponse;
        response.StatusCode = System.Net.HttpStatusCode.InternalServerError;
        response.StatusDescription = ex.Message.Replace("\r\n", "");
        return null;
    }
}

As before, we just use LINQ to fetch a list of Order records from our SQL Server database, then we convert them into our new wsOrder records. The WCF service itself takes care of converting these wsOrder records into JSON for us.

Let's run the solution, open up our browser, and view the orders for a few of the Northwind customers !

http://localhost:15021/Service1.svc/getOrdersForCustomer/ALFKI
http://localhost:15021/Service1.svc/getOrdersForCustomer/ANATR

Cool, hey ?
If you can write LINQ code, there's little extra you need to do to turn it into a WCF Web Service.

Passing parameters to WCF Web Service in a URL

Now, the Northwind database's Customer table has a Primary Key of type nchar(5), which is why our GetOrdersForCustomer function required one string parameter.

public List<wsOrder> GetOrdersForCustomer(string customerID)
{
NorthwindDataContext dc = new NorthwindDataContext();
...

But, when you come to modify this code for your own projects, if your Primary Key is of type integer, you must still use parameters of type string.

In other words, the following code, which uses an int parameter, won't work.

You must use functions which take a string parameter, then convert the parameter values into an integer yourself.

[ServiceContract]
  ...

    [OperationContract]
    [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped, UriTemplate = "getOrderDetails/{orderID}")]
    List<wsOrder> GetOrderDetails(string orderID);

    ...

public List<wsOrder> GetOrderDetails(string orderID)
{
    NorthwindDataContext dc = new NorthwindDataContext();
    int orderIDnumber = int.Parse(orderID)

    var orders = dc.Orders.Where(s => s.OrderID == orderIDnumber);
    ...

Calling a SQL Server Stored Procedure

Calling stored procedures is also incredibly easy, now that we have the basics setup.

First, I would recommend running your stored procedure, have a look at what parameters it requires, and what type of fields it returns in each row.

Let's consider how we'd run Northwind's CustOrderHist stored procedure. This takes the ID of a Customer record as a parameter, and it returns a list of ProductName strings, and Total integers.

USE [Northwind]
GO

EXEC [dbo].[CustOrderHist] 'ANTON'

Our plan is to add a new service which takes a customer ID parameter in the URL...

http://localhost:15021/Service1.svc/getCustomerOrderHistory/ANTON

...calls our stored procedure, and returns a JSON list of results...

Here's the steps that we need to follow, to create a Web Service to call this stored procedure.

1. Add the stored procedure to our .dbml file

Open up the Northwind.dbml file, and drag'n'drop the CustOrderHist stored procedure from the Server Explorer window to the right-hand side of the dbml file.

Add the Stored Procedure to the .dbml file

2. Create a class to store our results

We need to describe to our service what the results our new service will be returning. Let's create a new file called CustomerOrderHistory.cs, and define a CustomerOrderHistory class in it, containing the two fields which our JSON will be returning for each record:

using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace JSONWebService
{
    [DataContract]
    [Serializable]
    public class CustomerOrderHistory
    {
        [DataMember]
        public string ProductName { get; set; }

        [DataMember]
        public int Total { get; set; }
    }
}

3. Define an endpoint for this service

So, we know that this stored procedure will require a CustomerID parameter, and will return a List of these CustomerOrderHistory records. In our IService1.cs file, let's add an endpoint for this new service:

[OperationContract]
[WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, UriTemplate = "getCustomerOrderHistory/{customerID}")]
List<CustomerOrderHistory> GetCustomerOrderHistory(string customerID);

4. Call the stored procedure, and return the results

Finally, we just need to write the (very simple) code to call our stored procedure, and for each row that it returns, create a web-friendly CustomerOrderHistory record.

The only gotcha here is that when LINQ calls the CustOrderHist stored procedure, it will return each row of results in a new class, called CustOrderHistResult. LINQ created this class for you when you drag'n'dropped the stored procedure in the .dbml file.

So, for each of these LINQ-generated CustOrderHistResult records that were returned from our stored procedure, we need to create a new web-service-friendly CustomerOrderHistory record, and add it to our list of results.

public List<CustomerOrderHistory> GetCustomerOrderHistory(string customerID)
{
    try 
    {
        List<CustomerOrderHistory> results = new List<CustomerOrderHistory>();
        NorthwindDataContext dc = new NorthwindDataContext();
        foreach (CustOrderHistResult oneOrder in dc.CustOrderHist(customerID))
        {
            results.Add(new CustomerOrderHistory()
            {
                ProductName = oneOrder.ProductName,
                Total = oneOrder.Total ?? 0
            });
        }
        return results;
    }
    catch (Exception ex)
    {
        //  Return any exception messages back to the Response header
        OutgoingWebResponseContext response = WebOperationContext.Current.OutgoingResponse;
        response.StatusCode = System.Net.HttpStatusCode.InternalServerError;
        response.StatusDescription = ex.Message.Replace("\r\n", "");
        return null;
    }
}

After following these four simple steps, we now have our new web service, ready to call our stored procedure.

Summary

So we've now created three types of WCF Web Services.

We have a service which returns all records in a particular SQL Server table, a service which filters records by a parameter, and a third service which calls a stored procedure with a parameter.

These are the three scenarios which you'll probably need the most.

In the next section, we'll take this a step further and create a "POST" web service, which lets us send JSON data to a web service. The web service will convert the JSON data into something which LINQ can understand, and then we'll be able to write the data back into our SQL Server database.

< Previous Page

Next Page >

Comments