Get started with Azure table storage and Visual Studio Connected Services (ASP.NET)

Tip

Try the Microsoft Azure Storage Explorer

Microsoft Azure Storage Explorer is a free, standalone app from Microsoft that enables you to work visually with Azure Storage data on Windows, macOS, and Linux.

Overview

Azure Table storage enables you to store large amounts of structured data. The service is a NoSQL datastore that accepts authenticated calls from inside and outside the Azure cloud. Azure tables are ideal for storing structured, non-relational data.

This tutorial shows how to write ASP.NET code for some common scenarios using Azure table storage entities. These scenarios include creating a table, and adding, querying, and deleting table entities.

Prerequisites

What is Table storage

Azure Table storage stores large amounts of structured data. The service is a NoSQL datastore which accepts authenticated calls from inside and outside the Azure cloud. Azure tables are ideal for storing structured, non-relational data. Common uses of Table storage include:

  • Storing TBs of structured data capable of serving web scale applications
  • Storing datasets that don't require complex joins, foreign keys, or stored procedures and can be denormalized for fast access
  • Quickly querying data using a clustered index
  • Accessing data using the OData protocol and LINQ queries with WCF Data Service .NET Libraries

You can use Table storage to store and query huge sets of structured, non-relational data, and your tables will scale as demand increases.

Table storage concepts

Table storage contains the following components:

Tables storage component diagram

  • URL format: Code addresses tables in an account using this address format:
    http://<storage account>.table.core.windows.net/<table>

    You can address Azure tables directly using this address with the OData protocol. For more information, see OData.org.

  • Storage Account: All access to Azure Storage is done through a storage account. See Azure Storage Scalability and Performance Targets for details about storage account capacity.
  • Table: A table is a collection of entities. Tables don't enforce a schema on entities, which means a single table can contain entities that have different sets of properties. The number of tables that a storage account can contain is limited only by the storage account capacity limit.
  • Entity: An entity is a set of properties, similar to a database row. An entity can be up to 1MB in size.
  • Properties: A property is a name-value pair. Each entity can include up to 252 properties to store data. Each entity also has three system properties that specify a partition key, a row key, and a timestamp. Entities with the same partition key can be queried more quickly, and inserted/updated in atomic operations. An entity's row key is its unique identifier within a partition.

For details about naming tables and properties, see Understanding the Table Service Data Model.

Create an Azure storage account

The easiest way to create your first Azure storage account is by using the Azure Portal. To learn more, see Create a storage account.

You can also create an Azure storage account by using Azure PowerShell, Azure CLI, or the Storage Resource Provider Client Library for .NET.

Set up the development environment

This section walks you setting up your development environment, including creating an ASP.NET MVC app, adding a Connected Services connection, adding a controller, and specifying the required namespace directives.

Create an ASP.NET MVC app project

  1. Open Visual Studio.

  2. Select File->New->Project from the main menu

  3. On the New Project dialog, specify the options as highlighted in the following figure:

    Create ASP.NET project

  4. Select OK.

  5. On the New ASP.NET Project dialog, specify the options as highlighted in the following figure:

    Specify MVC

  6. Select OK.

Use Connected Services to connect to an Azure storage account

  1. In the Solution Explorer, right-click the project, and from the context menu, select Add->Connected Service.

  2. On the Add Connected Service dialog, select Azure Storage, and then select Configure.

    Connected Service dialog

  3. On the Azure Storage dialog, select the desired Azure storage account with which you want to work, and select Add.

Create an MVC controller

  1. In the Solution Explorer, right-click Controllers, and, from the context menu, select Add->Controller.

    Add a controller to an ASP.NET MVC app

  2. On the Add Scaffold dialog, select MVC 5 Controller - Empty, and select Add.

    Specify MVC controller type

  3. On the Add Controller dialog, name the controller TablesController, and select Add.

    Name the MVC controller

  4. Add the following using directives to the TablesController.cs file:

    using Microsoft.Azure;
    using Microsoft.WindowsAzure.Storage;
    using Microsoft.WindowsAzure.Storage.Auth;
    using Microsoft.WindowsAzure.Storage.Table;
    

Create a model class

Many of the examples in this article use a TableEntity-derived class called CustomerEntity. The following steps guide you through declaring this class as a model class:

  1. In the Solution Explorer, right-click Models, and, from the context menu, select Add->Class.

  2. On the Add New Item dialog, name the class, CustomerEntity.

  3. Open the CustomerEntity.cs file, and add the following using directive:

    using Microsoft.WindowsAzure.Storage.Table;
    
  4. Modify the class so that, when finished, the class is declared as in the following code. The class declares an entity class called CustomerEntity that uses the customer's first name as the row key and last name as the partition key.

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

Create a table

The following steps illustrate how to create a table:

Note

This section assumes you have completed the steps in Set up the development environment.

  1. Open the TablesController.cs file.

  2. Add a method called CreateTable that returns an ActionResult.

    public ActionResult CreateTable()
    {
        // The code in this section goes here.
    
        return View();
    }
    
  3. Within the CreateTable method, get a CloudStorageAccount object that represents your storage account information. Use the following code to get the storage connection string and storage account information from the Azure service configuration: (Change <storage-account-name> to the name of the Azure storage account you're accessing.)

    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
       CloudConfigurationManager.GetSetting("<storage-account-name>_AzureStorageConnectionString"));
    
  4. Get a CloudTableClient object represents a table service client.

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
    
  5. Get a CloudTable object that represents a reference to the desired table name. The CloudTableClient.GetTableReference method does not make a request against table storage. The reference is returned whether or not the table exists.

    CloudTable table = tableClient.GetTableReference("TestTable");
    
  6. Call the CloudTable.CreateIfNotExists method to create the table if it does not yet exist. The CloudTable.CreateIfNotExists method returns true if the table does not exist, and is successfully created. Otherwise, false is returned.

    ViewBag.Success = table.CreateIfNotExists();
    
  7. Update the ViewBag with the name of the table.

    ViewBag.TableName = table.Name;
    
  8. In the Solution Explorer, expand the Views folder, right-click Tables, and from the context menu, select Add->View.

  9. On the Add View dialog, enter CreateTable for the view name, and select Add.

  10. Open CreateTable.cshtml, and modify it so that it looks like the following code snippet:

    @{
        ViewBag.Title = "Create Table";
    }
    
    <h2>Create Table results</h2>
    
    Creation of @ViewBag.TableName @(ViewBag.Success == true ? "succeeded" : "failed")
    
  11. In the Solution Explorer, expand the Views->Shared folder, and open _Layout.cshtml.

  12. After the last Html.ActionLink, add the following Html.ActionLink:

    <li>@Html.ActionLink("Create table", "CreateTable", "Tables")</li>
    
  13. Run the application, and select Create table to see results similar to the following screen shot:

    Create table

    As mentioned previously, the CloudTable.CreateIfNotExists method returns true only when the table doesn't exist and is created. Therefore, if you run the app when the table exists, the method returns false. To run the app multiple times, you must delete the table before running the app again. Deleting the table can be done via the CloudTable.Delete method. You can also delete the table using the Azure portal or the Microsoft Azure Storage Explorer.

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. In this section, you'll see how to define 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 the entity 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. For any property that should be stored in the table service, the property must be a public property of a supported type that exposes both setting and retrieving values. The entity class must declare a public parameter-less constructor.

Note

This section assumes you have completed the steps in Set up the development environment.

  1. Open the TablesController.cs file.

  2. Add the following directive so that the code in the TablesController.cs file can access the CustomerEntity class:

    using StorageAspnet.Models;
    
  3. Add a method called AddEntity that returns an ActionResult.

    public ActionResult AddEntity()
    {
        // The code in this section goes here.
    
        return View();
    }
    
  4. Within the AddEntity method, get a CloudStorageAccount object that represents your storage account information. Use the following code to get the storage connection string and storage account information from the Azure service configuration: (Change <storage-account-name> to the name of the Azure storage account you're accessing.)

    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
       CloudConfigurationManager.GetSetting("<storage-account-name>_AzureStorageConnectionString"));
    
  5. Get a CloudTableClient object represents a table service client.

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
    
  6. Get a CloudTable object that represents a reference to the table to which you are going to add the new entity.

    CloudTable table = tableClient.GetTableReference("TestTable");
    
  7. Instantiate and initialize the CustomerEntity class.

    CustomerEntity customer1 = new CustomerEntity("Harp", "Walter");
    customer1.Email = "Walter@contoso.com";
    
  8. Create a TableOperation object that inserts the customer entity.

    TableOperation insertOperation = TableOperation.Insert(customer1);
    
  9. Execute the insert operation by calling the CloudTable.Execute method. You can verify the result of the operation by inspecting the TableResult.HttpStatusCode property. A status code of 2xx indicates the action requested by the client was processed successfully. For example, successful insertions of new entities results in an HTTP status code of 204, meaning that the operation was successfully processed and the server did not return any content.

    TableResult result = table.Execute(insertOperation);
    
  10. Update the ViewBag with the table name, and the results of the insert operation.

    ViewBag.TableName = table.Name;
    ViewBag.Result = result.HttpStatusCode;
    
  11. In the Solution Explorer, expand the Views folder, right-click Tables, and from the context menu, select Add->View.

  12. On the Add View dialog, enter AddEntity for the view name, and select Add.

  13. Open AddEntity.cshtml, and modify it so that it looks like the following code snippet:

    @{
        ViewBag.Title = "Add entity";
    }
    
    <h2>Add entity results</h2>
    
    Insert of entity into @ViewBag.TableName @(ViewBag.Result == 204 ? "succeeded" : "failed")
    
  14. In the Solution Explorer, expand the Views->Shared folder, and open _Layout.cshtml.

  15. After the last Html.ActionLink, add the following Html.ActionLink:

    <li>@Html.ActionLink("Add entity", "AddEntity", "Tables")</li>
    
  16. Run the application, and select Add entity to see results similar to the following screen shot:

    Add entity

    You can verify that the entity was added by following the steps in the section, Get a single entity. You can also use the Microsoft Azure Storage Explorer to view all the entities for your tables.

Add a batch of entities to a table

In addition to being able to add an entity to a table one at a time, you can also add entities in batch. Adding entities in batch reduces the number of round-trips between your code and the Azure table service. The following steps illustrate how to add multiple entities to a table with a single insert operation:

Note

This section assumes you have completed the steps in Set up the development environment.

  1. Open the TablesController.cs file.

  2. Add a method called AddEntities that returns an ActionResult.

    public ActionResult AddEntities()
    {
        // The code in this section goes here.
    
        return View();
    }
    
  3. Within the AddEntities method, get a CloudStorageAccount object that represents your storage account information. Use the following code to get the storage connection string and storage account information from the Azure service configuration: (Change <storage-account-name> to the name of the Azure storage account you're accessing.)

    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
       CloudConfigurationManager.GetSetting("<storage-account-name>_AzureStorageConnectionString"));
    
  4. Get a CloudTableClient object represents a table service client.

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
    
  5. Get a CloudTable object that represents a reference to the table to which you are going to add the new entities.

    CloudTable table = tableClient.GetTableReference("TestTable");
    
  6. Instantiate some customer objects based on the CustomerEntity model class presented in the section, Add an entity to a table.

    CustomerEntity customer1 = new CustomerEntity("Smith", "Jeff");
    customer1.Email = "Jeff@contoso.com";
    
    CustomerEntity customer2 = new CustomerEntity("Smith", "Ben");
    customer2.Email = "Ben@contoso.com";
    
  7. Get a TableBatchOperation object.

    TableBatchOperation batchOperation = new TableBatchOperation();
    
  8. Add entities to the batch insert operation object.

    batchOperation.Insert(customer1);
    batchOperation.Insert(customer2);
    
  9. Execute the batch insert operation by calling the CloudTable.ExecuteBatch method.

    IList<TableResult> results = table.ExecuteBatch(batchOperation);
    
  10. The CloudTable.ExecuteBatch method returns a list of TableResult objects where each TableResult object can be examined to determine the success or failure of each individual operation. For this example, pass the list to a view and let the view display the results of each operation.

    return View(results);
    
  11. In the Solution Explorer, expand the Views folder, right-click Tables, and from the context menu, select Add->View.

  12. On the Add View dialog, enter AddEntities for the view name, and select Add.

  13. Open AddEntities.cshtml, and modify it so that it looks like the following.

    @model IEnumerable<Microsoft.WindowsAzure.Storage.Table.TableResult>
    @{
        ViewBag.Title = "AddEntities";
    }
    
    <h2>Add-entities results</h2>
    
    <table border="1">
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>HTTP result</th>
        </tr>
        @foreach (var result in Model)
        {
        <tr>
            <td>@((result.Result as StorageAspnet.Models.CustomerEntity).RowKey)</td>
            <td>@((result.Result as StorageAspnet.Models.CustomerEntity).PartitionKey)</td>
            <td>@result.HttpStatusCode</td>
        </tr>
        }
    </table>
    
  14. In the Solution Explorer, expand the Views->Shared folder, and open _Layout.cshtml.

  15. After the last Html.ActionLink, add the following Html.ActionLink:

    <li>@Html.ActionLink("Add entities", "AddEntities", "Tables")</li>
    
  16. Run the application, and select Add entities to see results similar to the following screen shot:

    Add entities

    You can verify that the entity was added by following the steps in the section, Get a single entity. You can also use the Microsoft Azure Storage Explorer to view all the entities for your tables.

