Search
Close this search box.

SQL Azure, OData, and Windows Phone 7

One of the things I really wanted to do lately was to get SQL Azure, OData, and Windows Phone 7 working together; in essence, expose SQL Azure data using the OData protocol and consume that data on a Windows Mobile Phone 7 device. This blog will explain how to do just that. This example is also in our SQL Azure book in a bit more detail, but with the push for WP7 I thought I’d give a sneak-peak here.

You will first need to download and install a couple of things, the first of which is the OData client Library for Windows Phone 7 Series CTP which is a library for consuming OData feeds on the Windows Phone 7 series devices. This library has many of the same capabilities as the ADO.NET Data Services client for Silverlight. The install will simply extract a few files to the directory of your choosing.

The next item to download is the Windows Phone Developer Tools, which installs the Visual Studio Windows Phone application templates and associated components. These tools provide integrated Visual Studio design and testing for your Windows Phone 7 applications.

Our goal is to enable OData on a SQL Azure database so that we can expose our data and make it available for the Windows Phone 7 application to consume. OData is a REST-based protocol which standarizes the querying and updating of data over the Web. The first step then is to enable OData on the SQL Azure database by going into the SQL Azure Labs site and enabling OData. You will be required to log in with your Windows Live account, then once in the SQL Azure Labs portal select the SQL Azure OData Service tab. As the home page states, SQL Azure Labs is in Developer Preview.

The key here is the URI at the bottom of the page in the User Mapping section. I’ll blog another time on what the User Mapping is, but for now, highlight and copy the URI to the clipboard. You’ll be using it later.

Once OData is enabled on the selected SQL Azure database, you are ready to start building the Windows Phone application. In Visual Studio 2010, you will notice new installed templates for the Windows Phone in the New Project dialog. For this example, select the Windows Phone Application.

Once the project is created, you will need to add a reference to the OData Client Library installed earlier. Browse to the directory to which you extracted the OData Client Library and add the System.Data.Services.Client.dll library.

The next step is to create the necessary proxy classes that are needed to access a data service from a .NET Framework client application. The proxy classes can be generated by using the DataSvcUtil tool, a command-line tool that consumes an OData feed and generates the client data service classes. Use the following image as an example to generate the appropriate data service classes. Notice the /uri: paramter. This is the same URI listed in the first image above, and what the DataSvcUtil will use to generate the necessary proxy classes.

Once the proxy class is generated, add it to the project. Next, add a new class to your project and add the following namespaces which provide addtional functionality needed to query the OData source and work with collections.

using System.Linq;
using System.ComponentModel;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Windows.Data;
using TechBioModel;
using System.Data.Services.Client;
using System.Collections.ObjectModel;

Next, add the following code to the class. The LoadData method first initializes a new TechBio instance of the proxy generated class, passing the URI to the OData service to call out to the service. A LINQ query is used to pull the data you want and the results loaded into the Docs DataServiceCollection.

public class classname {
  public TechBioModel() {
    LoadData();
  }

  void LoadData() {
    TechBio context = new TechBio(new Uri(
        "https://odata.sqlazurelabs.com/OData.svc/v0.1/servername/TechBio"));

    var qry = from u in context
                  .Docs
              where u.AuthorId == 113 select u;

    var dsQry = (DataServiceQuery<Doc>)qry;

    dsQry.BeginExecute(r => {
      try {
        var result = dsQry.EndExecute(r);
        if (result != null) {
          Deployment.Current.Dispatcher.BeginInvoke(
              () => { Docs.Load(result); });
        }
      } catch (Exception ex) {
        MessageBox.Show(ex.Message.ToString());
      }
    }, null);
  }

  DataServiceCollection<Doc> _docs = new DataServiceCollection<Doc>();

  public DataServiceCollection<Doc> Docs {
    get { return _docs; }
  private
    set { _docs = value; }
  }
}

I learned from a Shawn Wildermuth blog post that the reason you need to use the Dispatcher is that this call is not guaranteed to be executed on the UI thread so the Dispatcher is required to ensure the this call is executed on the UI thread. Next, add the following code to the App.xaml. This will get called by the load of the phone with the application starts.

private static TechBioModel tbModel = null;
public static TechBioModel TBModel {
  get {
    if (tbModel == null)
      tbModel = new TechBioModel();

    return tbModel;
  }
}

To call the code above, add the following code to the OnNavigatedTo event of the phone iteslf (the MainPage constructor)

protected override void OnNavigatedTo(
    System.Windows.Navigation.NavigationEventArgs e) {
  base.OnNavigatedTo(e);

  if (DataContext == null)
    DataContext = App.TBModel;
}

Lastly, you need to go to the UI of the phone and add a ListBox and then tell the ListBox where to get its data. Here we are binding the ListBox to the Docs DataServiceCollection.

<ListBox Height="611" HorizontalAlignment="Left" Name="listBox1"
        VerticalAlignment="Top" Width="474"
        ItemsSource="{Binding Docs}" >

You are now ready to test. Run the project and when the project is deployed to the phone and run, data from the SQL Azure database is queried and displayed on the phone.

In this example you saw an simple example of how to consume an OData feed on on Windows Phone 7 application that gets its data from a SQL Azure database.

This article is part of the GWB Archives. Original Author: Scott Klein

Related Posts