General

Building Back-end Data and Services for Windows 8 Apps: OData – Part 1

UPDATE: Part 2 of this post is now published as well.

It’s been longer than I planned, but this post is a follow-up to my overview post, “Building Data and Services for Windows 8 Apps”. In that post, I outlined a couple of different ways that you can build out data storage and services to provide a back-end for your Windows 8 app (and of course, these services can be shared with other apps as well).

I introduced the scenario of building a game leaderboard service, which could provide a central location for storing scores for different games, and allow those games to both store updated scores, as well as retrieve a list of scores for that game. I also showed the basics of the two games I’m planning to use, Catapult Wars, an XNA sample game ported to HTML5 by my fellow technical evangelist Chris Bowen, and Space Cadet, a sample game created by my fellow technical evangelist David Isbitski.

In this post, I’m going to walk through the steps needed to create a data store and services for our game leaderboard using OData, and based on the underlying technologies of SQL Azure (for the database), Entity Framework, and WCF Data Services. In part 2, which I’ll publish tomorrow, I’ll walk through publishing my service to Windows Azure, and wiring it up to the Windows 8 games in JavaScript.

Creating the Database

In this scenario, I’m going to start with the database, since that’s where the data will need to go.

First things first, if you don’t already have a Windows Azure account, you’ll need to create one. If you’re an MSDN subscriber, many MSDN plans include a variety of Azure resources, including compute hours, SQL Azure databases, and more. If you don’t have an MSDN subscription, you can sign up for a 90-day trial that will give you everything you need to follow along, and by default includes a spending cap to ensure that you don’t have any out-of-pocket expenses if your services exceed the trial service limits (if your services exceed the trial limits, they’ll automatically be shut down).

Once I have a Windows Azure account set up, I sign into the Windows Azure management portal, at which point I should see a screen that looks something like this:

image_3

Note that in my case, I already have a few services in my account. For a new account, you won’t have any services showing in the management portal when you sign in.

Creating a new database is very simple. I just click the + New link at the bottom left of the page, select DATA SERVICES | SQL DATABASE | QUICK CREATE, and fill in the required fields, as shown below. If you’re following along, make sure to make note of the database name, login name, and password for future reference:

image_5

Once I’ve filled in all the required fields, I click the CREATE SQL DATABASE link, and the database (and database server, if you’re not using an existing server in your account) will be created and added to the management portal.

If I then click on the name of the new database, I see a screen similar to the following:

image_7

Note the highlighted areas, which include links to show the connection strings (you’ll need those to connect to your SQL Azure DB from Visual Studio), manage the allowed IP addresses (to allow specific IP addresses or ranges to access the database server from outside), and the fully qualified server name of the server instance on which the database has been installed. There’s a lot more I could do in the portal, but that’s outside the scope of this post, so I’ll move on to creating the database schema.

Creating the Schema

To keep this post to the point, I’m not going to go into great depth in terms of creating the data schema. This can be accomplished using SQL Server Management Studio (you’ll want the latest version to connect to SQL Azure), or the SQL Server Object Explorer in Visual Studio 2012 (I ended up using that, since I already have Visual Studio 2012 Ultimate installed, and I don’t have a local copy of SQL Server on this machine).

In SQL Server Object Explorer, I simply click the Add SQL Server button at the top of the window, then in the Connect to Server dialog, fill in the required info for the SQL Azure database (which you can get from the portal links above), like so:

image_10

NOTE: If you’re following along, and you get an error message that you can’t connect, you’ll need to go to the Windows Azure portal, and click the “Manage allowed IP addresses” link mentioned above, and add your current client IP address to the allowed list. You can always remove this later, once you’ve deployed your service.

Once I’ve connected to the database, I drill down into the server tree and find my database, and open up the Tables node. I right-click the Tables node and select Add New Table, and add the desired columns, and any desired constraints (for example, I usually set the Id column to use SQL Server’s Identity feature to auto-increment its value). I also set defaults for the Wins, Losses, and Ties columns, as shown below:

image_12

As mentioned in my earlier overview post, I’m keeping the schema pretty simple for our example. Now that I’ve got my database set up, and my table and schema complete, let’s look at how I can start exposing some data in a way that’s easily consumed by a Windows 8 app.

Creating the Project

NOTE: This tutorial assumes that you have already installed the latest Windows Azure SDK. If you have not yet, then go here, and look for the “install the SDK” link near the top of the page, and follow the instructions to install the SDK.

Since the plan in my case is to deploy this solution to Windows Azure, I’m going to start with a Windows Azure Cloud Service project, using C#, as shown below (note that to debug the Windows Azure Cloud Service locally, I will need to run Visual Studio as an administrator):

NewCloudService_2

Of course, if you’ve done any work with Azure, you already know that I also need to choose what kind of role I want in our Cloud service, and for my purposes, what I want is a WCF Service Web Role, which I’ll call GameLeaderServiceOData, as shown below (you can change the name of your web role project by hovering over the item in the right-hand pane and clicking the pencil icon):

WCFServiceWebRole_2

When the project has been created, Visual Studio will open up the default service code file, Services1.svc.cs. I don’t need this (because I’ll be adding my own service shortly), so I’ll close it, and delete both Service1.svc (and its code-behind file) as well as IService1.cs.

Create the Data Model

Again, in the interest of simplicity, I’m going to leverage the Entity Framework to quickly build a data model on the basis of the schema I created in my Windows Azure SQL database. To get started, I’ll add a new ADO.NET Entity Data Model to my project (Project | Add New Item…), and call it Scores.edmx, as shown below:

NewDataModel_2

I’ll go ahead and select Generate from Database for the model contents, then choose New Connection for the database connection, and provide the connection information for my SQL Azure database created earlier. For simplicity’s sake, and because this is a demo rather than production code, I’m storing the password as part of the entity connection settings in web.config. IMPORTANT: You should always perform an evaluation of the security needs of your application, and make the best choices for the security of your connection settings, in particular passwords, which may include encryption, or setting connection passwords via code rather than storing in configuration files. The best practices for this are beyond the scope of this post, however.

For my database objects, I’ll simply choose the Scores table created earlier, and accept the defaults for the rest of the settings (for more information on the Entity Data Model Wizard, see this topic on MSDN):

DatabaseObjects_2

Once I click Finish, Visual Studio will create the data model, and open the model diagram, which contains the single entity, Score.

Now that I’ve got a model, it’s time to wrap it up in a nice service.

Creating the OData Service

If you’re not already familiar with it, OData is the short name of the Open Data Protocol, a web protocol for querying and updating data. OData itself is simply a specification, while implementation may take different forms depending on the vendor supplying the implementation. One of Microsoft’s implementations of the OData protocol is WCF Data Services (more recently, Microsoft added OData support to the ASP.NET Web API stack, which I’ll be looking at in a future post in this series).

To make sure I’ve got the latest and greatest features for my WCF Data Service, I’m going to take advantage of the built-in support in Visual Studio for a packaging technology called NuGet, which allows various developers (both internal to Microsoft, as well as 3rd-party and community developers) to provide updated packages for their libraries and components, which I can easily add and update at the project level, using the Library Package Manager, either via command line or GUI, right in my Visual Studio project):

NuGetWCFDataServices_2

When I click Install, NuGet will add the WCF Data Services Server assemblies (along with any dependencies) to my project, add the required references, and then I’m good to go.

NOTE: One important reason for updating to the latest version of WCF Data Services is that version 5.1 added support for the format query parameter, which allows you to specify that you want the service to return JSON-formatted data, rather than the default ATOM-Pub format. This is very useful in scenarios where you are unable to pass an HTTP header to specify the desired format, for example when you’re testing your service in a browser, or when using APIs that only allow you to pass a service URI as part of the API.

The next step in getting my leaderboard working is to add a new WCF Data Service to my project, as shown below:

NewWCFDataService_2

Once I add the service to my project, Visual Studio will helpfully open the GameLeaderServiceOData.svc.cs file containing the implementation of my service, which I need to modify slightly to make it functional. A minimum of two changes are required:

  1. In the class declaration, I need to provide the name of the data source class (in my case, this is the entity name from our data model, which is GameLeaderODataEntities).
    • In the InitializeServiceMethod, I need to configure the access rules for the entity. In my case, I’m going to provide AllRead and AllWrite. Note that this step provides these permissions to anyone with the OData endpoint URL, so if you want to limit access to authenticated users, you also need to provide (either via configuration or code) for authentication in your solution.
The updated code for my service looks like this: 
<div class="csharpcode">
  <pre class="alt"><span class="lnum">   1:  </span><span class="kwrd">namespace</span> GameLeaderServiceOData</pre>

   2:  {
   3:      public class GameLeaderServiceOData : DataService<GameLeaderODataEntities>
   4:      {
   5:          // This method is called only once to initialize service-wide policies.
   6:          public static void InitializeService(DataServiceConfiguration config)
   7:          {
   8:              // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
   9:              // Examples:
  10:              config.SetEntitySetAccessRule("Scores", EntitySetRights.AllRead | EntitySetRights.AllWrite);
  11:              // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
  12:              config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
  13:          }
  14:      }
  15:  }

Note that because I updated the version of WCF Data Services using NuGet, I was getting an error in the IDE for DataServiceProtocolVersion.V3 on line 12. The issue was that the reference to the previous version of System.Data.Services.Client (which is included by default when I create a new service) was not removed by NuGet when an updated version was added. Removing the reference to System.Data.Services.Client fixed this issue.

