Introduction
Azure Table Storage is a No SQL key attribute data store. This helps in storing large amount of structured data (in terabytes) that do not have complex relationship and are schemaless. Stored data is persistent, highly scalable and can be retrieved fast. This article demonstrates how to create a new Azure Storage table, do CRUD operations (read, insert, update and delete data) on the table and drop the table created using C# programming.
Background
Table Storage Data is stored in partitions spanning across multiple storage nodes. Each data entity stored has a Row key, a partition key and a time stamp associated with it. Each partition is identified by a Partition key. In a partition, each data entity is associated with a Row key. Combination of Partition key and Row key uniquely identifies a data entity. Time stamp associated with a data entity tracks when the data entity was last modified. Row key and Partition key can be modified by developers. Time stamp is managed by server and cannot be modified by developer.
Using the Code
Consoles application is created using Visual Studio 2015 to demonstrate the Table storage operations in this article. Windows Azure Storage SDK is installed in the consoles applications from Nuget Package Manager
Create Customer Entity
Data entity class Customer is created. This entity data will be saved to Table storage. Customer class is derived from TableEntity class that is there in Microsoft.WindowsAzure.Storage.Table namespace. Each entity data in a table storage has to be associated with a Partition key and Row key. AssignPartitionKey method assigns customer type as Partition key and AssignRowKey method assigns customer id as Row key.
class Customer:TableEntity
{
private int customerID;
private string customerName;
private string customerDetails;
private string customerType;
public void AssignRowKey()
{
this.RowKey = customerID.ToString();
}
public void AssignPartitionKey()
{
this.PartitionKey = customerType;
}
public int CustomerID
{
get
{
return customerID;
}
set
{
customerID = value;
}
}
public string CustomerName
{
get
{
return customerName;
}
set
{
customerName = value;
}
}
public string CustomerDetails
{
get
{
return customerDetails;
}
set
{
customerDetails = value;
}
}
public string CustomerType
{
get
{
return customerType;
}
set
{
customerType = value;
}
}
}
Add Connection String to Configuration File
Connection string for Table storage contains the storage account name and access key for the storage account that can be picked up from Azure portal.
<add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;
AccountName=[Name of storage account];AccountKey=[Access Key for storage account]">
Create a Table
CloudStorageAccount.Parse method parses the connection string and returns an object of cloud storage account. Tables and entities stored in Table storage can be accessed using CloudTableClient class. GetTableReference method takes the table name as parameter and returns the reference to the cloud table. CreateIfNotExists method creates a new table if the table does not exist.
CloudStorageAccount cloudStorageAccount =
CloudStorageAccount.Parse
(ConfigurationManager.AppSettings["StorageConnectionString"]);
CloudTableClient tableClient = cloudStorageAccount.CreateCloudTableClient();
Console.WriteLine("Enter Table Name to create");
string tableName = Console.ReadLine();
CloudTable cloudTable = tableClient.GetTableReference(tableName);
CreateNewTable(cloudTable);
public static void CreateNewTable(CloudTable table)
{
if (!table.CreateIfNotExists())
{
Console.WriteLine("Table {0} already exists", table.Name);
return;
}
Console.WriteLine("Table {0} created", table.Name);
}
Insert and Update Data
TableOperation.Insert method takes customer entity as input and returns TableOperation object that has to be executed against the table. RetrieveRecord method searches and fetches the customer entity data that is being inserted. If the same customer data entity already exists, insert should not happen. It is partion to assign Row key and Partition key to the data record by invoking AssignPartitionKey and AssignRowKey methods in customer entity class.
public static void InsertRecordToTable(CloudTable table)
{
Console.WriteLine("Enter customer type");
string customerType = Console.ReadLine();
Console.WriteLine("Enter customer ID");
string customerID = Console.ReadLine();
Console.WriteLine("Enter customer name");
string customerName = Console.ReadLine();
Console.WriteLine("Enter customer details");
string customerDetails = Console.ReadLine();
Customer customerEntity = new Customer();
customerEntity.CustomerType = customerType;
customerEntity.CustomerID = Int32.Parse(customerID);
customerEntity.CustomerDetails = customerDetails;
customerEntity.CustomerName = customerName;
customerEntity.AssignPartitionKey();
customerEntity.AssignRowKey();
Customer custEntity = RetrieveRecord(table, customerType, customerID);
if (custEntity == null)
{
TableOperation tableOperation = TableOperation.Insert(customerEntity);
table.Execute(tableOperation);
Console.WriteLine("Record inserted");
}
else
{
Console.WriteLine("Record exists");
}
}
RetrieveRecord queries the table and returns customer entity that matches with the row key and partition key being queried for.
public static Customer RetrieveRecord(CloudTable table,string partitionKey,string rowKey)
{
TableOperation tableOperation = TableOperation.Retrieve<Customer>(partitionKey, rowKey);
TableResult tableResult = table.Execute(tableOperation);
return tableResult.Result as Customer;
}
Entity records are uniquely identified by a partition key and row key combination. Before updating the record, it is searched in the table using RetrieveRecord method by passing the partition key and row key as parameter. If the entity exists, then only update operation is done. TableOperation.Replace method does the update operation.
public static void UpdateRecordInTable(CloudTable table)
{
Console.WriteLine("Enter customer type");
string customerType = Console.ReadLine();
Console.WriteLine("Enter customer ID");
string customerID = Console.ReadLine();
Console.WriteLine("Enter customer name");
string customerName = Console.ReadLine();
Console.WriteLine("Enter customer details");
string customerDetails = Console.ReadLine();
Customer customerEntity = RetrieveRecord(table, customerType, customerID);
if (customerEntity != null)
{
customerEntity.CustomerDetails = customerDetails;
customerEntity.CustomerName = customerName;
TableOperation tableOperation = TableOperation.Replace(customerEntity);
table.Execute(tableOperation);
Console.WriteLine("Record updated");
}
else
{
Console.WriteLine("Record does not exists");
}
}
Display All Data Stored in Table
TableQuery object is created and executed against the table using ExecuteQuery.
public static void DisplayTableRecords(CloudTable table)
{
TableQuery<Customer> tableQuery = new TableQuery<Customer>();
foreach (Customer customerEntity in table.ExecuteQuery(tableQuery))
{
Console.WriteLine("Customer ID : {0}", customerEntity.CustomerID);
Console.WriteLine("Customer Type : {0}", customerEntity.CustomerType);
Console.WriteLine("Customer Name : {0}", customerEntity.CustomerName);
Console.WriteLine("Customer Details : {0}", customerEntity.CustomerDetails);
Console.WriteLine("******************************");
}
}
Delete Data from Table and Drop Table
TableOperation.Delete creates the table delete operation object that has to be executed against the table. Record to be deleted is identified by Row key and Partition key. If the record exists, then only it is deleted.
public static void DeleteRecordinTable(CloudTable table)
{
Console.WriteLine("Enter customer type");
string customerType = Console.ReadLine();
Console.WriteLine("Enter customer ID");
string customerID = Console.ReadLine();
Customer customerEntity = RetrieveRecord(table, customerType, customerID);
if (customerEntity != null)
{
TableOperation tableOperation = TableOperation.Delete(customerEntity);
table.Execute(tableOperation);
Console.WriteLine("Record deleted");
}
else
{
Console.WriteLine("Record does not exists");
}
}
DeleteIfExists method drops the table in Table storage if it exists.
public static void DropTable(CloudTable table)
{
if (!table.DeleteIfExists())
{
Console.WriteLine("Table does not exists");
}
}
Points of Interest
Sample code is attached along with this article. The solution is developed using Visual Studio 2015 community edition. To try out the sample proper Azure storage account name and access key has to be specified in the configuration file. Make sure that you are building the solution when you are connected to the internet so as to restore the nuget packages.
