Last week, we addressed a common customer question:
“How can we manage our bot state data without relying on the default connector state service?”
We provided one solution leveraging the botbuilder-azure packages available for nuget in .NET and as an npm module for Node.js. Those packages currently only support DocumentDB(CosmosDB) and Azure Table storage, in this article we’ll provide a step by step to create a custom state client for a SQL database. Since support for SQL is not included in the botbuilder-azure package, we’ll need to implement it ourselves.
In the .NET SDK, the default URL to access the default Connector State Service is "https://state.botframework.com" as per the following:
Note: Click here to see the default state service connection in the .NET SDK.
This default can be overridden by supplying a custom implementation of the IBotDataStore<BotData> interface in .NET.
IBotDataStore With Azure Sql
Note: You can use ANY SQL database you’d like as long as you provision a valid connectionString. You are not restricted to using Azure SQL.
We’ll be using the Entity Framework to map BotData objects to the SqlBotDataEntity objects saved in a SQL Server table. This should be the name of a standard System.Data.SqlClient connection string in the web.config of the bot project.
The SqlBotDataStore class shown below implements the IBotDataStore<BotData> interface which we’ll use to override the default state service connection. It is responsible for loading and persisting the SqlBotDataEntity objects. It uses an instance of the SqlBotDataContext class, which is an Entity Framework DbContext (also shown below). You’ll notice that the constructor is expecting a connection string parameter, this is the same connection string defined in web.config as defined above.
The SqlBotDataContext inherits from DbContext and has the BotData DbSet of SqlBotDataEntity objects.
Note: The DbContext class is part of the Entity Framework
The SqlBotDataEntity class inherits from IAddress and contains the fields stored in the database. It also has methods for serializing and deserializing the data field.
Note: The IAddress interface is part .NET SDK, in the Microsoft.Bot.Builder.Dialogs namespace
The Bot Builder .NET sdk uses Autofac for dependency injection. To override the default IBotDataStore using our new custom implementation, modify the project’s Global.asax.cs to match the following:
Ensure that these new classes are added to your project with the Entity Framework nuget package referenced. Also verify the connection string to your SQL database in the web.config file. Once you’ve confirmed these two steps, open the Package Manager Console window and enter the following two commands:
The above two commands will create two files, Configuration.cs and InitialSetup.cs DbMigration class which contains the following:
Note the Timestamp field. We want it to have a default value of the current UTC date.
Modify it per the following:
Finally, run the update-database command on your NuGet Package Manager Console:
This will execute the migration against the database, creating the SqlBotDataEntities table. Once the table is created, you can run the project. However, nothing specific will be stored in the three bot data bags (PrivateConversationData, ConversationData and UserData) because we haven’t added code to the bot that uses the data bags.
Modify the RootDialog so the MessageReceivedAsync method like this:
You’ll also need the following class BotDataInfo and method, IncrementInfoCount:
You should now be able to run the bot, connect to it from the emulator. Whenever you type in text, you’ll see simple echo responses like the following:
When you query your database, you should find that the bot’s state data is now being stored:
You’ll find the complete sample linked below. We’ve provided one implementation to store your bot’s state data into a SQL database. Although we used Azure SQL, you are by no means limited to using it - any valid SQL database with proper connection string and authorization should suffice.
We greatly appreciate all of the input and submissions from the open source community, you’ve all done a great job providing us feedback as we continue to develop the Microsoft Bot Framework.
Eric Dahlvang and Matthew Shim from the Bot Framework Team