Hands-On Lab

Integrating SharePoint and Cloud Datain Windows Phone 7 Applications

Lab version:1.0.0

Last updated:10/2/2018

Contents

Overview

Exercise 1: Creating a SharePoint List Data Source

Task 1 – Deploying the List Template to a SharePoint Site

Task 2 – Creating the Maintenance Requests List

Exercise 2: Creating a SQL Azure Database

Task 1 – Creating a SQL Azure Storage Account

Task 2 – Creating a SQL Azure Database Server

Task 3 – Creating a SQL Azure Database

Exercise 3: Enabling OData Access to a SQL Azure Database

Task 1 – Enabling OData Access

Exercise 4: Creating the Windows Phone 7 Application

Task 1 – Beginning the Exercise

Task 2 – Configuring Constants in the Windows Phone 7 Application

Task 3 – Generating the SQL Azure OData Proxy Class

Task 4 – Retrieving Maintenance Requests from SharePoint

Task 5 – Retrieving Replacement Part Inventory Levels from SQL Azure

Exercise 5: Testing in the Cloud

Task 1 – Testing the Application with the PartsInventory SQL Azure Database

Summary

Overview

Windows Phone 7 applications that that use SQL Azure Datain addition to SharePoint data enable integration between SharePoint data and services with external data hosted in the cloud. Visual Studio 2010 provides a rich development environment for creating, deploying, and integrating Windows Azure Service with Windows Phone 7 applications.

Objectives

In this hands-on lab, you will learn how to integrate SharePoint data with a SQL Azure Data in a Windows Phone 7 application. You will learn how to create a SQL Azure database and enable OData accesss to it. You will also learn how to query the SQL Azure database with the OData Client Library for Windows Phone 7 Series CTPfrom a Windows Phone 7 application. Finally, you will learn how to use SharePoint data to supplement the functionality in the Windows Phone 7 application.

  • Learn how to integrate SharePoint data with a Windows Azure Service in a Windows Phone 7 application.
  • See how to create a SQL Azure databasedatabase and enable OData accesss to it.
  • See how to query the SQL Azure database with the OData Client Library for Windows Phone 7 Series CTPfrom a Windows Phone 7 application.
  • Learn how to use SharePoint data to supplement the functionality in the Windows Phone 7 application.

Prerequisites

The following is required to complete this hands-on lab:

Note: See Setting Up A SharePoint and Windows Phone 7 Development Environment Module for instructions that describe how to set up the SharePoint and Windows Phone 7 developer machine.

  • Windows 7 x64 installed with all Windows Updates installed, in one of the following scenarios.

◦Installed on a physical machine

◦Installed on a bootable VHD

SharePoint 2010 installed on the Windows 7 x64 developer machine configured with a site collection that uses Forms Based Authentication (FBA).

  • Windows Phone 7 Developer Tools

◦

  • Windows Phone 7 Developer Tools - January 2011 Update

◦

  • Windows Phone Developer Tools Fix

◦

Note: The following prerequisites are not included in the Setting Up A SharePoint and Windows Phone 7 Development Environment Module installation instructions. If you are using a development machine built according to the Setting Up A SharePoint and Windows Phone 7 Development Environment Module instructions you must install these components.

  • OData Client Library for Windows Phone 7 Series CTP

◦

  • Windows Azure SDK and Windows Azure Tools for Microsoft Visual Studio (March 2011)

◦

Download and install the files indicated in the screenshot below.

Figure 1

Required downloads

  • KB981002- WCF: Hotfix rollup in .NET 3.5 SP1 for Win 7 and Win 2k8 R2

◦

  • Access to a Windows Azure account.

◦Go to to create an account.

  • Microsoft SQL Server 2008 R2 RTM - Management Studio Express

◦

Exercise 1: Creating a SharePoint List Data Source

Note:If you already performed the steps in the Integrating SharePoint and Cloud Services in Windows Phone 7 Applications lab from the Integrating SharePoint 2010 and Windows Azure Module, you can skip this exercise.

In this exercise, you will deploy a list template to SharePoint and make a list based upon the template. The list template defines a list used to hold maintenance requests. In this scenario, the Windows Phone 7 application will read the maintenance requests from the SharePoint list and query aSQL Azure database to determine if the appropriate replacement parts associated with each maintenance request are in stock. The Windows Phone 7 application will present this information to help maintenance personnel complete maintenance requests.

In this exercise, you will enable OData access to the SQL Azure database.

Task 1 – Deploying the List Template to a SharePoint Site

In this task, you willdeploy the maintenance requests list template to a SharePoint site. The list template contains sample data used in the lab.

  1. Open Internet Explorerand navigate to the SharePoint Team Site configured for Forms Based Authentication.

example:

  1. Log into the site using site collection administrator credentials.
  2. Click Site Actions, and select Site Settings.
  3. In the Galleries section, click List templates.
  4. In the Ribbon, click the Documents tab.
  5. Click Upload Document.
  6. Click Browse…
  7. Browse to the Maintenance Requests.stp file located at %TrainingKitPath%\Labs\IntegratingAzureCloudData\Source\Beforeand select it.
  8. Click Open.
  9. Click OK.
  10. Click Save.
  11. Verify the Maintenance Requests list template appears in the List Templates Gallery.

Figure 2

Maintenance Requests list template

Task 2 – Creating the Maintenance Requests List

In this task, you willuse the maintenance requests list template to create the maintenance requests list.

  1. Open Internet Explorer and navigate to the SharePoint Team Site configured for Forms Based Authentication.

example:

  1. Log into the site using site collection administrator credentials.
  2. Click Site Actions and select More Options.
  3. In the Filter By section, select List.
  4. Select the Maintenance Requests list.

Figure 3

Create list dialog

  1. In the Name textbox enter Maintenance Requests.
  2. Click Create.
  3. Verify the Maintenance Requests list contains the following sample data.

Figure 4

The new Maintenance Requests list

Exercise 2: Creating a SQL Azure Database

In this exercise, you will create a SQL Azure database. In this scenario, the Windows Phone 7 application queries the SQL Azure database and passes in the product make and model. The SQL Azure databasestores the different warehouses that currently have the replacement parts in stock and how many replacement parts are available in each warehouse.

Task 1 – Creating a SQL Azure Storage Account

In this task, you will open the lab solution in Visual Studio 2010.

  1. Open Internet Explorer, and go to
  2. Login using your Windows Azure credentials. If you do not have a Windows Azure Account, sign-up for a new one.

Create a new Storage Account by clicking the New Storage Account from the ribbon menu.

Figure 5

New SQL Azure Account

  1. In the Choose a subscription dropdown list, select the name of your Azure subscription. (You would have provided this information while signing up for the Azure account.)
  2. In the Enter a URLtextbox, provide a URL for the storage account.

Example: spwp7dev

Note: The dialog will verify the selectedurl is not already in use. If it is in use you must select a new url.

  1. In the Choose a region or affinity group dropdown list, select a value close to you.
  2. Click Create.

Task 2 – Creating a SQL Azure DatabaseServer

In this task, you willcreate a SQL Azure database server.

  1. Open Internet Explorer, and go to
  2. Login using your Windows Azure credentials. If you do not have a Windows Azure Account, sign-up for a new one.
  3. In the left pane, click Database.

Figure 6

Choose Database

  1. In the left pane select a subscription.
  2. In the main menu, in the Server section, click Create.

Figure 7

Create new database

  1. In the Create Server dialog, select a Region.
  2. Click Next.
  3. Enter an Administrator Login and password.
  4. Click Next.
  5. Check the Allow other Windows Azure services to access this server checkbox.
  6. Click Add.
  7. In the Rule nametextbox enter Everything.
  8. In the IP range starttextbox, enter 0.0.0.0.
  9. In the IP range endtextbox, enter 255.255.255.0.
  10. Click OK.

Figure 8

Create Server dialog

  1. Click Finish.

Figure 9

Server Information page

  1. Notice the Fully Qualified DNS Name. The fully qualified domain nameisused to connect to the SQL Azure database. The name is unique for each SQL Azure database created.
  2. Do not close the web browser. Leave it open so you can come back to it later in the lab and retrieve the Fully Qualified DNS Name for your SQL Azure database.

Task 3 – Creating a SQL Azure Database

In this task, you will create a SQL Azure database.

  1. Open Microsoft SQL Server 2008 R2 RTM - Management Studio Express.
  1. In the Connect to Server dialog, enter the Server name for your SQL Azure database server.

Note: The Server name is the Fully Qualified DNS Name for the SQL Azure database server you created in task 2.

  1. In the Authentication dropdown list, select SQL Server Authentication.
  2. Enter the Login and Password for your SQLAzure account.
  3. Click Connect.
  4. In the Object Explorer pane, highlight the Databases folder.
  5. Click the New Query button on the toolbar.
  6. Paste the following command into the query window:

TSQL

