ToDo Bot | Part 8 | Connecting Bot with Azure Cosmos DB

This is the bot series on ToDo Bot where we will be creating a Chatbot to create, view, and delete tasks. In this part, we will be connecting our bot with Azure Cosmos DB. For not to incur any charges for Azure Cosmos DB, we will use the Emulator.

The Azure Cosmos DB Emulator provides a local environment that emulates the Azure Cosmos DB service for development purposes. Using the Azure Cosmos DB Emulator, you can develop and test your application locally, without creating an Azure subscription or incurring any costs.

For more information, refer Microsoft Documentation.

Prerequisites

Azure Cosmos DB Local Emulator
Visual Studio
Bot Emulator

Second and third requirement link is available on the Downloads page.

Video

Create Database and Container

We have already created the database and container in our last part. Refer to my post on Working with Azure Cosmos DB Local Emulator.

Verify User and Generate User Id

Before going to the main functionalities of the bot, we have to verify the user. For that, add a new step UserExistsStepAsync in the waterfall dialog model in MainDialog. Also, create a method for the new steps.

AddDialog(new WaterfallDialog(nameof(WaterfallDialog), new WaterfallStep[]
            {
                UserExistsStepAsync,
                UserIDStepAsync,
                IntroStepAsync,
                ActStepAsync,
                FinalStepAsync,
            }));

In the UserExistsStepAsync, we will ask the user if he/she is a new user or a returning user. In the below code, you will notice that we are checking if the UserID is null.

private async Task<DialogTurnResult> UserExistsStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            if(User.UserID == null)
            {
                List<string> operationList = new List<string> { "Returning User", "New User" };
                // Create card
                var card = new AdaptiveCard(new AdaptiveSchemaVersion(1, 0))
                {
                    // Use LINQ to turn the choices into submit actions
                    Actions = operationList.Select(choice => new AdaptiveSubmitAction
                    {
                        Title = choice,
                        Data = choice,  // This will be a string
                    }).ToList<AdaptiveAction>(),
                };
                // Prompt
                return await stepContext.PromptAsync(nameof(ChoicePrompt), new PromptOptions
                {
                    Prompt = (Activity)MessageFactory.Attachment(new Attachment
                    {
                        ContentType = AdaptiveCard.ContentType,
                        // Convert the AdaptiveCard to a JObject
                        Content = JObject.FromObject(card),
                    }),
                    Choices = ChoiceFactory.ToChoices(operationList),
                    // Don't render the choices outside the card
                    Style = ListStyle.None,
                },
                    cancellationToken);
            }
            else
            {
                return await stepContext.NextAsync(null, cancellationToken);
            }
            
        }

We will keep a track of the user id for a conversation. Initially, the UserID will be null. Create a new property UserID in the User class.

public class User
    {
        public List<string> TasksList = new List<string>();
        public static string UserID { get; set; }
    }

At the start of the conversation (when a new user joins the conversation), make UserID null. Modify the OnMembersAddedAsync method in DialogAndWelcomeBot.cs.

protected override async Task OnMembersAddedAsync(IList<ChannelAccount> membersAdded, ITurnContext<IConversationUpdateActivity> turnContext, CancellationToken cancellationToken)
        {
            foreach (var member in membersAdded)
            {
                // Greet anyone that was not the target (recipient) of this message.
                // To learn more about Adaptive Cards, see https://aka.ms/msbot-adaptivecards for more details.
                if (member.Id != turnContext.Activity.Recipient.Id)
                {
                    var welcomeCard = CreateAdaptiveCardAttachment();
                    User.UserID = null;
                    var response = MessageFactory.Attachment(welcomeCard, ssml: "Welcome to Bot Framework!");
                    await turnContext.SendActivityAsync(response, cancellationToken);
                    await Dialog.RunAsync(turnContext, ConversationState.CreateProperty<DialogState>("DialogState"), cancellationToken);
                }
            }
        }

Capture the user response from the first step in UserIDStepAsync. In the below step, we are doing following actions –

If the user is a Returning User, ask the user to provide the User ID.
If the user is a New User, generate a new User ID. Also, check if the generated User ID is not present in the database. If present, generate a new user id again.

private async Task<DialogTurnResult> UserIDStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            if (User.UserID == null)
            {
                stepContext.Values["UserType"] = ((FoundChoice)stepContext.Result).Value;
                string userType = (string)stepContext.Values["UserType"];
                string userId = null;

                if ("Returning User".Equals(userType))
                {
                    return await stepContext.PromptAsync(UserValidationDialogID, new PromptOptions
                    {
                        Prompt = MessageFactory.Text("Please enter your user id.")
                    }, cancellationToken);
                }
                else
                {
                    do
                    {
                        userId = Repository.RandomString(7);
                    } while (await _cosmosDBClient.CheckNewUserIdAsync(userId, Configuration["CosmosEndPointURI"], Configuration["CosmosPrimaryKey"], Configuration["CosmosDatabaseId"], Configuration["CosmosContainerID"], Configuration["CosmosPartitionKey"]));

                    User.UserID = userId;
                    await stepContext.Context.SendActivityAsync(MessageFactory.Text("Please make a note of your user id"), cancellationToken);
                    await stepContext.Context.SendActivityAsync(MessageFactory.Text(User.UserID), cancellationToken);
                    return await stepContext.NextAsync(null, cancellationToken);
                }
            }
            else
            {
                return await stepContext.NextAsync(null, cancellationToken);
            }
            
        }

In the Returning User if condition, we have added a validation dialog id (UserValidationDialogID) in the prompt. This helps in validating the user id in the current context without going to the new step and validating.

Declare the dialog id at the class level of the MainDialog.

private readonly string UserValidationDialogID = "UserValidationDlg";

Declare a new Text Prompt dialog in the constructor of the MainDialog.

AddDialog(new TextPrompt(UserValidationDialogID, UserValidation));

Add a new method UserValidation in the MainDialog.cs for validating the User ID. In this method, we are calling a method from CosmosDBClient where we are querying the DB if the user id is already present.

We are passing the DB credentials and user id as arguments.

private async Task<bool> UserValidation(PromptValidatorContext<string> promptcontext, CancellationToken cancellationtoken)
        {
            string userId = promptcontext.Recognized.Value;
            await promptcontext.Context.SendActivityAsync("Please wait, while I validate your details...", cancellationToken: cancellationtoken);

            if (await _cosmosDBClient.CheckNewUserIdAsync(userId, Configuration["CosmosEndPointURI"], Configuration["CosmosPrimaryKey"], Configuration["CosmosDatabaseId"], Configuration["CosmosContainerID"], Configuration["CosmosPartitionKey"]))
            {
                await promptcontext.Context.SendActivityAsync("Your details are verified.", cancellationToken: cancellationtoken);
                User.UserID = userId;
                return true;
            }
            await promptcontext.Context.SendActivityAsync("The user id you entered is not found, please enter your user id.", cancellationToken: cancellationtoken);
            return false;
        }

The code related to the Cosmos DB will be written in the CosmosDBClient. Create a new folder Utilities at the project level and add the below class files to it.

CosmosDBClient.cs
Repository.cs
ToDoTask.cs

We will be using the same code with slight modifications from the sample for the DB connection as we discussed in the last part. Add the below code in the CosmosDBClient.cs.

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

namespace ToDoBot.Utilities
{
    public class CosmosDBClient
    {
        // The Cosmos client instance
        private CosmosClient cosmosClient;

        // The database we will create
        private Database database;

        // The container we will create.
        private Container container;

        public async Task GetStartedAsync(string EndpointUri, String PrimaryKey, string databaseId, string containerId, string partitionKey)
        {
            // Create a new instance of the Cosmos Client
            this.cosmosClient = new CosmosClient(EndpointUri, PrimaryKey, new CosmosClientOptions() { ApplicationName = "CosmosDBDotnetQuickstart" });
            await this.CreateDatabaseAsync(databaseId);
            await this.CreateContainerAsync(containerId, partitionKey);
            //await this.ScaleContainerAsync();
            //await this.AddItemsToContainerAsync();
            //await this.QueryItemsAsync();
            //await this.ReplaceFamilyItemAsync();
            //await this.DeleteFamilyItemAsync();
            //await this.DeleteDatabaseAndCleanupAsync();
        }

        // <CreateDatabaseAsync>
        /// <summary>
        /// Create the database if it does not exist
        /// </summary>
        private async Task CreateDatabaseAsync(string databaseId)
        {
            // Create a new database
            this.database = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(databaseId);
            Console.WriteLine("Created Database: {0}\n", this.database.Id);
        }
        // </CreateDatabaseAsync>


