CRUD Operations In Azure Cosmos DB SQL API Using ASP.NET Core Web API C#

The Azure Cosmos DB is a NoSQL database that is globally distributed and highly available. The Azure Cosmos DB supports multiple types of payload structures based on the type of API, including the JSON format. The database is easily scalable with a few clicks across the regions. The Cosmos DB supports the following API types:

• SQL API
• Cassandra API
• Gremlin API
• Table API
• MongoDB API

In this article, we will use the SQL API to perform insert, update, delete, and read operations from the Cosmos DB using the ASP.NET core Web API with C#, so let's start step by step so beginners can also understand. The following is the flow of our sample application, which we are going to create for the CRUD operations

Step 1: Set up Prerequisites

Set up the following one of the required pre-requisites to perform the CRUD operation in the Azure Cosmos DB

• Azure Subscription OR
• Azure Cosmos DB Emulator 

Azure Subscription 

To create the Azure cloud Cosmos DB account, you need an active Azure subscription. When you create the Cosmos DB account on the cloud, the Azure Cosmos DB Serverless account provides the first 1000 RU and 25 GB of storage for free per subscription. Refer to the following link to learn the basics and how to create the Azure Cosmos DB account.

• Introduction To Azure CosmosDB

Azure Cosmos DB Emulator (offline)

As mentioned above, the first approach requires an active Azure subscription as well as an active internet connection, which is not feasible for everyone who just wants to explore or learn about the Azure Cosmos DB. To overcome the preceding issue, we can use the Azure Cosmos DB capabilities and features using the Azure Cosmos DB Emulator without an active Azure subscription or an active internet connection. The Azure Cosmos DB Emulator currently does not have support for all the NoSQL APIs, but it will support the most commonly used NoSQL APIs, such as the SQL API and Mongo DB API, and in the future it may also support all the Cosmos DB APIs.

The following is the link to download the Azure Cosmos DB Emulator.

• Download the Emulator

After clicking on the link, the official Microsoft documentation website gets opened, from which you can find and install the latest version of the Azure Cosmos DB Emulator.

Step 2: Create Azure Cosmos DB 

As mentioned in Step 1, I hope you set up the required prerequisites from the provided options to create the Cosmos DB. In this article, we are going to use the Azure Cosmos DB emulator instead of a Cloud Azure Cosmos DB account.

After installing the Cosmos DB emulator, search for the Azure Cosmos DB emulator from the search bar of the window, which looks like the following:

The preceding image shows the Azure CosmosDB Account of the emulator, which will provide the same features as the Cloud Azure CosmosDB Account for development. Now click on the explorer and create the database and container. The explorer will then look like this:

We are going to store the employee basic details in Azure CosmosDB, and we are planning to use Department as a partition key and ID as the unique id for our employee records.

Note:

• The ID and Department properties should be part of your backend input while inserting or updating the records; otherwise, you will get the exceptions.

I hope you have completed the required setup as explained in this article, including the creation of the Azure Cosmos account and database.

Step 3: Create ASP.NET Core Web API Project

1. Start then All Programs and select "Microsoft Visual Studio".
2. Once the Visual Studio Opens, then click on Continue Without Code.
3. Then Go to Visual Studio Menu, click on File => New Project then choose ASP.NET Core Web API Project Template.
4. Then define the project name, location of the project, then click on the next button.
5. On the next screen, provide the additional details: framework, authentication type, and check the Enable Open API Support checkbox as shown below.

The preceding steps will create the ASP.NET Core Web API application and solution explorer. It will look like what is shown in the following image.

Step 4: Add Microsoft.Azure.Cosmos Nuget Package Reference

The Microsoft.Azure.Cosmos is the latest nuget package to interact with the Azure Cosmos DB. The Microsoft.Azure.Cosmos supports basic to custom and complex database operations, follow the following steps to add the Nuget package.

1. Right click on the Solution Explorer, find Manage NuGet Package Manager and click on it
2. After as shown in the image and type in search box Microsoft.Azure.Cosmos
3. Select Microsoft.Azure.Cosmos as shown in the image, 
4. Choose version of Microsoft.Azure.Cosmos library and click on install button

I hope you have followed the same steps and installed Microsoft.Azure.Cosmos nuget package. The next step is to delete the default controller and model class so we can start from scratch.

Step 5: Create the Model Class

• First, delete the default model class, which is created outside the folder structure, so we can start from scratch. 
• Next, create the folder named Model by right-clicking on the solution explorer.
• Create the model class Employee Model by right-clicking on the Model folder, as shown in the following image

Now open the EmployeeModel.cs class file and add the following code.

EmployeeModel.cs

namespace EmployeeManagement.Model {
    public class EmployeeModel {
        public string ? id {
            get;
            set;
        }
        public string ? Name {
            get;
            set;
        }
        public string ? Country {
            get;
            set;
        }
        public string ? City {
            get;
            set;
        }
        public string ? Department {
            get;
            set;
        }
        public string ? Designation {
            get;
            set;
        }
        public DateTime ? JoiningDate {
            get;
            set;
        }
    }
}

Step 6: Add the Controller

Create the Empty API Controller class EmployeeController by right-clicking on the Controller folder as shown in the following image.

After adding the model class and API controller class, the solution explorer will look like the following:

Now open the EmployeeController.cs file and add the following configuration.

Define the following route at controller level, so that we can add the multiple Get, Post, Put, or Delete and avoid the name ambiguity exception.

[ApiController]
[Route("[api/[controller]/[action]]")]
public class EmployeeController: ControllerBase {}

Declare the following variable and set the Azure Cosmos DB configuration by copying the details from step 2.

[ApiController]
[Route("api/[controller]/[action]")]
public class EmployeeController: ControllerBase {
    // Cosmos DB details, In real use cases, these details should be configured in secure configuraion file.
    private readonly string CosmosDBAccountUri = "https://localhost:8081/";
    private readonly string CosmosDBAccountPrimaryKey = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==";
    private readonly string CosmosDbName = "EmployeeManagementDB";
    private readonly string CosmosDbContainerName = "Employees";
}

Step 7: Create a Method to Add Employee

Add the following code into the EmployeeController.cs class to add the employees into the CosmosDB. This method takes input values using the EmployeeModel class. 

[HttpPost]
public async Task < IActionResult > AddEmployee(EmployeeModel employee) {
    try {
        var container = ContainerClient();
        var response = await container.CreateItemAsync(employee, new PartitionKey(employee.Department));
        return Ok(response);
    } catch (Exception ex) {
        return BadRequest(ex.Message);
    }
}

As explained in step 2, we are passing the ID and Department as a partition key as part of the employee input payload while adding the employee details. These parameters must be part of the input parameter payload.

Step 8:  Create a Method to Get Employees

Add the following code into the EmployeeController.cs class to get all the employees from the Cosmos database.

[HttpGet]
public async Task < IActionResult > GetEmployeeDetails() {
    try {
        var container = ContainerClient();
        var sqlQuery = "SELECT * FROM c";
        QueryDefinition queryDefinition = new QueryDefinition(sqlQuery);
        FeedIterator < EmployeeModel > queryResultSetIterator = container.GetItemQueryIterator < EmployeeModel > (queryDefinition);
        List < EmployeeModel > employees = new List < EmployeeModel > ();
        while (queryResultSetIterator.HasMoreResults) {
            FeedResponse < EmployeeModel > currentResultSet = await queryResultSetIterator.ReadNextAsync();
            foreach(EmployeeModel employee in currentResultSet) {
                employees.Add(employee);
            }
        }
        return Ok(employees);
    } catch (Exception ex) {
        return BadRequest(ex.Message);
    }
}

This example is not a good practice since we are fetching all the employees without paging or a partition key filter, but we are doing this to learn how it works without making it complicated. In the next article, I will show how to get a list of records with the paging.

Step 9: Create a Method to Get Employee by ID

Create the GetEmployeeDetailsById method in the EmployeeController.cs and add the following code to get the employee by employeeId and partition key from the Cosmos database.

[HttpGet]
public async Task < IActionResult > GetEmployeeDetailsById(string employeeId, string partitionKey) {
    try {
        var container = ContainerClient();
        ItemResponse < EmployeeModel > response = await container.ReadItemAsync < EmployeeModel > (employeeId, new PartitionKey(partitionKey));
        return Ok(response.Resource);
    } catch (Exception ex) {
        return BadRequest(ex.Message);
    }
}

Step 10: Create a Method to Update Employee

Create the UpdateEmployee method in the EmployeeController.cs and add the following code to update the employee by employeeId and partition key.

The CosmosDB does not support the partial update feature; rather, it actually replaces the existing item by getting the document to be updated and sending the same details to the database after fields change or update.

[HttpPut]
public async Task < IActionResult > UpdateEmployee(EmployeeModel emp, string partitionKey) {
    try {
        var container = ContainerClient();
        ItemResponse < EmployeeModel > res = await container.ReadItemAsync < EmployeeModel > (emp.id, new PartitionKey(partitionKey));
        //Get Existing Item
        var existingItem = res.Resource;
        //Replace existing item values with new values 
        existingItem.Name = emp.Name;
        existingItem.Country = emp.Country;
        existingItem.City = emp.City;
        existingItem.Department = emp.Department;
        existingItem.Designation = emp.Designation;
        var updateRes = await container.ReplaceItemAsync(existingItem, emp.id, new PartitionKey(partitionKey));
        return Ok(updateRes.Resource);
    } catch (Exception ex) {
        return BadRequest(ex.Message);
    }
}

Step 11: Create a Method to Delete Employee

Create the DeleteEmployee method in the EmployeeController.cs and add the following code to delete the employee by employeeId and partition key.

[HttpDelete]
public async Task < IActionResult > DeleteEmployee(string empId, string partitionKey) {
    try {
        var container = ContainerClient();
        var response = await container.DeleteItemAsync < EmployeeModel > (empId, new PartitionKey(partitionKey));
        return Ok(response.StatusCode);
    } catch (Exception ex) {
        return BadRequest(ex.Message);
    }
  }
}

The entire code of the EmployeeController.cs class file will look like the following after adding all the methods together:

EmployeeController.cs

using EmployeeManagement.Model;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Cosmos;
namespace EmployeeManagement.Controllers {
    [ApiController]
    [Route("api/[controller]/[action]")]
    public class EmployeeController: ControllerBase {
        // Cosmos DB details, In real use cases, these details should be configured in secure configuraion file.
        private readonly string CosmosDBAccountUri = "https://localhost:8081/";
        private readonly string CosmosDBAccountPrimaryKey = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw==";
        private readonly string CosmosDbName = "EmployeeManagementDB";
        private readonly string CosmosDbContainerName = "Employees";
        /// <summary>
        /// Commom Container Client, you can also pass the configuration paramter dynamically.
        /// </summary>
        /// <returns> Container Client </returns>
        private Container ContainerClient() {
                CosmosClient cosmosDbClient = new CosmosClient(CosmosDBAccountUri, CosmosDBAccountPrimaryKey);
                Container containerClient = cosmosDbClient.GetContainer(CosmosDbName, CosmosDbContainerName);
                return containerClient;
            }
            [HttpPost]
        public async Task < IActionResult > AddEmployee(EmployeeModel employee) {
                try {
                    var container = ContainerClient();
                    var response = await container.CreateItemAsync(employee, new PartitionKey(employee.Department));
                    return Ok(response);
                } catch (Exception ex) {
                    return BadRequest(ex.Message);
                }
            }
            [HttpGet]
        public async Task < IActionResult > GetEmployeeDetails() {
                try {
                    var container = ContainerClient();
                    var sqlQuery = "SELECT * FROM c";
                    QueryDefinition queryDefinition = new QueryDefinition(sqlQuery);
                    FeedIterator < EmployeeModel > queryResultSetIterator = container.GetItemQueryIterator < EmployeeModel > (queryDefinition);
                    List < EmployeeModel > employees = new List < EmployeeModel > ();
                    while (queryResultSetIterator.HasMoreResults) {
                        FeedResponse < EmployeeModel > currentResultSet = await queryResultSetIterator.ReadNextAsync();
                        foreach(EmployeeModel employee in currentResultSet) {
                            employees.Add(employee);
                        }
                    }
                    return Ok(employees);
                } catch (Exception ex) {
                    return BadRequest(ex.Message);
                }
            }
            [HttpGet]
        public async Task < IActionResult > GetEmployeeDetailsById(string employeeId, string partitionKey) {
                try {
                    var container = ContainerClient();
                    ItemResponse < EmployeeModel > response = await container.ReadItemAsync < EmployeeModel > (employeeId, new PartitionKey(partitionKey));
                    return Ok(response.Resource);
                } catch (Exception ex) {
                    return BadRequest(ex.Message);
                }
            }
            [HttpPut]
        public async Task < IActionResult > UpdateEmployee(EmployeeModel emp, string partitionKey) {
                try {
                    var container = ContainerClient();
                    ItemResponse < EmployeeModel > res = await container.ReadItemAsync < EmployeeModel > (emp.id, new PartitionKey(partitionKey));
                    //Get Existing Item
                    var existingItem = res.Resource;
                    //Replace existing item values with new values 
                    existingItem.Name = emp.Name;
                    existingItem.Country = emp.Country;
                    existingItem.City = emp.City;
                    existingItem.Department = emp.Department;
                    existingItem.Designation = emp.Designation;
                    var updateRes = await container.ReplaceItemAsync(existingItem, emp.id, new PartitionKey(partitionKey));
                    return Ok(updateRes.Resource);
                } catch (Exception ex) {
                    return BadRequest(ex.Message);
                }
            }
            [HttpDelete]
        public async Task < IActionResult > DeleteEmployee(string empId, string partitionKey) {
            try {
                var container = ContainerClient();
                var response = await container.DeleteItemAsync < EmployeeModel > (empId, new PartitionKey(partitionKey));
                return Ok(response.StatusCode);
            } catch (Exception ex) {
                return BadRequest(ex.Message);
            }
        }
    }
}

Now, we have all the code and required configuration to work with Azure CosmosDB in our sample application.

Step 12: Run the ASP.NET Core API Application

Now press Keyboard F5 or the Visual Studio Run button to run the application. After running the application, the following screen will be shown in the browser with swagger UI having all the methods which we have created in our ASP.NET core web API, as shown in the following screenshot:

Now, we will test the functionality by using the Swagger UI, but you can use Postman or any other tool to execute the API endpoints.

Step 13: Azure Cosmos DB CRUD Demo 

The following animated image shows how the insert, read, update, and delete operations of Azure Cosmos DB work.

Add Employees

Get List of Employees and Employee by Id

Update the Employee

Delete the Employee by Id

Summary

I hope, from all the examples above, you have learned how to insert, read, update, and delete data from Azure Cosmos DB using ASP.NET Core API C#. If you like it, share it with your friends and beginners, or any doubts or suggestions, then please those using the comment box.

Read more articles on ASP.NET, 

• How To Create ASP.NET Core MVC Application 
• Hosting ASP.NET Web API REST Service On IIS 10 
• Publishing ASP.NET Web API REST Service Using File System Method
• Building And Running ASP.NET Core Application In Docker Container