Update (12/19/17):
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:
var store = new SqlBotDataStore(ConfigurationManager.ConnectionStrings["BotDataContextConnectionString"].ConnectionString); builder.Register(c => store) .Keyed<IBotDataStore<BotData>>(AzureModule.Key_DataStore) .AsSelf() .SingleInstance();And in web.config, create the connection string:
<connectionStrings> <add name="BotDataContextConnectionString" providerName="System.Data.SqlClient" connectionString="Server=tcp:[YourDatabaseServerName].database.windows.net,1433;Initial Catalog=[YourDatabaseName];Persist Security Info=False;User ID=[YourDatabaseUserId];Password=[YourDatabaseUserPassword];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" /> </connectionStrings>Check out the sample here on Git Hub.
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.
You can find the completed sample on Github here.
Prerequisites:
- Free Azure Trial account
- Visual Studio 2017 or 2015 Community (update all extensions)
- Bot Application Template (in your Project Templates folder)
- Entity Framework nuget package
More about the Default State Client
In the .NET SDK, the default URL to access the default Connector State Service is "https://state.botframework.com" as per the following:
private void Initialize()
{
this.BotState = new BotState(this);
this.BaseUri = new Uri("https://state.botframework.com");
[...]
}
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.
public interface IBotDataStore<T>
{
Task<bool> FlushAsync(IAddress key, CancellationToken cancellationToken);
Task<T> LoadAsync(IAddress key, BotStoreType botStoreType, CancellationToken cancellationToken);
Task SaveAsync(IAddress key, BotStoreType botStoreType, T data, CancellationToken cancellationToken);
}
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.
<connectionStrings>
<add name="BotDataContextConnectionString"
providerName="System.Data.SqlClient"
connectionString="Server=tcp:[AzureDatabaseServerName].database.windows.net,1433;
Initial Catalog=[SqlDatabaseName];
Persist Security Info=False;
User ID=[SqlDatabaseUserName];
Password=[SqlDatabasePassword];
MultipleActiveResultSets=False;
Encrypt=True;
TrustServerCertificate=False;
Connection Timeout=30;" />
</connectionStrings>
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.
public class SqlBotDataStore : IBotDataStore<BotData>
{
string _connectionStringName { get; set; }
public SqlBotDataStore(string connectionStringName)
{
_connectionStringName = connectionStringName;
}
async Task<BotData> IBotDataStore<BotData>.LoadAsync(IAddress key, BotStoreType botStoreType, CancellationToken cancellationToken)
{
using (var context = new SqlBotDataContext(_connectionStringName))
{
try
{
SqlBotDataEntity entity = SqlBotDataEntity.GetSqlBotDataEntity(key, botStoreType, context);
if (entity == null)
return new BotData(eTag: String.Empty, data: null);
return new BotData(entity.ETag, entity.GetData());
}
catch (Exception ex)
{
throw new HttpException((int)HttpStatusCode.InternalServerError, ex.Message);
}
}
}
async Task IBotDataStore<BotData>.SaveAsync(IAddress key, BotStoreType botStoreType, BotData botData, CancellationToken cancellationToken)
{
SqlBotDataEntity entity = new SqlBotDataEntity(botStoreType, key.BotId, key.ChannelId, key.ConversationId, key.UserId, botData.Data)
{
ETag = botData.ETag,
ServiceUrl = key.ServiceUrl
};
...
}
...
}
The SqlBotDataContext inherits from DbContext and has the BotData DbSet of SqlBotDataEntity objects.
Note: The
DbContextclass is part of the Entity Framework
public class SqlBotDataContext : DbContext
{
public SqlBotDataContext()
: this("BotDataContextConnectionString")
{
}
public SqlBotDataContext(string connectionStringName)
: base(connectionStringName)
{
}
public DbSet<SqlBotDataEntity> BotData { get; set; }
}
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
IAddressinterface is part .NET SDK, in theMicrosoft.Bot.Builder.Dialogsnamespace
public class SqlBotDataEntity : IAddress
{
private static readonly JsonSerializerSettings serializationSettings = new JsonSerializerSettings()
{
Formatting = Formatting.None,
NullValueHandling = NullValueHandling.Ignore
};
internal SqlBotDataEntity() { }
internal SqlBotDataEntity(BotStoreType botStoreType, string botId, string channelId, string conversationId, string userId, object data)
{
this.BotStoreType = botStoreType;
this.BotId = botId;
this.ChannelId = channelId;
this.ConversationId = conversationId;
this.UserId = userId;
this.Data = Serialize(data);
}
// Entity Framework fields
#region Fields
...
// Methods used to serialize, get/set data
#region Methods
...
}
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.csto match the following:
protected void Application_Start()
{
GlobalConfiguration.Configure(WebApiConfig.Register);
var builder = new ContainerBuilder();
builder.RegisterModule(new DialogModule());
// registering our own IBotDataStore
var store = new SqlBotDataStore("BotDataContextConnectionString");
builder.Register(c => new CachingBotDataStore(store, CachingBotDataStoreConsistencyPolicy.LastWriteWins))
.As<IBotDataStore<BotData>>()
.AsSelf()
.InstancePerLifetimeScope();
builder.Update(Conversation.Container);
}
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:
public partial class InitialSetup : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.SqlBotDataEntities",
c => new
{
Id = c.Int(nullable: false, identity: true),
BotStoreType = c.Int(nullable: false),
BotId = c.String(),
ChannelId = c.String(maxLength: 200),
ConversationId = c.String(maxLength: 200),
UserId = c.String(maxLength: 200),
Data = c.Binary(),
ETag = c.String(),
ServiceUrl = c.String(),
Timestamp = c.DateTimeOffset(nullable: false, precision: 7),
})
.PrimaryKey(t => t.Id)
.Index(t => new { t.BotStoreType, t.ChannelId, t.ConversationId }, name: "idxStoreChannelConversation")
.Index(t => new { t.BotStoreType, t.ChannelId, t.ConversationId, t.UserId }, name: "idxStoreChannelConversationUser")
.Index(t => new { t.BotStoreType, t.ChannelId, t.UserId }, name: "idxStoreChannelUser");
}
public override void Down()
{
DropIndex("dbo.SqlBotDataEntities", "idxStoreChannelUser");
DropIndex("dbo.SqlBotDataEntities", "idxStoreChannelConversationUser");
DropIndex("dbo.SqlBotDataEntities", "idxStoreChannelConversation");
DropTable("dbo.SqlBotDataEntities");
}
}
Note the Timestamp field. We want it to have a default value of the current UTC date. Modify it per the following:
Timestamp = c.DateTimeOffset(nullable: false, precision: 7, defaultValueSql: "GETUTCDATE()"),
Finally, run the update-database command on your NuGet Package Manager Console:
PM> update-database
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:
private async Task MessageReceivedAsync(IDialogContext context, IAwaitable<object> result)
{
//retrieve the info objects, incrementing the count for each
var privateData = context.PrivateConversationData;
var conversationData = context.ConversationData;
var userData = context.UserData;
var privateConversationInfo = IncrementInfoCount(privateData, BotStoreType.BotPrivateConversationData.ToString());
var conversationInfo = IncrementInfoCount(conversationData, BotStoreType.BotConversationData.ToString());
var userInfo = IncrementInfoCount(userData, BotStoreType.BotUserData.ToString());
var activity = await result as Activity;
// calculate something for us to return
int length = (activity.Text ?? string.Empty).Length;
// return our reply to the user, showing the three counts
await context.PostAsync($"You sent {activity.Text} which was {length} characters. \n\nPrivate Conversation message count: {privateConversationInfo.Count}. \n\nConversation message count: {conversationInfo.Count}.\n\nUser message count: {userInfo.Count}.");
//persist the three info objects to the store
privateData.SetValue(BotStoreType.BotPrivateConversationData.ToString(), privateConversationInfo);
conversationData.SetValue(BotStoreType.BotConversationData.ToString(), conversationInfo);
userData.SetValue(BotStoreType.BotUserData.ToString(), userInfo);
context.Wait(MessageReceivedAsync);
}
You’ll also need the following class BotDataInfo and method, IncrementInfoCount:
public class BotDataInfo
{
public int Count { get; set; }
}
private BotDataInfo IncrementInfoCount(IBotDataBag botdata, string key)
{
BotDataInfo info = null;
if (botdata.ContainsKey(key))
{
info = botdata.GetValue<BotDataInfo>(key);
info.Count++;
}
else
info = new BotDataInfo() { Count = 1 };
return info;
}
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.
Happy Making!
Eric Dahlvang and Matthew Shim from the Bot Framework Team