Bringing Azure+OData+Silverlight Goodness to Open Data Apps in Ottawa

On Saturday April 24th 2010, at Open Data Ottawa Hackfest, a team built the ‘OttGuide’ application (here showing 6000+ bus stops on the Ottawa public bus transit network), built on the Open Data Application Framework using Silverlight and Bing Maps, accessing an OData service built with .NET 3.5 and hosted on Windows Azure and SQL Azure.  For more on open data and cross-platform togetherness, consider for the Make Web Not War conference on May 27th 2010 in Montreal.


This passed Saturday Apr 24th 2010, the very successful Open Data Ottawa Hackfest took place for the first time in Ottawa City Hall organised by Edward Ocampo-Gooding and friends.  A large number of people (enough to exhaust the free t-shirt supply) attended for 4+ hours from different fields (developers, designers, librarians, statisticians. media and city officials).  The event included opening presentations, a hack fest where attendees worked on applications showing use of Ottawa data and closed with a series of presentations showing the fruits of the attendees’ labour.

Prior to the event, the City of Ottawa (unlike cities like Vancouver and Edmonton) hadn’t officially published municipal data or a data publication policy.  This meant that attendees to the hackfest were using various interesting methods to get data for their application from screen scraping to manual data re-enty.

I had the opportunity to talk at length with the city’s CIO, Guy Michaud, and discovered that the city is in fact on the verge of agreeing a policy and then immediately publishing a few initial sets of satic data, most likely sometime in May.  It’s quite possible that the hackfest event helped push this action ahead, but Guy is clearly a guy with forward-looking ideas on the issue of data publishing.

I was told about the event a few weeks ago by friend and Microsoft Canada IT Pro Advisor Rick Claus who was planning on attending.  So, in those weeks, I took a look at some of the work that has been done to obtain Ottawa municipal data and at open data work in general.  In particular I looked in 3 things:


  • Craig Davey had done extensive work on taking Google-compatible data published on the City’s OCTranspo site, augmenting it, converting it to GTFS format and publishing it with updates in a feed.
  • I had been familiar for some time with Microsoft’s open protocol for providing access to queryable and updateable data sets in a RESTful way – OData.  I did further research into this.  OData builds on ADO.NET Data Services and is fully incorporated into WCF Data Services within the recently released .NET 4 framework.  OData is designed to be consumed (or produced) by many different clients on many platforms. There are client libraries available or in the works for .NET (3.5 with an update & 4.0), Silverlight 4.0, iPhone, Javascript, Windows Phone 7, PHP & Java.  In fact, any client that can make web requests and handle XML can access OData-based services.

The night before the event, on a total whim, I decided to knock up an OData service exposing OCTranpo data, so I: 

  • started with Craig’s GTFS-format data provided as CSV files in a ZIP file. 
  • created an ADO.NET Entity Framework Model in a new .NET 3.5 Class Library project in Visual Studio 2010 to represent the GTFS data with entities and relationships
  • used the designer tools to generate DDL script to create the database schema
  • generated the schema using the script
  • created a database in my local SQL Server 2008 server
  • wrote code to import the CSVs and use the ADO.NET Entity Framework classes to import the data which was taking 3+ hours
  • wrote new code to import the CSVs with the SQL using bulk copy class bypassing the model which did the import of over 2 million bus stop time rows in a few minutes
  • added a WCF Service project and removed the default service from the project template
  • added a WCF Data Service item, setting it up to use the class of the entity model I created and providing read access to all entities
  • copied the connection string from my entity model class library app.config into the WCF Service web.config
  • tested my OData service using the OData URL query syntax, here showing a list of all stops… but this could be queried to find things like a nearby stop (from GPS coordinates), the bus times for a stop, the trips (e.g. ‘saturday service to ABC on route x’) and routes (i.e. bus number) going through a stop, find the stops on a trip, when a specific service gets somewhere, or even for full journey planning, etc.


  • re-opened by solution as administrator so I could use the Azure Cloud tools
  • added a Windows Azure cloud project with no roles and added my existing WCF Service project to the roles
  • tested OData access running in the local Azure Development Fabric connecting to my local SQL Server database
  • created a SQL Azure server and database in the cloud using the SQL Azure portal
  • connected to SQL Azure using SQL Server Management Studio and created my database schema using a modified version of my existing DDL script (removing USE statements)
  • used the bcp utility to export tables from my local database to local files and then again to import those up to SQL Azure
  • updated the connection string in my web.config to use the SQL Azure database
  • tested OData access running in the local Azure Development Fabric connecting to the SQL Azure database
  • published by cloud project in Visual Studio, created a Hosted Service on the Windows Azure portal and deployed the application to Azure using the portal
  • tested OData access running in on Windows Azure connecting to the SQL Azure database

So I had an OData service exposing the OCTranspo bus routes, trips, stops and trip stop times – sweet!

When I got to the event at 3:15pm, I met up with Christian, Rick & John Weigelt (Microsoft Canada’s National Technology Officer).  They were looking at the VanGuide application and potential Ottawa data sources to hook up to it as a demonstration.  Demonstrations were due at 4pm.  Of course, as I’ve stated, good data sources were hard to find because the City of Ottawa hadn’t published any official open data sets.

OData service to the rescue…?

We set to work on integrating my sample OCTranspo OData service (which was already running the cloud), into the the Open Data Application Framework, transforming the VanGuide application into an ‘OttGuide’ application.  The framework includes a Silverlight application project that includes dynamic handling of web-based data sources (the dynamic list of landmark sources on the left of the UI) and asynchronous data retrieval and handling code (that takes KML and other format data and adds data points as map points on a Bing Maps Silverlight control).

We wanted to make this Ottawa specific, wo we went ahead and:

  • modified the data source list to show an entry for OCTranpo and removed the other ones
  • removed other Vancouver-specific map region options
  • added custom code to handle OCTranpo GTFS entities in a feed (our OData feed) using Linq to XML to create Landmark instances that the framework already understands
  • made the application zoom in on Ottawa
  • made some other cosmetic changes for Ottawa

The result is the application you see at the start of this blog entry.  For full disclosure, we got the full bus stop retrieval and map point creation working literally 1 minute after the public presentation section finished, but Christian managed to demonstrate the VanGuide version and the OData feed.

The ODAF framework means that an OttGuide application user can select bus stops, rate them, add comments, etc.  It would be relatively easy to add datasets for other ‘landmarks’ (e.g. water foundations, tourist spots, parks and even pot holes).

The same OData service could be used on clients like the new Windows Phone 7 platform to give bus services users valuable travel information :).  Perhaps more on that later…

Open data and open web technologies are current hot topics and OData is a great protocol to bring things together on many platforms.  Silverlight is a great technology to deliver rich user experiences on many platforms using data sources from a variety of platforms. 

Microsoft and Open Source Communities are sponsoring the Make Web Not War conference on May 27th 2010, showcasing the latest cross-platform techniques and technologies, including presentations, panels, workshops, a codefest, the FTW coding competition and a party!  Open Data, HTML 5, PHP, JQuery, Mobile, SEO and the Cloud, are amongst the topics being covered.  Speakers and panelists attending include myself, Microsoft Canada team members and many open source experts.

Make Web Not War


Released Today: Silverlight 4 RTW, Tools RC & Blend 4 Preview Update

Silverlight 4 went RTW (Release to Web) today – you can find it on the site.

What was not widely discussed is the fact that the Silverlight 4 Tools for Visual Studio 2010 release is officially an Release Candidate, not yet officially an RTW quite yet, though may be declared one if no major issues are found.

Scott Guthrie detailed this in the Channel 9 Live broadcast following the Silverlight 4 launch event.  Go to the Silverlight 4 launch video and select the Channel 9 Live On Demand option, then skip ahead to 01:34:30 where he talks about these 3 things.

Quick Tip: Add OData to your Windows Phone 7 CTP Silverlight Application in VS2010 RC

So you downloaded the cool new tools for Windows Phone 7 Development and you want to connect to one of those amazing OData feeds… (okay probably just the NetFlix one right now), but perhaps you’ve hit a snag or need a little help getting started..

First point:

You need OData client support, so:

  • Download the OData client for WP7
  • Run the downloaded file to extract a copy of System.Data.Services.Client.
  • Add a reference to the DLL to your project.
  • Add an imports/using state for System.Data.Services.Client.

Second point:

If you are still using the VS2010 RC (which you probably are as of today, as the current WP7 phone CTP is not compatible with the new VS2010 RTM), then you may have had a problem with “Add Service Reference”, specifically that it’s not appearing…

From the release notes:

The Add Service Reference option is not supported in the Windows Phone add-in to Visual Studio 2010. Workaround: Use Visual Studio 2010 Express for Windows Phone to add service references

So you can open up your project with VS 2010 Express for WP, which should also be on your machine.  However, if you have a solution with other projects, they may not be supported in the Express SKU… life is getting complicated.

So, you can easily add the reference manually:

  • Open the VS2010 command prompt from the Start menu.
  • CD to your project directory
  • Issue a command similar to: datasvcutil /uri: /dataservicecollection /language:CSharp /Version:2.0 /out:NetFlixRef.cs
  • Use Add Existing Item to add in the the proxy class (in this case NetFlixRef.cs). 
  • You then add an imports/using statement for the namespace in the class and off you go…
  • Add code similar to this to get started on the binding…

var Container = new SomeContainer(new Uri("", UriKind.Absolute));

var query = linq query on SomeContainer.SomeEntitySet;

var collection = new DataServiceCollection<SomeProxyEntity>();

someListBox.ItemsSource = collection;


  • … and then bind away in your XAML.

Quick Tip: Setting up OData Support In Silverlight 3

So the shiny new Visual Studio 2010 released yesterday and the final Silverlight 4 releases in 2 days.

What if you are still using Visual Studio 2008 and Silverlight 3 and you want to consume  all the latest OData goodness?

Background on OData

You know what OData is right?

Well first there was ADO.NET Data Services which provides production and consumption of data via a RESTful architecture using HTTP verbs to perform CRUD-like operations.  Client ‘consumer’ and server ‘producer’ classes where included in .NET 3.5 SP1 and client classes where included in Silverlight 3, under System.Data.Services.Client in both cases.

OData at is the evolution of that in the form of a full-on open protocol standard that Microsoft has ‘put out there’ for querying and updating data based on extensions to AtomPub.  Products such as SQL Azure, SharePoint 2010, SQL Server 2008 R2 Reporting Services and Windows Azure Table Storage will expose OData-accessible data and there a growing number of SDKs available on both ends of the communication channel.

For .NET 4.0, ADO.NET Data Services evolves into WCF Data Services (built on WCF) and the associated classes support the OData protocol.  Silverlight 4 will also have WCF Data Services supporting OData.

For .NET 3.5 SP1, the Data Services Update for .NET 3.5 SP1 was made available as a full release.  For Silverlight 3, only a CTP release is available – the ADO.NET Data Services for Silverlight 3 Update CTP3 (eat your heart out long-name marketing folks).

I installed VS2010 yesterday which installed the SL3 toolset (given that SL4 tools don’t come until this Thursday).  I thought it would be good to explain a few things for people that are still using Silverlight 3.

To do anything, you need some OData-accessible data.  Right now, you can go to the Netflix public catalog at and just start exploring the data by tinkering with the URL, following the examples at

OData repositories appear to have a version of 2 and cannot be accessed properly from “V1” ADO.NET Services Clients – you need the WCF Services (OData-compatible) client.

Getting OData Client support in your Silverlight 3 application in VS2008

So for those using Silverlight 3 in VS2008 you can give OData a fairly solid go by downloading that CTP.

To use the update OData friendly libraries, you need to:

  • Extract the System.Data.Services.Client.dll file to somewhere you remember.
  • Add System.Data.Services.Client as a normal reference to your application.
  • Go to the project properties, select the References tab, go into Reference Paths, add the directory that you put your extracted class in to.  As you come out of there, you should see your new path listed for System.Data.Services.Client.

You can now write OData-friendly client code for Silverlight 3 in VS 2008.

Using DataServiceCollection

One of the great new classes is DataServiceCollection.  It enables fully bound updateable observable collection behaviour so you can bind something like a DataGrid to an OData service (with it’s CRUD-like operations).  Think about that 🙂

Using DataServiceCollection requires that the objects you pass in, support INotifyPropertyChanged interface. 

In a Silverilght 3 app in VS2008 (and even VS2010), the Add Service Reference tool (which apparently runs DataSvcUtil.exe from the V3 framework) does not support adding this when it creates the service client proxy.

The .NET 4 version of this tool does do this work for you, so you may want to installed the 4.0 framework and use it like this to get fully-functional binding classes you can use with DataServiceCollection.

%windir%\Microsoft.NET\Framework\v4.0.30319\DataSvcUtil.exe /dataservicecollection /version:2.0 /language:CSharp /out:myproxyclass.vb /uri:http://odata


 odata in silverlight 3

Silverlight 4 Final Release on 15th 2010

Today, during the Day 2 Keynote of the DevConnections event in Las Vegas, Microsoft announced that Silverlight 4’s final release will be on April 15th this week and available to download.

I think many people were hoping for a release today, but oh well…

Today’s live streamed keynote included demonstrations of Silverlight 4’s features and associated productivity enhancements in Visual Studio 2010.

Note that there will also be an ‘RC’ Tools release for Visual Studio and an Expression Blend 4 Preview update.