Fetching Data from SQL Azure table in Windows 7.1 [Mango] Phone using WCF Data Service

In this article, we will discuss about how to consume data from cloud [SQL Azure] in a Windows 7.1 [Mango] phone. SQL Azure and Windows 7.1 [Mango] phones are two common terms that you can hear about from many tech-savvy people. So I thought it is the right time to integrate these technologies. In this article, I have tried my best not to focus on the theoretical aspects. Instead, I will present a step-by-step approach on how to consume Data from cloud in a Windows 7 phone application.

What we intend to achieve is given below:



In this article, we will cover

  1. Migrating local Database to SQL Azure
  2. Creating Data Model on Database in Cloud
  3. Expose Data Model as WCF Data Service
  4. Consumption of Data from cloud in Windows 7.1 or Mango phone through WCF Data Service.


The block diagram for the same is given below. We have a School Database on our local (In premise) SQL Server. We will be moving the local school database along with the schema and data to SQL Azure. Then, we will create a Data Model on the school database in the cloud and expose that Data model as WCF Data Service. Once the data from cloud is exposed as WCF Data Service, we will fetch that in the Windows 7.1 phone



So, actually, there are two major steps involved,

  1. Exposing Data from cloud as WCF Data Service
  2. Consuming WCF Data Service in Windows 7 Phone


Creating local Database and migrating to SQL Azure

Creating Database

The first step is to create the database. We are going to use School database. Script of sample School Database copy from here

Generate Database Script for SQL Azure

Right click on School Database and select Tasks. From Tasks, select Generate Script.



From the Pop up, select Set Scripting option.



Give the file name by selecting Save to file option.

Now the main thing to be noticed here is that we need to so some change in the advanced setting. For that, click on advanced options.




And in types of data to script, select Script and Data – both options.



After that, click next and Finish. You can see that a SQL file is created, and we will be using this script to migrate our in-house school database to SQL Azure.


Create School Database in SQL Azure

Login the SQL Azure portal with your live credential


SQL Azure can be explored by selecting Database option from the left panel.


To create School Database, select Data base server and from Database section, select Create option.


Provide Database name, Edition and Maximum Size.



Run the Script in SQL Azure

Open the SQL Server management studio


You will get Connect to server dialog box. Click on cancel.


After cancelling the dialog box, click on New Query (top left corner).


On clicking New Query, the Connect to Server dialog box appears again.


Now, here you need to provide the Server name of SQL Azure and Login credential of SQL Azure

You can get fully qualified server name in Properties tab of SQL Azure portal


You will get the server name in form of


where abc is name of your SQL Azure server. We need to provide this server name at the local SQL server management studio.


Make sure to select SQL Server Authentication and provide login user name and password of your SQL Azure database portal.

After that, before clicking Connect, click on Option


From Option, select School database.


Run the Script

Now once you have successfully connected to School Database in SQL Azure, copy the script and Run as given below.




After successfully run script, run the below command and all the tables name will get listed.



In this way, you have successfully migrated database to SQL AZURE Smile


Exposing SQL Azure Database as WCF DATA Service


Create a Web Application

Create a new project and select ASP.Net Web Application project template from the Web tab. Give a meaningful name to the web application.


Create a Data Model

We can create a Data Model, which can be exposed as WCF Data Service in three ways

  1. Using ADO.Net Entity model.
  2. Using LINQ to SQL class.
  3. Custom Data Model.

Here, I am going to use ADO.Net Entity model to create the data model. So to create an entity model, do the following:

  1. Right click on web application and add a new item
  2. Select ADO.Net Entity model from Data tab.


3. Since we have table in SQL Azure DataBase, we are going to choose the option Select from database



4. Choose a new connection.


After clicking on New Connection, this is the important step. We need to give extra care here Smile

So provide the information as below,


Click on Test Connection to test if the connection established successfully or not? After this, you will get prompted if the Connection string contains sensitive data regarding if you want to keep that in configuration file or mange through program. You can select any one of these options according to what you need.


On providing your option, click on Next button; you can see that all the tables, Views, and Stored Procedures’ are available to be chosen as a part of the data model for WCF Data Service.


5. Select tables, views and stored procedure from the database you want to make as the part of your data model.

Creating WCF Data Service

  1. Right click on Web Application project and add a new item.
  2. Select WCF Data Service from Web tab. Give any meaningful name. I am leaving the default name here.


3. After adding the WCF Data Service, we can see a service file with extension .svc has been added to the solution explorer.

Very first, we need to put data source name. To do so, uncomment the first commented line and put the data source name. In our case, the name of the model which we created in the 2nd step is the data source. Our data model name is SchoolEntities




Now we need to set access rules for the entity or entity set. Since we have only one table; so either we can give name of the table explicitly or if we want to set the same access rule for all the table in the data model or data source, we can put *.






So we are setting the access rule that, on the entity in data source perform all the operations.

So finally, the svc file will look like



Run WCF Data Service

