Integrating Azure in Office Solutions

Hands-On Lab

Integrating Azure in Office Solutions

Lab version: 1.0.0

Last updated: 5/9/2011

Contents

Overview 3

Exercise 1: Testing your Azure development environment 5

Task 1 – Deploying the SaleObservationService Sample 5

Task 2 – Testing the SaleObservationService Sample with a Simple Test Client 7

Exercise 1 Verification 10

Exercise 2: Consuming Azure Table Service Data in Excel 10

Task 1 – Creating the Project 10

Task 2 – Adding a Product Class 13

Task 3 – Adding a ProductDataContext Class 14

Task 4 – Adding a ProductDataSource Class 16

Task 5 – Adding Functionality to Excel Workbook to Use the Product Classes 18

Exercise 2 Verification 22

Exercise 3: Consuming Azure WCF Service Data in Excel 23

Task 1 – Creating the Project 24

Task 2 – Modifying the Class so That it Can Interoperate With COM 26

Task 3 – Implementing the Call to the WCF Service 28

Exercise 3 Verification 29

Summary 34

Overview

The Windows Azure platform is a group of cloud technologies providing a specific set of services to application developers. There is Windows Azure, which provides an environment for running applications and storing data in the cloud, SQL Azure for providing relational data services in the cloud based on SQL Server, and the Windows Azure AppFabric that provides services for connecting applications running in the cloud or on premises.

Windows Azure is a general platform that businesses use in many wide-ranging scenarios from serving as a platform for a new Web site to providing an enterprise with a robust, scalable backbone for enterprise application development and deployment.

Increasingly, developers need to be aware of how to integrate applications with services exposed by Azure. In this Hands-On Lab, you will learn how to consume Azure services within an Office application.

Objectives

The objective of this Hands-On Lab is to provide you with a foundation for developing Office solutions that integrate with Windows Azure. In particular, you will

·  Learn how to setup your Azure development environment

·  Understand how to access the Azure Table Service for data storage

·  Learn how to call methods exposed by WFC Services on Azure

System Requirements

The steps in this Hands On Lab follow the requirements of the Windows Azure Tools for Microsoft Visual Studio 1.4:

·  Supported Operating Systems:Windows 7;Windows Server 2008;Windows Server 2008 R2;Windows Vista

·  IIS 7.0 (with ASP.NET, WCF HTTP Activation, Static Content, and optionally CGI).

·  Microsoft Visual Studio 2010

·  SQL Server 2005 Express Edition (or above – must be a local instance).

Additionally, this lab uses

·  Excel 2010

·  The steps in this lab were developed and tested using Windows Server 2008 R2 along with a 32-bit version of Microsoft Excel 2010.

Setup

This Hands-On lab assumes that the Hands-On lab files are located in a folder named Student\Azure on the C:\ drive. If you haven’t already done so, perform the following steps

1.  Open Windows Explorer

2.  Right-click on the Local Disk (C:) item and choose New Folder

3.  Name the new folder Student

4.  Right-click on the Student folder and choose New Folder

5.  Name the new folder Azure

6.  Depending on where you installed the Training Kit, go to %Office2010DeveloperTrainingKitPath%\Labs\OfficeAzure\Source\Starter.

7.  Press Ctrl key and click on SaleObservationServiceTestClient, SaleObservatonService, and ExcelAzure.xlsx

8.  Drag the selected items into the Azure folder

Exercises

This Hands-On Lab comprises the following exercises:

1.  Setting up your Development Environment

2.  Consuming Azure Table Storage Server Data in Excel

3.  Calling an Azure hosted WCF Service from an Office Add-In

Estimated time to complete this lab: 60 minutes.

Starting materials

This Hands-On Lab includes the following starting materials.

·  SaleObservationService – This is a Visual Studio project that implements a sample WCF Service for Azure. You will build and deploy this project in exercise 1.

·  SaleObservationServiceTestClient – This is a Visual Studio project for testing the SaleObservationService. You will use this project in exercise 1.

·  ExcelAzure.xlsx – This is an Excel file that you will base your Visual Studio project off of in exercise 2.

·  ExcelAzure – This is not required to complete the lab. It is a completed example of exercise 2.

·  NorthwindSalesFunctions – This is not required to complete the lab. It is a completed example of exercise 3.

Exercise 1: Testing your Azure development environment

In this exercise, you will deploy a sample Azure WCF service and then use a simple test client to access the WCF Service to create sample data and verify that your environment is setup correctly.

Task 1 – Deploying the SaleObservationService Sample

In this task, you will review the SaleObservationService solution files and then build/deploy the project to Azure’s Development Fabric.

1.  If you have not already, setup the Azure development tools on your computer.

a.  Download & install the Windows Azure Tools for Visual Studio 1.4 .

b.  Setup Development Storage for local development

Note: Development Storage is a utility that simulates the Blob, Queue, and Table Services available on Windows Azure. By default, development storage is configured for SQL Server Express 2005 or 2008. You can also configure it to use a local instance of SQL Server using the dsInit tool. For more information see the following resources:

Windows Azure Tools for Visual Studio 1.4

Windows Azure Storage Emulator

2.  Review the sample Azure WCF service project

a.  Open Visual Studio 2010 (run as administrator)

b.  Open C:\Student\Azure\SaleObservationService\SaleObservationService.sln

c.  Double-click on the file named SaleObservationService.src in the Solution Explorer to view the code for this file. Observe there are four public methods:

i.  LoadData – The purpose of this method is to create a table for storing sale observation data and then populating it with data contained in the SaleObservations.xml file that is bundled with the project.

ii.  CategorySales – This function returns the total sales for a given category.

iii.  ProductSales – This function returns the total sales for a given product.

iv.  GetSaleObservation –This function returns a SaleObservation object given a sale observation ID.

d.  Observe the three classes for accessing Azure’s table storage service

i.  SaleObservation.cs – This is a class for modeling the data structure of an individual sale observation. It extends TableServiceEntity, an Azure class, to include a class attribute required by the Azure Table service along with two class additional class properties – PartitionKey and RowKey.

ii.  SaleObservationDataContext.cs – This is a class for representing the runtime context of a data service.

iii.  SaleObservationDataSource.cs – This class handles the details of connecting to the Azure table service and implements methods on the sales observation records.

3.  Run the sample Azure WCF service project. The project is configured to use Azure’s Development Fabric and Development Storage. This allows you to test and develop Azure solutions locally before you deploy them to Azure.

a.  Right-click on SaleObservationService.svc in the Solution Explorer and choose Set as Start Page

b.  Press F5 to begin debugging the project. If everything is setup correctly, you should see a page similar to the screenshot below.

Figure 1

SaleObservation Service

c.  Take note of the URL of the SaleObservationService as you will need to know this in later steps.

Task 2 – Testing the SaleObservationService Sample with a Simple Test Client

In task 2, you will use a basic test client application to call methods exposed by the SaleObservationService.

1.  Without closing the browser window containing the SaleObservationService service page, open another instance of Visual Studio (run as administrator).

2.  Open the C:\Student\Azure\SaleObservationTestClient \SaleObservationServiceTestClient.sln

3.  In the Solution Explorer, double-click on app.config

4.  Verify that the endpoint address in the configuration file matches the endpoint of your service (as noted above in Task 1, step 3, item c).

Figure 2

Endpoint Address

5.  Press F5 to debug the test client

6.  Click the button Load Data. The SaleObservationService exposes a method to create a table using the Azure Table Storage service and populate it with sample sales data. The test client merely calls this method when you click Load Data. After a brief moment, the test client should indicate that 58 items were loaded.

Figure 3

Test Sale Observation Client Dialog

7.  Enter Beverages in the Total by Category text box

8.  Click Total. When you click Total, the test client calls the CategorySales function exposed by the SaleObservationService.

Figure 4

CategorySales function result

9.  Enter Northwind Traders Beer in the Total by Product text box

10.  Click Total. When you click Total, the test client calls the ProductSales function exposed by the SaleObservationService.

Figure 5

ProductSales function result

11.  Close the test client

12.  Close the SaleObservationServiceTestClient solution in Visual Studio.

Exercise 1 Verification

If you completed steps 6-10 in task 2 above, you have verified that the SaleObservationService is running in the Azure Development Fabric and that your Azure Development Storage is working correctly. Another way to verify the existence of objects within Azure storage is to use Visual Studio. To do this, perform the following steps.

1.  In Visual Studio, select ViewàServer Explorer

2.  Expand the Windows Azure Storage node and click through to Windows Azure Storageà(Development)àTablesàSaleObservation

3.  Double-click on SaleObservation to see a read-only view of the items in the table.

Figure 6

Read-only view of the items

Exercise 2: Consuming Azure Table Service Data in Excel

In this exercise, you will learn how to interact with Azure’s table service to read/write records to Excel.

Task 1 – Creating the Project

One of the initial challenges you may experience with Windows Azure is how to get data in and out of it. In this exercise, you will create a project that demonstrates how you can create an Azure Table service table, load the table based on data found in Excel, and then retrieve data from the Azure Table service into Excel.

1.  Open Visual Studio 2010 (run as Administrator)

2.  Choose FileNew Project

3.  Select the C#OfficeExcel 2010 Workbook project template

4.  Name the project ExcelAzure and save it to your student directory at C:\Student\Azure\

Figure 7

New Project dialog

5.  Choose Copy an existing document and select workbook C:\Student\Azure\ExcelAzure.xlsx.

Figure 8

Use the existing ExcelAzure.xlsx file

6.  Click OK. Visual Studio creates the project and displays the ExcelAzure workbook.

7.  Observe that Sheet2 contains a list of Northwind products. You will use this list to populate a table on Azure using Azure’s Table service.

Figure 9

ExcelAzure.xlsx data

8.  Right-click on the ExcelAzure project and choose Properties

9.  On the Application tab, set the Target framework to .NET Framework 4 (not .NET Framework 4 Client Profile).

10.  Click Yes to close and reopen the project

Task 2 – Adding a Product Class

To use Windows Azure table storage in .NET, you construct a class that models the desired schema. In addition to the properties required by your model, the class must include a Timestamp, a PartitionKey and a RowKey property and it must be decorated with a DataServiceKey(“PartitionKey”, “RowKey”) custom attribute. To simplify this, the Microsoft.WindowsAzure.StorageClient namespace includes a TableServiceEntity class that already defines the mandatory properties and required attribute.

1.  Right-click on the ExcelAzure project and choose AddClass

2.  Name the class Product.cs

3.  Add a Reference to Microsoft.WindowsAzure.StorageClient.

a.  Right-click on References in the Solution Explorer and choose Add Reference

b.  On the Browse tab, select the Microsoft.WindowsAzure.StorageClient.dll that is located by default at C:\Program Files\Windows Azure SDK\v1.2\ref\ Microsoft.WindowsAzure.StorageClient.dll

4.  Add the StorageClient namespace to the Product class in addition to the namespaces added by default

C#

using Microsoft.WindowsAzure.StorageClient;

5.  Mark the class as public and have it derive from TableServiceEntity

C#

public class Product:TableServiceEntity

{

}

6.  Add public fields to the Product class

C#

public string Supplier { get; set; }

public string ID { get; set; }

public string ProductCode { get; set; }

public string ProductName { get; set; }

public string Description { get; set; }

public double StandardCost { get; set; }

public double ListPrice { get; set; }

public string QuantityPerUnit { get; set; }

public string Category { get; set; }

7.  Add two constructors to the Product class

C#

public Product(string ID)

{

PartitionKey = "Products";

RowKey = ID;

}

public Product()

{

PartitionKey = "Products";

}

Task 3 – Adding a ProductDataContext Class

The next step is to implement a TableServiceContext (an Azure specific DataServiceContext) that will represent the runtime context for our data service. This is necessary since while data services themselves are stateless; the context in which a developer interacts with them is not.

1.  Right-click on the ExcelAzure project and choose AddClass

2.  Name the class ProductDataContext.cs

3.  Add a reference to System.Data.Services.Client

a.  Right-click on References in the Solution Explorer and select Add Reference

b.  On the .NET tab, select System.Data.Services.Client

c.  Click OK

4.  Add the WindowsAzure and WindowsAzure.StorageClient namespaces to the class in addition to the namespaces added by default

C#

using Microsoft.WindowsAzure;

using Microsoft.WindowsAzure.StorageClient;

5.  Mark the class as public and have it derive from TableServiceContext

C#

public class ProductDataContext:TableServiceContext

{

}

6.  Add a constructor to the class

C#

public ProductDataContext(string baseAddress, StorageCredentials credentials)

: base(baseAddress, credentials)

{

}

7.  Add a Product property to the class