Connect Bot Framework to DB | READ and INSERT Data to Local SQL DB

In this post, we will create a chatbot that connects to the local SQL Database. This will be a simple customized bot that can be used for any use case. We will implement two CRUD operations READ and INSERT.

This bot uses Core Bot Template for the implementation.

Prerequisites

Connect Bot Framework to DB | INSERT Data to DB using Entity Framework

Video

This video is in Hindi Language. You can skip and follow along with this blog.

Creating the Bot

Launch Visual Studio. Create a new project using the Core Bot Template.

Give a name of the bot and click on Create. I have given the name as CoreBotDBConnection.

We need to add some dependencies that will help us create a connection between our code and Local SQL Database.

Step 1. Right click on Dependencies -> Manage Nuget Packages.

Step 2. Click on Browse -> Type Microsoft.EntityFrameworkCore.SqlServer -> Install -> OK -> I Accept.

There is a need for two more Packages i.e. Microsoft.EntityFrameworkCore.Tools & Microsoft.Extensions.Configuration.Json which also needs to be installed.

We now have to do Scaffolding task. Below is the command combined with the connection string.

Scaffold-DbContext -Connection "Data Source =(localdb)\MSSQLLocalDB;Initial Catalog=EmployeeDB;Integrated Security=true" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

In the File Menu under Tools click on Nuget Package Manager -> Package Manager Console. Paste the above command into the console. Make sure to provide the Server name, DB name, User ID, and Password.

Run the command and a new folder is generated with the name Models in your project directory. Click on it to find two files i.e. Employee class and DB Context class.

Editing the Project Structure

Remove the following files and folder which are not required for our requirement –

BookingDetails.cs
FlightBookingRecognizer.cs
BookingDialog.cs
DateResolverDialog.cs
CognitiveModels Folder

Remove the following lines of code from Startup.cs.

// Register LUIS recognizer
services.AddSingleton<FlightBookingRecognizer>();
 
// Register the BookingDialog.
services.AddSingleton<BookingDialog>();

Remove all the method content from all methods in MainDialog.cs. Also, remove the imports which are showing error. Update the MainDialog.cs with the below code.

// Copyright (c) Microsoft Corporation. All rights reserved.
// Licensed under the MIT License.
//
// Generated with Bot Builder V4 SDK Template for Visual Studio CoreBot v4.13.2

using Microsoft.Bot.Builder;
using Microsoft.Bot.Builder.Dialogs;
using Microsoft.Bot.Schema;
using Microsoft.Extensions.Logging;
using Microsoft.Recognizers.Text.DataTypes.TimexExpression;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace CoreBotDBConnetion.Dialogs
{
    public class MainDialog : ComponentDialog
    {
        
        protected readonly ILogger Logger;

        // Dependency injection uses this constructor to instantiate MainDialog
        public MainDialog(ILogger<MainDialog> logger)
            : base(nameof(MainDialog))
        {
            
            Logger = logger;

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

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

        private async Task<DialogTurnResult> IntroStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            return await stepContext.NextAsync(null, cancellationToken);
        }

        private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            return await stepContext.NextAsync(null, cancellationToken);
        }

        private async Task<DialogTurnResult> FinalStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            
            // Restart the main dialog with a different message the second time around
            var promptMessage = "What else can I do for you?";
            return await stepContext.ReplaceDialogAsync(InitialDialogId, promptMessage, cancellationToken);
        }
    }
}

Create Welcome Menu Card

This card will help us transfer the control to the respective sub-dialogs. We give 2 options to the user to either choose READ Data or INSERT Data.

Below is the updated code of MainDialog.cs that will show the Adaptive card and capture the user response.

// Copyright (c) Microsoft Corporation. All rights reserved.
// Licensed under the MIT License.
//
// Generated with Bot Builder V4 SDK Template for Visual Studio CoreBot v4.13.2

