Create and Query Azure Storage Tables

Introduction

Azure Table Storage is a NoSQL key-attribute store that allows you to store large amounts of structured, non-relational data. It’s a cost-effective and scalable solution for many common application needs.

This tutorial will guide you through the process of creating a new Azure Storage Table, inserting data entities, and querying those entities using the Azure SDK for .NET.

Prerequisites

• An Azure account. If you don't have one, sign up for a free account.
• A Storage Account. If you don't have one, you can create one in the Azure portal.
• .NET SDK installed on your machine.
• Visual Studio or your preferred .NET IDE.

Step 1: Set up your .NET Project

Create a new .NET Console Application project. You can do this via the command line:

dotnet new console -n AzureStorageTableTutorial
cd AzureStorageTableTutorial

Add the necessary Azure SDK packages:

dotnet add package Azure.Data.Tables

Step 2: Connect to Azure Storage Table

You'll need your Storage Account connection string. You can find this in the Azure portal under your Storage Account's "Access keys" section.

In your Program.cs file, add the following code:

using Azure;
using Azure.Data.Tables;
using System;
using System.Threading.Tasks;

public class Program
{
    private const string connectionString = "YOUR_AZURE_STORAGE_CONNECTION_STRING"; // Replace with your actual connection string
    private const string tableName = "MySampleTable";

    public static async Task Main(string[] args)
    {
        TableServiceClient tableServiceClient = new TableServiceClient(connectionString);
        TableClient tableClient = await tableServiceClient.CreateTableIfNotExistsAsync(tableName);

        Console.WriteLine($"Table '{tableName}' is ready.");

        // Further steps will go here
        await InsertEntityAsync(tableClient);
        await QueryEntitiesAsync(tableClient);
    }

    // Placeholder for InsertEntityAsync
    private static async Task InsertEntityAsync(TableClient tableClient) { }
    // Placeholder for QueryEntitiesAsync
    private static async Task QueryEntitiesAsync(TableClient tableClient) { }
}

Step 3: Define and Insert Entities

Entities in Azure Table Storage are represented as classes that implement the ITableEntity interface. The most important properties are PartitionKey and RowKey, which together form the primary key of an entity.

Let's create a simple entity for a product.

Add the following class definition to your Program.cs file:

public class Product : ITableEntity
{
    public string PartitionKey { get; set; } = "Products"; // Common PartitionKey for all products
    public string RowKey { get; set; } = Guid.NewGuid().ToString(); // Unique identifier for each product
    public string Name { get; set; }
    public double Price { get; set; }
    public DateTimeOffset? Timestamp { get; set; }
    public ETag ETag { get; set; }

    public Product(string name, double price)
    {
        Name = name;
        Price = price;
    }

    // Parameterless constructor required for ITableEntity deserialization
    public Product() { }
}

Now, update the InsertEntityAsync method in Program.cs:

private static async Task InsertEntityAsync(TableClient tableClient)
{
    var product1 = new Product("Laptop", 1200.50);
    var product2 = new Product("Keyboard", 75.99);
    var product3 = new Product("Mouse", 25.00);

    try
    {
        await tableClient.UpsertEntityAsync(product1);
        await tableClient.UpsertEntityAsync(product2);
        await tableClient.UpsertEntityAsync(product3);
        Console.WriteLine("Entities inserted successfully.");
    }
    catch (RequestFailedException ex)
    {
        Console.WriteLine($"Error inserting entity: {ex.Message}");
    }
}

Step 4: Query Entities

You can query entities by specifying filters based on PartitionKey, RowKey, and other properties.

Update the QueryEntitiesAsync method in Program.cs:

private static async Task QueryEntitiesAsync(TableClient tableClient)
{
    Console.WriteLine("\n--- Querying Entities ---");

    // Query all entities in the table
    Console.WriteLine("All Products:");
    await foreach (Product product in tableClient.QueryAsync())
    {
        Console.WriteLine($"- Name: {product.Name}, Price: {product.Price:C}");
    }

    // Query entities with a specific PartitionKey
    Console.WriteLine("\nProducts in 'Products' Partition:");
    string partitionFilter = $"PartitionKey eq '{product1.PartitionKey}'"; // Assuming product1 is accessible or using a known PartitionKey
    await foreach (Product product in tableClient.QueryAsync(filter: partitionFilter))
    {
        Console.WriteLine($"- Name: {product.Name}, Price: {product.Price:C}");
    }

    // Query entities with multiple filters (e.g., price greater than 50)
    Console.WriteLine("\nProducts with Price > $50:");
    string priceFilter = $"Price gt 50.00";
    await foreach (Product product in tableClient.QueryAsync(filter: priceFilter))
    {
        Console.WriteLine($"- Name: {product.Name}, Price: {product.Price:C}");
    }
}

Step 5: Run the Application

Replace YOUR_AZURE_STORAGE_CONNECTION_STRING with your actual connection string in Program.cs, and then run the application:

dotnet run

You should see output similar to this (with actual prices and potentially different GUIDs for RowKeys):

Table 'MySampleTable' is ready.
Entities inserted successfully.

--- Querying Entities ---
All Products:
- Name: Laptop, Price: $1,200.50
- Name: Keyboard, Price: $75.99
- Name: Mouse, Price: $25.00

Products in 'Products' Partition:
- Name: Laptop, Price: $1,200.50
- Name: Keyboard, Price: $75.99
- Name: Mouse, Price: $25.00

Products with Price > $50:
- Name: Laptop, Price: $1,200.50
- Name: Keyboard, Price: $75.99

Conclusion

Congratulations! You've successfully created an Azure Storage Table, inserted entities, and performed queries. Azure Table Storage is a powerful tool for managing large datasets in a cost-effective and scalable manner.

For more advanced scenarios, explore:

• Using different data types.
• Implementing complex queries with the Query Builder API.
• Batch operations.
• Transactions.
• Access control and security.

Explore More Azure Table Storage Features