Connect Bot Framework to DB [Part 3] | READ data from Azure SQL Database

This is part 3 of the series “Connect Microsoft Bot Framework to Database“. In the last part, we had connected Bot Framework with a local SQL database. In this part, we will connect the bot framework to Azure SQL Database.

Azure SQL Database is the intelligent, scalable, relational database service built for the cloud. It is evergreen and always up to date, with AI-powered and automated features that optimize performance and durability for you. Serverless compute and Hyperscale storage options automatically scale resources on-demand, so you can focus on building new applications without worrying about storage size or resource management.

Pre-requisites

Azure Subscription
Visual Studio
Bot Framework Emulator
Bot Framework v4 Templates

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

Video

Creating Azure SQL Database

Let’s Create Resource Group to keep all the resources related to our project.

Name the resoure group accordingly.

Click on Review + Create and Create in next Step. Inside the Resource Group, add SQL Database resource.

Fill the below form by providing necessary details.

So here we give the database name EmployeeDB. You can choose the server or create a new one. If you want to create a new server you can provide the server name, admin login, and password in the below form.

After creating the server let us configure the database, by default it will be standard, here the requirements will be very less so basic is selected and 100 MB for demonstration purpose. It will take some while to create a database. Let other settings be as it is.

Let us go to the resource and create some tables. Go to Query Editor, provide credentials that you have provided during server creation.

Let’s write the queries and run it to create a table.

CREATE TABLE EMPLOYEE
(
EMPID VARCHAR(10) CONSTRAINT pk_empid PRIMARY KEY,
EMPNAME VARCHAR(50),
)
GO
INSERT INTO EMPLOYEE VALUES (‘E1001’, ‘Steve’)
INSERT INTO EMPLOYEE VALUES (‘E1002’, ‘Rob’)
INSERT INTO EMPLOYEE VALUES (‘E1003’, ‘Bob’)
INSERT INTO EMPLOYEE VALUES (‘E1004’, ‘Jane’)

For verifying click on Tables, Right click table employee and click select top 1000 rows.

The data is inserted in the table.

Now we have to get the connection string to connect to our DB.

Click on Connection Settings, Click on ADO.NET

Let’s keep the above the authentication stored somewhere in notepad to be used for next steps.

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 Azure 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 =tcp:server_name.database.windows.net,1433;
Initial Catalog=database_name;Persist Security Info=False;
UserID=server_name;Password=input_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" 
-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, 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.

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

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

namespace EchoAzureDBBot.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 Azure 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.