Share via


Starting Azure Platform as a Service (PaaS) Development 101 Series: Lesson 2 of n – Table Storage

Welcome back to the series Smile You can always view the list of all lessons here. Lesson 2 will be all about Azure Table Storage.

First things first.!

Azure Storage Tables are part of Storage Accounts. In addition to tables, Storage Accounts host Queues, BLOBs, and Files. So what’s cool about tables? well, if you are looking for a scalable, convenient, and very cost effective solution to store non-relational data with a clustered index. Then you are in the right place. Below diagram quickly illustrates the structure where tables sit inside storage accounts and entities would sit inside the tables.

image

To learn more about Azure Table Storage click here.

What are we building today?

As you will get used to meaningless examples in this series Winking smile we will build a simple CRUD command line application for an Azure table. Checkout the below screenshot of the console application:

image

In addition to the CRUD operations, I will show you how to do a batch insert as well. Don’t get too excited though, according to this link over MSDN: A batch operation may contain up to 100 individual table operations, with the requirement that each operation entity must have same partition key. A batch with a retrieve operation cannot contain any other operations. Note that the total payload of a batch operation is limited to 4MB. I hope this will change in the near future.

Let’s get cracking and create the Storage Account and the Azure Table

Navigate to the Azure portal and start with creating a storage account. I called it “paas101”. Unfortunately, up till now you can not use the Azure portal to manage the tables. You need to use a tool for this. Personally I use Visual Studio. A quick search on Bing will do the trick for you.
image

Launch Visual Studio and open the Server Explorer. You will find an Azure node. If this is the first time you do this, Visual Studio will ask you to login using your Microsoft Account. Look for your Storage Account, expand it, right click Tables, and create a new table. I called it Lesson02.

image

Once the table is created, you will notice that by default 3 columns are created: the clustered index aka Partition Key, the Row Key, and an automatically generated Timestamp. Add an additional property and call it “SomeRandomNumber”. Yeah I know Smile with tongue out

image

I just added an entity manually, notice the Timestamp being populated automatically in the below screenshot.

image

Next step would be adding the “Windows Azure Storage” NuGet package to the project:

image

and now the last step before we start writing code is getting our hands on the access key. Without the access key our client application will not be able to interact with the Storage Account. Navigate to your Storage Account on the Azure portal and click on the Manage Access Keys button on the bottom:

image

Copy one of the access keys to the clipboard.

image

Now go back to your command line solution and add a new Class that will represent the entity. For the sake of the demo I called it “SomeEntity” Smile Notice from the code below that it is inheriting from the TableEntity class:

  1 using Microsoft.WindowsAzure.Storage.Table;
 2 
 3 namespace PaaS101.Lesson2
 4 {
 5     public class SomeEntity : TableEntity
 6     {
 7         public string SomeRandomNumber { get; set; }
 8         
 9         public SomeEntity(string entityPartitionKey,
10             string entityRowKey,
11             string someRandomNumber)
12         {
13             PartitionKey = entityPartitionKey;
14             RowKey = entityRowKey;
15             SomeRandomNumber = someRandomNumber;
16         }
17 
18         public SomeEntity() { }
19     }
20 }

The console application

Very straight forward, an endless loop displaying the menu and waiting for the user to input. A switch statement is there to trigger the right function based on user input.

  1         private const string Protocol = "https";
 2         private const string AccountName = "paas101";
 3         private const string AccountKey = "XXXXXX-KEY GOES HERE";
 4         private const string TableName = "Lesson02";
 5         private const string PartitionName = "Lesson02";
 6         private const string Success = "\nOperation completed successfuly. Press any key to go back.";
 7         private const string Fail = "\nSomething went wrong, press any key to go back.";
 8 
 9         private static string _connection;
