Using Windows Azure SQL Storage to Store Windows Phone Data

Introduction

Windows® Azure™ Mobile Service is an Azure service offering designed to provide cloud based backend data source capabilities for your windows applications. By default the mobile Services instance in Azure uses Azure SQL Database to store data. Azure SQL Database is a relational database service that extends core SQL Server features to the cloud.

In addition to data store, Mobile Services provide a turnkey way to authenticate users and send push notifications. With SDKs for Windows, Android, iOS, and HTML as well as a powerful and flexible REST API, Mobile Services lets you to build connected applications for any platform and delivers a consistent experience across devices. In this article, you will learn how to use mobile services to fetch and save Windows Phone 8 app data on the cloud.

Scenario

You are going to create a windows phone application that can save user profile information in Azure SQL database tables.

Prerequisites

1. Windows Account to access Windows Azure (Free)

2. Windows Azure account (At least Free Trial version)

3. Visual Studio 2013

4. Windows 8 system or VM

5. Windows Phone 8 SDK

Creating a Windows Phone 8 Sample Application

This application will consume Mobile Service as backend. For this demo, I have created a sample app called “ProfileManager”. You can download it from here: https://github.com/manoj-kumar1/Azure-MobSvc-As-Backend-For-SQL-DB

The Profile Manger app has the following screens to create and view user’s profiles.

Profile Manager
Profile Manager

Creating Mobile Service in Azure

You can create a mobile service in Windows Azure by following these steps:

1. Login to https://manage.windowsazure.com/

2. Select  “+New” at bottom

3. From menu select mobile service -> Create

Create Mobile Service
Create Mobile Service

4. Give a proper name to mobile service and select new or existing database instance:

New Mobile Service
New Mobile Service

5. Mobile service uses Azure SQL Database, so when you create a mobile service a new database will be created. You can create a new database or use any existing one. In this demo, I am going to create a new database along with its server.

New Mobile Service
New Mobile Service

6. In this way a new mobile service with the name “ProfileMobileSvc” will be created.

7. Create the required tables in this new “ProfileMobileSvc_Db” database.

  • Open mobile service “ProfileMobileSvc”.
  • Go to the Data tab, and select “Add a table” to add “UserProfile” as new table.
  • Open the table and add more columns: Name, Email, and Phone.

Now you are ready to use this mobile service as a backend in the Windows Phone 8 app “ProfileManager”.

Updating the App to Access Mobile Service in a Windows Phone 8 App

You need to update the phone client app to add WindowsAzure.MobileServices package and then add code to access Azure mobile services. You can follow these steps:

1. Search for “WindowsAzure.MobileServices” in Nuget and add the “Windows Azure Mobile Services” package to ProfileManager project.

2. Update App in App.xaml.cs to add the following (Key is from newly created Azure Mobile Service “ProfileMobileSvc” details for existing app):

using Microsoft.WindowsAzure.MobileServices;

public static MobileServiceClient MobileService = new MobileServiceClient(
            "https://profilemobilesvc.azure-mobile.net/",
               "<xxxxxxxxxxxxxxxxxxxxxxxxxx>");    //Put key of your mobile service

3. Add a new folder “Model” to the project, and then add the following class. This class represents the table in the cloud, which you created while configuring the mobile service database.

using Newtonsoft.Json;
 
namespace ProfileManager.Model
{
              public class UserProfile
              {
                     [JsonProperty(PropertyName = "id")]
              	   public string Id { get; set; }
 
                     [JsonProperty(PropertyName="name")]
                     public string Name { get; set; }
 
                     [JsonProperty(PropertyName = "email")]
                     public string Email { get; set; }
 
                     [JsonProperty(PropertyName = "phone")]
         public string Phone { get; set; }
}
}

4. Update MainViewModel add the following field:

private IMobileServiceTable<UserProfile> userProfileTbl = App.MobileService.GetTable<UserProfile>();

Change the Items property to the following:

private ObservableCollection<ItemViewModel> items;
public ObservableCollection<ItemViewModel> Items {
            get { return items; }
            private set { items = value; NotifyPropertyChanged("Items"); }
}

Change the LoadData method to the following (It is not asynchronous method):

items.Clear();
public async void LoadData()
        {
            items.Clear();
            // Sample data; replace with real data
            var azureData = true;
            MobileServiceCollection<UserProfile, UserProfile> profiles;
            if (azureData)
            {
                profiles = await userProfileTbl.ToCollectionAsync();
                foreach (var profile in profiles)
                {
                    items.Add(new ItemViewModel() { ID = profile.Id.ToString(), Name = profile.Name, Email = profile.Email, Phone = profile.Phone });
                }
            }
            else
            {

            items.Add(new ItemViewModel() { ID = "0", Name = "Manoj Kumar", Email = "manoj@gmail.com", Phone = "172890567" });
            }
            Items = items;
 
            this.IsDataLoaded = true;
  }

 

5. Update EditPage to save a new user profile to Azure using mobile service. Add field:

private IMobileServiceTable<UserProfile> userProfileTbl = App.MobileService.GetTable<UserProfile>();

Update save method to the following:

private async void saveBtn_Click(object sender, RoutedEventArgs e)
       {
              await userProfileTbl.InsertAsync(new UserProfile() { Name=txtName.Text, Phone=txtPhone.Text, Email=txtEmail.Text});
              App.ViewModel.LoadData();
              NavigationService.Navigate(new Uri("/MainPage.xaml", UriKind.Relative));
}

6. Build and run the application.

7. Click “Add User” and add a new user profile.

8. Verify that user is listed in the home screen.

9. Verify that the user has been added into the “UserProfile” table in Azure through Mobile Service “ProfileMobileSvc”.

 User Profile
User Profile

Profile

Some General Guidelines and Limitations of Windows Azure SQL Database

  • Only TCP/IP connections are allowed.
  • Windows Azure SQL Database does not support SQL Server Agent or jobs.
  • Windows Azure SQL Database may not preserve the uncommitted timestamp values of the current database (DBTS) across failovers.
  • Windows Azure SQL Database does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.
  • By default, Windows Azure SQL Database supports up to 150 databases in each SQL Database server, including the master database.
  • Windows Azure SQL Database provides two database editions: Web Edition and Business Edition. Web Edition databases can grow up to a size of 5 GB and Business Edition databases can grow up to a size of 150 GB.
  • Features of 2008 R2 or earlier versions are not supported in Azure SQL: SQL Server Utility, SQL Server PowerShell Provider, Master Data Services, Data Auditing, Data Compression, Policy-Based Management, Backup and Restore, Replication, SQL Server Agent/Jobs, Extended Stored Procedures, Service Broker, Database Mirroring, Table Partitioning, (CLR) and CLR User-Defined Types.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read