Basics of Working With Windows Azure Table Storage

The Windows Azure platform gives developers 4 ways of storing and retrieving data. Broadly these are referred to as Data Services. The 4 ways are:

1. SQL Database
2. Table Storage
3. Blob Storage
4. Queues

In this article, I am going to discuss table storage. Table storage is a NoSQL data store aka non-relational data store. To work with examples in this article, you need to have a Windows Azure account. Microsoft currently has a free 90 day trial offer that allows developers to experiment with the Windows Azure platform. To sign up for the 90 day trial, you need to have a credit card. For those of you, who don't have a credit card or unwilling to give credit card information to Microsoft for some reason, I will show you how to simulate Azure table storage on the local machine using the table storage emulator.

Once you have signed up for Windows Azure, you can create a table storage account by logging into Azure management. The management portal is well laid out and very intuitive. You won't have any trouble figuring out how to create a table storage account.

Getting Started

First and foremost, you need to download and install Windows Azure SDK for .NET. I used a tool called Web Platform Installer to download the SDK. And I recommend you also use it to download the SDK. The tool is located at: http://www.microsoft.com/web/downloads/platform.aspx. When you install and run the tool, it displays all packages that you can download and work with. You would want to search for the Azure .NET SDK in the web platform installer and install it. The latest package, at the time of writing this article, was version 1.8 released in Oct 2012. Azure has SDKs for other languages and platforms like Python and node.js.

Once you have downloaded and installed the SDK, open up Visual Studio, create a console application and call it "AzureTableStorageSample".

You need to add the following assembly references to your project:

1. System.Configuration.dll (To read a connection string from app.config)
2. Microsoft.WindowsAzure.Storage.dll
3. Microsoft.WindowsAzure.StorageClient.dll
4. System.Data.OData (Install this assembly from the Nuget console by typing Install-Package Microsoft.Data.OData -Version 5.0.2 If you don't see the Nuget console then you can turn it on by selecting TOOLS -> Library Package Manage -> Package Manager Console menu option. Installing this assembly from the Nuget console will install additional assemblies that are required by it).

Storing an entity in an Azure table

Once the required assemblies are added, add a new C# file to the project with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.WindowsAzure.Storage.Table; 
namespace AzureTableStorage
{
    public class EmployeeEntity : TableEntity
    {
        public EmployeeEntity()
        {
        } 
        public EmployeeEntity(int id, string name, double sal)
        {
            Id = id;
            Name = name;
            Salaray = sal; 
            PartitionKey = id.ToString();
            RowKey = name;
        } 
        public int Id { get; set; }
        public string Name { get; set; }
        public double  Salaray { get; set; }
    }
}

The stuff that is stored inside an Azure table is called Entities. An entity is analogous to a row in SQL Server table. Inside your application code, it is nothing but an object of some type that you want to store in a table. In the code above, I have defined a class called EmployeeEntity deriving it from TableEntity. Every entity stored inside Azure table has 3 mandatory properties: ParitionKey, RowKey and Timestamp. The first 2 properties must be assigned values by the application code and form the primary key of the table.

Let's now have a look at how objects of the type EmployeeEntity can be stored in an Azure table. Open up program.cs and the following using statements at the top of the file.

using System.Configuration;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;

Inside the main method, copy and paste the following code:

string connStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connStr);
CloudTableClient client = storageAccount.CreateCloudTableClient();
CloudTable table = client.GetTableReference("employee");
table.CreateIfNotExists();
var emp = new EmployeeEntity(1, "BillG", 123456.33);
TableOperation insertOp = TableOperation.Insert(emp);
table.Execute(insertOp);

The preceding code is pretty simple and self-explanatory. For the code above to read the Azure table connection string, you need to put the right connection string in the app.config file.

<connectionStrings>
   <add name="ConnString"connectionString="DefaultEndpointsProtocol=http;AccountName=XXXXX;AccountKey=XXXX"></add>
</connectionStrings>

You can get the values for the AccountName and AccountKey keys from the Storage section of the Azure management portal. Log into your Azure account, go to the Storage section and click the "Manage Keys" link at the bottom. Clicking on "Manage Keys" will open up a popup with all the details that you need to construct the connection string.

Upon executing the program, it will create a table called employee and add an employee entity. You can check whether the employee entity is stored in the table or not by going to the Server Explorer and connecting to your storage account as shown below.



Retrieving an entity from a table

Let's now retrieve the entity that we just stored. Add the following method after the main() method inside your program.cs file. Once added, invoke the method from main() to see if the employee we added to the table can be retrieved or not.

private static EmployeeEntity RetrieveEmployee()
{
      string connStr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
      CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connStr);
      CloudTableClient client = storageAccount.CreateCloudTableClient();
      CloudTable table = client.GetTableReference("employee");
      table.CreateIfNotExists();
      TableOperation retOp = TableOperation.Retrieve<EmployeeEntity>("1", "BillG");
      TableResult tr = table.Execute(retOp);
      return tr.Result as EmployeeEntity;
}

Simulating Azure Table Storage on the local machine

First, you need to run a program called DSInit to setup SQL Server or LocalDB as the table storage. When you run the program, it creates a database in the specified SQL Server instance with quite a few tables. To run the program, run Windows Azure command prompt from the Windows Azure start menu as an administrator. At the prompt, run the following command..
DSInit /sqlInstance:<SqlServerInstanceName>

In my case, "sqlexpress" is the name of my local SQL Server name. I ran the command as follows:

DSInit /sqlInstance:sqlexpress

The next step is to start the storage emulator from the Start menu. The storage emulator sits quietly in the system tray but when you open it from the system tray it is as in the following:.



You now need to change the connection string to point to your local Azure table storage setup. The following connection string should work for you:

<connectionStrings>
   <add name="ConnString" connectionString="UseDevelopmentStorage=true;DevelopmentStorageProxyUri=http://ipv4.fiddler"></add>
</connectionStrings>

When testing in the local environment, clients are forced to use a web proxy. So you must run Fiddler (www.fiddler2.com) before you run the program in the local environment. I read somewhere on the internet that this wasn't a requirement in the previous versions of the SDK and is considered a bug by some people. So you may not be forced to use a web proxy when the next version is released.