Get a single entity

This section illustrates how to get a single entity from a table using the entity's row key and partition key.

Note

This section assumes you have completed the steps in Set up the development environment, and uses data from Add a batch of entities to a table.

  1. Open the TablesController.cs file.

  2. Add a method called GetSingle that returns an ActionResult.

    public ActionResult GetSingle()
    {
        // The code in this section goes here.
    
        return View();
    }
    
  3. Within the GetSingle method, get a CloudStorageAccount object that represents your storage account information. Use the following code to get the storage connection string and storage account information from the Azure service configuration: (Change <storage-account-name> to the name of the Azure storage account you're accessing.)

    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
       CloudConfigurationManager.GetSetting("<storage-account-name>_AzureStorageConnectionString"));
    
  4. Get a CloudTableClient object represents a table service client.

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
    
  5. Get a CloudTable object that represents a reference to the table from which you are retrieving the entity.

    CloudTable table = tableClient.GetTableReference("TestTable");
    
  6. Create a retrieve operation object that takes an entity object derived from TableEntity. The first parameter is the partitionKey, and the second parameter is the rowKey. Using the CustomerEntity class and data presented in the section Add a batch of entities to a table, the following code snippet queries the table for a CustomerEntity entity with a partitionKey value of "Smith" and a rowKey value of "Ben":

    TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>("Smith", "Ben");
    
  7. Execute the retrieve operation.

    TableResult result = table.Execute(retrieveOperation);
    
  8. Pass the result to the view for display.

    return View(result);
    
  9. In the Solution Explorer, expand the Views folder, right-click Tables, and from the context menu, select Add->View.

  10. On the Add View dialog, enter GetSingle for the view name, and select Add.

  11. Open GetSingle.cshtml, and modify it so that it looks like the following code snippet:

    @model Microsoft.WindowsAzure.Storage.Table.TableResult
    @{
        ViewBag.Title = "GetSingle";
    }
    
    <h2>Get Single results</h2>
    
    <table border="1">
        <tr>
            <th>HTTP result</th>
            <th>First name</th>
            <th>Last name</th>
            <th>Email</th>
        </tr>
        <tr>
            <td>@Model.HttpStatusCode</td>
            <td>@((Model.Result as StorageAspnet.Models.CustomerEntity).RowKey)</td>
            <td>@((Model.Result as StorageAspnet.Models.CustomerEntity).PartitionKey)</td>
            <td>@((Model.Result as StorageAspnet.Models.CustomerEntity).Email)</td>
        </tr>
    </table>
    
  12. In the Solution Explorer, expand the Views->Shared folder, and open _Layout.cshtml.

  13. After the last Html.ActionLink, add the following Html.ActionLink:

    <li>@Html.ActionLink("Get single", "GetSingle", "Tables")</li>
    
  14. Run the application, and select Get Single to see results similar to the following screen shot:

    Get single

Get all entities in a partition

As mentioned in the section, Add an entity to a table, the combination of a partition and a row key uniquely identify an entity in a table. Entities with the same partition key can be queried faster than entities with different partition keys. This section illustrates how to query a table for all the entities from a specified partition.

Note

