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 with Core API that is a Document DB and, according to Microsoft, it is the best choice.
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}");
}
}
}
}
