• Article

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 File>>New Project
  3. Select the C#>>Office>>Excel 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 Add>>Class
  2. Name the class Product.cs
  3. Add a Reference to Microsoft.WindowsAzure.StorageClient.
    1. Right-click on References in the Solution Explorer and choose Add Reference
    2. 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 Add>>Class
  2. Name the class ProductDataContext.cs
  3. Add a reference to System.Data.Services.Client
    1. Right-click on References in the Solution Explorer and select Add Reference
    2. On the .NET tab, select System.Data.Services.Client
    3. 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

    C#

    public IQueryable<Product> Product { get { return this.CreateQuery<Product>("Product"); } }

  8. Press F6 and verify that the project builds

Task 4 – Adding a ProductDataSource Class

Now that you have a data model (Product) and a data context (ProductDataContext), the final step is to create a data source. The data source class handles the details of connecting to the Azure Table Storage service using REST in addition to the methods for interacting with the data.

  1. Right-click on the ExcelAzure project and choose Add >> Class
  2. Name the class ProductDataSource.cs

  3. 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;

  4. Mark the class as public

    C#

    public class ProductDataSource { }

  5. Add two class variables at the top of the class – storageAccount and context

    C#

    public class ProductDataSource { private static CloudStorageAccount storageAccount; private ProductDataContext context; }

  6. Add a static constructor for the class. Note that the constructor configures the storage account so that it uses the DevelopmentStorageAccount. Additionally, the constructor creates the Product table if it does not already exist.

    C#

    static ProductDataSource() { try { storageAccount = CloudStorageAccount.DevelopmentStorageAccount; CloudTableClient c = new CloudTableClient( storageAccount.TableEndpoint.AbsoluteUri, storageAccount.Credentials); if (!c.DoesTableExist("Product")) { CloudTableClient.CreateTablesFromModel(typeof(ProductDataContext), storageAccount.TableEndpoint.AbsoluteUri, storageAccount.Credentials); } } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } }

  7. Add an instance constructor for the class

    C#

    public ProductDataSource() { this.context = new ProductDataContext( storageAccount.TableEndpoint.AbsoluteUri, storageAccount.Credentials); this.context.RetryPolicy = RetryPolicies.Retry(3, TimeSpan.FromSeconds(1)); }

  8. Add the following data related methods

    C#

    public IEnumerable<Product> Select() { var results = from g in this.context.Product where g.PartitionKey == "Products" select g; return results; } public void AddProduct(Product newItem) { this.context.AddObject("Product", newItem); this.context.SaveChanges(); } // use to add multiple products in a batch // call Save() after adding the products public void AddProducts(Product newItem) { this.context.AddObject("Product", newItem); } public void Save() { this.context.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch); } public void DeleteProduct(Product item) { this.context.DeleteObject(item); this.context.SaveChanges(); } public void DeleteProducts() { foreach (var p in this.Select()) { this.context.DeleteObject(p); } this.context.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch); }

  9. Press F6 to verify the project builds

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

At this point, you have completed all of the steps required to interact with Azure’s Table Storage service. The final step is to implement functionality in the Excel workbook to use the Product related classes to interact with Azure.

  1. Double-click on Sheet2.cs in the Solution Explorer to open the worksheet in design view
  2. Scroll to the right until to reach an empty column
  3. Drag a Button control from the Toolbox to an unused portion of the worksheet (i.e. column P)
    1. Name the button buttonLoad

    2. Set the Text property to Load Data

      Figure 10

      Text property set to Load Data

  4. Double-click on the buttonLoad to automatically generate and navigate to an event handler for the button

  5. Enter the following code inside the buttonLoad_Click event handler. Note that you will implement ReadStringand ReadDouble in the next step.

    C#

    // Load starting in row 2 (row 1 contains column headings) Excel.Range rg = this.Cells[2, 1]; ProductDataSource ds = new ProductDataSource(); while (rg.Value != null) { Product p = new Product(); p.PartitionKey = "Products"; p.RowKey = ReadString(rg.Offset[0, 1]); p.Supplier = ReadString(rg); p.ID = ReadString(rg.Offset[0, 1]); p.ProductCode = ReadString(rg.Offset[0, 2]); p.ProductName = ReadString(rg.Offset[0, 3]); p.Description = ReadString(rg.Offset[0, 4]); p.StandardCost = ReadDouble(rg.Offset[0, 5]); p.ListPrice = ReadDouble(rg.Offset[0, 6]); p.QuantityPerUnit = ReadString(rg.Offset[0, 9]); p.Category = ReadString(rg.Offset[0, 12]); ds.AddProducts(p); rg = rg.Offset[1, 0]; } ds.Save();

  6. In order to ensure that the proper data types are assigned to the product objects properties, add two additional methods to Sheet2

    C#

    private string ReadString(Excel.Range rg) { if (rg.Value == null) { return ""; } else { return rg.Value.ToString(); } } private double ReadDouble(Excel.Range rg) { double result = 0.0; try { result = Convert.ToDouble(rg.Value); } catch (Exception) { } return result; }

  7. Double-click on Sheet1 in the Solution Explorer to view the designer for Sheet1.
  8. Drag two Button controls from the Toolbox on to the surface of Sheet1
    1. Name the buttons buttonList and buttonDelete

    2. Set the Caption properties for the buttons to List Products and Delete All respectively

      Figure 11

      List Products and Delete All buttons

  9. Double-click on the List Products button to generate and navigate to an event handler for the button

  10. Add the following code to the buttonList_Click event handler

    C#

    this.Cells.ClearContents(); Excel.Range rg = this.Cells[1, 1]; ProductDataSource ds = new ProductDataSource(); foreach (var p in ds.Select()) { rg.Value = p.ID; rg.Offset[0, 1].Value = p.ProductName; rg = rg.Offset[1, 0]; }

  11. Right-click on Sheet1.cs in the Solution Explorer and choose View Designer
  12. Double-click on the Delete All button to generate and navigate to an event handler for the button

  13. Add the following code to the buttonDelete_Click event handler

    C#

    ProductDataSource ds = new ProductDataSource(); ds.DeleteProducts(); buttonList_Click(null, null);

Exercise 2 Verification

To verify your work, perform the following actions.

Confirm that the SaleObservationService from Exercise 1 is running in debug mode, then

  1. Press F5 to debug your project
  2. After Excel starts, select Sheet2
  3. Click Load Data

  4. Switch to Sheet1 and click List Products. Observe that your solution retrieves a list of product ID’s and product names from Azure’s table storage.

    Figure 12

    List Products button results

  5. Click Delete All. Observe that your solution deletes all of the items from the Products table.
  6. Switch to Sheet2 and click Load Data.
  7. Close Excel, terminating your debugging session.

< previous | next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents