Table storage is a schema-less data store, so has some familiarity to No-SQL databases.
For this post I’m just going to cover the code snippets for the basic CRUD type operations.
Creating a table
We can create a table within table storage using the following code.
At this point the table is empty of both data and ofcourse, being schema-less it has no form, i.e. it’s really just an empty container at this point.
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
table.CreateIfNotExists();
Deleting a table
Deleting a table is as simple as this
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("mytable");
table.Delete();
Entities
Using the Azure table API we need to implement the ITableEntity interface or derive our entities from the TableEntity class. For example
class Plant : TableEntity
{
public Plant()
{
}
public Plant(string type, string species)
{
PartitionKey = type;
RowKey = species;
}
public string Comment { get; set; }
}
In this simple example we map the type of plant to a ParitionKey and the species to the RowKey, obviously you might prefer using Guid’s or other ways of keying into your data. The thing to remember is that, the ParitionKey/RowKey must be unique to the table. Obviously the example above is not going to make code very readable so it’s more likely that we’d also declare properties with more apt names, such as Type and Species, but it was meant to be a quick and simple piece of code.
Writing an entity to table storage
Writing of entities (and many other entity operations) is handled by the Execute method on the table. Which operation we use is determined by the TableOperation passed as a parameter to the Execute method.
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
table.CreateIfNotExists();
var p = new Plant("Flower", "Rose")
{
Comment = "Watch out for thorns"
};
table.Execute(TableOperation.Insert(p));
This will throw an exception if we already have an entity with the PartitionKey/RowKey combination in the table storage. So we might prefer to tell the table storage to insert or update…
Updating entities within table storage
If we prefer to handle both insertion OR updating within a single call we can use the TableOperation.InsertOrReplace method
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
var p = new Plant("Flower", "Rose")
{
Comment = "Thorns along the stem"
};
table.Execute(TableOperation.InsertOrReplace(p));
There’s also a TableOperation.InsertOrMerge which in essence merges new properties (if new one’s exist) onto an existing entity if the entity already exists.
Retrieving entities from table storage
Retrieving an entity by it’s ParitionKey/RowKey is accomplished using the TableOperation Retrieve.
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
var entity = (Plant)table.Execute(
TableOperation.Retrieve<Plant>(
"Flower", "Rose")).Result;
Console.WriteLine(entity.Comment);
Deleting an entity from table storage
Deleting an entity is a two stage process, first we need to get the entity and then we can pass this to the Execute method with the TableOperation.Delete and the entity will be removed from the table storage.
Note: obviously I’ve not included error handling in this or other code snippets. Particularly here where a valid entity may not be found.
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
var entity = (Plant)table.Execute(
TableOperation.Retrieve<Plant>(
"Flower", "Crocus")).Result;
table.Execute(TableOperation.Delete(entity));
Query Projections
In cases where, maybe our data has many properties (for example), we might prefer to query against our data and use projection capabilities to reduce those properties retrieved. To do this we use the TableQuery. For example let’s say all we’re after is the Comment from our entities, then we could write the following
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
var projectionQuery = new TableQuery<DynamicTableEntity>()
.Select(new [] { "Comment" });
EntityResolver<string> resolver =
(paritionKey, rowKey, timeStamp, properties, etag) =>
properties.ContainsKey("Comment") ?
properties["Comment"].StringValue :
null;
foreach (var comment in table.ExecuteQuery(projectionQuery, resolver))
{
Console.WriteLine(comment);
}
The TableQuery line is where we create the projection, i.e. what properties we want to retrieve. In this case we’re only interested in the “Comment” property. But we could add other properties (excluding the standard ParitionKey, RowKey, and Timestamp properties as these will be retrieved anyway).
The next line is the resolver which is passed to ExecuteQuery along with the projectionQuery. This is basically a predicate which acts as the “custom deserialization logic”. See Windows Azure Storage Client Library 2.0 Tables Deep Dive. Whilst an old article, it’s still very relevant. Ofcourse, the example above, shows using an anonymous delegate, in situations where we’re doing a lot of these sorts of projection queries we’d just create a method for this and pass that into ExecuteQuery as the resolver.
Querying using LINQ
Whilst LINQ is supported for querying table storage data, at the time of writing, it’s a little limited or requires you to write your queries in a specific way.
Let’s first look at a valid LINQ query against our plant table
var storageAccount = CloudStorageAccount.Parse(
CloudConfigurationManager.GetSetting(
"StorageConnectionString"));
var client = storageAccount.CreateCloudTableClient();
var table = client.GetTableReference("plants");
var query = from entity in table.CreateQuery<Plant>()
where entity.Comment == "Thorns along the stem"
select entity;
foreach (var r in query)
{
Console.WriteLine(r.RowKey);
}
In this example we’ll query the table storage for any plant’s with a Comment “Thorns along the stem”, but now if we were to try to query for a Comment which contains the word “Thorns”, like this
var query = from entity in table.CreateQuery<Plant>()
where entity.Comment.Contains("Thorns along the stem")
select entity;
Sadly we’ll get a (501) Not Implemented back from the table storage service. So there’s obviously a limit to how we query our table storage data, which is fair enough. Obviously if we want more complex query capabilities we’d probably be best served using a different data store.
We can also use projections on our query, i.e.
var query = from entity in table.CreateQuery<Plant>()
where entity.Comment == "Thorns along the stem"
select entity.RowKey;
or using anonymous types, such as
var query = from entity in table.CreateQuery<Plant>()
where entity.Comment == "Thorns along the stem"
select new
{
entity.RowKey,
entity.Comment
};