How to Create Stored Procedures in Azure Cosmos DB

In my previous articles, we have learned how to create Azure Cosmos DB as well as how to implement the Azure Cosmos DB CRUD operation programmatically with ASP.NET Core. If you wanted to read my previous articles, then read them using the following links:

In this article, we will learn how to create stored procedures in Azure Cosmos DB. Let’s begin with the basics so everyone can understand, including beginners.

What is a Cosmos DB Stored Procedure?

A Stored Procedures are sets of pre-compiled instructions in Azure Cosmos DB, written in JavaScript, allowing us to perform operations like reading, adding, updating, and deleting data.

Azure Cosmos DB can understand and work with many conditions and queries. For instance, if you are using the SQL API, you can use SQL queries to ask questions about your data. So, you could ask, "Can you show me all the employees who live in Latur?.

However, there’s a difference when it comes to stored procedures. Unlike SQL databases, where you might write stored procedures using SQL, in Azure Cosmos DB, you write stored procedures in JavaScript.

Why use stored procedures?

  • Efficiency
  • Atomicity
  • Speed
  • Security
  • Maintainability
  • Consistency

Efficiency

They can perform multiple operations in a single call, which reduces the amount of work and time needed to execute certain database operations in a single call instead of sending multiple requests to the database server.

Atomicity

They ensure tasks are done correctly and can rollback changes if something goes wrong while processing the database operation.

Speed

They execute quickly as they run close to where the data is stored and pre-compiled.

Security

They help keep the data safe by limiting direct access to the data in the database, reducing the risk of unwanted access.

Maintainability

Encapsulating logic within stored procedures makes the codebase more organised, maintainable, and reusable, allowing for hassle-free updates and modifications.

Consistency

By providing a consistent interface to the database and hiding its complexities, stored procedures allow for seamless interactions even when there are changes in the underlying database schema, ensuring uninterrupted and smooth application functionality.

Now lets start implementing the stored procedures for the Azure Cosmos DB SQL API step by step.

Step 1 :  Set up Prerequisites

Before we write our stored procedure, we need to create a Cosmos DB account, a database, and a container to hold our employee collection. These steps have already been covered in my previous article. If you have missed them, then please refer to them using the following link:

As explained in our previous articles, we will use the Azure Cosmos DB emulator for ease of understanding. The following is our Azure Cosmos DB container structure.



In our EmployeeManagementDB database, we have an Employee collection with the following structure

{
    "id": "1",
    "Name": "Sarika S",
    "Country": "India",
    "City": "Phaltan",
    "Department": "IT",
    "Designation": "Senior DataBase Architect"
}
    

Step 2 : Open the Cosmos DB Emulator or Explorer

Now that we have all the prerequisites, including an understanding of our Azure Cosmos DB database and collection structure, we can now open the editor, depending on whether it is the local Cosmos DB emulator or the Azure Cosmos DB Explorer, to create the stored procedure. Follow the following steps to create the stored procedure.
  • Navigate to the SQL API section under Explorer.
  • Expand the Employee section.
  • Access the Stored Procedures section.
  • Click on New Stored Procedure to create a new procedure.
Refer to the following image for the visual representation:
 


Step 3 : Create the Stored Procedure to Get All Employees

After clicked on the create new stored procedure as shown the preceding step two image , The following editor is get opens, follow the following steps

  • Remove the existing default code.
  • Use the code as given in the editor.
  • Provide the unique ID for the stored procedure for unique identification.
  • Click on Save, and then the stored procedure will be shown under the stored procedure blend.
Refer to the following image for the visual representation of creating and saving the stored procedure in Azure Cosmos DB.



getAllEmployees Stored Procedure Code

function getAllEmployees() {
    var context = getContext();
    var response = context.getResponse();
    var collection = context.getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM Employees',
        function (err, feed, options) {
            if (err) {
                throw new Error("Error while querying for documents: " + err.message);
            }
            response.setBody(feed);
        }
    );

    if (!isAccepted) {
        throw new Error('The query was not accepted by the server.');
    }
}

Please download the stored procedure code from Github for more details.
Summary

I hope that from the preceding examples and explanations, you have learned how to create the stored procedure in the Azure Cosmos database. If you have any feedback or suggestions, please send them via the comment box.

Understanding Horizontal vs. Vertical Scaling

In this article, we will learn about vertial and horizontal scalling in simple language, as many get confused while distinguishing between these two concepts of scalling the infrastructure.

What is Scalling?

Just like adjusting the number of lanes on a highway helps it handle more cars, increasing its capacity and allowing vehicles to reach their destinations more efficiently, in the world of computing and IT systems, scaling has a similar purpose. When we discuss 'capacity' here, we're referring to resources such as virtual machines (VMs), memory, CPU, storage, and more. Boosting this capacity might involve adding more VMs or amplifying the memory and CPU of existing systems. The essence is to adjust these resources to accommodate fluctuating demands, ensuring peak performance, cost-effectiveness, and consistent system uptime.

What is Vertical Scaling (Scaling up or Down)?

Vertical scaling involves adding more resources, such as memory or CPU, to an existing server or replacing that server with a more powerful one.

Example:

Let's consider a website hosted on a server with 4 GB of RAM and 2 CPU cores. If the website starts to get more traffic and needs more resources to handle the requests, one might upgrade the server to have 8 GB of RAM and 4 CPU cores.

Scenario:

Suppose you have an e-commerce website. On a regular day, your traffic is moderate. However, during a holiday sale, the traffic spikes significantly. To ensure your website doesn't crash, you might consider upgrading to a more powerful server (with a higher CPU, RAM, or storage). This is vertical scaling.

Pros:

  • Simpler to implement as there's no distribution of services.
  • There is no need for changes in application architecture.

Cons:

  • There are physical limits to how much you can scale vertically.
  • Potential for longer downtimes during upgrades.
  • Can be more expensive in the long run.

What is Horizontal Scaling (Scaling Out or In)?

Horizontal scaling involves adding more servers to or removing servers from the existing pool to distribute the load.

Example:

Consider the same website, which is experiencing more traffic. Instead of upgrading the existing server, you add three more servers with the same configuration. Now, the incoming traffic and workload are distributed across these four servers.

Scenario:

Continuing with the e-commerce website example, instead of upgrading to a single powerful server, you decide to distribute the load by adding more servers. So, when the traffic spikes during the holiday sale, the load balancer directs traffic to one of the many servers you have, distributing the load and ensuring no single server is overwhelmed.

Pros:

  • Can potentially scale indefinitely, as you can keep adding more servers.
  • Failures can be handled more gracefully. If one server fails, others can take over.
  • Better for ensuring high availability and redundancy.

Cons:

  • More complex infrastructure and can require changes in application architecture to support distributed processing.
  • Managing and maintaining multiple servers can be challenging.
  • Network overhead can become an issue if not managed properly.
To decide between horizontal and vertical scaling, one must consider the nature of the application, budget, expected traffic, and how long-term the solution needs to be. Some applications might start with vertical scaling due to its simplicity and then move to horizontal scaling as the user base grows. Others, especially those built in the cloud era, might opt for horizontal scaling from the get-go, ensuring high availability and fault tolerance.

Summary

I hope the above article helped you understand horizontal scaling and vertical scaling in straightforward terms. If you found the content valuable, please share it with your friends and on social media.

Related articles 

Difference Between Fetch and Pull Git Commands

In this blog post, we will learn about the difference between the get Fetech and pull commands, which are commonly used when working with a centralised code repository like Azure DevOps, etc.


Consider the scenario. Our code repository is hosted on Azure DevOps, and we are using code editors like Visual Studio or Visual Studio code. We need to use the following commands, either through the command line or using the visual options available in our code editor tools.

Git Fetch:

The "git fetch" command is used to retrieve changes from a remote repository to your local repository. It doesn't automatically merge or apply these changes to your local branch. It only updates your local references to the remote branches.

Scenario

when you want to see what changes are available in the remote repository without merging them into your working branch immediately. It's helpful for reviewing changes before deciding to incorporate them into your codebase.

In short, use "git fetch" when you want to see the new changes in the remote repo but don’t want to change your local repo yet.

Example:

#Fetch changes from the remote repository (origin in this case)
git fetch origin

#List branches, including remote branches, to see what's available
git branch -a

After using fetch, your local repo stays the same until you decide to add the new changes.

Git Pull:

The "git pull" command is used to fetch changes from a remote repository and automatically merge them into your current local branch. It is essentially a combination of "git fetch" and "git merge."

Scenario

When you want to quickly update your local branch with the latest changes from the remote branch, you are fine to merge them into your working branch after verifying the changes and knowing any implications.

Example

#Pull changes from the remote repository (origin) and merge them into the current branch
git pull origin dev

In this example, "dev" is the branch you want to pull changes from. The "git pull" command will fetch changes from the remote "dev" branch and merge them into your current local branch.

Summary

In summary, use "git fetch" to just see the new changes from the remote repo, and use "git pull" when you are ready to add those changes to your local repo quickly. Choose what’s right for you at the moment.

How to use Azure Cosmos DB Locally for Development

In this article, we will learn how to use the Azure Cosmos DB for local development without consuming cloud resources with a CRUD operation example. This method reduces the cost of testing numerous scenarios during development. 

Step 1: Set up Prerequisites

To use Azure Cosmos DB for local development and to perform CRUD operations in the Azure Cosmos DB, set up the following prerequisites: 

Azure Cosmos DB Emulator (offline)

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.

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 

In this article, we are going to use the Azure Cosmos DB emulator instead of a Cloud Azure Cosmos DB account. If you are new to Azure Cosmos DB, please read the articles below to learn how to create an Azure Cosmos DB account.
We are using the Azure Cosmos DB emulator in this article. search for the Azure Cosmos DB emulator from the search bar of the window once you download and install the emulator, 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 storing the employee basic data in the 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 the latest nuget package to interact with the Azure cosmos DB. The Microsoft.Azure.Cosmos supports the 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 into the image and type in search box Microsoft.Azure.Cosmos
  3. Select Microsoft.Azure.Cosmos as shown into 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 the 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 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 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 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 good practise 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 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 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 to 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 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 

Watch the following animated image to see 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 use the Azure Cosmos DB for local development without consuming cloud resources with a CRUD operation example. If you like it, share it with your friends and subscribe to the blog and YouTube channel for more articles.

Building CRUD Web API for Cosmos DB using C# ASP.NET Core

In this article, we will be building a CRUD (create, read, update, and delete) Web API using C# and ASP.NET Core to interact with Cosmos DB. This article will take you through the process of setting up a Cosmos DB database, creating a Web API project, and implementing CRUD operations using C#.

 
Azure Cosmos DB CRUD Flow By Vithal Wadje


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.

Azure Cosmos DB Emulator (offline)

As explained, 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.

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 

In this article, we are going to use the Azure Cosmos DB emulator instead of a Cloud Azure Cosmos DB account. If you are new to Azure Cosmos DB, please read the articles below to learn how to create an Azure Cosmos DB account. 
We are using the Azure Cosmos DB emulator in this article. search for the Azure Cosmos DB emulator from the search bar of the window once you download and install the emulator, 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 storing the employee basic data in the 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 the latest nuget package to interact with the Azure cosmos DB. The Microsoft.Azure.Cosmos supports the 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 into the image and type in search box Microsoft.Azure.Cosmos
  3. Select Microsoft.Azure.Cosmos as shown into 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 the 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 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 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 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 good practise 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 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 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 to 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 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 

Watch the following animated image to see 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





Download the source code from Github using the following link:

Summary

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode