WP7: Say hello to OData from SQLAzure

Let’s face it. Windows Azure & Windows Phone 7 are two of the hottest products out of Redmond for now. After dabbling around for a couple of days and reading some great blog posts, I felt somewhat confident to try making WP7 work with Azure. And the dumb me had to bring things down to the lowest simplicity to understand how stuff works; so that’s what I shall be sharing today. Also, I am mostly a dork with Azure; I am trying to learn with the bootcamp materials in spare time. So, huge shout-out to Azure MVP Michael Collier (@MichaleCollier) for helping me around Azure.

OData“(Open Data Protocol) seems to be an extremely versatile open standard for sharing & updating data. The seamless exposition of pure data over HTTP through Atom/Jason has to be exciting, as are the filtering & pagination options. Head over here to learn more about the OData standard, producers, consumers and what it means for developers. Now, OData can act as the perfect bridge between Azure cloud data & a client application on the Windows Phone, as we shall see. The needed steps to try this out first hand are laid down in the points below:

  • First, we want to get access to Azure. There are free trials you can sign up for here or reach out to one of the Azure MVPs for an Azure Pass. The tokens might take a day or two to activate; but the free instances/storage should be plenty for trying out things.
  • Next, we focus on data. If you have any SQL Server data source, it can be exposed through an OData feed for reads/updates. Michael Crump(@mbcrump) wrote a  brilliantly detailed post here on how to set up an OData source.
  • However, if you intend to move your relational data up onto the cloud, you are in for a pleasant surprise. SQL Azure, I think, makes it super easy to handle Databases and table relationships. And, once your data is in SQL Azure, OData feeds should come free with one-click of a checkbox! Really neat!
  • So, once we have our Azure access set up, we head over to the Silverlight Azure portal here. We make sure our Subscriptions are active and that we are not consuming beyond our quota. Next, we click on the “Database” tab, select the appropriate Subscription & create a new “SQL Azure Server“. Please note, as pointed out by @MichaelCollier, this is not a SQL Server; rather an Azure endpoint with infrastructure hidden under the covers. Setting up the SQL Azure server involves setting up your Admin rights, DataCenter region & firewall settings, so you get to control which IPs can hit the server. Next, we proceed to create an actual DB within that SQLAzure server; this & a host of other DB management tasks are easily accomplished with the toolbars at the top. Once in DB management mode, it is incredibly easy to manage tables, their schema & actual data in the tables.  Hopefully the illustrations below capture what you see & will need to do in order to get your DB set up.



Now, for this demo, we will simply use the Web UI to create a DB called “Demo” and within it, create a table called “Team” with three simply data columns. We also, for the sake of the demo, fill up the table with some data. Obviously, this is not the way one would get data into the SQL Azure tables; but we are just trying to prove a point. And yes, I am pretending that we are in the same team as Scott … bwa ha ha!

  • So, now we have our data source. What if we wanted to share the data as an OData feed for consumption? No worries! We head over to SQL Azure Labs here and sign in with the same Live ID that is tied to the Azure Pass that was used previously to host the DB. Next, we pick the appropriate SQL Azure server & the Database. Do you notice a little checkbox below the DB selection? Aha, that, my friend, is all you need to do to share the DB contents as OData! The following illustration shows the SQL Azure Labs screen with the OData option.


We need to make sure the DB can be targeted by any user without authentication, for demo purposes. This setting gets us a unique public URI, the OData doorway to your SQL Azure data! Make sure to take a note of the URL (after checking it works in the browser) as we shall need it in the Windows Phone app. That’s it, we are done in Azure!

  • Our OData feed is at the URL: https://odata.sqlazurelabs.com/OData.svc/v0.1/<server_name>/Demo. Please note that the URL goes only upto the DB name; we shall drill down further in code.

