Azure – Cosmos DB

In this post, we will see how to create a database in Cosmos DB using Azure Portal and how to manage CRUD operations with a console application.
But, what is Cosmos DB?
From Microsoft web site:
Azure Cosmos DB is a fully managed NoSQL database for modern app development. Single-digit millisecond response times, and automatic and instant scalability, guarantee speed at any scale. Business continuity is assured with SLA-backed availability and enterprise-grade security. App development is faster and more productive thanks to turnkey multi region data distribution anywhere in the world, open source APIs and SDKs for popular languages.
In this post, we will use a Cosmos DB database using Core API, that it is a Document DB and it is the best choice according to Microsoft.


First of all, we create a Cosmos DB:





Here, we can select the API that we want to use



Here, we define the resource group and the account name





The account is created and now, we can define a database called DbUsers







Finally, we create a container called Users




Now, we will insert some items in the container:















Finally, in order to check everything went fine, we run some queries:






It works fine and now, we can start to create the console application (we have to remember to take the keys that we will use in our console application):



We open Visual Studio, we create a console application project and first of all, we define a class called User:

[User.cs]

using Newtonsoft.Json;

namespace TestCosmosDB
{
    public class User
    {
        [JsonProperty(PropertyName = "id")]
        public string Id { get; set; }

        [JsonProperty(PropertyName = "username")]
        public string UserName { get; set; }

        [JsonProperty(PropertyName = "password")]
        public string Password { get; set; }

        public string UserType { get; set; }

        [JsonProperty(PropertyName = "created")]
        public string Created { get; set; }
        
        [JsonProperty(PropertyName = "channel")]
        public Channel[] Channels { get; set; }
    }

    public class Channel
    {
        [JsonProperty(PropertyName = "channelid")]
        public string ChannelId { get; set; }

        [JsonProperty(PropertyName = "channelname")]
        public string ChannelName { get; set; }
    }
}


Then, we define a function called GetListUsers used to get the list of Users:

static async Task<List<User>> GetListUsers(Container containerUsers)
{
     // definition of query
     var sqlQuery = "SELECT * FROM Users";
     // definition a query of Cosmos DB
     QueryDefinition queryDefinition = new QueryDefinition(sqlQuery);
     FeedIterator<User> queryResultSetIterator = containerUsers.GetItemQueryIterator<User>(queryDefinition);

     // define a list of Users
     List<User> lstUsers = new List<User>();

     while (queryResultSetIterator.HasMoreResults)
     {
          FeedResponse<User> currentResultSet = await queryResultSetIterator.ReadNextAsync();
          foreach (User user in currentResultSet)
          {
              lstUsers.Add(user);
          }
     }
     return lstUsers;
 }



Finally, we modify the Main method:

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace TestCosmosDB
{
    class Program
    {
        // define the Cosmo DB endpoint
        const string endpointUrl = "https://damiano.documents.azure.com:443/";
        // define the primary key for the Azure DocumentDB account
        const string key = "FxYRo07GN1pB6rBwNR88dobNJgGHAbBnSLF6t7oIdNVIjTgyrXHE3fSfOGgYRR4wve5t0lVQZO9MHcloIjLaaA==";
        // name of database and container we have created
        const string databaseName = "DbUsers";
        const string containerName = "Users";
    

        static async Task Main(string[] args)
        {
            // define a Cosmos client instance
            CosmosClient cosmosDBClient = new CosmosClient(endpointUrl, key);
            // define the instance of container
            Container containerUsers = await cosmosDBClient.GetDatabase(databaseName).CreateContainerIfNotExistsAsync(containerName, "/UserType");
            
            var lstUser = await GetListUsers(containerUsers);

            foreach(var item in lstUser)
            {
                var channels = string.Empty;
                if(item.Channels!= null)
                {
                    foreach(var itemChannel in item.Channels)
                    {
                        channels += $"{itemChannel.ChannelName} # ";
                    }
                }
                Console.WriteLine($"{item.Id} | {item.UserName} | {item.UserType} | {channels}");
            }
        }
    }
}


If we run the application, this will be the result:



Obviously, we can use the same query we ran in the portal, but we have to do some changes.
First of all, we define a new class called UserQuery, that it will be the output object:

[UserQuery.cs]

using Newtonsoft.Json;

namespace TestCosmosDB
{
    public class UserQuery
    {
        [JsonProperty(PropertyName = "id")]
        public string Id { get; set; }
        [JsonProperty(PropertyName = "username")]
        public string UserName { get; set; }
        [JsonProperty(PropertyName = "channelname")]
        public string Channel { get; set; }
    }
}


Then we have to modify the method called GetListUsers:

static async Task<List<UserQuery>> GetListUsers(Container containerUsers)
{
      // definition of query
      var sqlQuery = "SELECT u.id, u.username, c.channelname FROM Users u join c in u.channel where c.channelid='001'";
      // definition a query of Cosmos DB
      QueryDefinition queryDefinition = new QueryDefinition(sqlQuery);
      FeedIterator<UserQuery> queryResultSetIterator = containerUsers.GetItemQueryIterator<UserQuery>(queryDefinition);

      // define a list of Users
      List<UserQuery> lstUsers = new List<UserQuery>();

      while (queryResultSetIterator.HasMoreResults)
      {
           FeedResponse<UserQuery> currentResultSet = await queryResultSetIterator.ReadNextAsync();
           foreach (UserQuery user in currentResultSet)
           {
                lstUsers.Add(user);
            }
      }

      return lstUsers;
}


Finally, we have to modify the Main method:

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace TestCosmosDB
{
    class Program
    {
        // define the Cosmo DB endpoint
        const string endpointUrl = "https://damiano.documents.azure.com:443/";
        // define the primary key for the Azure DocumentDB account
        const string key = "FxYRo07GN1pB6rBwNR88dobNJgGHAbBnSLF6t7oIdNVIjTgyrXHE3fSfOGgYRR4wve5t0lVQZO9MHcloIjLaaA==";
        // name of database and container we have created
        const string databaseName = "DbUsers";
        const string containerName = "Users";
    

        static async Task Main(string[] args)
        {
            // define a Cosmos client instance
            CosmosClient cosmosDBClient = new CosmosClient(endpointUrl, key);
            // define the instance of container
            Container containerUsers = await cosmosDBClient.GetDatabase(databaseName).CreateContainerIfNotExistsAsync(containerName, "/UserType");
            
            var lstUser = await GetListUsers(containerUsers);

            foreach(var item in lstUser)
            {
                var channels = string.Empty;
                Console.WriteLine($"{item.Id} | {item.UserName} | {item.Channel}");
            }
        }
    }
}


If we run the application, this will be the result:




Now, we will create some methods in order to manage CRUD operations:

[Add]

static async Task AddNewUser(Container containerUsers)
{
     // we define a new User
     User objUser = new User
     {
         Id = "5",
         UserName = "New User from Code2",
         Password = "Pass123",
         UserType = "Admin",
         Created = DateTime.Now.ToShortDateString(),
         Channels = new Channel[1]{ new Channel
             { ChannelId = Guid.NewGuid().ToString(), ChannelName = "New channel" }
         }
     };

     // we insert the new item 
     await containerUsers.CreateItemAsync<User>(objUser, new PartitionKey(objUser.UserType));
}


Finally, we modify the Main method and then we will run the application:

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace TestCosmosDB
{
    class Program
    {
        // define the Cosmo DB endpoint
        const string endpointUrl = "https://damiano.documents.azure.com:443/";
        // define the primary key for the Azure DocumentDB account
        const string key = "FxYRo07GN1pB6rBwNR88dobNJgGHAbBnSLF6t7oIdNVIjTgyrXHE3fSfOGgYRR4wve5t0lVQZO9MHcloIjLaaA==";
        // name of database and container we have created
        const string databaseName = "DbUsers";
        const string containerName = "Users";
    

        static async Task Main(string[] args)
        {
            // define a Cosmos client instance
            CosmosClient cosmosDBClient = new CosmosClient(endpointUrl, key);
            // define the instance of container
            Container containerUsers = await cosmosDBClient.GetDatabase(databaseName).CreateContainerIfNotExistsAsync(containerName, "/UserType");

            await AddNewUser(containerUsers);

            var lstUser = await GetListUsers(containerUsers);

            foreach (var item in lstUser)
            {
                var channels = string.Empty;
                if (item.Channels != null)
                {
                    foreach (var itemChannel in item.Channels)
                    {
                        channels += $"{itemChannel.ChannelName} # ";
                    }
                }
                Console.WriteLine($"{item.Id} | {item.UserName} | {item.UserType} | {channels}");
            }
        }
    }
}




