Azure Table Storage With ASP.NET Core WEB API

Introduction:

 In this blog, you will learn what is Azure table storage, when to use and how to connect and program with Azure table storage using ASP.NET Core WEB API.

What is Azure table storage?
  • The Azure Storage is one of the azure services used to store the unstructured data basically know as NoSQL. It offers a massively scalable object store for data objects, disk storage for Azure virtual machines, a file system service for the cloud, and a messaging store for reliable messaging.
  • An entity contains a set of properties, and each property defines a name-value pair.
  • It is a lightweight, easy to manage service, making it ideal for users just getting started with NoSQL or cloud data services.
  • You can store any number of entities in the table. One storage account may contain any number of tables, up to the capacity limit of the storage account.
Table Storage Concept Diagram
Cocept Diagram
When to Use?

When you want to store flexible datasets like user data for web applications, address books, device information, or other types of metadata your service requires.

“Each NoSQL database has its good and bad side”

Pros
  • Easy setup
  • Cheap
  • Minimal work required
  • Easy to understand
  • Simple model: Entity, PartitionKey, RowKey
Cons
  • Low on scalability
  • Lack of basic database operations – No «like» or «contains»
  • No backup procedure
  • Anytime you don’t specify both a partition key and row key in a WHERE clause, expect a slow table scan to occur.
Create an Azure Table Storage:

Step 1: Log in to the Azure portal

Step 2: Click on create the resource and search storage account

Storage Account Template

Step 3: Select the storage account template and click on create

Step 4: Enter Subscription, resource group, Storage account Name, region, performance, and redundancy from create a storage account wizard and click on Review + Create.

Step 5: Once the validation is passed click on create as shown below figure.

Step 6: After successful deployment, go to the storage account resource and select Tables from the Data Storage section.

Step 7: Click on Add table to create a new table. In my case, I named the table Employee

Programming with Azure Table:

From this section, you will learn how to program with Azure tables with ASP.NET Core WEB API.

Step 1: Create a new Empty ASP.NET Core Web API project from Visual Studio

Step 2: Add a new entity class

EmployeeEntity.cs

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

        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string PhoneNumber { get; set; }
    }

TableEntity class was defined in Microsoft.Azure.Cosmos.Table library.

Download Microsoft.Azure.Cosmos.Table package from NuGet Package manager.

The PartitionKey and the RowKey are mandatory for the Azure table  

PartitionKey: A table’s entities are organized by partition. A partition is a consecutive range of entities possessing the same partition key value. The partition key is a unique identifier for the partition within a given table, specified by the PartitionKey property

RowKey: The row key is a unique identifier for an entity within a given partition. Together with the PartitionKey and RowKey uniquely identify every entity within a table.

Step 3: Right click on the Controller folder and add a new empty Web API controller and name it as EmployeeController.cs

Step 4: Download Microsoft.Azure.Cosmos.Table library from NuGet Package Manager.

POST Action

[HttpPost]
        public IEnumerable<EmployeeEntity> Post([FromBody]EmployeeEntity emp)
        {
            string _dbCon1 = config.GetSection("ConnectionStrings").GetSection("MyAzureTable").Value;
            // Method 2
            string _dbCon2 = config.GetValue<string>("ConnectionStrings:MyAzureTable");
            var account = CloudStorageAccount.Parse(_dbCon2);
            var client = account.CreateCloudTableClient();

            var table = client.GetTableReference("Employee");

            table.CreateIfNotExists();

            EmployeeEntity employeeEntity = new EmployeeEntity(emp.FirstName, emp.LastName);
            employeeEntity.FirstName = emp.FirstName;
            employeeEntity.LastName = emp.LastName;           
            employeeEntity.PhoneNumber = emp.PhoneNumber;
            employeeEntity.Email = emp.Email;
            var query = new TableQuery<EmployeeEntity>();
            TableOperation insertOperation = TableOperation.Insert(employeeEntity);
            
            
            table.Execute(insertOperation);
            var lst = table.ExecuteQuery(query);
            return lst.ToList();
            
        }

