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.
    Azure
  2. Get and Delete Screen - Here, I use an HTML table. There are three buttons here - Edit, Change Status, and Delete.

    Azure

Step 1

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

Azure

 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.

Azure

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.

  1. public class TableManager  
  2. {  
  3.     // private property  
  4.     private CloudTable table;  
  5.   
  6.     // Constructor   
  7.     public TableManager(string _CloudTableName)  
  8.     {  
  9.         if (string.IsNullOrEmpty(_CloudTableName))  
  10.         {  
  11.             throw new ArgumentNullException("Table""Table Name can't be empty");  
  12.         }  
  13.         try  
  14.         {  
  15.             string ConnectionString = "Your Azure Storage Connection String goes here";  
  16.             CloudStorageAccount storageAccount = CloudStorageAccount.Parse(ConnectionString);  
  17.             CloudTableClient tableClient = storageAccount.CreateCloudTableClient();  
  18.               
  19.             table = tableClient.GetTableReference(_CloudTableName);  
  20.             table.CreateIfNotExists();  
  21.         }  
  22.         catch (StorageException StorageExceptionObj)  
  23.         {  
  24.             throw StorageExceptionObj;  
  25.         }  
  26.         catch (Exception ExceptionObj)  
  27.         {  
  28.             throw ExceptionObj;  
  29.         }  
  30.     }  
  31. }  

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.

  1. public void InsertEntity<T>(T entity, bool forInsert = true) where T : TableEntity, new()  
  2. {  
  3.     try  
  4.     {  
  5.         if (forInsert)  
  6.         {  
  7.             var insertOperation = TableOperation.Insert(entity);  
  8.             table.Execute(insertOperation);  
  9.         }  
  10.         else  
  11.         {  
  12.             var insertOrMergeOperation = TableOperation.InsertOrReplace(entity);  
  13.             table.Execute(insertOrMergeOperation);  
  14.         }  
  15.     }  
  16.     catch (Exception ExceptionObj)  
  17.     {  
  18.         throw ExceptionObj;  
  19.     }  
  20. }  

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’

  1. public List<T> RetrieveEntity<T>(string Query = null) where T : TableEntity, new()  
  2. {  
  3.     try  
  4.     {  
  5.         // Create the Table Query Object for Azure Table Storage  
  6.         TableQuery<T> DataTableQuery = new TableQuery<T>();  
  7.         if (!String.IsNullOrEmpty(Query))  
  8.         {  
  9.             DataTableQuery = new TableQuery<T>().Where(Query);  
  10.         }  
  11.         IEnumerable<T> IDataList = table.ExecuteQuery(DataTableQuery);  
  12.         List<T> DataList = new List<T>();  
  13.         foreach (var singleData in IDataList)  
  14.             DataList.Add(singleData);  
  15.         return DataList;  
  16.     }  
  17.     catch (Exception ExceptionObj)  
  18.     {  
  19.         throw ExceptionObj;  
  20.     }  
  21. }  

Step 6

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

  1. public bool DeleteEntity<T>(T entity) where T : TableEntity, new()  
  2. {  
  3.     try  
  4.     {  
  5.         var DeleteOperation = TableOperation.Delete(entity);  
  6.         table.Execute(DeleteOperation);  
  7.         return true;  
  8.     }  
  9.     catch (Exception ExceptionObj)  
  10.     {  
  11.         throw ExceptionObj;  
  12.     }  
  13. }  

Step 7 - Model

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

Define the below properties:

  1. public class Student : TableEntity  
  2. {  
  3.     public Student() { }  
  4.   
  5.     public string Name { get; set; }  
  6.     public string Email { get; set; }  
  7.     public string Department { get; set; }  
  8.     public bool? IsActive { get; set; }  
  9. }  

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.

  1. public ActionResult Index(string id)  
  2. {  
  3.     if (!string.IsNullOrEmpty(id))  
  4.     {  
  5.         // Get particular student info  
  6.         TableManager TableManagerObj = new TableManager("person"); // 'person' is the name of the table  
  7.         // pass query where RowKey eq value of id
  8.         List<Student> SutdentListObj = TableManagerObj.RetrieveEntity<Student>("RowKey eq '" + id + "'");  
  9.         Student StudentObj = SutdentListObj.FirstOrDefault();  
  10.         return View(StudentObj);  
  11.     }  
  12.     return View(new Student());  
  13. }  

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

  1. @model Student  
  2. @using (Html.BeginForm("Index""Home"new { id = Model.RowKey }, FormMethod.Post))  
  3. {  
  4.     <fieldset>  
  5.         <legend>Student Form</legend>  
  6.         <ol>  
  7.             <li>  
  8.                 <label>Name</label>  
  9.                 <input type="text" name="Name" required="required" value="@Model.Name" />  
  10.             </li>  
  11.             <li>  
  12.                 <label>Email</label>  
  13.                 <input type="email" name="Email" value="@Model.Email" />  
  14.             </li>  
  15.             <li>  
  16.                 <label>Department</label>  
  17.                 <input type="text" name="Department" value="@Model.Department" />  
  18.             </li>  
  19.         </ol>  
  20.         <input type="submit" value="Submit" />  
  21.     </fieldset>  
  22. }  

Put an HttpPost Action Result into Home Controller.

  1. [HttpPost]  
  2. public ActionResult Index(string id, FormCollection formData)  
  3. {  
  4.     Student StudentObj = new Student();  
  5.     StudentObj.Name = formData["Name"] == ""null : formData["Name"];  
  6.     StudentObj.Department = formData["Department"] == ""null: formData["Department"];  
  7.     StudentObj.Email = formData["Email"] == ""null: formData["Email"];  
  8.    
  9.     // Insert  
  10.     if (string.IsNullOrEmpty(id))  
  11.     {  
  12.         StudentObj.PartitionKey = "Student";  
  13.         StudentObj.RowKey = Guid.NewGuid().ToString();  
  14.    
  15.         TableManager TableManagerObj = new TableManager("person");  
  16.         TableManagerObj.InsertEntity<Student>(StudentObj, true);  
  17.     }  
  18.     // Update  
  19.     else  
  20.     {  
  21.         StudentObj.PartitionKey = "Student";  
  22.         StudentObj.RowKey = id;  
  23.    
  24.         TableManager TableManagerObj = new TableManager("person");  
  25.         TableManagerObj.InsertEntity<Student>(StudentObj, false);  
  26.     }   
  27.     return RedirectToAction("Get");  
  28. }  

Step 9 - Select and Delete

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

  1. public ActionResult Get()  
  2. {  
  3.     TableManager TableManagerObj = new TableManager("person");  
  4.     List<Student> SutdentListObj = TableManagerObj.RetrieveEntity<Student>();  
  5.     return View(SutdentListObj);  
  6. }  

The View of Get Action Result looks like this.

  1. @model List<Student>  
  2. <table border="1">  
  3. <thead>  
  4.     <tr>  
  5.         <th>Name</th>  
  6.         <th>Department</th>  
  7.         <th>Email</th>  
  8.         <th>Status</th>  
  9.         <th>Action</th>  
  10.     </tr>  
  11. </thead>  
  12. <tbody>  
  13.     @foreach (Student StudentObj in Model)  
  14.     {  
  15.         <tr>  
  16.             <td>@StudentObj.Name</td>  
  17.             <td>@StudentObj.Department</td>  
  18.             <td>@StudentObj.Email</td>  
  19.             <td>  
  20.                 @if (!StudentObj.IsActive)  
  21.                 {  
  22.                     <text> <span style="color:red;">In Active</span> </text>  
  23.                 }  
  24.                 else  
  25.                 {  
  26.                     <text> <span style="color:green;">Active</span> </text>  
  27.                 }  
  28.             </td>  
  29.             <td>  
  30.                 <span>@Html.ActionLink("Edit""Index""Home"new { id = StudentObj.RowKey }, new { })</span>  
  31.                 ||  
  32.                 <span>@Html.ActionLink("Delete""Delete""Home"new { id = StudentObj.RowKey }, new { })</span>  
  33.             </td>  
  34.         </tr>  
  35.     }                  
  36. </tbody>  
  37. </table>  

Add Delete action result into Home Controller.

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

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

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.

Azure

 

See, the Department is blank for Albert Einstein.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now