Hi All.

I have had the pleasure of developing complex production web applications leveraging Azure Table storage and I wanted to contribute with a detailed end to end example.

I am a massive fan of Azure Table storage. Not having to deal with Entity Framework or complex database schemas is an absolute joy and the speed of the Azure Table technology when interfacing through C# is excellent.

Learn about the differences between SQL and No-SQL technologies and when its a good idea to use either here:- https://www.guru99.com/sql-vs-nosql.html.

Here is a quick definition to get us started:-

Azure Table storage is a service that stores non-relational structured data (also known as structured NoSQL data) in the cloud, providing a key/attribute store with a schemaless design. Because Table storage is schemaless, it’s easy to adapt your data as the needs of your application evolve. 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.

You can learn more about it here:- https://docs.microsoft.com/en-us/azure/storage/tables/table-storage-overview

Rather than just giving a simple example. In this article we will be creating a end-to-end REST API which will use Azure Table Storage for our back-end storage.

This tutorial assumes you already have Visual Studio installed so I’m going to skip that part. Also, I don’t cover authentication or error checking. I will leave that up to you.

The easiest, free, way to get started developing with Azure Table Storage is to use the free Azure Storage Emulator which you can get here:- https://go.microsoft.com/fwlink/?linkid=717179&clcid=0x409

When you go to production you can purchase an Azure or Azure Stack subscription and create your production storage account there. Cent for cent, using Azure Table Storage is very cost efficient.

Go ahead and download and install the storage emulator.

Once you have installed the emulator, start the storage emulator from the start menu.

Next we will want to be able to easily browse and manipulate the storage emulator storage account. For that download and install the excellent Azure Storage Explorer here:- https://go.microsoft.com/fwlink/?LinkId=708343&clcid=0x409

Open your storage explorer and, so long as you have started the emulator above, you will now be able to browse the emulator storage account as below.

Without writing a single line of code you could create, import and export and query tables right from this interface.

So that is how simple it is to get table storage up and running locally on our devices. Now we can get into the fun part which is authoring our .Net Framework Web API.

Lets start by creating a new Visual Studio ASP .Net Web Application project targeting the .Net Framework.

Go ahead and name the project AzureTableAPI and target the latest version of the .Net Framework.

Lets select an empty project and just select the option for Web API.

You can download the entire solution here:- https://mcse.cloud/wp-content/uploads/2021/03/AzureTableAPI.zip and we will then break down the solution below.

Note:- If you have any issues following along check against the complete project provided above.

The very first thing we want to do is to install the latest Microsoft Azure Cosmos Table Nuget package which you can get from here:- https://www.nuget.org/packages/Microsoft.Azure.Cosmos.Table.

But we are going to use the package manager console in Visual Studio. You will find this at the bottom of your Visual Studio window or from the tools menu bar.

The next thing we need to do is define a connection string in our web.config file so that we’ll be able to talk to our emulator storage account. This connection string is always the same for the emulator.

Note:- In production you would update this with the connection string for your production storage account.

Okay. So lets pause for a moment and lets consider how this solution will work.

We want a very easy, “light touch”, experience when calling into the storage system (which in our case is Azure Table Storage). So when we create any model we want/need it to have all the necessary methods to work with the Azure Table Storage sub-system automatically.

Therefore. Lets start our solution by defining an AzureTableEntity.cs class which will take care of all the underlying work between whatever models we choose to define later and the back-end table storage and lets inherit from the base Azure Table Entity class.

To get our class started we need to start using the Microsoft.Azure.Cosmos.Table reference library and we want our AzureTableEntity class to inherit from the base TableEntity class.

Within this class, lets start by creating a default constructor. and we are going to extend the TableEntity class with three additional attributes.

One will be the Table name of any associated models and we also want to populate a creation and modification date stamp by default on any table entities we create.

You will notice that we set the [IgnoreProperty] on the Table attribute. This is because we don’t want this attribute written into the AzureTable storage. The Table attribute will be used internally in our methods discussed below.

Finally we will also store the connection string we need when connecting to the Azure Table Storage but we will mark this as a private read-only so we do not expose our connection string.

Great. So now we are going to create the necessary methods to support interacting with the underlying Azure Tables. So creating rows, getting rows, updating rows and deleting rows.

Lets start with the create method. This will return an instance of the AzureTableEntity.

We will reuse this knowledge over and over again but this method will:-

  1. Create a cloud table client based on the Storage Account connection string we’re storing in the private variable above.
  2. This will attempt to get the required table. If the table doesn’t exist. This will create it for us. (How cool is that!).
  3. We then need to define the correct table operation. In this case we are going to insert or replace the row and execute the table operation.
  4. We will return the AzureTableEntity (The created row).

Now we need a method to search for an retrieve table rows so lets get that setup and discuss how it works.

Note:- This method has been developed so that is would work with any model which inherits from the AzureTableEntity class we’re defining here. For now understand that <T> is any Type which inherits from our AzureTableEntity class. We will look at how this works later in the article.

  1. Create a cloud table client based on the Storage Account connection string we’re storing in the private variable above.
  2. This will attempt to get the required table. If the table doesn’t exist. This will create it for us.
  3. We then need to define the correct table operation. In this case we are going to search for table rows based on a query string using such as “RowKey eq ‘123’” which will be passed as a parameter.
  4. Finally we will create a list based on the Type passed in as a parameter and return this list.

Lets create a separate method for Updating a row. This is almost identical to the create row. It will have a slightly different table operation and we’ll update our modified datestamp here so we don’t need to worry about that again.

  1. Create a cloud table client based on the Storage Account connection string we’re storing in the private variable above.
  2. This will attempt to get the required table. If the table doesn’t exist. This will create it for us.
  3. We then need to define the correct table operation. In this case we are going to merge the row.
  4. Finally we will return the updated AzureTableEntity.

And finally we need a method to delete a table row. So lets define that in our AzureTableEntity class.

Note:- Its very important when we delete a row that we define the Etag. Here we set it to all with the asterix.

  1. Create a cloud table client based on the Storage Account connection string we’re storing in the private variable above.
  2. This will attempt to get the required table. If the table doesn’t exist. This will create it for us.
  3. Set the Etag.
  4. We then need to define the correct table operation. In this case we are going to delete the row.
  5. We will not return anything as the row is now deleted.

And that completes our AzureTableEntity class. Thats all the hard work done 🙂

So lets go ahead and make the model that we will be exposing through the Web API. For this article I am going to make a model called Car and populate that with some simple attributes. Lets start by making the new class called Car.cs.

Lets start by inheriting our AzureTableEntity class and with the default constructor.

There are three critical attributes we need to setup which relate to the inherited AzureTableEntity class. All our models that will inherit from our custom AzureTableEntity class will need these attributes.

  1. The TableName. This is the name of the table which will be created and queried in the Azure Storage Account
  2. The PartitionKey. This is used for partitioning data within the table.
  3. The RowKey. This is the unique identifier of the row within the table and must be unique. For this we’ll use a GUID

Note:- The PartitionKey and RowKey are strings. Defined by the underlying Azure Table Storage subsystem.

Lets add some more Car model specific attributes and lets make some of them required.

There will always need to be some business / processing logic which is specific to a class. So I want to show you how to setup these methods within our Car class prior to interfacing with the Azure Table Storage.

Lets start with one that will create a car via our API.

  1. Here we will set the Next Service date for a new car by adding 6 months.
  2. Now we use our inherited method from our AzureTableEntity class and in a single line we can create the required table and add the entity. (How cool is that!).
  3. Return the Car which has been create as a table row.

So now you can see how easy it is to create methods within your model classes and use the power of the underlying AzureTableEntity class to interact with the Azure Storage subsystem.

Lets create two methods within our Car class. One to get a single car and one to get all cars. In this way we will have a chance to look at the very easy and productive query language used by the Azure Storage Table subsystem. For more information on the language go here:- https://docs.microsoft.com/en-us/azure/storage/tables/table-storage-design-for-query

  1. First we define the filter string using the query language. Here we know the RowKey is the primary key so if we make this query for a single RowKey we will either get back one record or nothing.
  2. Again we make use of the GetRows method we have inherited from our underlying AzureTableEntity class. Also now you will see we are passing in our Type. This will ensure we get back all the properties of the Car class.

Here we have a very similar method to the one above but in this case we are using the PartitionKey in the search query and this will return all the cars or nothing (null).

  1. Note that our return is now a List of Cars.
  2. We are now returning all rows with the same PartitionKey (equivalent to all).

We should also add one more constructor at this time to setup a Car which already exists in our table storage.

  1. Now when we instantiate a car we can pass in the existing RowKey rather than generating a new RowKey as per the default constructor.
  2. We will still setup the mandatory PartitionKey and RowKey.
  3. Finally we will call the Get method we created above to get the Car from the table and populate things such as its colour, make and model. (How easy is that!)

Lets add a Car class specific update method which will give us a chance to ensure that we only update required attributes.

Later on we will need a way to validate whether our car exists in the table so lets add a little helper method to return true if a row exists.

  1. Here we are returning a boolean if a Car with the currently defined RowKey exists.

So the last class we need to setup is our Car API Controller. There are circumstances where a controller will have multiple classes but generally accepted best practice is to have a controller for each of your models.

Like all the links shared above, I highly recommend that you bookmark this Tutorial Teacher article. Its brilliant and you can get it here:- https://www.tutorialsteacher.com/webapi.

So lets create our Car Controller class.

Your Controller name must end with Controller.

So now we need to setup routes to do all our work inbound from the internet. Up until this point we have had some flexibility in regards to how we name things and go about things but REST is a standard. And so we need to comply to that standard.

This is an opportunity to share the original dissertation by Roy Fielding which you can read here:- https://www.ics.uci.edu/~fielding/pubs/dissertation/fielding_dissertation_2up.pdf

Roy fielding is a brilliant Engineer who produced numerous technical papers and advancements in technology but, in my humble opinion, will be best know for creating one of the most prolific, productive and structural standards in use today in the form of RESTful services. Massive respect Roy! Anyway, I digress…

So we are going to setup routes for POST (create), GET (read), PUT (update) and Delete (delete). Also sometimes referred to as “CRUD”.

Lets get started with the POST route. There is a bit for us to break down here.

  1. We have used an attribute to state that this route supports the POST verb.
  2. We have determined that this API route can be found at https://{web domain name}/v1/car. It is always recommended to apply some form of versioning to your API routes. This is so you don’t break existing applications using your API when you choose to roll out the latest and greatest.
  3. We expect the interface posting to our API to provide us with a Car.
  4. We will then create the car (remember this will also set the service date) and then create the table row.
  5. We will return the response. Here it will either create or it will throw a system error.

All this work and now comes the reward. Time to test our API is running. So first lets start our project without debugging.

Make a note of the URL that opens. This will be different for you.

I recommend the Postman tool for working with our API. You can get Postman here:- https://www.postman.com/downloads/

Once your Postman is installed. Lets submit a request to our POST route.

  1. Enter your URL but remember to add our POST API route which is /v1/car
  2. Change the verb to POST
  3. Select Body and then change the form to raw and JSON.
  4. Enter in your own car details. Your colour, make and model. Select Send to create your car.

Notice how the attributes you POSTed in your JSON body only contained the [Required] attributes but in the return we have all our inherited and auto-generated attributes such as the Service date.

You will also see that the Created and Modified date stamps were populated automatically. I personally find these an essential replacement for the TimeStamp attribute in the base TableEntity class.

Feel free to create a few more cars at this time.

And in Azure Storage explorer. We now see our Cars table for the first time and a list of Cars. One thing I wish to point out, is that we chose to [ignore] the table attribute and that is why you do not see it in our Azure Storage table.

So lets go back into Visual Studio and add two GET routes. One for getting a single Car and one for getting all Cars.

  1. We have used an attribute to state that these routes support the GET verb.
  2. Notice how we use the same route v1/car as we did above. This is because HTTP routes based on the route (v1/car) and the verb (GET vs POST as above).
  3. For the single get route, we are going to pass the unique identifier for each entry (which we auto-generate), which is the RowKey. For the v1/cars route we want all the cars and we know our Car class has a method which returns all rows with the same PartitionKey.
  4. This time we’re going to check if we have Cars to remove, if we don’t we will use the appropriate HTTP code (404) to let the end caller know there was no response.

Start debugging again. Back in Postman, lets get a single car and all cars.

Back in Visual Studio. Lets add two additional routes for PUT and DELETE. Lets look into the PUT route.

  1. We have used an attribute to state that these routes support the PUT verb.
  2. We use the same route but it will work because this time we’re addressing the route with the PUT verb.
  3. We will then get the Car to be updated from the request body.
  4. If there is no car in the table then there will be nothing to update. We can use our helper method to make this easy.
  5. Finally we’ll update the Row and return the updated object.

Start debugging and then in Postman, we can update the color of a Car.

Here is the final DELETE route which will complete this article.

  1. We have used an attribute to state that these routes support the DELETE verb.
  2. We use the same route but it will work because this time we’re addressing the route with the DELETE verb.
  3. First we check that such a car accepts and then we will delete the record.
  4. As the row will no longer exist, we will return the Accepted HTTP status code.

Lets start debugging again and lets delete a Car record with Postman.

So that concludes the tutorial. I have been meaning to do this forever. I’ll need to do a .Net Core version soon.

Hope this helps!