Azure Storage CRUD Operations In MVC Using C# - Azure Table Storage - Part One

Introduction

In this article, I’m going to describe how to execute CRUD Operations in Azure Table Storage. CRUD stands for Create, Read, Update and Delete.

I'll show you the entire CRUD operation using ASP.NET MVC application, step-by-step. 

Overview

Azure Table is structured column storage on NoSQL Cloud Database. It is not a relational database. Azure Table Storage can store just a table without any foreign keys or any other kind of relation. These tables are highly scalable and ideal for handling a large amount of data. Tables can be stored and queried for a large amount of data. The relational database can be stored using SQL Data Services, which is a separate service.

You can get more overview of Azure Table Storage from here.

To create this project, I’m using,

• .NET Framework 4.6
• Windows Azure Storage 8.1.1 package
• Visual Studio 2015
• MVC 5.0

Consideration

To create this CRUD operation, the following skills should be considered.

• Good knowledge of ASP.NET MVC 5
• OOPs concepts in C#
• Basic knowledge of NoSQL

Scope of Work (SOW)

I'm creating a Student database with CRUD operations

1. Insert and Edit Screen - Here, I used a HTML form. At the time of edit, I'll fill it with Student data.
   
2. Get and Delete Screen - Here, I use an HTML table. There are three buttons here - Edit, Change Status, and Delete.
   
   

Step 1

Create Azure Storage Account. Go to https://portal.azure.com and create Azure Storage Account.

 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. Create a new project by going to File -> New -> Project…

Go to NuGet Package Manager console window and install -> “Install-Package WindowsAzure.Storage -Version 8.1.1”.

Step 3

Add “TableManager” class for accessing the Azure Table. And, use two namespaces.

1. WindowsAzure.Storage;
2. WindowsAzure.Storage.Table;

Remove the default Constructor from “TableManager” class. And, add a parameterized Constructor with the string parameter.

This constructor will create the table if it's not there in Storage. We put the Azure Storage Connection String in this constructor, which was declared above.

public class TableManager
{
    // private property
    private CloudTable table;

    // Constructor
    public TableManager(string _CloudTableName)
    {
        if (string.IsNullOrEmpty(_CloudTableName))
        {
            throw new ArgumentNullException("Table", "Table Name can't be empty");
        }
        try
        {
            string ConnectionString = "Your Azure Storage Connection String goes here";
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(ConnectionString);
            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

            table = tableClient.GetTableReference(_CloudTableName);
            table.CreateIfNotExists();
        }
        catch (StorageException StorageExceptionObj)
        {
            throw StorageExceptionObj;
        }
        catch (Exception ExceptionObj)
        {
            throw ExceptionObj;
        }
    }
}

Step 4

Add a generic method for Insert or Merge/Replace into "TableManager" class, where the generic value should be an inherited object of "TableEntity". I use Repository Pattern here.

Why  do I use Generic Method?
Azure Table is Column-based NoSQL. Here, we can store any kind of Data and any Object. This is a flavor of NoSQL. We don’t know, which type of object we are going to insert. If we use a generic method, we can access any object.

public void InsertEntity<T>(T entity, bool forInsert = true) where T : TableEntity, new()
{
    try
    {
        if (forInsert)
        {
            var insertOperation = TableOperation.Insert(entity);
            table.Execute(insertOperation);
        }
        else
        {
            var insertOrMergeOperation = TableOperation.InsertOrReplace(entity);
            table.Execute(insertOrMergeOperation);
        }
    }
    catch (Exception ExceptionObj)
    {
        throw ExceptionObj;
    }
}

Step 5

Similarly,  add another generic method to Retrieve entity into "TableManager" class. In this method, you can pass the query. According to query, it will return the dataset.

Now, the question is, what kind of query can we pass here. Here is the demonstration of how to use the operator in Azure Table Storage.

Operator	expression
Equal	eq
GreaterThan	gt
GreaterThanOrEqual	ge
LessThan	lt
LessThanOrEqual	le
NotEqual	ne
And	and
Not	not
Or	or

Example

I’m using Student Object here. I want to get the Student data where the name of the student is equal to Albert Einstein.

The query looks like - Name eq ‘Albert Einstein’

public List<T> RetrieveEntity<T>(string Query = null) where T : TableEntity, new()
{
    try
    {
        // Create the Table Query Object for Azure Table Storage
        TableQuery<T> DataTableQuery = new TableQuery<T>();
        if (!String.IsNullOrEmpty(Query))
        {
            DataTableQuery = new TableQuery<T>().Where(Query);
        }
        IEnumerable<T> IDataList = table.ExecuteQuery(DataTableQuery);
        List<T> DataList = new List<T>();
        foreach (var singleData in IDataList)
            DataList.Add(singleData);
        return DataList;
    }
    catch (Exception ExceptionObj)
    {
        throw ExceptionObj;
    }
}

Step 6

Add another generic method to Delete entity into "TableManager" class.

