Skip to content

Files

Latest commit

 

History

History
593 lines (443 loc) · 35.1 KB

table-storage-how-to-use-dotnet.md

File metadata and controls

593 lines (443 loc) · 35.1 KB
title description services author ms.service ms.component ms.devlang ms.topic ms.date ms.author
Get started with Azure Table storage and Azure Cosmos DB Table API using .NET
Store structured data in the cloud using Azure Table storage or the Azure Cosmos DB Table API.
cosmos-db
SnehaGunda
cosmos-db
cosmosdb-table
dotnet
sample
08/17/2018
sngun

Get started with Azure Table storage and the Azure Cosmos DB Table API using .NET

[!INCLUDE storage-selector-table-include] [!INCLUDE storage-table-applies-to-storagetable-and-cosmos]

You can use Azure Table storage or the Azure Cosmos DB Table API to store structured NoSQL data in the cloud, providing a key/attribute store with a schemaless design. Because Table storage and the Azure Cosmos DB Table API are schemaless, it's easy to adapt your data as the needs of your application evolve. Access to Table storage data and the Azure Cosmos DB Table API is fast and cost-effective for many types of applications, and is typically lower in cost than traditional SQL for similar volumes of data.

You can use Table storage or the Azure Cosmos DB Table API to store flexible datasets such as user data for web applications, address books, device information, or other types of metadata your service requires. You can store any number of entities in a table, and a storage account or Table API account may contain any number of tables, up to the capacity limit of the storage account or Table API account.

About this sample

This sample shows you how to use the Microsoft Azure CosmosDB Table Library for .NET in common Azure Table storage and Table API scenarios. The name of the package indicates it is for use with Azure Cosmos DB, but the package works with both the Azure Cosmos DB Table API and Azure Tables storage, each service just has a unique endpoint. These scenarios are explored using C# examples that illustrate how to:

  • Create and delete tables
  • Insert, update and delete rows
  • Query tables

Prerequisites

You need the following to complete this sample successfully:

[!INCLUDE storage-dotnet-client-library-version-include]

More samples

For additional examples using Table storage, see Getting Started with Azure Table Storage in .NET. You can download the sample application and run it, or browse the code on GitHub.

Create an Azure service account

[!INCLUDE cosmos-db-create-azure-service-account]

Create an Azure storage account

Create an Azure Cosmos DB Table API account

[!INCLUDE cosmos-db-create-tableapi-account]

Create a Windows console application project

In Visual Studio, create a new Windows console application. The following steps show you how to create a console application in Visual Studio 2017. The steps are similar in other versions of Visual Studio.

  1. Select File > New > Project.
  2. Select Installed > Visual C# > Windows Classic Desktop.
  3. Select Console App (.NET Framework).
  4. In the Name field, enter a name for your application.
  5. Select OK.

All code examples in this sample can be added to the Main() method of your console application's Program.cs file.

You can use the Azure CosmosDB Table Library in any type of .NET application, including an Azure cloud service or web app, and desktop and mobile applications. In this guide, we use a console application for simplicity.

Install the required NuGet packages

There are three recommended packages you need to reference in your project to complete this sample:

To obtain the NuGet packages, follow these steps:

  1. Right-click your project in Solution Explorer, and choose Manage NuGet Packages.
  2. Search online for "Microsoft.Azure.Storage.Common", choose version <= 9.0.0.1 and select Install to install the Azure Storage Common Library for .NET (Preview) and its dependencies. Ensure the Include prerelease box is checked as this is a preview package.
  3. Search online for "Microsoft.Azure.CosmosDB.Table", and select Install to install the Microsoft Azure CosmosDB Table Library.
  4. Search online for "WindowsAzure.ConfigurationManager", and select Install to install the Microsoft Azure Configuration Manager Library.

Note

The ODataLib dependencies in the Storage Common Library for .NET are resolved by the ODataLib packages available on NuGet, not from WCF Data Services. The ODataLib libraries can be downloaded directly, or referenced by your code project through NuGet. The specific ODataLib packages used by the Storage Client Library are OData, Edm, and Spatial. While these libraries are used by the Azure Table storage classes, they are required dependencies for programming with the Storage Common Library.

Tip

Developers already familiar with Azure Table storage may have used the WindowsAzure.Storage package in the past. It is recommended that all new table applications use the Azure Storage Common Library and the Azure Cosmos DB Table Library, however the WindowsAzure.Storage package is still supported. If you use the WindowsAzure.Storage library, include Microsoft.WindowsAzure.Storage.Table in your using statements.

Determine your target environment

