In this post, we will create a chatbot that connects to both the QnA Maker service and Azure SQL DB. This will be a simple customized bot that can be used for any use case.
Prerequisites
- Create a QnA Maker Knowledge Base with the sample FAQs. I have used the FAQ URL from the official documentation.
- Create Azure SQL DB with sample table and data.
Working Demo
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.

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:{your server_name}.database.windows.net,1433;Initial Catalog=database_name;Persist Security Info=False;User ID={your user_id};Password={your 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, 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 AzureSQLDBQnABot.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 Azure SQL DB or QnA Maker.
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 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 AzureSQLDBQnABot.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 ChoicePrompt(nameof(ChoicePrompt)));
AddDialog(new AzureSQLDBDialog());
AddDialog(new QnAMakerDialog());
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> { "Use Azure SQL DB", "Use QnA Maker" };
// 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("Use Azure SQL DB"))
{
return await stepContext.BeginDialogAsync(nameof(AzureSQLDBDialog), null, cancellationToken);
}
else
{
return await stepContext.BeginDialogAsync(nameof(QnAMakerDialog), 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.
- AzureSQLDBDialog
- QnAMakerDialog

Update the code for AzureSQLDBDialog.cs
to ask the user to provide the employee code and capture the user response.
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 AzureSQLDBQnABot.Dialogs
{
public class AzureSQLDBDialog : CancelAndHelpDialog
{
public AzureSQLDBDialog()
: base(nameof(AzureSQLDBDialog))
{
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 Code.")
}, cancellationToken);
}
private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
{
stepContext.Values["EmployeeCode"] = (string)stepContext.Result;
// Do some query on SQL Database
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);
}
}
}
}
Similarly, update for QnAMakerDialog.cs
as well.
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 AzureSQLDBQnABot.Dialogs
{
public class QnAMakerDialog : CancelAndHelpDialog
{
public QnAMakerDialog()
: base(nameof(QnAMakerDialog))
{
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 question.")
}, cancellationToken);
}
private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
{
stepContext.Values["Question"] = (string)stepContext.Result;
// Call QnA Maker service
return await stepContext.PromptAsync(nameof(ConfirmPrompt), new PromptOptions
{
Prompt = MessageFactory.Text("Would you like to ask more questions?")
}, 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);
}
}
}
}
If you notice in the above code, we have not implemented the actual logic of querying the database and calling the QnA Maker service. This we will do it now.
Implement Querying the Azure SQL Database
Update the AzureSQLDBDialog.cs
with below code.
using AzureSQLDBQnABot.Models;
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 AzureSQLDBQnABot.Dialogs
{
public class AzureSQLDBDialog : CancelAndHelpDialog
{
ChatbotDBContext context;
public ChatbotDBContext Context { get { return context; } }
public AzureSQLDBDialog()
: base(nameof(AzureSQLDBDialog))
{
context = new ChatbotDBContext();
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 Code.")
}, cancellationToken);
}
private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
{
stepContext.Values["EmployeeCode"] = (string)stepContext.Result;
// Do some query on SQL Database
var empNumber = (string)stepContext.Values["EmployeeCode"];
Employee employee = FetchEmployeeName(empNumber);
if (employee == null)
{
await stepContext.Context.SendActivityAsync(MessageFactory.Text($"Employee with id {empNumber} not found."), cancellationToken);
}
else
{
var replyText = employee.Empid + ": " + employee.Empname;
await stepContext.Context.SendActivityAsync(MessageFactory.Text(replyText, replyText), 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);
}
}
public Employee FetchEmployeeName(string no)
{
Employee employee;
try
{
employee = (from e in Context.Employees
where e.Empid == no
select e).FirstOrDefault();//Query for employee details with id
}
catch (Exception)
{
throw;
}
return employee;
}
}
}
Implement QnA Maker Service
Update QnAMakerDialog.cs
with the below code.
using Microsoft.Bot.Builder;
using Microsoft.Bot.Builder.AI.QnA;
using Microsoft.Bot.Builder.Dialogs;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Threading;
using System.Threading.Tasks;
namespace AzureSQLDBQnABot.Dialogs
{
public class QnAMakerDialog : CancelAndHelpDialog
{
private readonly IConfiguration _configuration;
private readonly IHttpClientFactory _httpClientFactory;
public QnAMakerDialog(IConfiguration configuration, IHttpClientFactory httpClientFactory)
: base(nameof(QnAMakerDialog))
{
_configuration = configuration;
_httpClientFactory = httpClientFactory;
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 question.")
}, cancellationToken);
}
private async Task<DialogTurnResult> ActStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken)
{
stepContext.Values["Question"] = (string)stepContext.Result;
// Call QnA Maker service
var httpClient = _httpClientFactory.CreateClient();
var qnaMaker = new QnAMaker(new QnAMakerEndpoint
{
KnowledgeBaseId = _configuration["QnAKnowledgebaseId"],
EndpointKey = _configuration["QnAEndpointKey"],
Host = _configuration["QnAEndpointHostName"]
},
null,
httpClient);
var options = new QnAMakerOptions { Top = 1 };
// The actual call to the QnA Maker service.
var response = await qnaMaker.GetAnswersAsync(stepContext.Context, options);
if (response != null && response.Length > 0)
{
await stepContext.Context.SendActivityAsync(MessageFactory.Text(response[0].Answer), cancellationToken);
}
else
{
await stepContext.Context.SendActivityAsync(MessageFactory.Text("No QnA Maker answers were found."), cancellationToken);
}
return await stepContext.PromptAsync(nameof(ConfirmPrompt), new PromptOptions
{
Prompt = MessageFactory.Text("Would you like to ask more questions?")
}, 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);
}
}
}
}
Update the MainDialog.cs
to add the following properties and initialize them in the constructor.
private readonly IConfiguration _configuration;
private readonly IHttpClientFactory _httpClientFactory;
// Dependency injection uses this constructor to instantiate MainDialog
public MainDialog(ILogger<MainDialog> logger, IConfiguration configuration, IHttpClientFactory httpClientFactory)
: base(nameof(MainDialog))
{
Logger = logger;
_configuration = configuration;
_httpClientFactory = httpClientFactory;
AddDialog(new TextPrompt(nameof(TextPrompt)));
AddDialog(new ChoicePrompt(nameof(ChoicePrompt)));
AddDialog(new AzureSQLDBDialog());
AddDialog(new QnAMakerDialog(configuration, httpClientFactory));
AddDialog(new WaterfallDialog(nameof(WaterfallDialog), new WaterfallStep[]
{
IntroStepAsync,
ActStepAsync,
FinalStepAsync,
}));
// The initial child Dialog to run.
InitialDialogId = nameof(WaterfallDialog);
}
Also update the Startup.cs
to add the services in ConfigureServices
method.
services.AddHttpClient();
services.AddSingleton<ICredentialProvider, ConfigurationCredentialProvider>();
Update the appsettings.json
file to add the QnA Maker configuration details.
{
"MicrosoftAppId": "",
"MicrosoftAppPassword": "",
"LuisAppId": "",
"LuisAPIKey": "",
"LuisAPIHostName": "",
"QnAKnowledgebaseId": "<QnA Maker Knowledge base Id>",
"QnAEndpointKey": "<End Point Key>",
"QnAEndpointHostName": "<Host Name>"
}
You can find the above configuration details in QnA Maker publish page.

Thank you All!!! Hope you find this useful.
If you liked our content and it was helpful, you can buy us a coffee or a pizza. Thank you so much.

Leave a Reply