is there any way that a azure chatbot can query the databse and return answer from the database? may be using qna maker or luis?

Viewed 1214

I am trying to build a chat bot which queries a database and returns answer from the database depending on the question asked. is there any way to connect the azure chat bot to the database say azure SQL database?

2 Answers

Per your additional information, I have changed my answer. The original suggestion using QnA Maker is below. If you need to retrieve data from a database, such as student scores the best way to do this is with a slot-filling dialog. Personally, I use and prefer waterfall dialogs initiated by intent recognition with LUIS, but there are other ways to do it.

  • First, use LUIS to identify the user intent and start the appropriate dialog. I like the DispatchBot sample for showing how to do basic intent recognition (but that sample has the dialogs in the same .js file, which I do not like. I prefer keep my dialogs in separate files).
  • If possible, you'll want to try to extract the relevant entities so that the user doesn't have to retype them. There are numerous tutorials on how to do this but here is a good one.
  • Use the dialog to collect all the required information to make your database call. The multi-turn prompt sample is a good example of how to do this. You also need to account in your code for the entity extraction so you don't reprompt the user. I do this via an if statment: if the entity exists, return that to the next step; else prompt the user for the entity. Here is an example.
if (step._info.options.entities.orderNumber) {
    return await step.next(step._info.options.entities.orderNumber[0].toUpperCase());
} else {
    return await step.prompt(ORDER_PROMPT, {
        prompt: 'Please provide your order number.',
        retryPrompt: 'Please enter a valid order number.',
    });
}
  • You'll need to temporarily store the value somewhere. The easiest thing to do is store it in the step context (I call mine step) of your waterfall. For example, the step following the one above you would store the order number in my example via step.values.orderNumber = step.result; ( or step.result.value if returning a choice selection).
  • Once you have all the values you can query your DB. The method for doing that will depend on which DB you are using. But if we assume you are doing that via some sort of helper, you would just call something like const score = await queryMyDb(student, subject). Then you can reply with the score to the user.

This should give you enough to make an attempt at retrieving the information. If you try and still have trouble, you can open a separate request showing your code and the specific problems you are encountering.


Previous answer before additional context received:

Assuming you are just wanting to retrieve answers to questions in a single question-answer pair, it sounds like you just need to deploy QnA Maker and create a bot to utilize it. Microsoft provides good instructions on how to Create, train, and publish your QnA Maker knowledge base. Give it a try and if you have any issues, come back and ask via a separate question along with the code you've tried.

QnA Maker can support prompts for multi-turn conversations as well. You're still dealing with single question-answer pairs, but you can provide a easy selection for users to bring up related answers with a click of a button.

If you are using azure storage like table storage or blob. I suggest using Azure search service. This will help you solve your problem, but it will certainly add up the cost.

Related