Dynamic Column Management In Azure Table Storage With ASP.NET MVC And C#

Introduction

This article will demonstrate how to manage Dynamic Column in Azure Table Storage. In the previous article, I have given an idea of how to store value in Azure Table Storage. Dynamic Column generation is not a new requirement in a real-life project. Here, I’m giving you the procedure to manage this by Azure Table Storage.

Overview

Azure Storage is a Microsoft managed service providing cloud storage that is highly applicable, scalable, and redundant. Azure Storage includes Blobs, Tables, Queues, and Files.

Azure Table Storage is a service that stores unstructured No-SQL data in the cloud, providing a key/attribute store with a scheme-less design. Access to table storage data is fast and cost-effective for many types of applications and is typically lower in cost than traditional SQL for similar volumes of data.

Concept

Because Azure Table Storage is NoSql, we should create a dynamic column in this Storage mechanism. Suppose a user wants to create a new column, they don’t need to contact the developers. This should be the flavor of No-SQL.

Consideration

Before starting this, the following skills should be considered.

Good knowledge of ASP.NET MVC 5
OOPs concepts in C#
Basic knowledge of NoSQL
Azure Table Storage Basic Concept (here)

To create this project, I’m using.

.Net Framework 4.6
Windows Azure Storage 8.1.4 package (Nuget)
Visual Studio 2015
MVC 5.0

Scope of Work (SOW)

A Registration form will be developed where fields or columns are dynamic, and user created.

Column Manage screen – In this screen user can create columns for registration.
Registration Screen – In this screen, the Registration Form will be generated by a user-defined column.

Step 1

Create an Azure Storage Account. From Azure Portal.

Access Key & Connection String

After creating Azure Storage Account, go to that account and copy the access key and connection string of that account. It will be used in our MVC application.

Step 2

Create a new empty MVC application. The step to create a new project is File -> New -> Project…

Go to NuGet package manager console window and install -> “Install-Package WindowsAzure.Storage -Version 8.1.4”.

Step 3

Go to my previous article of Azure Table Storage. You can see a class, “TableManager” there. Use this class for accessing the Azure table.

Add a model, “DataModel”, for managing Master Property or Column Data.

public class DataModel : TableEntity
{
public string ColoumnName { get; set; }
public string DataType { get; set; }
}

Add a screen in the MVC application with Property Name as ColoumnName and Data Type (here is used, string, number and bool) to set Master Property data.

Note
There are some rules to create a Dynamic Column Name

The name should be in alpha only
White spaces or special characters are not allowed

Step 4

Add two methods in the class “TableManager” to insert and Retrieve Dynamic Column Data

public void InsertEntity(DynamicTableEntity entity)
{
try
{
var insertOperation = TableOperation.Insert(entity);
table.Execute(insertOperation);
}
catch (Exception ExceptionObj)
{
throw ExceptionObj;
}
}
public List<DynamicTableEntity> RetrieveEntity(string Query)
{
try
{
TableQuery<DynamicTableEntity> DataTableQuery = new TableQuery<DynamicTableEntity>();
if (!string.IsNullOrEmpty(Query))
{
DataTableQuery = new TableQuery<DynamicTableEntity>().Where(Query);
}
IEnumerable<DynamicTableEntity> IDataList = table.ExecuteQuery(DataTableQuery);
return IDataList.ToList<DynamicTableEntity>();
}
catch (Exception ExceptionObj)
{
throw ExceptionObj;
}
}

Step 5

For the registration page add an HTTPGET action result into MVC application to populate dynamic column, where user can insert data.

[HttpGet]
public ActionResult Registration(string id)
{
TableManager tablePropMaster = new TableManager("PropMaster");
ViewBag.Tenant = id;
List<DataModel> ModelList = tablePropMaster.RetrieveEntity<DataModel>("PartitionKey eq '" + id + "'");
return View(ModelList);
}

Step 6

In the view screen of Registration add this HTML code.

@using (Html.BeginForm("Registration", "Home",
new { id = ViewBag.Tenant },
FormMethod.Post,
new { autocomplete = "off",
enctype = "multipart/form-data",
data = "data-parsley-validate",
@class = "form-horizontal"
}))
{
<h2>Registration</h2>
foreach (DataModel dm in Model)
{
<div class="form-group">
<label for="firstName" class="col-sm-3 control-label">@dm.DisplayColoumnName</label>
<div class="col-sm-12">
@if (dm.DataType == EdmType.String.ToString())
{
<input type="text" placeholder="@dm.DisplayColoumnName" name="@dm.ColoumnName" class="form-control">
}
else if (dm.DataType == EdmType.Int32.ToString()
|| dm.DataType == EdmType.Int64.ToString()
|| dm.DataType == EdmType.Double.ToString())
{
<input type="number" placeholder="@dm.DisplayColoumnName" name="@dm.ColoumnName" class="form-control">
}
else if (dm.DataType == EdmType.Boolean.ToString())
{
<select class="form-control" name="@dm.ColoumnName">
<option value="true">True</option>
<option value="false">False</option>
</select>
}
</div>
</div>
}
<button type="submit" class="btn btn-primary">Register</button>
}

Step 7

On submitting the registration form add a POST method to insert the registration data.

[HttpPost]
public ActionResult Registration(FormCollection form, string id)
{
TableManager tablePropData = new TableManager("PropData");
TableManager tablePropMaster = new TableManager("PropMaster");
DynamicTableEntity dte = new DynamicTableEntity();
List<DataModel> ModelList = tablePropMaster.RetrieveEntity<DataModel>("PartitionKey eq '" + id + "'");
EntityProperty Prop;
foreach (DataModel dm in ModelList)
{
if (dm.DataType == EdmType.String.ToString())
{
Prop = new EntityProperty(form[dm.ColoumnName]);
dte.Properties[dm.ColoumnName] = Prop;
}
if (dm.DataType == EdmType.Int32.ToString()
|| dm.DataType == EdmType.Int64.ToString()
|| dm.DataType == EdmType.Double.ToString())
{
Prop = new EntityProperty(Convert.ToDouble(form[dm.ColoumnName]));
dte.Properties[dm.ColoumnName] = Prop;
}
if (dm.DataType == EdmType.Boolean.ToString())
{
Prop = new EntityProperty(Convert.ToBoolean(form[dm.ColoumnName]));
dte.Properties[dm.ColoumnName] = Prop;
}
}
dte.PartitionKey = id;
dte.RowKey = Guid.NewGuid().ToString();
tablePropData.InsertEntity(dte);
return RedirectToAction("Registration");
}

Step 8

Here is the code to retrieve the dynamic column value.

TableManager tablePropData = new TableManager("PropData");
List<DynamicTableEntity> DataList = tablePropData.RetrieveEntity("PartitionKey eq 'Microsoft'");

Error Handling

You can handle the error by Storage Exception class. Find the error list here.