using AdaptiveCards;
using CoreBotDBConnetion.Utility;
using Microsoft.Bot.Builder;
using Microsoft.Bot.Builder.Dialogs;
using Microsoft.Bot.Builder.Dialogs.Choices;
using Microsoft.Bot.Schema;
using Microsoft.Extensions.Logging;
using Microsoft.Recognizers.Text.DataTypes.TimexExpression;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace CoreBotDBConnetion.Dialogs
{
    public class MainDialog : ComponentDialog
    {
        UserRepository userRepository;
        protected readonly ILogger Logger;

        // Dependency injection uses this constructor to instantiate MainDialog
        public MainDialog(ILogger<MainDialog> logger, UserRepository _userRepository)
            : base(nameof(MainDialog))
        {
            userRepository = _userRepository;
            Logger = logger;

            AddDialog(new TextPrompt(nameof(TextPrompt)));
            AddDialog(new ChoicePrompt(nameof(ChoicePrompt)));
            AddDialog(new ReadDialog(userRepository));
            AddDialog(new InsertDialog(userRepository));
            AddDialog(new WaterfallDialog(nameof(WaterfallDialog), new WaterfallStep[]
            {
                IntroStepAsync,
                ActStepAsync,
                FinalStepAsync,
            }));

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

        private async Task<DialogTurnResult> IntroStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            await stepContext.Context.SendActivityAsync(MessageFactory.Text("Please choose an option to proceed further."), cancellationToken);
            List<string> operationList = new List<string> { "READ Data", "INSERT Data" };
            // 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);
        }

        private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            stepContext.Values["UserOperation"] = ((FoundChoice)stepContext.Result).Value;

            string operation = (string)stepContext.Values["UserOperation"];

            if (operation.Equals("READ Data"))
            {
                return await stepContext.BeginDialogAsync(nameof(ReadDialog), null, cancellationToken);
            }
            else
            {
                return await stepContext.BeginDialogAsync(nameof(InsertDialog), null, cancellationToken);
            }
        }

        
        private async Task<DialogTurnResult> FinalStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {

            // Restart the main dialog with a different message the second time around
            var promptMessage = "What else can I do for you?";
            return await stepContext.ReplaceDialogAsync(InitialDialogId, promptMessage, cancellationToken);
        }
    }
}

Create Sub Dialog Classes

Create two sub dialog classes inside Dialogs folder for each of the user operation.

InsertDialog
ReadDialog

Implement READ Dialog

Update the code for ReadDialog.cs to ask the user to provide the employee id and capture the user response. Then call the FetchEmployeeDetails method to get the employee details.

using CoreBotDBConnetion.Models;
using CoreBotDBConnetion.Utility;
using Microsoft.Bot.Builder;
using Microsoft.Bot.Builder.Dialogs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace CoreBotDBConnetion.Dialogs
{
    public class ReadDialog : CancelAndHelpDialog
    {
        UserRepository userRepository;
        public ReadDialog(UserRepository _userRepository)
            : base(nameof(ReadDialog))
        {
            userRepository = _userRepository;

            AddDialog(new TextPrompt(nameof(TextPrompt)));
            AddDialog(new ChoicePrompt(nameof(ChoicePrompt)));
            AddDialog(new ConfirmPrompt(nameof(ConfirmPrompt)));
            AddDialog(new WaterfallDialog(nameof(WaterfallDialog), new WaterfallStep[]
            {
                IntroStepAsync,
                ActStepAsync,
                FinalStepAsync,
            }));

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

        private async Task<DialogTurnResult> IntroStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            return await stepContext.PromptAsync(nameof(TextPrompt), new PromptOptions
            {
                Prompt = MessageFactory.Text("Please enter the Employee Id.")
            }, cancellationToken);
        }

        private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            stepContext.Values["EmployeeId"] = (string)stepContext.Result;
            Employee employee = userRepository.FetchEmployeeDetails((string)stepContext.Values["EmployeeId"]);
            if (employee == null)
            {
                await stepContext.Context.SendActivityAsync(MessageFactory.Text($"Employee with id {(string)stepContext.Values["EmployeeId"]} not found."), cancellationToken);
            }
            else
            {
                await stepContext.Context.SendActivityAsync(MessageFactory.Text($"{employee.EmpId} - {employee.EmpName}"), cancellationToken);
            }

            return await stepContext.PromptAsync(nameof(ConfirmPrompt), new PromptOptions
            {
                Prompt = MessageFactory.Text("Would you like to search for more Employee names?")
            }, cancellationToken);
        }

        private async Task<DialogTurnResult> FinalStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            if ((bool)stepContext.Result)
            {
                return await stepContext.ReplaceDialogAsync(InitialDialogId, null, cancellationToken);
            }
            else
            {
                return await stepContext.EndDialogAsync(null, cancellationToken);
            }
        }
    }
}

Create a new folder Utility and add a new class file UserRepository.cs. This class file contains all the code for CRUD operations.

Add the following code in the UserRepository.cs file. The class file contains the code for reading and inserting the data.

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

