Saving State data in SQL with .NET

The original release of this article discussed in-depth how to implement the IBotDataStore interface in order to use a SQL database to save your bot’s state data. Since then, we’ve expanded the botbuilder-azure package to support SQL. With the latest version of the package, all you need to do is instantiate the SqlBotDataStore client in Global.asax of your .NET application as follows:

“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.

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.

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:

PM> enable-migrations
PM> add-migration "Initial Setup"

The above two commands will create two files, Configuration.cs and InitialSetup.csDbMigration class which contains the following:

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.

The Table

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:

Summary

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.