You have three environment options for running the examples in this guide:

  • You can run your code against an Azure Storage account in the cloud.
  • You can run your code against an Azure Cosmos DB account in the cloud.
  • You can run your code against the Azure storage emulator. The storage emulator is a local environment that emulates an Azure Storage account in the cloud. The emulator is a free option for testing and debugging your code while your application is under development. The emulator uses a well-known account and key. For more information, see Use the Azure storage emulator for development and testing.

If you are targeting a storage account in the cloud, copy the primary access key for your storage account from the Azure portal. For more information, see Storage account access keys.

Note

You can target the storage emulator to avoid incurring any costs associated with Azure Storage. However, if you do choose to target an Azure storage account in the cloud, costs for performing this sample will be negligible.

If you are targeting an Azure Cosmos DB account, copy the primary access key for your Table API account from the Azure portal. For more information, see Update your connection string.

Configure your storage connection string

The Azure Storage Common Library for .NET supports using a storage connection string to configure endpoints and credentials for accessing storage services. The best way to maintain your storage connection string is in a configuration file.

For more information about connection strings, see Configure a connection string to Azure Storage.

Note

Your account key is similar to the root password for your storage account. Always be careful to protect your storage account key. Avoid distributing it to other users, hard-coding it, or saving it in a plain-text file that is accessible to others. Regenerate your key by using the Azure portal if you believe it may have been compromised.

To configure your connection string, open the app.config file from Solution Explorer in Visual Studio. Add the contents of the <appSettings> element shown below. Replace account-name with the name of your account, and account-key with your account access key:

<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
    <appSettings>
        <add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=account-name;AccountKey=account-key" />
    </appSettings>
</configuration>

For example, if you're using an Azure Storage account, your configuration settings appear similar to:

<add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=storagesample;AccountKey=<account-key>" />

If you're using an Azure Cosmos DB account, your configuration settings appear similar to:

<add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=tableapiacct;AccountKey=<account-key>;TableEndpoint=https://tableapiacct.table.cosmosdb.azure.com:443/;" />

To target the storage emulator, you can use a shortcut that maps to the well-known account name and key. In that case, your connection string setting is:

<add key="StorageConnectionString" value="UseDevelopmentStorage=true;" />

Add using directives

Add the following using directives to the top of the Program.cs file:

using Microsoft.Azure; // Namespace for CloudConfigurationManager
using Microsoft.Azure.Storage; // Namespace for StorageAccounts
using Microsoft.Azure.CosmosDB.Table; // Namespace for Table storage types

Parse the connection string

[!INCLUDE storage-cloud-configuration-manager-include]

Create the Table service client

The CloudTableClient class enables you to retrieve tables and entities stored in Table storage. Here's one way to create the Table service client:

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

Now you are ready to write code that reads data from and writes data to Table storage.

Create a table

This example shows how to create a table if it does not already exist:

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Retrieve a reference to the table.
CloudTable table = tableClient.GetTableReference("people");

// Create the table if it doesn't exist.
table.CreateIfNotExists();

Add an entity to a table

Entities map to C# objects by using a custom class derived from TableEntity. To add an entity to a table, create a class that defines the properties of your entity. The following code defines an entity class that uses the customer's first name as the row key and last name as the partition key. Together, an entity's partition and row key uniquely identify it in the table. Entities with the same partition key can be queried faster than entities with different partition keys, but using diverse partition keys allows for greater scalability of parallel operations. Entities to be stored in tables must be of a supported type, for example derived from the TableEntity class. Entity properties you'd like to store in a table must be public properties of the type, and support both getting and setting of values. Also, your entity type must expose a parameter-less constructor.

public class CustomerEntity : TableEntity
{
    public CustomerEntity(string lastName, string firstName)
    {
        this.PartitionKey = lastName;
        this.RowKey = firstName;
    }

    public CustomerEntity() { }

    public string Email { get; set; }

    public string PhoneNumber { get; set; }
}

Table operations that involve entities are performed via the CloudTable object that you created earlier in the "Create a table" section. The operation to be performed is represented by a TableOperation object. The following code example shows the creation of the CloudTable object and then a CustomerEntity object. To prepare the operation, a TableOperation object is created to insert the customer entity into the table. Finally, the operation is executed by calling CloudTable.Execute.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create a new customer entity.
CustomerEntity customer1 = new CustomerEntity("Harp", "Walter");
customer1.Email = "[email protected]";
customer1.PhoneNumber = "425-555-0101";

// Create the TableOperation object that inserts the customer entity.
TableOperation insertOperation = TableOperation.Insert(customer1);

// Execute the insert operation.
table.Execute(insertOperation);

Insert a batch of entities

You can insert a batch of entities into a table in one write operation. Some other notes on batch operations:

  • You can perform updates, deletes, and inserts in the same single batch operation.
  • A single batch operation can include up to 100 entities.
  • All entities in a single batch operation must have the same partition key.
  • While it is possible to perform a query as a batch operation, it must be the only operation in the batch.