CREATE DATABASE PartsInventory (MAXSIZE=1GB)

  1. Click Execute on the toolbar.
  2. In the Object Explorer pane, expand the Databases folder and highlight the PartsInventorydatabase. If you cannot see the PartsInventory, highlight the Databases folder and click the refresh button in the Object Explorer toolbar.
  3. With the PartsInventoryhighlighted, click the New Query button in the toolbar.
  4. Paste the following SQL commands into the query window. These commands create the Parts table.

TSQL

USE [PartsInventory]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Parts](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Warehouse] [varchar](256) NOT NULL,

[Make] [varchar](256) NOT NULL,

[Model] [varchar](256) NOT NULL,

[NumberInStock] [varchar](256) NOT NULL,

CONSTRAINT [PK_ Parts] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)

)

GO

SET ANSI_PADDING OFF

GO

  1. Click Execute on the toolbar.
  2. With the PartsInventorydatabase highlighted, click the New Query button in the toolbar.
  3. Paste the following SQL commands into the query window. These commands create the sample data in the Parts table.

TSQL

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('Denver', 'Fabrikam', 'BLR15000-S', 12)

GO

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('Cincinnati', 'Litware', 'MP-4200', 2)

GO

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('Springfield', 'Fabrikam', 'BLR15000-S', 11)

GO

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('San Diego', 'Fabrikam', 'SD-1', 1)

GO

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('Breckenridge', 'Fabrikam', 'SD-1', 3)

GO

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('Denver', 'Litware', 'MP-4200', 6)

GO

INSERT INTO [PartsInventory].[dbo].[Parts]

VALUES ('Philadelphia', 'Litware', 'MP-4200', 3)

GO

  1. Click Execute on the toolbar.
  2. With the PartsInventory highlighted, click the New Query button in the toolbar.
  3. Paste the following SQL command into the query window. This command will return the sample data inserted in the Parts table to verify the data exists.

TSQL

SELECT * FROM [PartsInventory].[dbo].[Parts]

GO

  1. Click Execute on the toolbar.
  2. Verify the sample data exists in the Parts table.

Figure 10

The Parts table

  1. Close Microsoft SQL Server 2008 R2 RTM - Management Studio Express.

Exercise 3: Enabling OData Access to a SQL Azure Database

In this exercise, you will enable OData access to the SQL Azure database.

Task 1 – Enabling ODataAccess

In this task, you will open the lab solution in Visual Studio 2010.

  1. Open Internet Explorer, and go to
  1. In left menu, click SQL Azure OData Service.
  2. Login using your Windows Azure credentials.
  3. In the Connection Information section, in the Server Name textbox, enter the Fully Qualified DNS Name for the SQL Azure database server created in exercise 2.
  4. In the Connection Information section, enter your SQL Azure administrator password.

Figure 11

Configure OData Service Connection

  1. Click Connect.
  2. In the Database Informationsection, in the Database dropdown list, select PartsInventory.
  3. Check the Enable ODatacheckbox.
  4. In the User Mapping section, in the Anonymous User Access dropdown list, select dbo.

Figure 12

User Mapping for the OData Service

Note: The Windows Phone 7 application uses the URL displayed at the bottom of the web page to interact with the PartsInventory SQL Azure database.

Exercise 4: Creating the Windows Phone 7 Application

In this exercise, you will create a Windows Phone 7 application to query the maintenance requests SharePoint list and the SQL Azure database created in exercise 2.

Task 1 – Beginning the Exercise

In this task, you will open the lab solution in Visual Studio 2010.

  1. Make sure that you have downloaded and installed the items listed in System Requirements above prior to beginning this exercise.
  1. Launch Visual Studio 2010 as administrator and open the lab project by selecting File » Open » Project.
  2. Browse to the WP7.Cloud.PartsClient.sln file located at %TrainingKitPath%\Labs\IntegratingAzureCloudData\Source\Beforeand select it.
  3. Click Open to open the solution.

Task 2 – Configuring Constants in the Windows Phone 7 Application

In this task, you will configure the constants used in the Windows Phone 7 application to work with your development environment.

  1. In the WP7.Cloud.PartsClient project, in the Utilities folder, open the Constants.csfile.
  2. Change the value for the USER_NAME and USER_PASSWORD constants to represent a Forms Based Authentication user specific to your development environment. For this lab, the user requires reader permissions.
  3. Change the value for the AUTHENTICATION_SERVICE_URL constant to the URL specific to your development environment.

The following code example demonstrates the value for a SharePoint server named fbawp7.

C#

public const string AUTHENTICATION_SERVICE_URL ="

  1. Change the value for the SQL_AZURE_DATABASE_URL constant to the URL specific to the SQL Azure database created in exercise 2.

The following code example demonstrates the value for a SQL Azure database server named mysqlazuredb. Your database server name will be different.

C#

public const string SQL_AZURE_DATABASE_URL = "

Task 3 – Generating the SQL Azure ODataProxy Class

In this task, you willuse the datasvcutil.exe command line program to generate a proxy class for the PartsInventory SQL Azure database.

  1. Open a command prompt.
  2. Change to the %System%\Microsoft.NET\Framework\v4.0.30319 directory.
  3. Execute the following command. Replace the <SQL Azure Database Server Name>token with the name of the SQL Azure database server created in exercise 2.

Command

Datasvcutil.exe /uri:/<SQL Azure Database Server Name>/PartsInventory /out:c:\PartsInventory.cs /Version:2.0 /DataServiceCollection

Figure 13

Generate the proxy class

  1. Copy the c:\PatrsInventory.cs file to the %TrainingKitPath%\Labs\Azure\Data\Source\Before\WP7.Cloud.PartsClient\OData directory.
  2. In the Solution Explorer, right click the ODatafolder, select Add, and then select Existing Item.
  3. Browse to the %TrainingKitPath%\Labs\IntegratingAzureCloudData\Source\Before\WP7.Cloud.PartsClient\OData directory.
  4. Select the PartsInventory.cs file.
  5. Open the PartsInventory.cs file.
  6. Change the namespace to WP7.Cloud.PartsClient.

Figure 14

Change the namespace

  1. Save PartsInventory.cs.

Task 4 – RetrievingMaintenance Requests from SharePoint

In this task, you will use the SharePoint lists.asmx Web service to return maintenance requests from the SharePoint list.

  1. In the WP7.Cloud.PartsClient project, in the ViewModels folder, open the MainViewModel.csfile.
  1. Add the following code under the //TODO: 8.2.1 comment to define the LoadMaintenanceTasksmethod:

C#

public void LoadMaintenanceTasks()

{

XElementviewFields = new XElement("ViewFields",

new XElement("FieldRef",

new XAttribute("Name", "Title")),

new XElement("FieldRef",

new XAttribute("Name", "Body")),

new XElement("FieldRef",

new XAttribute("Name", "Make")),

new XElement("FieldRef",

new XAttribute("Name", "Model")));

WP7.Cloud.PartsClient.SPListsService.ListsSoapClient lists =

new WP7.Cloud.PartsClient.SPListsService.ListsSoapClient();

lists.CookieContainer = App.CookieJar;

lists.GetListItemsCompleted +=

newEventHandler<WP7.Cloud.PartsClient.SPListsService.

GetListItemsCompletedEventArgs>(lists_GetListItemsCompleted);

lists.GetListItemsAsync(Constants.SHAREPOINT_LIST_NAME,

string.Empty, null, viewFields, null, null, null);

}

The above code uses the proxy class Visual Studio 2010 generated for the lists.asmx service to query the Maintenance Tasks SharePoint list.

  1. Add the following code under the //TODO: 8.2.2 comment to define the lists_GetListItemsCompletedmethod:

C#

void lists_GetListItemsCompleted(object sender,

WP7.Cloud.PartsClient.SPListsService.GetListItemsCompletedEventArgs e)

{

IEnumerableXElement> rows =

e.Result.Descendants(XName.Get("row", "#RowsetSchema"));

IEnumerableSPMaintenanceTaskmaintenanceTasks =

from element in rows

select new SPMaintenanceTask

{

Title = (string)element.Attribute("ows_Title"),

Body = Utils.HtmlToText((string)element.Attribute("ows_Body")),

Make = (string)element.Attribute("ows_Make"),

Model = (string)element.Attribute("ows_Model")

};

Deployment.Current.Dispatcher.BeginInvoke(() =>

{

if (MaintenanceTasks == null)

{

MaintenanceTasks = new ObservableCollectionSPMaintenanceTask>();

}

MaintenanceTasks.Clear();

maintenanceTasks.ToList().ForEach(a => MaintenanceTasks.Add(a));

});

}

The lists_GetListItemsCompletedmethod fires when the call to the lists.asmx SharePoint Web service completes. The method parses the result set, creates an instance of the SPMaintenanceTask that represents each maintenance task in the SharePoint list, and adds the SPMaintenanceTask instances to the MaintenanceTasks observable collection. The MaintenanceTasks observable collection is bound to the MainPage user control in the Windows Phone 7 application. The MainPage user control displays the maintenance requests retrieved from the SharePoint list.