public bool DeleteEntity<T>(T entity) where T : TableEntity, new()
{
    try
    {
        var DeleteOperation = TableOperation.Delete(entity);
        table.Execute(DeleteOperation);
        return true;
    }
    catch (Exception ExceptionObj)
    {
        throw ExceptionObj;
    }
}

Step 7 - Model

Add Model, “Student.cs”, which is inherited from Microsoft.WindowsAzure.Storage.Table.TableEntity.

Define the below properties:

public class Student : TableEntity
{
    public Student() { }

    public string Name { get; set; }
    public string Email { get; set; }
    public string Department { get; set; }
    public bool? IsActive { get; set; }
}

Step 8 - Insert and Update

Add a controller named "Home". There will be a default Index Action Result; just add a parameter named id as string. 

Note - I am going to use partition key and row key in the coming codes. If you want to know more about partitionkey and row key, get it from here.

public ActionResult Index(string id)
{
    if (!string.IsNullOrEmpty(id))
    {
        // Get particular student info
        TableManager TableManagerObj = new TableManager("person"); // 'person' is the name of the table
        // pass query where RowKey eq value of id
        List<Student> SutdentListObj = TableManagerObj.RetrieveEntity<Student>("RowKey eq '" + id + "'");
        Student StudentObj = SutdentListObj.FirstOrDefault();
        return View(StudentObj);
    }
    return View(new Student());
}

Then, right-click on Index and add an empty View for Insert or Update screen.

@model Student
@using (Html.BeginForm("Index", "Home", new { id = Model.RowKey }, FormMethod.Post))
{
    <fieldset>
        <legend>Student Form</legend>
        <ol>
            <li>
                <label>Name</label>
                <input type="text" name="Name" required="required" value="@Model.Name" />
            </li>
            <li>
                <label>Email</label>
                <input type="email" name="Email" value="@Model.Email" />
            </li>
            <li>
                <label>Department</label>
                <input type="text" name="Department" value="@Model.Department" />
            </li>
        </ol>
        <input type="submit" value="Submit" />
    </fieldset>
}

Put an HttpPost Action Result into Home Controller.

[HttpPost]
public ActionResult Index(string id, FormCollection formData)
{
    Student StudentObj = new Student();
    StudentObj.Name = formData["Name"] == ""? null : formData["Name"];
    StudentObj.Department = formData["Department"] == ""? null: formData["Department"];
    StudentObj.Email = formData["Email"] == ""? null: formData["Email"];

    // Insert
    if (string.IsNullOrEmpty(id))
    {
        StudentObj.PartitionKey = "Student";
        StudentObj.RowKey = Guid.NewGuid().ToString();

        TableManager TableManagerObj = new TableManager("person");
        TableManagerObj.InsertEntity<Student>(StudentObj, true);
    }
    // Update
    else
    {
        StudentObj.PartitionKey = "Student";
        StudentObj.RowKey = id;

        TableManager TableManagerObj = new TableManager("person");
        TableManagerObj.InsertEntity<Student>(StudentObj, false);
    }
    return RedirectToAction("Get");
}

Step 9 - Select and Delete

Add another Action Result into the Home Controller, where we can get all records from Azure Storage.

public ActionResult Get()
{
    TableManager TableManagerObj = new TableManager("person");
    List<Student> SutdentListObj = TableManagerObj.RetrieveEntity<Student>();
    return View(SutdentListObj);
}

The View of Get Action Result looks like this.

@model List<Student>
<table border="1">
<thead>
    <tr>
        <th>Name</th>
        <th>Department</th>
        <th>Email</th>
        <th>Status</th>
        <th>Action</th>
    </tr>
</thead>
<tbody>
    @foreach (Student StudentObj in Model)
    {
        <tr>
            <td>@StudentObj.Name</td>
            <td>@StudentObj.Department</td>
            <td>@StudentObj.Email</td>
            <td>
                @if (!StudentObj.IsActive)
                {
                    <text> <span style="color:red;">In Active</span> </text>
                }
                else
                {
                    <text> <span style="color:green;">Active</span> </text>
                }
            </td>
            <td>
                <span>@Html.ActionLink("Edit", "Index", "Home", new { id = StudentObj.RowKey }, new { })</span>
                ||
                <span>@Html.ActionLink("Delete", "Delete", "Home", new { id = StudentObj.RowKey }, new { })</span>
            </td>
        </tr>
    }
</tbody>
</table>

Add Delete action result into Home Controller.

You must get the entity object first to delete that entity.

public ActionResult Delete(string id)
{
    TableManager TableManagerObj = new TableManager("persons");
    List<Student> SutdentListObj = TableManagerObj.RetrieveEntity<Student>("RowKey eq '" + id + "'");
    Student StudentObj = SutdentListObj.FirstOrDefault();
    TableManagerObj.DeleteEntity<Student>(StudentObj);
    return RedirectToAction("Get");
}

Error Handling

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

Conclusion

If we notice in the Azure Storage Explorer, we can see the data like this.

 

See, the Department is blank for Albert Einstein.