namespace CoreBotDBConnetion.Utility
{
    public class UserRepository
    {
        EmployeeDBContext context;
        public EmployeeDBContext Context { get { return context; } }
        public UserRepository()
        {
            context = new EmployeeDBContext();
        }

        public Employee FetchEmployeeDetails(string empid)
        {
            Employee employee;
            try
            {
                employee = (from r in Context.Employees
                            where r.EmpId == empid
                            select r).FirstOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
            return employee;
        }

        public bool InsertEmployee(Employee employee)
        {
            bool status = false;
            try
            {
                Context.Employees.Add(employee);
                Context.SaveChanges();
                status = true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return status;
        }
    }
}

Implement INSERT Dialog

Update the code for InsertDialog.cs to ask the user to provide the employee id & employee name and capture the user response. Then call the InsertEmployee method to insert the employee details.

using CoreBotDBConnetion.Models;
using CoreBotDBConnetion.Utility;
using Microsoft.Bot.Builder;
using Microsoft.Bot.Builder.Dialogs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace CoreBotDBConnetion.Dialogs
{
    public class InsertDialog : CancelAndHelpDialog
    {
        UserRepository userRepository;
        public InsertDialog(UserRepository _userRepository)
            : base(nameof(InsertDialog))
        {
            userRepository = _userRepository;

            AddDialog(new TextPrompt(nameof(TextPrompt)));
            AddDialog(new ChoicePrompt(nameof(ChoicePrompt)));
            AddDialog(new ConfirmPrompt(nameof(ConfirmPrompt)));
            AddDialog(new WaterfallDialog(nameof(WaterfallDialog), new WaterfallStep[]
            {
                EmployeeIdStepAsync,
                EmployeeNameStepAsync,
                ActStepAsync,
                FinalStepAsync,
            }));

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

        private async Task<DialogTurnResult> EmployeeIdStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            return await stepContext.PromptAsync(nameof(TextPrompt), new PromptOptions
            {
                Prompt = MessageFactory.Text("Please enter the Employee Id.")
            }, cancellationToken);
        }

        private async Task<DialogTurnResult> EmployeeNameStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            stepContext.Values["EmployeeId"] = (string)stepContext.Result;
            return await stepContext.PromptAsync(nameof(TextPrompt), new PromptOptions
            {
                Prompt = MessageFactory.Text($"Please enter the Employee Name for id {(string)stepContext.Values["EmployeeId"]}")
            }, cancellationToken);
        }

        private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            stepContext.Values["EmployeeName"] = (string)stepContext.Result;

            Employee employee = new Employee();
            employee.EmpId = (string)stepContext.Values["EmployeeId"];
            employee.EmpName = (string)stepContext.Values["EmployeeName"];

            bool status = userRepository.InsertEmployee(employee);

            if (status)
            {
                await stepContext.Context.SendActivityAsync(MessageFactory.Text("Employee Inserted"), cancellationToken);
            }
            else
            {
                await stepContext.Context.SendActivityAsync(MessageFactory.Text("Employee Not Inserted or Employee already exists"), cancellationToken);
            }

            return await stepContext.PromptAsync(nameof(ConfirmPrompt), new PromptOptions
            {
                Prompt = MessageFactory.Text("Would you like to insert more Employee details?")
            }, cancellationToken);
        }

        private async Task<DialogTurnResult> FinalStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
        {
            if ((bool)stepContext.Result)
            {
                return await stepContext.ReplaceDialogAsync(InitialDialogId, null, cancellationToken);
            }
            else
            {
                return await stepContext.EndDialogAsync(null, cancellationToken);
            }
        }
    }
}

Also update the Startup.cs to add the service in ConfigureServices method.

services.AddSingleton<UserRepository>();

Update the appsettings.json file to add the connection string details.

{
  "MicrosoftAppId": "",
  "MicrosoftAppPassword": "",
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DBConnectionString": "data source=(localdb)\\MSSQLLocalDB;initial catalog=EmployeeDB;Integrated Security=true"
  }
}

Update the EmployeeDBContext.cs to read the appsettings.json file and get the connection string. Currently, the connection string is hard-coded.

Update the OnConfiguring method with the below code.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                              .SetBasePath(Directory.GetCurrentDirectory())
                              .AddJsonFile("appsettings.json");
            var config = builder.Build();
            var connectionString = config.GetConnectionString("DBConnectionString");

            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(connectionString);
            }
        }

Run the project and test the output in the bot emulator. You can find the complete code in GitHub.