        // <CreateContainerAsync>
        /// <summary>
        /// Create the container if it does not exist. 
        /// </summary>
        /// <returns></returns>
        private async Task CreateContainerAsync(string containerId, string partitionKey)
        {
            // Create a new container
            this.container = await this.database.CreateContainerIfNotExistsAsync(containerId, partitionKey, 400);
            Console.WriteLine("Created Container: {0}\n", this.container.Id);
        }
        // </CreateContainerAsync>

        // <QueryItemsAsync>
        /// <summary>
        /// Run a query (using Azure Cosmos DB SQL syntax) against the container
        /// </summary>
        public async Task<bool> CheckNewUserIdAsync(string userId, string EndpointUri, string PrimaryKey, string databaseId, string containerId, string partitionKey)
        {
            await GetStartedAsync(EndpointUri, PrimaryKey, databaseId, containerId, partitionKey);

            var sqlQueryText = $"SELECT c.id FROM c WHERE c.id = '{userId}'";

            Console.WriteLine("Running query: {0}\n", sqlQueryText);

            QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
            FeedIterator<ToDoTask> queryResultSetIterator = this.container.GetItemQueryIterator<ToDoTask>(queryDefinition);

            while (queryResultSetIterator.HasMoreResults)
            {
                FeedResponse<ToDoTask> currentResultSet = await queryResultSetIterator.ReadNextAsync();
                if (currentResultSet.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            return false;
            
        }

        public async Task<int> AddItemsToContainerAsync(string userId, string task)
        {
            ToDoTask todotask = new ToDoTask
            {
                Id = userId,
                Task = task,
            };

            try
            {
                // Read the item to see if it exists.  
                ItemResponse<ToDoTask> todotaskResponse = await this.container.ReadItemAsync<ToDoTask>(todotask.Id, new PartitionKey(todotask.Task));
                Console.WriteLine("Item in database with id: {0} already exists\n", todotaskResponse.Resource.Id);
                return -1;
            }
            catch (CosmosException ex) when (ex.StatusCode == HttpStatusCode.NotFound)
            {
                
                ItemResponse<ToDoTask> todotaskResponse = await this.container.CreateItemAsync<ToDoTask>(todotask, new PartitionKey(todotask.Task));

                // Note that after creating the item, we can access the body of the item with the Resource property off the ItemResponse. We can also access the RequestCharge property to see the amount of RUs consumed on this request.
                Console.WriteLine("Created item in database with id: {0} Operation consumed {1} RUs.\n", todotaskResponse.Resource.Id, todotaskResponse.RequestCharge);

                return 1;
            }

            
        }
        // </AddItemsToContainerAsync>

    }
}

Cosmos DB returns the result in JSON, therefore, we have to create a model class. ToDoTask.cs is the model class that contains the properties from the DB.

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ToDoBot.Utilities
{
    public class ToDoTask
    {
        [JsonProperty(PropertyName = "id")]
        public string Id { get; set; }
        public string Task { get; set; }
        
        public override string ToString()
        {
            return JsonConvert.SerializeObject(this);
        }
    }
}

Repository.cs will contain some reusable code such as generating a random string for User ID.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ToDoBot.Utilities
{
    public class Repository
    {
        private static Random random = new Random();
        public static string RandomString(int length)
        {
            const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
            return new string(Enumerable.Repeat(chars, length)
              .Select(s => s[random.Next(s.Length)]).ToArray());
        }
    }
}

In the sample code, the DB credentials were stored in the AppConfig file. In our project, we will store it in the appsettings.json.

{
  "MicrosoftAppId": "",
  "MicrosoftAppPassword": "",
  "LuisAppId": "",
  "LuisAPIKey": "",
  "LuisAPIHostName": "",
  "CosmosEndPointURI": "https://localhost:8081",
  "CosmosPrimaryKey": "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+5QDU5DE2nQ9nDuVTqobD4b8mGGyGMbIZnqyMsEcaGQy67XIw/Jw==",
  "CosmosDatabaseId": "ToDoBotDB",
  "CosmosContainerID": "ToDoTask",
  "CosmosPartitionKey": "/Task"
}

To read the appsettings.json, declare the below property at the class level of the MainDialog. Also, declare the CosmosDBClient.

protected readonly IConfiguration Configuration;
private readonly CosmosDBClient _cosmosDBClient;

Modify the MainDialog constructor as below.

public MainDialog(ToDoLUISRecognizer luisRecognizer, ILogger<MainDialog> logger, IConfiguration configuration, CosmosDBClient cosmosDBClient)
            : base(nameof(MainDialog))
        {
            _luisRecognizer = luisRecognizer;
            Logger = logger;
            Configuration = configuration;
            _cosmosDBClient = cosmosDBClient;
            

            AddDialog(new TextPrompt(nameof(TextPrompt)));
            AddDialog(new TextPrompt(UserValidationDialogID, UserValidation));
            AddDialog(new ChoicePrompt(nameof(ChoicePrompt)));
            AddDialog(new CreateTaskDialog(_cosmosDBClient));
            AddDialog(new ViewTaskDialog());
            AddDialog(new DeleteTaskDialog());
            AddDialog(new WaterfallDialog(nameof(WaterfallDialog), new WaterfallStep[]
            {
                UserExistsStepAsync,
                UserIDStepAsync,
                IntroStepAsync,
                ActStepAsync,
                FinalStepAsync,
            }));

            // The initial child Dialog to run.
            InitialDialogId = nameof(WaterfallDialog);
        }

You will notice that we have changed the declaration of the CreateTaskDialog by passing an argument cosmos DB client to it.

Add New Task in the Database

Declare the cosmos DB client at the class level of the CreateTaskDialog. Also, modify the parameters in the constructor.

private readonly CosmosDBClient _cosmosDBClient;
        public CreateTaskDialog(CosmosDBClient cosmosDBClient) : base(nameof(CreateTaskDialog))
        {
            
            _cosmosDBClient = cosmosDBClient;
            var waterfallSteps = new WaterfallStep[]
            {
                TasksStepAsync,
                ActStepAsync,
                MoreTasksStepAsync,
                SummaryStepAsync
            };

            AddDialog(new WaterfallDialog(nameof(WaterfallDialog), waterfallSteps));
            AddDialog(new TextPrompt(nameof(TextPrompt)));
            AddDialog(new ConfirmPrompt(nameof(ConfirmPrompt)));
            AddDialog(new CreateMoreTaskDialog());

            InitialDialogId = nameof(WaterfallDialog);
        }

Modify the SummaryStepAsync of the CreateTaskDialog to store the tasks in the DB.

private async Task<DialogTurnResult> SummaryStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            var userDetails = (User)stepContext.Result;
            
            await stepContext.Context.SendActivityAsync(MessageFactory.Text("Here are the tasks you provided - "), cancellationToken);
            for (int i = 0; i < userDetails.TasksList.Count; i++)
            {
                await stepContext.Context.SendActivityAsync(MessageFactory.Text(userDetails.TasksList[i]), cancellationToken);
            }
            await stepContext.Context.SendActivityAsync(MessageFactory.Text("Please wait while I add your tasks to the database..."), cancellationToken);
            for (int i = 0; i < userDetails.TasksList.Count; i++)
            {
                if (await _cosmosDBClient.AddItemsToContainerAsync(User.UserID, userDetails.TasksList[i]) == -1)
                {
                    await stepContext.Context.SendActivityAsync(MessageFactory.Text("The Task '" + userDetails.TasksList[i] + "' already exists"), cancellationToken);
                    
                }
                
            }
            await stepContext.Context.SendActivityAsync(MessageFactory.Text("Add Task operation completed. Thank you."), cancellationToken);

            return await stepContext.EndDialogAsync(userDetails, cancellationToken);
        }

Since we have used the configuration services and cosmos DB client services, we will have to register them in the Startup.cs.

public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers().AddNewtonsoftJson();

            // Create the Bot Framework Adapter with error handling enabled.
            services.AddSingleton<IBotFrameworkHttpAdapter, AdapterWithErrorHandler>();

            services.AddSingleton<ICredentialProvider, ConfigurationCredentialProvider>();

            // Create the storage we'll be using for User and Conversation state. (Memory is great for testing purposes.)
            services.AddSingleton<IStorage, MemoryStorage>();

            // Create the User state. (Used in this bot's Dialog implementation.)
            services.AddSingleton<UserState>();

            // Create the Conversation state. (Used by the Dialog system itself.)
            services.AddSingleton<ConversationState>();

            // Register LUIS recognizer
            services.AddSingleton<ToDoLUISRecognizer>();

            // Register Cosmos DB Client
            services.AddSingleton<CosmosDBClient>();

            // The MainDialog that will be run by the bot.
            services.AddSingleton<MainDialog>();

            // Create the bot as a transient. In this case the ASP Controller is expecting an IBot.
            services.AddTransient<IBot, DialogAndWelcomeBot<MainDialog>>();
        }

Finally, run the project and test the bot in the emulator. Also, check the DB for new items added.