Mikes Knowledge Base

WCF Web Services & iOS - Part 2

By Mike Gledhill

Pages:

So, we now have a very basic WCF Web Service, which returns a string in JSON format.

In this section, we're going to link in some SQL Server data, and walk through some of the problems you might encounter.

To do this, you will need the following:

A copy of Microsoft Visual Studio 2010
A copy of Microsoft SQL Server, with Admin rights
The Northwind database on your SQL Server

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.

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 VS2010 recognises the changes we've made to the .dbml file.

Now, 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... change 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.

Defining a WCF Service [DataContract] class

Remember, our Visual Studio project knows what a Customer record is, SQL Server knows what the Customer table looks like.. but we're creating a JSON service, available to the outside world, and our Services server certainly doesn't know what a Customer record is. So we have to tell it.

Once we've done this, we can create services which return wsCustomer records, and our WCF Service will know what this means, and how to write suitable JSON records.

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 example, let's just return three of the fields:

Customer ID
Customer Name
City

Here's how our wsCustomer table needs to be defined in the Customer.cs file.

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 :

public class Service1 : IService1
{
  public List<wsCustomer> GetAllCustomers()
  {
    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;
  }

  ...

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 far 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

Before we move on, I just want to point out that you don't particularly need 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:

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.

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:

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]s 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)
{
    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;
}

As before, we just use LINQ to fetch a list of [Order] records, then 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, and 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 a customer ID string as a parameter, and 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 results 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)
{
    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;
}

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