[Delete]

static async Task DeleteUser(Container containerUsers)
{
     string id = "77d32829-9a64-41dc-939d-5b7fcea080e2";
     await containerUsers.DeleteItemAsync<User>(id, new PartitionKey("Admin"));
}



Finally, we modify Main and we run the application:

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace TestCosmosDB
{
    class Program
    {
        // define the Cosmo DB endpoint
        const string endpointUrl = "https://damiano.documents.azure.com:443/";
        // define the primary key for the Azure DocumentDB account
        const string key = "FxYRo07GN1pB6rBwNR88dobNJgGHAbBnSLF6t7oIdNVIjTgyrXHE3fSfOGgYRR4wve5t0lVQZO9MHcloIjLaaA==";
        // name of database and container we have created
        const string databaseName = "DbUsers";
        const string containerName = "Users";
    

        static async Task Main(string[] args)
        {
            // define a Cosmos client instance
            CosmosClient cosmosDBClient = new CosmosClient(endpointUrl, key);
            // define the instance of container
            Container containerUsers = await cosmosDBClient.GetDatabase(databaseName).CreateContainerIfNotExistsAsync(containerName, "/UserType");

            await DeleteUser(containerUsers);

            var lstUser = await GetListUsers(containerUsers);

            foreach (var item in lstUser)
            {
                var channels = string.Empty;
                if (item.Channels != null)
                {
                    foreach (var itemChannel in item.Channels)
                    {
                        channels += $"{itemChannel.ChannelName} # ";
                    }
                }
                Console.WriteLine($"{item.Id} | {item.UserName} | {item.UserType} | {channels}");
            }
        }

        static async Task DeleteUser(Container containerUsers)
        {
            string id = "5";
            await containerUsers.DeleteItemAsync<User>(id, new PartitionKey("Admin"));
        }
    }
}





[Update]

static async Task UpdateUser(Container containerUsers)
{
     // we can use Linq to run the query
     var user = containerUsers.GetItemLinqQueryable<User>(allowSynchronousQueryExecution: true).Where(u => u.Id == "2").ToList().FirstOrDefault();
     // we change the UserName
     user.UserName = "New User Name";

     await containerUsers.UpsertItemAsync<User>(user, new PartitionKey(user.UserType));
 }


Finally, we modify the method Main and we run the application:

using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TestCosmosDB
{
    class Program
    {
        // define the Cosmo DB endpoint
        const string endpointUrl = "https://damiano.documents.azure.com:443/";
        // define the primary key for the Azure DocumentDB account
        const string key = "FxYRo07GN1pB6rBwNR88dobNJgGHAbBnSLF6t7oIdNVIjTgyrXHE3fSfOGgYRR4wve5t0lVQZO9MHcloIjLaaA==";
        // name of database and container we have created
        const string databaseName = "DbUsers";
        const string containerName = "Users";
    

        static async Task Main(string[] args)
        {
            // define a Cosmos client instance
            CosmosClient cosmosDBClient = new CosmosClient(endpointUrl, key);
            // define the instance of container
            Container containerUsers = await cosmosDBClient.GetDatabase(databaseName).CreateContainerIfNotExistsAsync(containerName, "/UserType");

            var lstUser = await GetListUsers(containerUsers);

            foreach (var item in lstUser)
            {
                var channels = string.Empty;
                if (item.Channels != null)
                {
                    foreach (var itemChannel in item.Channels)
                    {
                        channels += $"{itemChannel.ChannelName} # ";
                    }
                }
                Console.WriteLine($"{item.Id} | {item.UserName} | {item.UserType} | {channels}");
            }

            Console.Write("-----------------------");
            Console.WriteLine();

            await UpdateUser(containerUsers);

            var lstUserNew = await GetListUsers(containerUsers);

            foreach (var item in lstUserNew)
            {
                var channels = string.Empty;
                if (item.Channels != null)
                {
                    foreach (var itemChannel in item.Channels)
                    {
                        channels += $"{itemChannel.ChannelName} # ";
                    }
                }
                Console.WriteLine($"{item.Id} | {item.UserName} | {item.UserType} | {channels}");
            }
        }
    }
}