10 
11         static void Main(string[] args)
12         {
13             _connection = string.Format("DefaultEndpointsProtocol={0};AccountName={1};AccountKey={2}",
14             Protocol, AccountName, AccountKey);
15 
16             Console.TreatControlCAsInput = true;
17             Console.Clear();
18 
19             while (true)
20             {
21                 Console.Clear();
22 
23                 Console.WriteLine("Welcome to Lesson 02, let's play with Table Storage.");
24                 Console.WriteLine("\t1. Insert a new entity");
25                 Console.WriteLine("\t2. Update a specific entity");
26                 Console.WriteLine("\t3. Delete a specific entity");
27                 Console.WriteLine("\t4. Insert a new batch of entities");
28                 Console.WriteLine("\t5. Display all entities");
29                 Console.WriteLine("\t6. Exit");
30 
31                 Console.WriteLine("So what do you want to do?");
32 
33                 int input;
34                 if (!int.TryParse(Console.ReadKey().KeyChar.ToString(), out input)) return;
35 
36                 switch (input)
37                 {
38                     case 1:
39                         InsertNewEntity();
40                         break;
41                     case 2:
42                         UpdateSpecificEntity();
43                         break;
44                     case 3:
45                         DeleteSpecificEntity();
46                         break;
47                     case 4:
48                         InsertNewBatch();
49                         break;
50                     case 5:
51                         DisplayAllEntities();
52                         break;
53                     case 6:
54                         Exit();
55                         break;
56                 }          
57             }
58         }
59 
60         private static void Exit()
61         {
62             Environment.Exit(0);
63         }

Insert a new entity

  1         private static void InsertNewEntity()
 2         {
 3             try
 4             {
 5                 var client = CloudStorageAccount.Parse(_connection).CreateCloudTableClient();
 6 
 7                 var table = client.GetTableReference(TableName);
 8                 table.CreateIfNotExists();
 9 
10                 table.Execute(TableOperation.Insert(
11                     new SomeEntity(PartitionName, Guid.NewGuid().ToString(),
12                         new Random().Next(0,9).ToString())));
13                 
14                 Console.Clear();
15                 Console.WriteLine(Success);
16                 Console.ReadKey();
17             }
18             catch (StorageException ex)
19             {
20                 Console.WriteLine(ex.Message);
21             }
22         }

Once I enter 1 as my choice, the function will be called to create a new entity with a random number between 0 and 9. When done it will display a message, either a success or an exception. Hitting any key will take you back to the main menu.

image

Below is the newly added entity to the Azure table.

image

Update a specific entity

To do that, you will have to do a retrieve operation first to get the entity and then afterwards update it and finally push it back. We will need both the Partition Key and Row Key to be able to retrieve the entity and update it.

  1         private static void UpdateSpecificEntity()
 2         {
 3             try
 4             {
 5                 var client = CloudStorageAccount.Parse(_connection).CreateCloudTableClient();
 6 
 7                 var table = client.GetTableReference(TableName);
 8                 table.CreateIfNotExists();
 9 
10                 Console.Clear();
11 
12                 Console.WriteLine("\nEnter partition key: ");
13                 var partitionKey = Console.ReadLine();
14 
15                 Console.WriteLine("\nEnter row key: ");
16                 var rowKey = Console.ReadLine();
17 
18                 Console.WriteLine("\nEnter some number: ");
19                 var someNumber = Console.ReadLine();
20 
21                 var retrieveOperation = TableOperation.Retrieve<SomeEntity>(partitionKey, rowKey);
22                 var retrievedResult = table.Execute(retrieveOperation);
23 
24                 var updateEntity = (SomeEntity)retrievedResult.Result;
25 
26                 if (updateEntity != null)
27                 {
28                     updateEntity.SomeRandomNumber = someNumber;
29 
30                     var updateOperation = TableOperation.InsertOrReplace(updateEntity);
31                     table.Execute(updateOperation);
32 
33                     Console.WriteLine(Success);
34                 }
35                 else
36                 {
37                     Console.WriteLine(Fail);
38                 }
39 
40                 Console.ReadKey();
41             }
42             catch (StorageException ex)
43             {
44                 Console.WriteLine(ex.Message);
45             }          
46         }

In the below screenshots you can see me updating one of the entities with Partition Key “Lesson02” and RowKey “aab27b1a-4a97-44b4-994c-daef89865d9a”.

image

image

image

Delete a specific entity

Delete shares the same logic as updates. You will have to retrieve the entity before deleting it.

  1         private static void DeleteSpecificEntity()
 2         {
 3             try
 4             {
 5                 var client = CloudStorageAccount.Parse(_connection).CreateCloudTableClient();
 6 
 7                 var table = client.GetTableReference(TableName);
 8                 table.CreateIfNotExists();
 9 
10                 Console.Clear();
11 
12                 Console.WriteLine("\nEnter partition key: ");
13                 var partitionKey = Console.ReadLine();
14 
15                 Console.WriteLine("\nEnter row key: ");
16                 var rowKey = Console.ReadLine();
17 
18                 var retrieveOperation = TableOperation.Retrieve<SomeEntity>(partitionKey, rowKey);
19                 var retrievedResult = table.Execute(retrieveOperation);
20 
21                 var deleteEntity = (SomeEntity)retrievedResult.Result;
22 
23                 if (deleteEntity != null)
24                 {
25                     var deleteOperation = TableOperation.Delete(deleteEntity);
26                     table.Execute(deleteOperation);
27                     
28                     Console.WriteLine(Success);
29                 }
30                 else
31                 {
32                     Console.WriteLine(Fail);
33                 }
34 
35                 Console.ReadKey();
36             }
37             catch (StorageException ex)
38             {
39                 Console.WriteLine(ex.Message);
40             }
41         }

In the below screenshots you can see me deleting one of the entities. Remember, you will always need the Partition Key and Row Key to retrieve entities.

image

image

Insert a new batch of entities

To insert a batch of entities, you will have to use a batch operation. In the below code we will insert 10 new entities every time the function is called.

  1         private static void InsertNewBatch()
 2         {
 3             try
 4             {
 5                 var client = CloudStorageAccount.Parse(_connection).CreateCloudTableClient();
 6 
 7                 var table = client.GetTableReference(TableName);
 8                 table.CreateIfNotExists();
 9 
10                 var batchOperation = new TableBatchOperation();
11                 for (var i = 0; i < 10; i++)
12                 {
13                     batchOperation.Insert(new SomeEntity(PartitionName, Guid.NewGuid().ToString(),i.ToString()));
14                 }
15 
16                 table.ExecuteBatch(batchOperation);
17 
18                 Console.Clear();
19                 Console.WriteLine(Success);
20                 Console.ReadKey();
21             }
22             catch (StorageException ex)
23             {
24                 Console.WriteLine(ex.Message);
25             }
26         }

Once done it will display a message, either a success or an exception. Hitting any key will take you back to the main menu.

image

Display all entities of single partition

Finally, we will use LINQ to query the table and get the entities of a specific partition. Up till now, you can do direct hits on string comparisons with the queries. You can not do stuff like Contains. I have been in project were we loaded tables into cache and did our LINQ queries there with Contains etc. Another option would be using Azure Search.

  1         private static void DisplayAllEntities()
 2         {
 3             try
 4             {
 5                 var client = CloudStorageAccount.Parse(_connection).CreateCloudTableClient();
 6 
 7                 var table = client.GetTableReference(TableName);
 8                 table.CreateIfNotExists();
 9 
10                 var query = new TableQuery<SomeEntity>().Where(
11                     TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, PartitionName));
12                 var list = table.ExecuteQuery(query).ToList();
13 
14                 Console.Clear();
15 
16                 foreach (var someEntity in list)
17                 {
18                     Console.WriteLine("{0}\t{1}\t{2}", someEntity.Timestamp, someEntity.RowKey, someEntity.SomeRandomNumber);
19                 }
20 
21                 Console.WriteLine(Success);
22                 Console.ReadKey();
23             }
24             catch (StorageException ex)
25             {
26                 Console.WriteLine(ex.Message);
27             }
28         }

In the screenshot below you can see the list of all entities sitting in the “Lesson02” partition.

image

aaaand we are done with the lesson. Let me know if you have any questions or comments Smile