At this point, assuming I’ve provided all of the correct credentials, and I have data in my database, I should be ready to test my service.

Testing the Service

Testing my new service is pretty straightforward, I can just press the F5 key, and Visual Studio will build my project, start up the Azure Compute and Storage Emulators, and launch my project using IIS Express, and then open the service URL in a browser, as shown below:

ODataService_4

Note that while I’m debugging locally, my service does not need to connect to Windows Azure at all. The Windows Azure SDK Tools provide everything I need to run the “cloud” right on my desktop.

The initial response from the service lists the entities that are available to work with, using ATOM-pub format. To view the individual records, I can simply append the entity name to the URI:

ScoresOData_2

By default, I don’t see the ATOM-Pub data because IE defaults to feed reader view. If I turn off feed reader view, I can see the underlying XML (ATOM-Pub) formatted data, which contains both the values for each of the entity properties, but also includes metadata to help OData clients to understand the underlying datatypes, etc.:

ScoresOData2_2

Additionally, if I want to work with data that’s more consistent with the JavaScript apps that I favor for the Windows Store, I can modify the service URI to request the data in JSON format, like so: http://127.0.0.1:81/GameLeaderServiceOData.svc/Scores?$format=json. This will prompt me to open or save a file called Scores.js, which looks like this:

{"odata.metadata":"http://127.0.0.1:81/GameLeaderServiceOData.svc/$metadata#Scores","value":[
{"Id":3,"Game":"Space Cadet","Player":"Seeker","Score1":500,"Wins":0,"Losses":0,"Ties":0},
{"Id":4,"Game":"Catapult Wars","Player":"Seeker","Score1":null,"Wins":1,"Losses":3,"Ties":0},
{"Id":5,"Game":"Space Cadet","Player":"Player1","Score1":800,"Wins":null,"Losses":null,"Ties":null}]}

As you can see, my database has 3 records. One nice advantage of JSON is that it’s a very compact way of representing data, but you might have noticed that it doesn’t embed the metadata in the .js file. Note, however, that the JSON data includes a reference to the metadata URI, so clients interested in the metadata know where to find it.

Rich Querying

One major advantage of using an OData service for the leaderboard back-end is that OData supports a rich query syntax that makes it easy to retrieve just the data I want, using some simple querystring parameters. For example, lets say that with the above data, I wanted to retrieve just the scores for the game Space Cadet. I could simply use the $filter query parameter to limit the query (adding the $format query parameter if I wanted JSON instead of ATOM format):

http://127.0.0.1:81/GameLeaderServiceOData.svc/Scores?$filter=Game eq ‘Space Cadet’&$format=json

OData also supports ordering via the $orderby parameter, as well as paging via the $top and $skip parameters:

http://127.0.0.1:81/GameLeaderServiceOData.svc/Scores?$filter=Game eq ‘Space Cadet’&$orderby=Score1 desc&$top=10

OData supports many more query parameters, which you can read about in the OData Protocol Documentation (of particular interest are the OData Core and URL Conventions docs).

If you’re working with OData on a platform where there’s an OData client library (for example, XAML-based Windows Store apps), you can use LINQ-style queries, but the above URL-based query syntax is really great for working with from JavaScript and xhr (XmlHttpRequest) queries.

Wrap-up (for now)

My service is now up and running locally, and in the interest of keeping this post to a reasonable length, I’m going to pause here for now. Tomorrow, I’ll share how I can take my local service and publish it to Windows Azure, so that it’s available anywhere, anytime, and then how I can integrate my service with a couple of JavaScript-based Windows 8 games. UPDATE: Part 2 is now up!

While you’re waiting for part 2, head on over to Generation App. While you’re there, sign up to receive tips on Windows 8 app development, and keep an eye out for contests and promotions. I know a couple of local developers here in Mid-Atlantic who received free Samsung slates running Windows 8 as a result of getting their apps published in the Windows Store. Who knows what the next contest might offer…

Comments

Comment by Tarun Pothulapati on 2013-11-18 11:43:00 +0000

How to remove the reference to System.Data.Services

Comment by Tarun Pothulapati on 2013-11-18 11:49:00 +0000

I get this error Error 2 Assembly ‘Microsoft.Data.Services, Version=5.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ uses ‘Microsoft.Data.OData, Version=5.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ which has a higher version than referenced assembly ‘Microsoft.Data.OData, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ c:UserstarunDocumentsVisual Studio 2012ProjectsWindowsAzure5packagesMicrosoft.Data.Services.5.6.0libnet40Microsoft.Data.Services.dll GameLeaderServiceOData

awhen I’m trying to test the project

Comment by devhammer on 2013-11-18 12:59:00 +0000

You should be able to expand the References section of the project in Solution Explorer, locate the reference, right-click it and remove. Then you can install the latest version via Nuget.