Just press F5 to run the WCF Data Service. The Data Service will be hosted in the default ASP.Net server.

On running, you can see all the table is listed there.


Append the URL with People and you will get all the records from People table


Note: If your browser is not showing the expected result, make sure that the Feed reading of the browser is off. To do so, from the menu of IE, select Tools-> Internet Options -> Content



So we have exposed data from cloud using WCF Data service. Now any OADTA client can consume the data from cloud by consuming WCF Data Service.  Smile


Consuming WCF Data Service in Windows 7 Phone

First, we need to do is to create proxy of WCF Data service for Windows 7 phone. So to do this,

  1. Open command prompt in administrator mode.
  2. Navigate to folder C:\Windows\Microsoft.Net\FrameWork\V4.0.30319 Version of framework may vary depending on .Net framework you are working on.
  3. Once navigated to this particular folder, run the below command to create proxy class. We will add this proxy class in Windows 7 mobile application.



Explanation of command

  • There is space between all /out, /uri and /version
  • We are redirecting the output to D drive and Temp folder. So make sure that the Temp folder exists in the D drive.
  • School.cs is name of proxy class. You can give any name of your choice here. Once the command runs successfully, you will get School.cs class in Temp folder of D drive.
  •  After uri tag, give the uri of WCF data service.


Now after the execution of this command, we will have School.cs in Temp folder of D Drive.  Smile

Download the ODATA client library for Windows 7 phone from the below link and save the dll on local drive. We will add this DLL in Windows 7 phone application.

Download OData client library for Windows 7 Phone from here

Create a windows 7.1 phone application. Open Visual Studio and select the Windows phone Application project template from Silverlight for Windows Phone tab.



Choose  Windows Phone 7.1


Right click on the project and add existing item. Then navigate to Temp folder where you have created the WCF Data Service proxy class. In our case, it is School.cs. We created this in the previous step.


Now we need to add ODATA client library reference in the Windows 7 Phone application. Right click on the project and add reference. Navigate to the folder where you saved System.Data.Service.Clientdll


Display Data from Person Table in Windows 7 Phone application

We are going to display records of Person table in the list box. So first, let us create an entity class at client side. Right click and add a class called Person



namespace PhoneClient
    public class Person

        public string PersonID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }



Create a list box and we will bind the data in this list box. On click event of the Button, Data will get bound to the list box.

In below XAML ,

  1. We are creating a Button . On click event of Button , Data would be fetched from SQL Azure table.
  2. In List Box , we are binding the returned result




    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"

    <!--LayoutRoot is the root grid where all page content is placed-->
    <Grid x:Name="LayoutRoot" Background="Transparent">
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>

        <!--TitlePanel contains the name of the application and page title-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
            <TextBlock x:Name="ApplicationTitle" Text="Mango with SQL Azure" Style="{StaticResource PhoneTextNormalStyle}"/>

            <Button x:Name="btnGetData" Height="100" Width="449" Content="Get Data From SQL Azure Table" Background=" blue" />

        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">

            <ListBox x:Name="lstDataFromCloud"
                 ItemsSource="{Binding}" >
                        <StackPanel Orientation="Horizontal" >
                            <TextBlock Text="{Binding PersonID}" Margin="20" />
                            <TextBlock Text="{Binding LastName}" Margin="20" />
                            <TextBlock Text="{Binding FirstName }" Margin="20" />





Now on click event of the button, we need to call the WCF Data Service and bind the list box . We are making an asynchronous call here .


using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using Microsoft.Phone.Controls;
using System.Data.Services.Client;
using SchoolModel;

namespace PhoneClient
    public partial class MainPage : PhoneApplicationPage
        // Constructor
        SchoolEntities schoolEntity;
        List<Person> lstPersonClient;
        List<SchoolModel.Person> lstpersonWcf;

        public MainPage()
            schoolEntity = new SchoolEntities(new Uri("http://localhost:27274/WcfDataService1.svc/"));
            btnGetData.Click += new RoutedEventHandler(btnGetData_Click);

            lstPersonClient = new List<Person>();
            lstpersonWcf = new List<SchoolModel.Person>();


        void btnGetData_Click(object sender, RoutedEventArgs e)


            var query = schoolEntity.CreateQuery<SchoolModel.Person>("People");
            query.BeginExecute(ar =>
            }, query);


                private void DataLoad(IAsyncResult result)
            DataServiceQuery<SchoolModel.Person> query = result.AsyncState as DataServiceQuery<SchoolModel.Person>;
            lstpersonWcf = query.EndExecute(result).ToList();
            Dispatcher.BeginInvoke(() =>
                       foreach (var r in lstpersonWcf)
                           lstPersonClient.Add(new Person {PersonID = r.PersonID.ToString() , LastName= r.LastName , FirstName = r.FirstName});
                       this.lstDataFromCloud.DataContext = lstPersonClient ;



On running you should get output as below,  Smile



If this post was useful to you then buy me a coffee Smile .  Thanks for reading.



Billing in SQL Azure

1. Web edition databases are billed at the 1GB rate for databases below 1GB of total data or at 5GB rate for databases between 1GB and 5GB size.

2. Business edition databases are billed at 10GB increments (10GB, 20, 30, 40 and 50GB).

In a particular day billing is done on the pick DB size. To understand billing better let us take an example, suppose there is Web Edition DB with maximum size 5 GB. On a particular day if database usages are 800MB then you will be charged for 1 GB on that day. If next day DB usage increases to 2 GB then you would be charge for 5 GB. In Business edition charging window is of 10 GB.

SQL Azure introduces two dynamic management views database_usage and bandwidth_usage. These views can be used in TSQL for billing and bandwidth information.


If you want to calculate pricing for any particular type of Database edition probably you would have to do simple mathematical calculations.

Let us say,

R = Monthly Rate of Data base edition. Monthly rate for both web and business edition is different.

D = Number of Day in Month

N = Number of Databases

U = Unit consumption in a given day

Price for Month = (R /D)*U*N

So if

R = 10 $ per month

D = 30 days

N = 1 number of Database

U = 5 unit

Price for Day = (10/30)*1*4 = 1.34 $

TSQL to get current month prices



select sku,
    sum (
    case when sys.database_usage.sku = 'web'
            then (quantity*10/31)
        when sys.database_usage.sku = 'business'
            then (quantity*100/31)
    end ) as 'cost'
    from sys.database_usage
    where datepart(yy,time) = datepart(yy, getutcdate()) and
    datepart(mm,time) = datepart(mm, getutcdate())
    group by sku

In above SQL query

1. 100 $ is monthly charge for business edition and 10 $ is for Web Edition [Assumption]

2. Number of Days is 31

3. Query is running against Master Database.

If you want to list out different units with their edition below query can do the task


Above query will return time, edition and quantity.

select time,sku,quantity   from sys.database_usage    where datepart(yy,time) = datepart(yy, getutcdate()) and   datepart(mm,time) = datepart(mm, getutcdate())

Different Data Base Editions in SQL Azure

There are two Data Base Editions in SQL Azure.


Web Edition Relational Database includes:

  1. · Up to 5 GB of T-SQL based relational database*
  2. · Self-managed DB, auto high availability and fault tolerance
  3. · Support existing tools like Visual Studio, SSMS, SSIS, BCP
  4. · Best suited for Web application, Departmental custom apps

Business Edition DB includes:

  1. · Up to 50 GB of T-SQL based relational database*
  2. · Self-managed DB, auto high availability and fault tolerance
  3. · Additional features in the future like auto-partition, CLR, fanouts etc
  4. · Support existing tools like Visual Studio, SSMS, SSIS, BCP
  5. · Best suited for Saas ISV apps, custom Web application, Departmental apps

While creating Database in SQL Azure, we can choose either of two options


If we choose Web Edition then Maximum size we can choose is 5 GB


If we choose Business Edition then Maximum size we can choose is 50 GB


Billing scheme for both edition is different. 

  1. 1. Web edition databases are billed at the 1GB rate for databases below 1GB of total data or at 5GB rate for databases between 1GB and 5GB size.
  2. 2. Business edition databases are billed at 10GB increments (10GB, 20, 30, 40 and 50GB).

In a particular day billing is done on the pick DB size. To understand billing better let us take an example, suppose there is Web Edition DB with maximum size 5 GB. On a particular day if database usages are 800MB then you will be charged for 1 GB on that day. If next day DB usage increases to 2 GB then you would be charge for 5 GB. Whereas in Business edition charging window is of 10 GB.

SQL Azure Architecture

SQL Azure resides in Microsoft Data center provides relational database to application with four layer of abstraction.

Four layers of abstraction can be depicted as below,


Client Layer

This layer is closest to the application. SQL Azure connects with client application with Tabular Data Stream interface in exactly the same way SQL Server does.


Client layer can be ranging from a managed .NET application to PHP application. It could be a SilverLight web application or WCF Data Service. Client layers connect with other layers using Tabular Data Stream.


Client layer may be in client data center [On premise] or it may be in Data center of service provider [Cloud].

Service Layer


Service layer performs task of provisioning of database to the user cross ponding to Azure account. This mainly performs

a. Provisioning of Database to a Azure account

b. Billing of the usage

c. It performs the task of connection routing between client applications from the client layer to the actual physical SQL server in Platform layer.

Platform Layer

Physically SQL Server resides in this layer. There could be many instances of SQL server.


SQL Azure Fabric manages instance of SQL Server.

Task performed by SQL Azure Fabric

1. It enables automatic failover

2. It does load balancing

3. It does automatic replication between physical servers.


Each instance of SQL Server is individually managed by SQL Azure Fabric.

Management Services performs below tasks,

1. Monitoring health of individual servers.

2. Installing patch up

3. Upgrading services

4. Automated installations

Infrastructure Layer

In this layer IT level administration tasks being performed. Physical hardware and operating systems is being administered in this layer.