How to get data from database in Microsoft BOT Framework 4

Question

I am building a chatbot using Microsoft bot framework 4. I would like to store the dialog contents in a SQL Server database and would like to retrieve them using the entity framework.

I tried to add code that get data from the database but the emulator reports errors. Following is my code:

        protected override async Task OnMembersAddedAsync(IList<ChannelAccount> membersAdded, ITurnContext<IConversationUpdateActivity> turnContext, CancellationToken cancellationToken)
    {
        foreach (var member in membersAdded)
        {
            if (member.Id != turnContext.Activity.Recipient.Id)
            {
                var start = GetStartDialog();

                if (start != null) {
                    var attachments = new List<Attachment>();
                    var reply = MessageFactory.Attachment(attachments);
                    var b = new HeroCard() { Title = start.Title, Text = start.Body, Buttons = new List<CardAction>() { new CardAction() { Title = "Option 1", Value = 1, Type = ActionTypes.MessageBack }, new CardAction() { Title = "Option 2", Value = 2, Type = ActionTypes.MessageBack } } };
                    reply.Attachments.Add(b.ToAttachment());

                    await turnContext.SendActivityAsync(reply, cancellationToken);
                }
            }
        }
    }

        public Dialog GetStartDialog()
    {
        BOTEntities db = new BOTEntities();
        Dialog retVal = db.Dialogs.Where(o => o.DialogID.Equals(1)).FirstOrDefault();
        return retVal;
    }

Any ideas as to why my code is not executing?

Thanks in advance for any help.

Answer 1

If you haven't already considered it, please look over the Entity Framework storage extension available from the BotBuilder-Community repo.

It is designed for v4 of the Bot Builder .NET SDK and can be installed via NuGet.

In short, you include the state storage components and then attach the EntityFrameworkTranscriptStore(). This will allow you to connect to and execute your script(s) for creating tables, etc.

As this is a built out solution, this may alleviate the error(s) you are receiving.

Hope of help!