The connection string is defined in appsettings.json

"ConnectionStrings": {
    "MyAzureTable": "<Connection String>"
  },

You can get a Storage account connection string from the Azure portal as shown in the below figure.

By default, two keys are available. Click on Show Keys, copy the connection string and paste it in the appsettings.json file.

The above Post Action is used to post the records in the Azure table.

Posting the record using the PostMan.

PUT Action

[HttpPut]
        public IEnumerable<EmployeeEntity> Put([FromBody] EmployeeEntity emp)
        {
            string _dbCon1 = config.GetSection("ConnectionStrings").GetSection("MyAzureTable").Value;
            // Method 2
            string _dbCon2 = config.GetValue<string>("ConnectionStrings:MyAzureTable");
            var account = CloudStorageAccount.Parse(_dbCon2);
            var client = account.CreateCloudTableClient();

            var table = client.GetTableReference("Employee");

            table.CreateIfNotExists();

            EmployeeEntity employeeEntity = new EmployeeEntity(emp.FirstName, emp.LastName);
            employeeEntity.FirstName = emp.FirstName;
            employeeEntity.LastName = emp.LastName;
            employeeEntity.PhoneNumber = emp.PhoneNumber;
            employeeEntity.Email = emp.Email;
            var query = new TableQuery<EmployeeEntity>();
            TableOperation insertOperation = TableOperation.InsertOrMerge(employeeEntity);
            table.Execute(insertOperation);
            var lst = table.ExecuteQuery(query);
            return lst.ToList();

        }

The above Put Action is used to update the record in the Azure table.

Updating the record using the PostMan

GET Action

[HttpGet]          
        public IEnumerable<EmployeeEntity> Get()
        {

            var condition = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Raj");
            var query = new TableQuery<EmployeeEntity>().Where(condition);

            string _dbCon1 = config.GetSection("ConnectionStrings").GetSection("MyAzureTable").Value;
            // Method 2
            string _dbCon2 = config.GetValue<string>("ConnectionStrings:MyAzureTable");
            var account = CloudStorageAccount.Parse(_dbCon2);
            var client = account.CreateCloudTableClient();
            var table = client.GetTableReference("Employee");
            var lst = table.ExecuteQuery(query);
            return lst.ToList();
        }

The Get Action will return the data where the “PartitionKey” value is “Raj”

If we remove the where condition it will fetch all the records from the table

Get the record using the PostMan

DELETE Action

[HttpDelete]
        public IEnumerable<EmployeeEntity> Delete([FromBody] EmployeeEntity emp)
        {
            string _dbCon1 = config.GetSection("ConnectionStrings").GetSection("MyAzureTable").Value;
            // Method 2
            string _dbCon2 = config.GetValue<string>("ConnectionStrings:MyAzureTable");
            var account = CloudStorageAccount.Parse(_dbCon2);
            var client = account.CreateCloudTableClient();
            var table = client.GetTableReference("Employee");
            table.CreateIfNotExists();
            EmployeeEntity employeeEntity = new EmployeeEntity(emp.FirstName, emp.LastName);
            employeeEntity.FirstName = emp.FirstName;
            employeeEntity.LastName = emp.LastName;
            employeeEntity.PhoneNumber = emp.PhoneNumber;
            employeeEntity.Email = emp.Email;
            employeeEntity.ETag = "*"; // wildcard 
            var query = new TableQuery<EmployeeEntity>();
            TableOperation insertOperation = TableOperation.Delete(employeeEntity);
            table.Execute(insertOperation);
            var lst = table.ExecuteQuery(query);
            return lst.ToList();

        }

The Delete Action will delete the record from the table where the “PartitionKey” value is “Raj”.

Source Code: https://github.com/gowthamece/AzureTableStorage/tree/master/AzureTableStorageDemo

gowthamk91

Leave a Reply

%d bloggers like this: