How to use WCF services to access SQL Azure Database from Windows Phone 7 app – Part 1

This is part one in a series of four and will step by step explain how to use WCF services to access SQL Azure Database from Windows Phone 7 app. As an example I will develop a Windows Phone app where the user can create an account and later on log in to the account by user name and password. The accounts are saved in SQL Azure and I am using WCF for communication between the WP7 app and SQL Azure Database.

The book Beginning Windows Phone 7 Development has a very detailed chapter about using SQL Azure Database.

Part 1: Signing up to Windows Azure and create your SQL Azure Database

Part 2: Creating a cloud service (WCF service) to connect to the SQL Azure Database

Part 3: Creating a Windows Phone 7 app using WCF to connect to the SQL Azure Database

Part 4: Deploying the WCF service to Windows Azure

Windows Azure service account

The first thing you have to do is to buy a Windows Azure service account, you need this to sign up for SQL Azure. Microsoft have several offers at the moment so there is no cost to start with. If you exceed the limits in the offer you have selected, the exceeding usage will be billed. I have a MSDN Ultimate subscription so I get a very good offer to get started with. Go to Windows Azure Offer and sign up with the one that suits you the best.

SQL Azure Project

When your Windows Azure service account is activated go to SQL Azure and sign up with your Windows Azure account. Click the project name you created when getting the Windows Azure service account. Follow the steps creating admin user and password. Make sure that you keep all the information you enter, you need this later on. When I do registrations like this I always have Textpad open and enter all the information there.

Click “Create Database” and enter your database name, select web edition and 1GB. Make sure to keep the server name generated, you need this later on.

SQL Azure Firewall

Select the “Firewall settings” tab and check the “Allow Microsoft Services access to this server.

Click “Add Rule”, enter a name that tells you what computer/network the following IP range is opening up to. If you are on the computer/network you want to use just copy the IP address displayed in both the IP range boxes and submit.

Finally you want to test your connection. Go back to the “Database” tab, select your database and click “Test Connectivity”. Enter the admin user name and password you created earlier. Try to connect and you should get the following message: “Successfully connected to the database”. If not, remember that it can take up to 5 minutes before the firewall rule you added takes effect.

Creating the Database tables in SQL Azure

We will now connect to SQL Azure by using SQL Server Management Studio from SQL Server 2008 R2 (note that the R2 version have a lot of features enabled for SQL Azure). In the connection window you select “Database Engine” and enter the server name you got when setting up the SQL Azure Database, Select “SQL Server Authentication” and the admin user name and password you created for your SQL Azure Database.

I had some connection issues and found this great SQL Azure Connectivity Troubleshoot Guide.

When you are connected select the database you created earlier and click “New Query”.

For this example we will create a simple User table by inserting the database script below in the query window and execute query.

USE [CloudExample]
GO

CREATE TABLE [dbo].[User]
(
	[UserId]   [int] IDENTITY (1,1) NOT NULL,
	[FullName] [nvarchar](60) NOT NULL,
	[UserName] [nvarchar](20) NOT NULL,
	[Password] [nvarchar](20) NOT NULL,
	CONSTRAINT [PK_User] PRIMARY KEY ([UserId])
)
GO

The query is executed successfully and the User table is now created in the SQL Azure Database.

Next step

We have now created our database with the user table in SQL Azure. The next step is to create a cloud service (using WCF) that will connect to the SQL Azure Database. Part 2: Creating a cloud service (WCF service) to connect to the SQL Azure Database.