preload
Feb 13

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
You can subscribe to my comments feed to keep track of new comments.

31 Comments to “How to use WCF services to access SQL Azure Database from Windows Phone 7 app – Part 2”

  1. Brian says:

    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?

  2. 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.

  3. Ash says:

    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.

  4. Hi Ash,

    Thanks for filling in the missing spots :)

  5. Robert says:

    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!

  6. Dan says:

    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;
    }

  7. Dan says:

    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;

  8. 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.

  9. 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!

  10. Jim Princing says:

    I can never get anything to run properly :(

    I don’t understand, the Linq thing above seems shorter and simpler?

  11. 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.

  12. jun says:

    can i know how to get CloudExampleEntities ??? i missing it…

  13. jun says:

    I also have error with my User()

  14. 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.

  15. Michael Hunt says:

    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

  16. Michael Hunt says:

    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.

  17. Jon says:

    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??

  18. Michael Hunt says:

    @Jon, I’ve tried several times to get this tutorial working but with no joy, seems like a few people are having this problem

  19. Hi guys,

    It has been quite a while since I did this now and unfortunately I do not have the answer to your issues. The tutorial should work if you follow it step by step, maybe try starting from scratch again?

    I might revisit this topic in the future and write some new articles about it.

  20. Ruth Ramya says:

    Could you pls provide me the sample..coz i have done the same step by step procedure in metro app but am not able to get the exact result….can u pls help me out ??

    else could u pls guide me context.AddToUser() frm where we get this method?

  21. YC says:

    Hi Per, thank you for this guide but I’m totally new to this.
    I’m following exactly your tutorial step by step until here, I got two errors,

    ‘CloudExampleServiceRole.CloudExampleEntities’ does not contain a definition for ‘AddToUsers’ accepting a first argument of type ‘CloudExampleServiceRole.CloudExampleEntities’ could be found.

    The same goes to ‘CreateQuery’.

    I know this is a noob question but if you can help me I will be very glad and appreciate.
    Thanks.

  22. YC says:

    For you guys using Visual Studio 2012 and Entity Framework 5..
    Can try replacing the Context.AddToUsers(… with Context.Users.Add(….
    And if you have problem with CreateQuery also.. Please refer to
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/a0738f30-993f-4c53-9f4e-06c5e9dbd7ae

    http://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.iobjectcontextadapter(v=vs.103).aspx

  23. Matvey says:

    I am trying to connect
    “”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”. ”
    But i can’t
    what user name should i use ?

  24. Ruth Ramya,

    I am sorry but I don’t have this code anymore so it will be hard to share it with you. Have a look at what YC writes about using Context.Users.Add instead of Context.AddToUsers…

    YC,

    Thanx for explaining this one. There have been several changes made in the framework since I wrote this post.

  25. Matvey,

    Please check part 1 of this post. You add a username and password when you create your SQL Azure database.

  26. Sanny says:

    In the part where you say ” Enter the database name, use SQL Server Authentication with the user name and password you created for your SQL Azure Database”. What do you need to enter for the server name box?.

  27. tekin says:

    İt doesn’t work ” context.AddToUser ” ?

  28. tekin says:

    It doesn’t work “context.AddToUser”?

  29. tekin,

    Have a look at what YC writes in the comment field about using Context.Users.Add instead of Context.AddToUsers…

    The implementation details have changed a bit since I wrote this article.

  30. Usama Zafar says:

    Please Help I can’t find the context.CreateQuery() method! I am using Visual Studio 2012 please help!

No Pingbacks to “How to use WCF services to access SQL Azure Database from Windows Phone 7 app – Part 2”

Leave a Reply

Subscribe to my comments feed

Subscribe to my feeds Follow me on Twitter
DZone MVB