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
- Azure Subscription
- Visual Studio
- Bot Framework Emulator
- Bot Framework v4 Templates
- Data Storage and Processing Tool set
- 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 –
- Using SSMS (SQL Server Management Studio)
- 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.