Now that we have an OData feed out of Azure, the WP7 consumption part is easy and has been blogged about in several posts. Essentially, the Add Service Reference part of consuming the OData endpoint is broken; so we have to revert to a few interim steps.

  • We head over here and download the binaries of the “OData Client Library for WP7“. This essentially abstracts out from you having to parse the OData Atom/XML feed to hydrate your objects; it uses sophisticated data bindings/annotations/reflections to build you a “DataServiceCollection” (read ObservableCollection) that you can easily bind to Silverlight UI elements.
  • The downloaded files provide for a “DataSvcUtil.exe” file that you could point to an OData feed & it will build the proxy classes for you. So, next we fire up a Command prompt, navigate to where we downloaded the libraries, and use the following command:

DataSvcUtil.exe /uri:https://odata.sqlazurelabs.com/OData.svc/v0.1/server_name/Demo /out:TeamModel.cs /Version:2.0 /DataServiceCollection
  • The URI supplied is the same URI we got out of our Azure feed. The out parameter names the proxy file to be created .. cs for C# & vb for VB. The DataServiceCollection option is also important as it sets up your object mappings from the OData data store & also implements the “INotifyPropertyChanged” interface on them. This is what the Observable Collections use for effortless UI binding; just change the underlying object & the UI updates itself. The above command should have created a C# file for us to use in the mentioned directory.
  • Next, we fire up a simple Windows Phone project; again keeping things really simple here to illustrate the point of OData consumption. We need to add a Reference to the “System.Data.Services.Client.dll” found in the OData library directory & also add the generated cs file to our solution.
  • For simplicity, we use the default MainPage.xaml page and do a few tricks while the page loads. Again, placement of code is questionable; but just for the demo, we add this to the Xaml’s code file.

       #region "Data Members"

        readonly DataServiceContext context;
        DataServiceCollection team;


        #region "Constructor"

        public MainPage()

            // Instantiate the context.
            context = new DataServiceContext(new Uri("https://odata.sqlazurelabs.com/OData.svc/v0.1/server_name/Demo"));


        #region "Methods"

        public void LoadTeam()
            // Instantiate Team collection.
            team = new DataServiceCollection(context);

            // Custom URI, filters possible.
            Uri uriQuery = new Uri("/Teams()", UriKind.Relative);

            // Asynchronously load the result of the query.

            team.LoadCompleted += (sender, args) =>
                if (args.Error != null)
                    // Oopsie.
                    // Success.
                    this.teamList.ItemsSource = team;


  • Essentially, we create a context using the class from the generated file & point it to the URI of our OData service. We then fire off an asynchronous call to fetch data on a custom URI off the base OData feed. Please note that this is where you can get fancy and use filters or other where clauses to get to exactly the data you need. If hell doesn’t break lose, we should get our feed back and it should hydrate our strongly-typed DataServiceCollection! The Xaml code follows; please note that with the use of a ViewModel pattern as we do for Panorama/Pivot pages, most of this object hydration should happen when the ViewModels are initialized & then UI binding should be automatic. This is what I had whipped up quick; also no fancy progress bars for the demo:

















And, the XAML for binding is this:

          <ListBox Name="teamList" Margin="0,0,-12,0" ItemsSource="{Binding Teams}">
                        <StackPanel Margin="10,0,0,25" Width="432">
                            <TextBlock Text="{Binding Name}" TextWrapping="Wrap" Style="{StaticResource PhoneTextExtraLargeStyle}" Foreground="Red"/>
                            <TextBlock Text="{Binding TwitterHandle}" Foreground="Blue" Margin="20,0,0,0" />

As you can see, our Listbox simply binds to each Team member object & then drills down to display the Name & TwitterHandle. Simple, effective pull of OData from Sql Azure. Wasn’ t this fun?

I think I have a few more demos of using Push Notifications from the cloud, that I will try to blog about eventually. In the meantime, any comments on the above are most welcome. Tell me what I am doing wrong or what could be improved!



4 thoughts on “WP7: Say hello to OData from SQLAzure

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s