This section assumes you have completed the steps in Set up the development environment, and uses data from Add a batch of entities to a table.

  1. Open the TablesController.cs file.

  2. Add a method called GetPartition that returns an ActionResult.

    public ActionResult GetPartition()
    {
        // The code in this section goes here.
    
        return View();
    }
    
  3. Within the GetPartition method, get a CloudStorageAccount object that represents your storage account information. Use the following code to get the storage connection string and storage account information from the Azure service configuration: (Change <storage-account-name> to the name of the Azure storage account you're accessing.)

    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
       CloudConfigurationManager.GetSetting("<storage-account-name>_AzureStorageConnectionString"));
    
  4. Get a CloudTableClient object represents a table service client.

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
    
  5. Get a CloudTable object that represents a reference to the table from which you are retrieving the entities.

    CloudTable table = tableClient.GetTableReference("TestTable");
    
  6. Instantiate a TableQuery object specifying the query in the Where clause. Using the CustomerEntity class and data presented in the section Add a batch of entities to a table, the following code snippet queries the table for a all entities where the PartitionKey (customer's last name) has a value of "Smith":

    TableQuery<CustomerEntity> query = 
        new TableQuery<CustomerEntity>()
        .Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Smith"));
    
  7. Within a loop, call the CloudTable.ExecuteQuerySegmented method passing the query object you instantiated in the previous step. The CloudTable.ExecuteQuerySegmented method returns a TableContinuationToken object that - when null - indicates that there are no more entities to retrieve. Within the loop, use another loop to iterate over the returned entities. In the following code example, each returned entity is added to a list. Once the loop ends, the list is passed to a view for display:

    List<CustomerEntity> customers = new List<CustomerEntity>();
    TableContinuationToken token = null;
    do
    {
        TableQuerySegment<CustomerEntity> resultSegment = table.ExecuteQuerySegmented(query, token);
        token = resultSegment.ContinuationToken;
    
        foreach (CustomerEntity customer in resultSegment.Results)
        {
            customers.Add(customer);
        }
    } while (token != null);
    
    return View(customers);
    
  8. In the Solution Explorer, expand the Views folder, right-click Tables, and from the context menu, select Add->View.

  9. On the Add View dialog, enter GetPartition for the view name, and select Add.

  10. Open GetPartition.cshtml, and modify it so that it looks like the following code snippet:

    @model IEnumerable<StorageAspnet.Models.CustomerEntity>
    @{
        ViewBag.Title = "GetPartition";
    }
    
    <h2>Get Partition results</h2>
    
    <table border="1">
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>Email</th>
        </tr>
        @foreach (var customer in Model)
        {
        <tr>
            <td>@(customer.RowKey)</td>
            <td>@(customer.PartitionKey)</td>
            <td>@(customer.Email)</td>
        </tr>
        }
    </table>
    
  11. In the Solution Explorer, expand the Views->Shared folder, and open _Layout.cshtml.

  12. After the last Html.ActionLink, add the following Html.ActionLink:

    <li>@Html.ActionLink("Get partition", "GetPartition", "Tables")</li>
    
  13. Run the application, and select Get Partition to see results similar to the following screen shot:

    Get Partition

Delete an entity

This section illustrates how to delete an entity from a table.

Note

This section assumes you have completed the steps in Set up the development environment, and uses data from Add a batch of entities to a table.

  1. Open the TablesController.cs file.

  2. Add a method called DeleteEntity that returns an ActionResult.

    public ActionResult DeleteEntity()
    {
        // The code in this section goes here.
    
        return View();
    }
    
  3. Within the DeleteEntity method, get a CloudStorageAccount object that represents your storage account information. Use the following code to get the storage connection string and storage account information from the Azure service configuration: (Change <storage-account-name> to the name of the Azure storage account you're accessing.)

    CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
       CloudConfigurationManager.GetSetting("<storage-account-name>_AzureStorageConnectionString"));
    
  4. Get a CloudTableClient object represents a table service client.

    CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
    
  5. Get a CloudTable object that represents a reference to the table from which you are deleting the entity.

    CloudTable table = tableClient.GetTableReference("TestTable");
    
  6. Create a delete operation object that takes an entity object derived from TableEntity. In this case, we use the CustomerEntity class and data presented in the section Add a batch of entities to a table. The entity's ETag must be set to a valid value.

    TableOperation deleteOperation = 
        TableOperation.Delete(new CustomerEntity("Smith", "Ben") { ETag = "*" } );
    
  7. Execute the delete operation.

    TableResult result = table.Execute(deleteOperation);
    
  8. Pass the result to the view for display.

    return View(result);
    
  9. In the Solution Explorer, expand the Views folder, right-click Tables, and from the context menu, select Add->View.

  10. On the Add View dialog, enter DeleteEntity for the view name, and select Add.

  11. Open DeleteEntity.cshtml, and modify it so that it looks like the following code snippet:

    @model Microsoft.WindowsAzure.Storage.Table.TableResult
    @{
        ViewBag.Title = "DeleteEntity";
    }
    
    <h2>Delete Entity results</h2>
    
    <table border="1">
        <tr>
            <th>First name</th>
            <th>Last name</th>
            <th>HTTP result</th>
        </tr>
        <tr>
            <td>@((Model.Result as StorageAspnet.Models.CustomerEntity).RowKey)</td>
            <td>@((Model.Result as StorageAspnet.Models.CustomerEntity).PartitionKey)</td>
            <td>@Model.HttpStatusCode</td>
        </tr>
    </table>
    
  12. In the Solution Explorer, expand the Views->Shared folder, and open _Layout.cshtml.

  13. After the last Html.ActionLink, add the following Html.ActionLink:

    <li>@Html.ActionLink("Delete entity", "DeleteEntity", "Tables")</li>
    
  14. Run the application, and select Delete entity to see results similar to the following screen shot:

    Get single

Next steps

View more feature guides to learn about additional options for storing data in Azure.