Scott Klein

  Home  |   Contact  |   Syndication    |   Login
  40 Posts | 0 Stories | 32 Comments | 0 Trackbacks

News

Twitter












Tag Cloud


Archives

Post Categories

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.

 

posted on Wednesday, September 29, 2010 8:41 PM

Feedback

# re: SQL Azure, OData, and Windows Phone 7 9/30/2010 9:23 AM Catto
Hey Now Scott,

Great Post! using System.Data.Services.Client;

Thx 4 the info,
Catto

# re: SQL Azure, OData, and Windows Phone 7 10/1/2010 5:36 PM Hari
Great Post! Thanks a lot. However I am tryin to update/modify the azure database (not consume).

I am trying to use SQL Azure with ODATA on the windows phone 7 platform. However I am having problems in updating/adding/deleting the database via DataServiceQuery. Can someone help me with this with sample code. Thanks a lot!

Note that the SubmitChange function cannot be called on the phone since the System.Data.Linq dll is not available for devices.

# re: SQL Azure, OData, and Windows Phone 7 10/9/2010 1:08 AM checkpoint firewall audit
Microsoft® SQL Azure™ (formerly SQL Server Data Services and later SQL Services) Database is a cloud-based relational database service built on Microsoft SQL Server® technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database helps to ease provisioning and deployment of multiple databases. Developers do not have to install, setup, patch or manage any software. High availability and fault tolerance is built-in and no physical administration is required. SQL Azure Database supports Transact-SQL (T-SQL). Customers can use existing knowledge in T-SQL development and a familiar relational data model for symmetry with existing on-premises databases. The maximum size of SQL Azure database currently is 50 GB.

# re: SQL Azure, OData, and Windows Phone 7 10/9/2010 8:34 AM Scott
Hari, adding/updating/deleting shouldn't be a problem. Can you post your code and I'll take a look?

# re: SQL Azure, OData, and Windows Phone 7 10/14/2010 6:53 AM Ralph Squillace
Dear Scott, hello! Anyone using the odata library on Windows Phone 7 should read this for the RTM version: http://blogs.msdn.com/b/astoriateam/. As it is about the middle of the month, in theory the work should begin appearing but I thought I'd make a note of it. :-)

Cheers, Ralph

# re: SQL Azure, OData, and Windows Phone 7 4/3/2011 7:49 PM Joe Healy
Note the sql azure team reblogged this - nice job - send it to your mvp lead re the reblog - http://geekswithblogs.net/ScottKlein/archive/2010/09/29/sql-azure-odata-and-windows-phone-7.aspx

# re: SQL Azure, OData, and Windows Phone 7 7/22/2011 5:52 AM Martin
Great code man. Is it possible that i get a copy of a working project for this explaination?

# re: SQL Azure, OData, and Windows Phone 7 10/29/2011 4:32 AM nfl jersey supply
s per game while averaging a career-high 18 If you purchase one of these wholesale jerseys, you will be particular you will be pleased with the types of materials as we

# re: SQL Azure, OData, and Windows Phone 7 1/5/2012 3:57 AM mlb jerseys cheap
Jerseys are not able to cover up your design and represents your persona from just about every doable angle of reflection. The worth of jersey has been regarded as a regular living in the unique pleasure. Popular mlb jerseys cheap gives together with the freedom and liberation that means alternative. The classical jersey will normally attack roll sport trend and certainly not be absent.

# re: SQL Azure, OData, and Windows Phone 7 1/9/2012 4:46 AM adidas porsche design
I am extremely pleased to find this site.I wanted to say thanks to you with regard to this great read!! I certainly enjoyed every little bit of it and I've you bookmarked to look at new stuff you publish. Keep updating. Thank you very much.

# re: SQL Azure, OData, and Windows Phone 7 7/5/2012 1:55 AM asos
There are some attention-grabbing points in time on this article but I don?t know if I see all of them heart to heart. There may be some validity however I will take maintain opinion until I look into it further. Good article , thanks and we want more! Added to FeedBurner as properly.

# re: SQL Azure, OData, and Windows Phone 7 12/21/2012 9:48 PM Gowtham G
Really a nice post..

I didnt try this yet.. but thanks in advance...!


# re: SQL Azure, OData, and Windows Phone 7 10/3/2013 4:41 AM Adam
Deprecated :(

Post A Comment
Title:
Name:
Email:
Comment:
Verification: