Azure Table Storage

Using Azure Table Storage

Introduction

Table Storage is a key-value store that uses a partition key to help with scale out distribution of data and a row key for unique access to a particular entry within a partition. Together, these keys are used to uniquely identify a record in the account.

Accessing the account

First, we will need to add the following to our configuration file (app.config if it’s a console application, web.config if it’s a web application):

<configuration>
<appSettings>
<add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=<your account name>;AccountKey=<your account key>" />
</appSettings>
</configuration>

view raw
app.config
hosted with ❤ by GitHub

Then, we will access our account with the following code:

using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.Storage;
public static CloudStorageAccount GetAccount()
{
const string configName = "StorageConnectionString";
string value = RoleEnvironment.GetConfigurationSettingValue(configName);
return CloudStorageAccount.Parse(value);
}

view raw
GetAccount.cs
hosted with ❤ by GitHub

Creating a Table

To create a Table, we can use the CreateIfNotExists function, as shown in the sample below:

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public CloudTable CreateTable(CloudStorageAccount storageAccount)
{
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("orders");
table.CreateIfNotExists();
return table;
}

view raw
CreateTable.cs
hosted with ❤ by GitHub

Inserting records

To insert a record we will first need a class that inherits from TableEntity:

using System;
using Microsoft.WindowsAzure.Storage.Table;
public class OrderEntity : TableEntity
{
public OrderEntity(string customerName, string orderDate)
{
PartitionKey = customerName;
RowKey = orderDate;
}
public OrderEntity()
{
}
public string OrderNumber { get; set; }
public DateTime OrderDate { get; set; }
public DateTime ShippedDate { get; set; }
public string Status { get; set; }
}

view raw
OrderEntity.cs
hosted with ❤ by GitHub

Then, we create a new OrderEntity, fill the values, and execute an Insert operation on the Table:

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public void InsertOrder(CloudStorageAccount storageAccount)
{
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("orders");
OrderEntity newOrder = new OrderEntity("Smith", "20150410");
newOrder.OrderNumber = "1";
newOrder.OrderDate = Convert.ToDateTime("20150410");
newOrder.ShippedDate = Convert.ToDateTime("20150411");
newOrder.Status = "shipped";
TableOperation insertOperation = TableOperation.Insert(newOrder);
table.Execute(insertOperation);
}

view raw
InsertOrder.cs
hosted with ❤ by GitHub

Inserting multiple records

We can also insert multiple records in a batch transaction, with the following limitations:

  • All operations must take place on the same partition.
  • The batch can include up to 100 operations.
  • The batch payload cannot be greater than 4 MB.
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public void InsertOrders(CloudStorageAccount storageAccount)
{
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("orders");
TableBatchOperation batchOperation = new TableBatchOperation();
OrderEntity newOrder1 = new OrderEntity("Smith", "20150410");
newOrder1.OrderNumber = "1";
newOrder1.OrderDate = Convert.ToDateTime("20150410");
newOrder1.ShippedDate = Convert.ToDateTime("20150411");
newOrder1.Status = "shipped";
OrderEntity newOrder2 = new OrderEntity("Jones", "20150413");
newOrder2.OrderNumber = "2";
newOrder2.OrderDate = Convert.ToDateTime("20150413");
newOrder2.ShippedDate = Convert.ToDateTime("19000101");
newOrder2.Status = "pending";
batchOperation.Insert(newOrder1);
batchOperation.Insert(newOrder2);
table.ExecuteBatch(batchOperation);
}

view raw
InsertOrders.cs
hosted with ❤ by GitHub

Updating records

To update a record, we first get the record, then we can create an InsertOrReplace operation:

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public void ShipOrder(CloudStorageAccount storageAccount, string partitionKey, string rowKey)
{
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("orders");
TableOperation retrieveOperation = TableOperation.Retrieve<OrderEntity>(partitionKey, rowKey);
TableResult retrievedResult = table.Execute(retrieveOperation);
OrderEntity toUpdate = (OrderEntity) retrievedResult.Result;
if (toUpdate != null)
{
toUpdate.Status = "shipped";
toUpdate.ShippedDate = Convert.ToDateTime("20150417");
TableOperation insertOrReplaceOperation = TableOperation.InsertOrReplace(toUpdate);
table.Execute(insertOrReplaceOperation);
}
}

view raw
ShipOrder.cs
hosted with ❤ by GitHub

Deleting a record

To delete a record, we must first retrieve the record (as shown in the previous code sample) and the create a Delete operation:

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public void DeleteOrder(CloudTable table, OrderEntity toDelete)
{
TableOperation deleteOperation = TableOperation.Delete(toDelete);
table.Execute(deleteOperation);
}

view raw
DeleteOrder.cs
hosted with ❤ by GitHub

Querying Azure Table Storage

In the first versions of the Azure Storage library (versions 1.x), the experience was much like that of an Entity Repository, in the sense that we had a Context and lots of LINQ calls. Although it was easy to understand, it was really difficult to determine exactly when the query was done to the service. And it was pretty upsetting to be limited only to “Where”, “First” and “Select” LINQ functions.

Since version 2.0, we have a TableQuery object available, which gives us more control over our query, and we can better understand what’s happening.

Querying for an entity

To query for an entity, we will need the partition key and the row key.

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public object GetEntity(string partitionKey, string rowKey)
{
CloudStorageAccount account = GetAccount();
CloudTableClient cloudTableClient = account.CreateCloudTableClient();
CloudTable table = cloudTableClient.GetTableReference("YourTableName");
TableOperation retrieveOperation = TableOperation.Retrieve(partitionKey, rowKey);
TableResult retrievedResult = table.Execute(retrieveOperation);
return retrievedResult.Result;
}

view raw
GetEntity.cs
hosted with ❤ by GitHub

Applying Filters

Table Storage supports OData, which allows us to make some basic querying: filter, top, select, orderby and a few others. The Storage Client library exposes then through the TableQuery class.

This allows us, for example, to get all records in a partition:

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
public void GetFromPartition(CloudStorageAccount storageAccount, string partitionKey)
{
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
CloudTable table = tableClient.GetTableReference("orders");
TableQuery<OrderEntity> query = new TableQuery<OrderEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparison.Equal, partitionKey));
foreach (OrderEntity entity in table.ExecuteQuery(query))
{
// Do something
}
}

view raw
GetFromPartition.cs
hosted with ❤ by GitHub

There are two important elements here: GenerateFilterCondition and QueryComparisons.

There are some variations to the GenerateFilterCondition function, according to the data type, being this one the one used for strings.

GenerateFilterConditionForGuid
GenerateFilterConditionForLong
GenerateFilterConditionForDouble
GenerateFilterConditionForInt
GenerateFilterConditionForDate
GenerateFilterConditionForBool
GenerateFilterConditionForBinary

And the available options for QueryComparisons are:

QueryComparisons.Equal
QueryComparisons.NotEqual
QueryComparisons.GreaterThan
QueryComparisons.LessThan
QueryComparisons.GreaterThanOrEqual
QueryComparisons.LessThanOrEqual

If we need to combine different filters, we use the TableQuery.CombineFilters() function:

string filter1 = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Argentina");
string filter2 = TableQuery.GenerateFilterCondition("Name", QueryComparisons.Equal, "Guillermo");
string combinedFilter = TableQuery.CombineFilters(filter1, TableOperators.And, filter2);
TableQuery query = new TableQuery().Where(combinedFilter);

view raw
CombineFilters.cs
hosted with ❤ by GitHub

With the operators being:

TableOperators.And
TableOperators.Or
TableOperators.Not

Selects

The Select function allows us to query only for the fields we need. This function can be really useful in the cases when we need to save network bandwidth, like when we are building a WinRT app.

For example, if we want to get only the names of our customers:

CloudTable customerTable = tableClient.GetTableReference("customers");
TableQuery query = new TableQuery().Select(new string[] { "Name" });
customerTable.ExecuteQuery(query);

view raw
SelectFunction.cs
hosted with ❤ by GitHub

Take

If we need to limit the amount of results, we just use the Take function:

query.Take(10);

view raw
Take.cs
hosted with ❤ by GitHub

Combining all

Of course, we can combine all these functions together:

TableQuery query = new TableQuery()
.Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Argentina"))
.Select(new string[] { "LastName", "Name" })
.Take(5);

view raw
CombiningAll.cs
hosted with ❤ by GitHub

Strongly typed queries

All the previous example functions return dynamic values, but we can also make queries that return the class we need, by using TableQuery<OrderEntity>.

Happy coding!

@gjbellmann

1 thought on “Using Azure Table Storage

Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s