The following code example creates two entity objects and adds each to TableBatchOperation by using the Insert method. Then, CloudTable.ExecuteBatch is called to execute the operation.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create the batch operation.
TableBatchOperation batchOperation = new TableBatchOperation();

// Create a customer entity and add it to the table.
CustomerEntity customer1 = new CustomerEntity("Smith", "Jeff");
customer1.Email = "[email protected]";
customer1.PhoneNumber = "425-555-0104";

// Create another customer entity and add it to the table.
CustomerEntity customer2 = new CustomerEntity("Smith", "Ben");
customer2.Email = "[email protected]";
customer2.PhoneNumber = "425-555-0102";

// Add both customer entities to the batch insert operation.
batchOperation.Insert(customer1);
batchOperation.Insert(customer2);

// Execute the batch operation.
table.ExecuteBatch(batchOperation);

Retrieve all entities in a partition

To query a table for all entities in a partition, use a TableQuery object. The following code example specifies a filter for entities where 'Smith' is the partition key. This example prints the fields of each entity in the query results to the console.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Construct the query operation for all customer entities where PartitionKey="Smith".
TableQuery<CustomerEntity> query = new TableQuery<CustomerEntity>().Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Smith"));

// Print the fields for each customer.
foreach (CustomerEntity entity in table.ExecuteQuery(query))
{
    Console.WriteLine("{0}, {1}\t{2}\t{3}", entity.PartitionKey, entity.RowKey,
        entity.Email, entity.PhoneNumber);
}

Retrieve a range of entities in a partition

