Azure for Developers Tutorial Step 7: Use Table Storage instead of a SQL Database

This is the seventh and final step of the Azure for Developers tutorial, in which we set up a WCF service running in Azure to provide CRUD operations to a client application. For more information, please check out the Introduction.

We have a WCF service running in a web role that reads from and writes to a SQL Database. It submits messages to an Azure queue, and there is a worker role that retrieves the entries from the queue and writes them to blob storage. We have the diagnostics working, and we have a client that calls the service.

Why do I care?

If you have a lot of data, it’s much less expensive to store it in Windows Azure Tables than in a SQL Database. But table storage is like indexed sequential flat files from days of yore – there are no secondary indeces. You get to define a partition key for your table; Microsoft tries to keep all of the data in a partition together. You don’t want to have one partition with all of your millions of records in it – this is not efficient. But you might split the data by what country your customer is in, or by a range of customer id’s, or something like that. You also can define a Row Key, which, when combined with the partition key, makes up the primary key for the table. So if country was your partition key, the row key might be customerID, for example.

You can store different kinds of data in the same table, but this is not a good design idea, as it will confuse the people filling in for you when you’re on vacation.

Let’s see the code…

Let’s add a class and write some code to replicate the same calls we make to the SQL Database, but use table storage instead. For GetCustomerList, we are returning a dataset, so we’ll create the dataset programmatically so we don’t have to make any changes to our client for it to run against Table Storage instead of a SQL Database.

To access table storage, we will associate table entities with a model class called Customer and use a context to track instances of that class, which represent entities to be insert in the table or retrieved from the table.

Right-click on References in the CustomerServicesWebRole and select “Add Reference”. Go to the .NET tab and look for System.Data.Services.Client and select it and click OK.

Now right-click on the CustomerServicesWebRole and select Add Class. Call the class Customer. This is going to be our data model class definition. First, let’s add the basic properties:

public string FirstName { get; set; }
public string LastName { get; set; }
public string FavoriteMovie { get; set; }
public string FavoriteLanguage { get; set; }

Now let’s add the properties required for table storage. You need to have properties for the PartitionKey and RowKey. These two combined make up the primary key. I’m going to make my partition key “customer”, which might lead one to believe that I’m going to put customers and something else in the same table. Let’s assume that I have accounts for customers and accounts for employees, and the fields are the same for both types of data. I’m only going to use this model for customers, though, so I’m going to set the partition key when instantiating a new object.

private readonly string partitionKey = "customer";
public string PartitionKey { get; set; }
public string RowKey { get; set; }

Now we need a default constructor, and a constructor that accepts parameters. In the constructor, I am setting the partition key and the rowkey. I’m using firstname + lastname as the rowkey. I realize this is innately stupid, but I just want a simple example. When you write something you’re actually going to use in production, pick your partition key and row key carefully.

public Customer() { }

public Customer(string firstName, string lastName, string favoriteMovie, string favoriteLanguage)
{
  PartitionKey = partitionKey;
  RowKey = firstName + " " + lastName;

  FirstName = firstName;
  LastName = lastName;
  FavoriteMovie = favoriteMovie;
  FavoriteLanguage = favoriteLanguage;
}

Now our class needs an attribute to specify the primary key for our entities:

  [DataServiceKey("PartitionKey", "RowKey")]
  public class Customer

And we need a using statement:

using System.Data.Services.Common;

That takes care of the Customer class. Now we need a class that will replicate the classes that access the SQL database. These are relatively short, so let’s put all of them in one class. Right-click on the web role project and select Add Class. Call the class TableStorageMethods.

Add these using statements:

using Microsoft.WindowsAzure.StorageClient;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.ServiceRuntime;
using System.Diagnostics;
using System.Data.Services.Client;
using System.Data;

Next, add some private variables and a constructor. We need a variable for the table client that you’re going to use to access table storage, and I’m putting the table name in as a private variable rather than hardcode it.

private CloudTableClient cloudTableClient;
string tableName = "customer";

public TableStorageMethods()
{
  //get a reference to the cloud storage account, and then make sure the table exists
  CloudStorageAccount cloudStorageAccount = 
    CloudStorageAccount.Parse(RoleEnvironment.GetConfigurationSettingValue("DataConnectionString"));
  cloudTableClient = cloudStorageAccount.CreateCloudTableClient();
  cloudTableClient.CreateTableIfNotExist(tableName);
}

We need four methods. First, let’s look at AddCustomer. You have to get the data service context object, and then add the record and save the changes.

internal string ST_AddCustomer(string firstName, string lastName,
  string favoriteMovie, string favoriteLanguage)
{
  Trace.TraceInformation("[AddCustomer] called. FirstName = {0}, LastName = {1}, Movie = {2}, "
    + "Language = {3}", firstName, lastName, favoriteMovie, favoriteLanguage);

  Customer cust = new Customer(firstName, lastName, favoriteMovie, favoriteLanguage);

  string errorMessage = string.Empty;

  try
  {
    //add the record to the table
    TableServiceContext tableServiceContext = cloudTableClient.GetDataServiceContext();
    tableServiceContext.AddObject(tableName, cust);
    tableServiceContext.SaveChanges();
  }
  //you might want to handle these two exceptions differently
  catch (DataServiceRequestException ex)
  {
    errorMessage = "Error adding entry.";
    Trace.TraceError("[ST_AddCustomer] firstName = {0}, lastName = {1}, exception = {2}", 
      firstName, lastName, ex);
  }
  //this exception could be caused by a problem with the storage account
  catch (StorageClientException ex)
  {
    errorMessage = "Error adding entry.";
    Trace.TraceError("[ST_AddCustomer] firstName = {0}, lastName = {1}, exception = {2}", 
      firstName, lastName, ex);
  }
  //general catch
  catch (Exception ex)
  {
    errorMessage = "Error adding entry.";
    Trace.TraceError("[ST_AddCustomer] firstName = {0}, lastName = {1}, exception = {2}", 
      firstName, lastName, ex);
  }
  return errorMessage;
}

We need a method to get the record and retrieve the favorites for a specific customer. We’re using a linq query to retrieve the record with a matching partition key and row key.

internal string ST_GetCustomerFavorites(out string favoriteMovie, out string favoriteLanguage,
  string firstName, string lastName)
{
  Trace.TraceInformation("[GetCustomerFavorites] called. FirstName = {0}, LastName = {1}", 
    firstName, lastName);
  string errorMessage = string.Empty;
  favoriteMovie = string.Empty;
  favoriteLanguage = string.Empty;

  Customer cust = new Customer(firstName, lastName, string.Empty, string.Empty);

  try
  {
    TableServiceContext tableServiceContext = cloudTableClient.GetDataServiceContext();
    IQueryable<Customer> entities = (from e in tableServiceContext.CreateQuery<Customer>(tableName)
                                     where e.PartitionKey == cust.PartitionKey && e.RowKey == cust.RowKey
                                     select e);

    Customer getCust = entities.FirstOrDefault();
    favoriteMovie = getCust.FavoriteMovie;
    favoriteLanguage = getCust.FavoriteLanguage;
  }
  catch (Exception ex)
  {
    Trace.TraceError("[ST_GetCustomerFavorites] firstName = {0}, lastName = {1}, exception = {2}", 
      firstName, lastName, ex);
    errorMessage = "Error retrieving data.";
  }
  return errorMessage;
}

We need a method to update the favorite movie and favorite language for a specific person:

internal string ST_SetCustomerFavorites(string firstName, string lastName,
  string favoriteMovie, string favoriteLanguage)
{
  Trace.TraceInformation("[SetCustomerFavorites] FirstName = {0}, LastName = {1}, Movie = {2}, "
    + "Language = {3}", firstName, lastName, favoriteMovie, favoriteLanguage);

  string errorMessage = string.Empty;

  Customer cust = new Customer(firstName, lastName, favoriteMovie, favoriteLanguage);

  try
  {
    TableServiceContext tableServiceContext = cloudTableClient.GetDataServiceContext();
    IQueryable<Customer> entities = 
      (from e in tableServiceContext.CreateQuery<Customer>(tableName)
       where e.PartitionKey == cust.PartitionKey && e.RowKey == cust.RowKey
       select e);

    Customer entity = entities.FirstOrDefault();
    entity.FavoriteLanguage = favoriteLanguage;
    entity.FavoriteMovie = favoriteMovie;

    tableServiceContext.UpdateObject(entity);
    tableServiceContext.SaveChanges();

  }
  catch (Exception ex)
  {
    Trace.TraceError("[ST_SetCustomerFavorites] FirstName = {0}, LastName = {1}, ex = {2}", 
      firstName, lastName, ex);
    errorMessage = "Error setting customer favorites.";
  }
  return errorMessage;
}

And lastly, we need a method to get the list of customers. I don’t want to change my client application based on the data source, and the SQL Database method returns a dataset, so I’ve written this one to also return a dataset.

internal string ST_GetListOfCustomers(out DataSet customers)
{
  Trace.TraceInformation("[GetListOfCustomers] called.");
  string errorMessage = string.Empty;

  //since the SQL Azure version returns a dataset, create a dataset and return it.
  //this way you don't have to change the client code
  customers = new DataSet();
  DataTable dt = new DataTable();
  DataColumn wc = new DataColumn("ID", typeof(Int32));
  wc.AutoIncrement = true;
  wc.AutoIncrementSeed = 1;
  wc.AutoIncrementStep = 1;
  dt.Columns.Add(wc);

  dt.Columns.Add("FirstName", typeof(String));
  dt.Columns.Add("LastName", typeof(String));
  dt.Columns.Add("FavoriteMovie", typeof(String));
  dt.Columns.Add("FavoriteLanguage", typeof(String));

  try
  {
    //retrieve the list of customers
    TableServiceContext tableServiceContext = cloudTableClient.GetDataServiceContext();
    DataServiceQuery<Customer> dataServiceQuery = 
      tableServiceContext.CreateQuery<Customer>(tableName);
    IEnumerable<Customer> entities = 
      dataServiceQuery.Where(e => e.PartitionKey == "customer").AsTableServiceQuery<Customer>();
    if (entities != null)
    {
      //add the entries to the DataTable
      foreach (Customer cust in entities)
      {
        DataRow newRow = dt.NewRow();
        newRow["FirstName"] = cust.FirstName;
        newRow["LastName"] = cust.LastName;
        newRow["FavoriteMovie"] = cust.FavoriteMovie;
        newRow["FavoriteLanguage"] = cust.FavoriteLanguage;
        dt.Rows.Add(newRow);
      }
    }
    else
    {
      Trace.TraceError("[ST_GetListOfCustomers] No rows found in table.");
      errorMessage = "No rows found in table.";
    }
  }
  catch (Exception ex)
  {
    Trace.TraceError("[ST_GetListOfCustomers] ex = {0}", ex);
    errorMessage = "Error getting list of customers.";
  }
  //add the data table to the dataset
  customers.Tables.Add(dt);

  return errorMessage;
}

Now we need to change our service to call the TableStorageMethods instead of the SQL Database methods. Let’s put in a toggle that we can change back and forth.

Open CustomerServices.svc in the web role and add an enumeration under the private variables for the queue.

public enum DataBaseType { sqlazure, tablestorage }
private DataBaseType currentDataBase = DataBaseType.tablestorage;

Now let’s change each method to check the value of currentDataBase and call the appropriate routine. When I defined the names of the methods for table storage, I used the same names as the SQL Database methods but with “ST_” prefixed to them so I can easily change these.

In GetFavorites, change this:

CustomerFavorites cf = new CustomerFavorites();
errorMessage = cf.GetCustomerFavorites(out favoriteMovie, out favoriteLanguage, 
  firstName, lastName);

to this:

if (currentDataBase == DataBaseType.sqlazure)
{
  CustomerFavorites cf = new CustomerFavorites();
  errorMessage = cf.GetCustomerFavorites(out favoriteMovie, out favoriteLanguage,
    firstName, lastName);
}
else
{
  TableStorageMethods tsm = new TableStorageMethods();
  errorMessage = tsm.ST_GetCustomerFavorites(out favoriteMovie, out favoriteLanguage,
    firstName, lastName);
}

We’ll follow the same pattern for the rest. In UpdateFavoritesByName, change this:

CustomerFavoritesUpdate cfu = new CustomerFavoritesUpdate();
errorMessage = cfu.SetCustomerFavorites(firstName, lastName, favoriteMovie, favoriteLanguage);

to this:

if (currentDataBase == DataBaseType.sqlazure)
{
  CustomerFavoritesUpdate cfu = new CustomerFavoritesUpdate();
  errorMessage = cfu.SetCustomerFavorites(firstName, lastName, favoriteMovie, favoriteLanguage);
}
else
{
  TableStorageMethods tsm = new TableStorageMethods();
  errorMessage = tsm.ST_SetCustomerFavorites(firstName, lastName, favoriteMovie, favoriteLanguage);
}

In AddACustomer, change this:

CustomerFavoritesAdd cfa = new CustomerFavoritesAdd();
errorMessage = cfa.AddCustomer(firstName, lastName, favoriteMovie, favoriteLanguage);

to this:

if (currentDataBase == DataBaseType.sqlazure)
{
  CustomerFavoritesAdd cfa = new CustomerFavoritesAdd();
  errorMessage = cfa.AddCustomer(firstName, lastName, favoriteMovie, favoriteLanguage);
}
else
{
  TableStorageMethods tsm = new TableStorageMethods();
  errorMessage = tsm.ST_AddCustomer(firstName, lastName, favoriteMovie, favoriteLanguage);
}

And lastly, in GetCustomerList, change this:

CustomerList cl = new CustomerList();
errorMessage = cl.GetListOfCustomers(out customers);

to this:

if (currentDataBase == DataBaseType.sqlazure)
{
  CustomerList cl = new CustomerList();
  errorMessage = cl.GetListOfCustomers(out customers);
}
else
{
  TableStorageMethods tsm = new TableStorageMethods();
  errorMessage = tsm.ST_GetListOfCustomers(out customers);
}

Now let’s run our service. We don’t need to update our service reference, because we didn’t make any changes to the service contract. Run the client application, and click Get Customer List. We will see nothing, because we haven’t added any records to the version running against Table Storage yet.

So add a couple of records, and then retrieve the customer list. So now everything is running against Azure table storage. If I run the Cerebrata Cloud Storage Studio and look in my development storage, and I can see the customer table with the entries I just added.

So now we have a WCF service running in a web role that performs CRUD operations against SQL Azure or Windows Azure Table Storage, and writes diagnostic information. Our WCF service has a method that lets us add an entry to the queue. Then we have a worker role that retrieves the entry from the queue and writes it to blob storage. We have a client that calls the WCF service.

If you set the connection strings correctly in the ServiceConfiguration.Cloud.cscfg file, you can publish your service to the cloud. Then just change the URL at the top of the DAC class in the TestClient, and it will point to that service. Then you can run your client application against the service running in Azure.

That wraps up the 7-part series called “Azure for Developers”, showing the features of Windows Azure and talking about how I’ve used them in my production environment at GoldMail. For a completed version of the code, check out the version from the June 2012 San Diego Code Camp talk, which you can download from here.

Tags:

3 Responses to “Azure for Developers Tutorial Step 7: Use Table Storage instead of a SQL Database”

  1. Arfeen Says:

    Hi,
    I’ve got a question. When we experimented with Azure table storage some time ago, We felt that storing/reading the table storage was taking a long time compared to SQL server. So when we had a lot of concurrent users hitting our wcf service, it slowed everybody down.

    How has your experience been with using table storage? Is it fast enough for you?

    • robindotnet Says:

      I believe Azure Table Storage is faster than SQL Azure, but it depends on how you structure your table storage. I don’t have any personal experience with it. If I were you, I would turn on the Table Storage diagnostics in the (new) portal, and check it to see what it looks like. If it’s consistent with what you are seeing, then contact Azure Support.

  2. Azure for Developers: Introduction to the Tutorial | RobinDotNet's Blog Says:

    […] Step 7: Use Table Storage instead of a SQL Database […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: