Windows Phone 7 Development : Using Cloud Services As Data Stores - Creating a Cloud Database

1/17/2011 9:07:05 AM
The first step is to create an SQL Azure database to store the notes a user creates with this application. Think of SQL Azure as a hosted database in the cloud where you do not have to worry about the infrastructure. With your familiar knowledge in working with a Microsoft SQL server, you will be able to work in SQL Azure. NotepadService, which you will be creating in the next section, will connect to this database by using Entity Framework to create, read, update, and delete records from the database.


The Entity Framework is an object-relational mapping (ORM) tool that allows you to generate objects based on the tables in a database, taking care of the interaction to the database that otherwise you have to code yourself; the Entity Framework will save you lots time.

1. Creating an SQL Azure Database

You will be creating a database in SQL Azure in order perform create, read, update, and delete operations for the Notepad application.

1.1. Signing Up for SQL Azure

You will learn to create an SQL Azure account in the following steps.

  1. Open a browser of your choice.

  2. Go to to sign up and buy the Windows Azure service account. Follow the direction provided by Microsoft in order to purchase and acquire the service account in order to continue with the following steps. You can use Microsoft Azure each month for free (25 hours of computing time, 500 MB storage, 10,000 storage transactions, 1 GB database, and 500 MB data transfer); this promotional offer might end soon, though.

  3. Go to and sign in using the account you created in Step 1.

  4. Once signed in, click the SQL Azure menu tab on the left side. When the Windows Azure page loads, you will see the project that you created during the registration process in Step 1. See Figure 1, which corresponds to this step.

    Figure 1. SQL Azure main screen
1.2. Connecting to the SQL Azure Project

After you register and purchase your Azure service account, you can now log in to an SQL Azure portal.

  1. Click the project hyperlink NAS-DEV. In your case, you should click the name of the project that corresponds to the one you created in the "Signing up for SQL Azure" section.

  2. Click the I Accept button on the Terms of Use page, as shown in Figure 2.

    Figure 2. SQL Azure Terms of Use screen
1.3. Creating an SQL Azure Database

Here you will be creating an SQL Azure database with a username and password.

  1. On the Create Server page, enter "NotepadAdmin" as the administrator username and "P@ssword" as the administrator password. Retype the password, and choose North Central US on the Location drop-down. See Figure 3 for the inputs. Note that for the location, you would want to choose the region where you are closest for optimal performance. If you are planning to deploy the application to a specific region, then you would want to select the appropriate region here.

    Figure 3. SQL Azure Create Server screen
  2. Click the Create Server button and you will see Figure 4. Take note of the server name assigned to your SQL Azure database as you will need this information to connect using SQL Server Management Studio in Step 20.

    Figure 4. SQL Azure Server Administration screen
  3. Click Create Database. When the pop-up window appears, enter "NotepadDB" as the name of your database, choose "Web" on the "Specify an edition" drop-down menu, and choose "1GB" on the "Specify the max size" drop-down menu, as shown in Figure 5. Now click the Create button.

    Figure 5. SQL Azure create database screen
1.4. Configuring the SQL Azure Firewall

In the next steps, you will learn to properly configure the SQL Azure firewall so you can connect to the database. By default SQL Azure denies all access to the database until you add specific IP.

  1. Notice that NotepadDB now appears in the list of databases on the Databases tab. Now click the "Firewall Settings" tab, where you will add your IP to the firewall in order to access the SQL Azure database that you just created from the Microsoft SQL Management Console application to perform various database-related tasks.

  2. Select "Allow Microsoft Services to access to this server" check box, which will allow programs like Microsoft SQL Management consoles to connect directly to the SQL Azure database, as shown in Figure 6.

    Figure 6. SQL AzureFirewall Settings
  3. Click the Add Rule button, and when the popup appears, enter "MyComputer" into the Name text box. Notice that the popup displays your IP address, which you should now copy and paste into the IP Range text boxes, as shown in Figure 7.

    Figure 7. SQL Azure Add Firewall Rule screen
  4. Click the Submit button. Notice that firewall rules can take up to five minutes to go in effect.

1.5. Testing the SQL Azure Database Connection

In this section, you will learn to test to make sure all the configuration steps are performed properly and you can connect to the database.

  1. Let's test to see if you can properly connect. Click the Databases tab.

  2. From the list of databases, select NotepadDB, click the "Test Connectivity" button, and the pop-up window will appear, as shown in Figure 8.

    Figure 8. SQL Azure Database Connectivity Test
  3. Enter "NotepadAdmin" and "P@ssword" (or your own versions of these) into the Username and Password boxes and click the Connect button. You may choose to use your own username password of your choice.

  4. Click the Connect button and you will see a "Successfully connected to the database" message.

  5. Click the Close button to return to the main page behind.

2. Creating a Database in SQL Azure

In the following section, you will learn to create database tables in NotepadDB, which is hosted directly in SQL Azure using the Microsoft SQL Server Management application.

2.1. Using SQL Server Management Studio to Connect to the Cloud Database

You will be connecting directly to the SQL Azure database NotepadDB you created in the foregoing steps using the SQL Management application.

  1. You want to make sure that you can connect to SQL Azure directly from SQL Management Studio in order to perform various database operations. If you do not have SQL Management Studio installed, you can download the free SQL Server 2008 R2 Express ( Open SQL Server Management Studio by going to Start => Programs => Microsoft SQL Server 2008 R2, as shown in Figure 9.

    Figure 9. SQL Server Management Studio from the Windows menu
  2. In the Connect to Server window, put the server name you obtained from Step 7 into the Server name text box, put NotepadAdmin and P@ssword into the Login and Password text box, and click the Connect button, as shown in Figure 10.

    Figure 10. SQL Server Management Studio Connect to Server screen
  3. Once you are connected successfully to your SQL Azure database, SQL Server Management Studio will display an Object Explorer window on the left side of its IDE, as shown in Figure 3-14. Expand the Databases folder and you will find NotepadDB, which you just created, listed there, also shown in Figure 11.

    Figure 11 SQL Server Management Studio Object Explorer
2.2. Creating SQL Azure Database Tables

Once you are connected to NotepadDB, you can create the tables you'll use to store and manage the notes your users will create and save. You will be creating the database schema shown in Figure 12.

Figure 12. NotepadDB database schema

  1. Right-click NotepadDB from the Object Explorer window, and from the context menu choose New Query.

  2. You will be executing SQL scripts in the query window in order to create tables in NotepadDB.

  3. To the newly opened query window, enter or cut and paste the following database script.

    USE [NotepadDB]

    CREATE TABLE [dbo].[User]
    [UserId] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,

    CREATE TABLE [dbo].[Note]
    [NoteId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [NoteText] [nvarchar](max) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,

    ALTER TABLE [dbo].[Note]
    REFERENCES [dbo].[User] ([UserId])

    ALTER TABLE [dbo].[Note] CHECK CONSTRAINT [FK_Note_User]

  4. Notice that when you expand the tables from NotepadDB in Object Explorer, you will see two tables: Note and User, as shown in Figure 13.

    Figure 13. NotepadDB tables

Now you have the live database in SQL Azure ready to be used by your web service that you will be creating in next section. You will be creating a web service using Windows Communication Foundation (WCF). The web service layer provides managed APIs the phone application can use to access the database.

  •  iPhone Application Development : Working with Text, Keyboards, and Buttons (part 5) - Implementing the View Controller Logic
  •  iPhone Application Development : Working with Text, Keyboards, and Buttons (part 4) - Hiding the Keyboard
  •  iPhone Application Development : Working with Text, Keyboards, and Buttons (part 3) - Creating Styled Buttons
  •  iPhone Application Development : Working with Text, Keyboards, and Buttons (part 2) - Adding Text Views
  •  iPhone Application Development : Working with Text, Keyboards, and Buttons (part 1) - Adding Text Fields
  •  Building Android Apps : Controlling the Phone with JavaScript (part 3) - Accelerometer
  •  Building Android Apps : Controlling the Phone with JavaScript (part 2) - Geolocation
  •  Building Android Apps : Controlling the Phone with JavaScript (part 1) - Beep, Vibrate, and Alert
  •  Building Your First Windows Phone 7 Application (part 5) - Styling Your Application
  •  Building Your First Windows Phone 7 Application (part 4) - Customizing Your First Windows Phone Application
  •  Building Your First Windows Phone 7 Application (part 3) - Writing Your First Windows Phone Code
  •  Building Your First Windows Phone 7 Application (part 2) - Using Your First Windows Phone Silverlight Controls
  •  Building Your First Windows Phone 7 Application (part 1) - Creating a Windows Phone Project
  •  Introducing Windows Phone 7 and the Windows Phone Platform
  •  Windows Phone Application Platform
  •  iPhone Application Development : Basic User Input and Output
  •  Mobile Phone Game Programming : A Quick J2ME Primer
  •  Mobile Phone Game Programming : Java As a Mobile Game Platform
  •  Mobile Phone Game Programming : Getting to Know Mobile Platforms
  •  Mobile Application Security : The Apple iPhone - Local Data Storage: Files, Permissions, and Encryption
    Most View
    Big Shot Rivals: Sony vs. Samsung
    500px - The Stunning Photography Gallery Comes To iPhone
    The Complete Guide To Photography On Your Mac! (Part 1)
    Installing the HP-UX 11i Operating Environment (part 1) - Integrity Early Boot and Begin Loading HP-UX from an Ignite-UX Server, Integrity Early Boot and Begin Loading HP-UX from Media
    Maingear Alpha 24 Super Stock - No Retreat, No Surrender
    Synology 4-Bay NAS: Three Models Review (Part 4)
    Get Yourself A Portable DAC
    HTC Butterfly - Smartphone With Full HD Display
    Windows 8 Explorer : Diagnosis and Recovery - The Processes Tab
    Microsoft Dynamics Sure Step 2010 : A repeatable process for the sales teams (part 2)
    Top 10
    Zotac GeForce GTX Titan Black
    Toshiba Q Series 256GB and 512GB, Toshiba Q Series Pro 256GB
    The Gaming Mouse: Ozone Neon
    SanDisk Ultra Plus 256GB And X110 256GB
    SanDisk Extreme II 480GB, Plextor M5 Pro Extreme 256GB and 512GB
    Samsung SSD 840 Evo 250GB, 500GB And 1TB
    PNY XLR8 240GB and XLR8 PRO 240GB
    OCZ Vector 150 240GB And 480GB, And Vertex 460 240GB
    Nvidia GeForce GTX 750 Ti
    Nokia Lumia 1020 41 Megapixels Of Awesome