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

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.