Connect Bot Framework to DB [Part 2] | READ Data from Local SQL Database

This is part 2 of the series “Connect Microsoft Bot Framework to Database“. In the last part, we have used Entity Framework to connect to the DB. In this part, we will use the same Entity Framework inside the Bot Framework to connect the bot with the local DB.

Pre-requisites

  1. Azure Subscription
  2. Visual Studio
  3. Bot Framework Emulator
  4. Bot Framework v4 Templates
  5. Data Storage and Processing Tool set
  6. SQL Server Management Studio (If 5th pre-requisite not present)

The link of above requirement can be found from Downloads page.


Video


Creating Database in Local Server

We have two ways of creating DB –

  1. Using SSMS (SQL Server Management Studio)
  2. Using SQL Server Object Explorer from Visual Studio

We have covered both approaches in Part 1. You can follow it to create the DB table. We will continue directly with creating the bot.

Creating the Bot

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

Provide the project name and click on Create.


We need to add some dependencies that will help us create a connection between our code and Microsoft 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 need of one more Package i.e. Microsoft.EntityFrameworkCore.Tools 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)\ProjectsV13;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 and DB name. In my case the server name is “(localdb)\ProjectsV13” and DB name is “EmployeeDB”.

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.

Under Bot folder click on Echobot.cs and paste the below code.

using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Bot.Builder;
using Microsoft.Bot.Schema;
using System.Linq;
using System;
using EchoDatabaseBot.Models;

namespace EchoDatabaseBot.Bots
{
    public class EchoBot : ActivityHandler
    {
        EmployeeDBContext context;
        public EmployeeDBContext Context { get { return context; } }
        public EchoBot()
        {
            context = new EmployeeDBContext();
        }

        public Employee FetchEmployeeName(string no)
        {
            Employee employee;
            try
            {
                employee = (from e in Context.Employee
                            where e.EmpId == no
                            select e).FirstOrDefault();//Query for employee details with id
            }
            catch (Exception)
            {
                throw;
            }
            return employee;
        }

        protected override async Task OnMessageActivityAsync(ITurnContext<IMessageActivity> turnContext, CancellationToken cancellationToken)
        {
            var empNumber = turnContext.Activity.Text;
            Employee employee = FetchEmployeeName(empNumber);
            var replyText = employee.EmpId + ": " + employee.EmpName;
            await turnContext.SendActivityAsync(MessageFactory.Text(replyText, replyText), cancellationToken);
        }

        protected override async Task OnMembersAddedAsync(IList<ChannelAccount> membersAdded, ITurnContext<IConversationUpdateActivity> turnContext, CancellationToken cancellationToken)
        {
            var welcomeText = "Hello and welcome! I have connected this bot to Local DB."; //welcome message
            foreach (var member in membersAdded)
            {
                if (member.Id != turnContext.Activity.Recipient.Id)
                {
                    await turnContext.SendActivityAsync(MessageFactory.Text(welcomeText, welcomeText), cancellationToken);
                }
            }
        }
    }
}

Run the application using Cntrl+F5.

Go to Bot Framework Emulator and click on Open Bot and paste the URL in the space provided.

Paste the URL and click on Connect after which you will get the below screen.

You have entered the live chat and get the details of the employee by entering the employee id.

Voila!! There we have our ChatBot using Local DB.

Thank you All!!! Hope you find this useful.


One thought on “Connect Bot Framework to DB [Part 2] | READ Data from Local SQL Database

Add yours

Leave a Reply

Up ↑

%d