Saving Bot Activities in Azure SQL Database

In this blog post, we’ll walkthrough of how to integrate Bot Framework v4 SDK with Azure SQL Database and save Bot activities/ conversations into the database.

We’ll leverage Bot.Builder.Community.Storage.EntityFramework package to save conversations using EntityFrameworkTranscriptStore with TranscriptLoggerMiddleware into Azure SQL Database.

Let's start -

Create Azure Sql Database:

If you are new to Azure SQL and looking to understand more about different offerings of Azure SQL, you can start with this article.

Once you decided the type of Azure SQL Database that suits your requirement, you can navigate to the Select SQL Deployment options page and create your Database.

For this demo, I have created a single database in the serverless compute tier.

You can follow this article which explains the steps of creating an Azure SQL Database.

Connect to the database:

Once your Azure sql database is created, you can use the Query editor (preview) in the Azure portal to connect to the database and execute queries. Alternatively, you can also connect your database using SSMS and Azure Data Studio, etc.

• In the Azure portal, search for and select SQL databases, and then select your database from the list.
• On the page for your database, select Query editor (preview) in the left menu.
• Enter your server admin login information and select OK.

Note: Click on the Connection strings link in the above blade and copy the connection string somewhere which we’ll use later in this demo.

Create tables in your Azure SQL database:

Now, you need to create tables in your Azure SQL database to store the Bot Activities.

• Execute the CreateTableScript.sql script in your Azure SQL Database.
  Alternatively, you can also Click Me to see, `Create table script`

  CREATE TABLE [dbo].[BotDataEntity]( [Id] [int] IDENTITY(1,1) NOT NULL, [RealId] [varchar](1024) NOT NULL UNIQUE, [Document] [nvarchar](max) NOT NULL, [CreatedTime] [datetimeoffset](7) Not NULL, [TimeStamp] [datetimeoffset](7) Not NULL, CONSTRAINT [PK_BotDataEntity] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[BotDataEntity] ADD DEFAULT (getutcdate()) FOR [CreatedTime] GO ALTER TABLE [dbo].[BotDataEntity] ADD DEFAULT (getutcdate()) FOR [TimeStamp] GO CREATE TABLE [dbo].[TranscriptEntity]( [Id] [int] IDENTITY(1,1) NOT NULL, [Channel] [varchar](256) NOT NULL, [Conversation] [varchar](1024) NOT NULL, [Activity] [nvarchar](max) NOT NULL, [TimeStamp] [datetimeoffset](7) NOT NULL, CONSTRAINT [PK_TranscriptEntity] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) GO CREATE NONCLUSTERED INDEX [IX_TranscriptChannel] ON [dbo].[TranscriptEntity] ( [Channel] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_TranscriptTimeStamp] ON [dbo].[TranscriptEntity] ( [TimeStamp] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_TranscriptConversation] ON [dbo].[TranscriptEntity] ( [Conversation] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE [dbo].[TranscriptEntity] ADD DEFAULT (getutcdate()) FOR [TimeStamp] GO
• Once you execute the CreateTableScript.sql script in your Azure SQL Database, you should be able to see the below two tables (BotDataEntity & TranscriptEntity) created.
  

  We have just created the table structure, it doesn’t contain any data yet.

Getting Bot project ready:

For this Demo, we will be using Bot Framework v4 Echo bot sample which accepts input from the user and echoes it back.

You may download the working code sample from this GitHub link, or do the following changes to your existing Bot project.

• Import these packages into your Bot project.
  

• Add the following code to your bot project’s Startup.cs.

   var loggerConnectionString = Configuration["StoreConnectionString"];
   var logger = new EntityFrameworkTranscriptStore(loggerConnectionString);
   services.AddSingleton<ITranscriptStore>(logger);
  

  
  

• Add ITranscriptStore as a parameters to AdapterWithErrorHandler class, and .Use TranscriptLoggerMiddleware:

     public AdapterWithErrorHandler(ITranscriptStore transcriptLogger, IConfiguration configuration, ILogger<BotFrameworkHttpAdapter> logger, ConversationState conversationState = null)
            : base(configuration, logger)
        {
            Use(new TranscriptLoggerMiddleware(transcriptLogger));
  

  
  

• The connection string you have copied from Azure sql database, put that into your Bot project against StoreConnectionString key in appsettings.json file, something like shown below -

    {
      "MicrosoftAppId": "",
      "MicrosoftAppPassword": "",
      "StoreConnectionString": "Server=tcp:YourSQLServerName.database.windows.net,1433;Initial Catalog=YourDatabaseName;Persist Security Info=False;User ID=YourSqlUserId;Password=YourSqlPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    }
  

  Node: I have left MicrosoftAppId and MicrosoftAppPassword blank as I’ll be running my Bot locally in Emulator but if your Bot is deployed on some remote server e.g. Azure app service or Bot needs to communicate to other external services e.g., Cognitive services, then you will need to put MicrosoftAppId and MicrosoftAppPassword as well to authenticate your Bot.

We are almost done. Now you should be able to run your Bot project.

Let’s see it in Action:

Run your bot code/ project (if you have already hosted it to some server e.g., Azure app service, you may skip this part) -

• If you are running from the terminal, navigate to your Bot project folder and run dotnet run command.

  #run the bot
  dotnet run
  

• If you are running from Visual Studio
  • Launch Visual Studio
  • File -> Open -> Project/Solution
  • Navigate to your Bot project folder e.g. EntityFrameworkTranscriptStoreExample
  • Select your Bot project file e.g. EntityFrameworkTranscriptStoreExample.csproj file
  • Press F5 to run the project

    Note: As a prerequisite to run this project, make sure you have .NET Core (>=3.1) version installed on your machine.

Test the Bot using Bot Framework Emulator:

Bot Framework Emulator is a desktop application that allows bot developers to test and debug their bots on localhost or run remotely through a tunnel. You may install the Bot Framework Emulator from here.

Connect to the bot using Bot Framework Emulator:

• Launch Bot Framework Emulator
• File -> Open Bot
• Enter a Bot URL e.g., http://localhost:3978/api/messages. You may also enter the URL of the remote web server hosting the Bot code but in that case, you will also need to provide MicrosoftAppId and MicrosoftAppPassword for Bot to successfully authenticate.

Once connected you can send some messages to the Bot, and it should echo it back.

Logs:

Let’s Connect the database to see if Bot Activities are being recorded. You should see the messages saved into the Azure SQL tables we have created.

• BotDataEntity should store one entry for each unique connection.
• TranscriptEntity should contain all the conversations.
  

If you query the tables, you should be able to fetch all the messages exchanged between the user and Bot.

Please note that depending on your Bot, you may need to specify in your Bot’s terms of use directly to notify your users whenever storing user data is involved.

Hope it helps! :-)