If you don't want to query all entities in a partition, you can specify a range by combining the partition key filter with a row key filter. The following code example uses two filters to get all entities in partition 'Smith' where the row key (first name) starts with a letter before 'E' in the alphabet, then prints the query results.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create the table query.
TableQuery<CustomerEntity> rangeQuery = new TableQuery<CustomerEntity>().Where(
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Smith"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThan, "E")));

// Loop through the results, displaying information about the entity.
foreach (CustomerEntity entity in table.ExecuteQuery(rangeQuery))
{
    Console.WriteLine("{0}, {1}\t{2}\t{3}", entity.PartitionKey, entity.RowKey,
        entity.Email, entity.PhoneNumber);
}

Retrieve a single entity

You can write a query to retrieve a single, specific entity. The following code uses TableOperation to specify the customer 'Ben Smith'. This method returns just one entity rather than a collection, and the returned value in TableResult.Result is a CustomerEntity object. Specifying both partition and row keys in a query is the fastest way to retrieve a single entity from the Table service.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create a retrieve operation that takes a customer entity.
TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>("Smith", "Ben");

// Execute the retrieve operation.
TableResult retrievedResult = table.Execute(retrieveOperation);

// Print the phone number of the result.
if (retrievedResult.Result != null)
{
    Console.WriteLine(((CustomerEntity)retrievedResult.Result).PhoneNumber);
}
else
{
    Console.WriteLine("The phone number could not be retrieved.");
}

Replace an entity

To update an entity, retrieve it from the Table service, modify the entity object, and then save the changes back to the Table service. The following code changes an existing customer's phone number. Instead of calling Insert, this code uses Replace. Replace causes the entity to be fully replaced on the server, unless the entity on the server has changed since it was retrieved, in which case the operation will fail. This failure is to prevent your application from inadvertently overwriting a change made between the retrieval and update by another component of your application. The proper handling of this failure is to retrieve the entity again, make your changes (if still valid), and then perform another Replace operation. The next section will show you how to override this behavior.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create a retrieve operation that takes a customer entity.
TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>("Smith", "Ben");

// Execute the operation.
TableResult retrievedResult = table.Execute(retrieveOperation);

// Assign the result to a CustomerEntity object.
CustomerEntity updateEntity = (CustomerEntity)retrievedResult.Result;

if (updateEntity != null)
{
    // Change the phone number.
    updateEntity.PhoneNumber = "425-555-0105";

    // Create the Replace TableOperation.
    TableOperation updateOperation = TableOperation.Replace(updateEntity);

    // Execute the operation.
    table.Execute(updateOperation);

    Console.WriteLine("Entity updated.");
}
else
{
    Console.WriteLine("Entity could not be retrieved.");
}

Insert-or-replace an entity

Replace operations will fail if the entity has been changed since it was retrieved from the server. Furthermore, you must retrieve the entity from the server first in order for the Replace operation to be successful. Sometimes, however, you don't know if the entity exists on the server and the current values stored in it are irrelevant. Your update should overwrite them all. To accomplish this, you would use an InsertOrReplace operation. This operation inserts the entity if it doesn't exist, or replaces it if it does, regardless of when the last update was made.

In the following code example, a customer entity for 'Fred Jones' is created and inserted into the 'people' table. Next, we use the InsertOrReplace operation to save an entity with the same partition key (Jones) and row key (Fred) to the server, this time with a different value for the PhoneNumber property. Because we use InsertOrReplace, all of its property values are replaced. However, if a 'Fred Jones' entity hadn't already existed in the table, it would have been inserted.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create a customer entity.
CustomerEntity customer3 = new CustomerEntity("Jones", "Fred");
customer3.Email = "[email protected]";
customer3.PhoneNumber = "425-555-0106";

// Create the TableOperation object that inserts the customer entity.
TableOperation insertOperation = TableOperation.Insert(customer3);

// Execute the operation.
table.Execute(insertOperation);

// Create another customer entity with the same partition key and row key.
// We've already created a 'Fred Jones' entity and saved it to the
// 'people' table, but here we're specifying a different value for the
// PhoneNumber property.
CustomerEntity customer4 = new CustomerEntity("Jones", "Fred");
customer4.Email = "[email protected]";
customer4.PhoneNumber = "425-555-0107";

// Create the InsertOrReplace TableOperation.
TableOperation insertOrReplaceOperation = TableOperation.InsertOrReplace(customer4);

// Execute the operation. Because a 'Fred Jones' entity already exists in the
// 'people' table, its property values will be overwritten by those in this
// CustomerEntity. If 'Fred Jones' didn't already exist, the entity would be
// added to the table.
table.Execute(insertOrReplaceOperation);

Query a subset of entity properties

A table query can retrieve just a few properties from an entity instead of all the entity properties. This technique, called projection, reduces bandwidth and can improve query performance, especially for large entities. The query in the following code returns only the email addresses of entities in the table. This is done by using a query of DynamicTableEntity and also EntityResolver. You can learn more about projection in the Introducing Upsert and Query Projection blog post. Projection is not supported by the storage emulator, so this code runs only when you're using an account in the Table service.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Define the query, and select only the Email property.
TableQuery<DynamicTableEntity> projectionQuery = new TableQuery<DynamicTableEntity>().Select(new string[] { "Email" });

// Define an entity resolver to work with the entity after retrieval.
EntityResolver<string> resolver = (pk, rk, ts, props, etag) => props.ContainsKey("Email") ? props["Email"].StringValue : null;

foreach (string projectedEmail in table.ExecuteQuery(projectionQuery, resolver, null, null))
{
    Console.WriteLine(projectedEmail);
}

Delete an entity

You can easily delete an entity after you have retrieved it by using the same pattern shown for updating an entity. The following code retrieves and deletes a customer entity.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create a retrieve operation that expects a customer entity.
TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>("Smith", "Ben");

// Execute the operation.
TableResult retrievedResult = table.Execute(retrieveOperation);

// Assign the result to a CustomerEntity.
CustomerEntity deleteEntity = (CustomerEntity)retrievedResult.Result;

// Create the Delete TableOperation.
if (deleteEntity != null)
{
    TableOperation deleteOperation = TableOperation.Delete(deleteEntity);

    // Execute the operation.
    table.Execute(deleteOperation);

    Console.WriteLine("Entity deleted.");
}
else
{
    Console.WriteLine("Could not retrieve the entity.");
}

Delete a table

Finally, the following code example deletes a table from a storage account. A table that has been deleted will be unavailable to be re-created for a period of time following the deletion.

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Delete the table it if exists.
table.DeleteIfExists();

Retrieve entities in pages asynchronously

If you are reading a large number of entities, and you want to process/display entities as they are retrieved rather than waiting for them all to return, you can retrieve entities by using a segmented query. This example shows how to return results in pages by using the Async-Await pattern so that execution is not blocked while you're waiting for a large set of results to return. For more details on using the Async-Await pattern in .NET, see Asynchronous programming with Async and Await (C# and Visual Basic).

// Initialize a default TableQuery to retrieve all the entities in the table.
TableQuery<CustomerEntity> tableQuery = new TableQuery<CustomerEntity>();

// Initialize the continuation token to null to start from the beginning of the table.
TableContinuationToken continuationToken = null;

do
{
    // Retrieve a segment (up to 1,000 entities).
    TableQuerySegment<CustomerEntity> tableQueryResult =
        await table.ExecuteQuerySegmentedAsync(tableQuery, continuationToken);

    // Assign the new continuation token to tell the service where to
    // continue on the next iteration (or null if it has reached the end).
    continuationToken = tableQueryResult.ContinuationToken;

    // Print the number of rows retrieved.
    Console.WriteLine("Rows retrieved {0}", tableQueryResult.Results.Count);

// Loop until a null continuation token is received, indicating the end of the table.
} while(continuationToken != null);

Next steps

Now that you've learned the basics of Table storage, follow these links to learn about more complex storage tasks: