This is part two 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
Creating a Windows Azure project in Visual Studio 2010
You need to create a Windows Azure project in Visual Studio 2010. Click “New project” and select Cloud template. You need to have installed Windows Azure SDK before doing this. When you create your Cloud project you get prompted about .NET Framework 4 roles. For this project you will select WCF Service Web Role. We now have a Cloud project ready to use.
Creating Object Model
We will now implement a layer to interact with the database and we are using Entity Framework to do so. By using refactor I rename the “WCFServiceWebRole1″ to “CloudExampleServiceRole”. Right click the CloudExampleServiceRole in the solution explorer and click “Add” and “New item”. Select the “Data” template and “ADO .NET Entity Data Model”, give it a name and click “OK”. An Entity Data Model wizard will pop up, select “Generate from database” and “Next”. Select “New Connection” , choose “Microsoft SQL Server” and “Continue”. Then you need to add connection properties to the SQL Azure Database. Enter the database name, use SQL Server Authentication with the user name and password you created for your SQL Azure Database. Select the database you want to connect to and click “OK”. Select “Yes, include sensitive data in the connection string” and click “Next”. Select the User table and click “Finish”.
You have now created the object model for the User table in your SQL Azure Database.
Create a WCF contract
We will create a WCF contract so that we can add a user and login a user from the database.
Replace the auto generated code in IService1.cs with the code below.
using System;
using System.ServiceModel;
namespace CloudExampleServiceRole
{
[ServiceContract]
public interface IService1
{
[OperationContract]
void AddUser(string fullName, string userName, string password);
[OperationContract]
string LoginUser(string userName, string password);
}
}
Creating the service
We will now create the service with a method for AddUser and a method for LoginUser.
Replace the auto generated code in Service1.svs.cs with the code below.
using System;
using System.ServiceModel;
using System.Data.Objects;
namespace CloudExampleServiceRole
{
public class Service1 : IService1
{
public void AddUser(string fullName, string userName, string password)
{
using (var context = new CloudExampleEntities())
{
context.AddToUser(new User()
{
UserName = userName,
Password = password,
FullName = fullName,
});
context.SaveChanges();
}
}
public string LoginUser(string username, string password)
{
string query = @"SELECT value User.FullName FROM CloudExampleEntities.User AS User WHERE User.UserName = @username AND User.Password = @password";
ObjectParameter[] parameters = new ObjectParameter[2];
parameters[0] = new ObjectParameter("username", username);
parameters[1] = new ObjectParameter("password", password);
using (var context = new CloudExampleEntities())
{
ObjectQuery<string> results = context.CreateQuery<string>(query, parameters);
foreach (string result in results)
{
if (result != null)
{
return result;
}
}
}
return null; ;
}
}
}
Running the Azure WCF locally on your machine
We have completed the service and can run it by pressing F5 in Visual Studio, this will launch the service in your browser (do not close while using the service locally) and Windows Azure Emulator will run in the background. Note that this might differ depending on what OS your are using, I am using Windows 7.
Next step
We have now created the Azure project with object model and WCF services. The next step now is to consume these services from the Windows Phone 7 application. Part 3: Creating a Windows Phone 7 app using WCF to connect to the SQL Azure Database.
Follow me on twitter @PerOla
Share & enjoy
My CloudExampleEntities() and User() objects are undefined. How exactly do you use refactor. There is no button at the top. Which file do I right-click and refactor?
Hi Brian,
Renaming this part can sometimes be a bit tricky. I am using ReSharper. You can name your entity class when creating the Entity Data Model. On the step “Choose your data connection” you name your entity at the bottom. If you expand your edmx file and click the Designer.cs file you will see the name of your Entity class.
2 Things
1 – Replace CloudExampleEntities with the name that you had given to your ADO .NET Entity Data Model.
2 – For the context method
context.AddToUser(new User(), remember to add the s in the method name since it was named as such.
Hi Ash,
Thanks for filling in the missing spots
Hi Per,
I use the entity name from my edmx file and the method AddToUsers was renamed.
I also have my database and a new table at Azure SQL with the firewall rules set to local IP, the connection is OK but the AddToUsers() fails with the message “User account could not be added, Please try again”.
Any suggestion? Thanks!
If you use LINQ to SQL you can drastically shorten the code, for example…
public bool LoginUser(string s_username, string s_password)
{
using (var context = new PPGESUserEntities())
{
IQueryable query = from user in context.Users where user.UserName == s_username && user.Password == s_password select user;
foreach (User result in query)
{
// Do something with the user data
return true;
}
}
return false;
}
hmmm it seems triangle brackets arnt allowed…
IQueryable query = from user in context.Users where user.UserName == s_username && user.Password == s_password select user;
should read…. replace the () brackets for triangle html ones
IQueryable(User) query = from user in context.Users where user.UserName == s_username && user.Password == s_password select user;
Hi Robert, I am not sure why you get this error message. You should try to add some breakpoints and debug to see what really happens.
Hi Dan,
Thank you for the LINQ to SQL code. I often use this myself but I wanted to keep it as basic as possible for these examples.
Cheers!
I can never get anything to run properly
I don’t understand, the Linq thing above seems shorter and simpler?
Hi Jim,
What problem do you encounter since it does not run properly?
The LINQ is shorter and if you are familiar with LINQ you can use that.
can i know how to get CloudExampleEntities ??? i missing it…
I also have error with my User()
Hi jun,
The CloudExampleEntities are generated when you created you Object Model and the ADO. NET Entity Data Model. Maybe you gave it another name then CloudExampleEntities? Follow the steps in the section Creating Object Model above and you should have it.
At first my service would not run, then it ran perfectly but now I keep receiving an error in my browser:
403 – Forbidden: Access is denied.
You do not have permission to view this directory or page using the credentials that you supplied.
any ideas?
thanks
mh
Had to restart my emulators to solve previous problem but now I receive an error on trying to login after successful adding of a new user
The EntitySet ‘User’ is not defined in the EntityContainer ‘festDBEntities’. Near simple identifier, line 1, column 46.
Hi Michael, for your previous problem…if you get error 403 again, just type in the http://127.0.0.1:81/Service1.svc in you browser again. It will solve the problem.
Now i’m having the same error with you where i can’t invoke the login function in the service…hope that mr. Per can help us…
is it any coding error in the Login User??
@Jon, I’ve tried several times to get this tutorial working but with no joy, seems like a few people are having this problem
Dude, Can you please guide me out from this problem ? Urgent !! Thank you
http://stackoverflow.com/questions/10570253/there-was-an-error-while-trying-to-deserialize-parameter-http-tempuri-org-log