Saving Bot Activities in Azure SQL Database

Posted on by The Bot Framework Team

In this article we’ll provide a walkthrough of how to create a Bot using the C# Bot Builder SDK and store message activity to an Azure SQL server using the Entity Framework.

Prerequisites:

Creating the Bot Project

The Bot Application template .zip file (see Prerequisites) should be in your Project Templates folder. Typically –

C:\Users[YOURUSERNAME]]\Documents\Visual Studio 2017\Templates\ProjectTemplates\Visual C#

Creating a new bot is as easy as creating any Visual Studio project. Click the File menu option, choose New, then Project.

Creating the Entity Framework Project

Next, right click on the solution and choose Add -> New Project.

Find the Class Library (.NET Framework) template, name the project, and click OK.

Once the two projects are created, the bot project needs to reference the data project. This is accomplished by right clicking on the bot project’s References node, choosing Add Reference, selecting Projects, clicking the checkbox beside the data project, and clicking the OK button.

NuGet Packages

Right click on the solution again and choose Manage NuGet Packages for Solution. Click the Browselink and search for Bot.Builder. Select it, choose the latest version, then on the rich hand menu select the Bot project and click Install.

Once Bot.Builder is updated, AutoMapper also needs to be installed. This is used to map the Bot.Builder Activities to the Activities saved in the database. Choose Browse in the NuGet package manager, enter automapper in the search box, select it, check the box next to the bot project, and click Install.

The Entity Framework is the last NuGet package that needs to be added. It should be added to both projects. Search for it, select it, click the checkboxes beside the project names and click Install.

Creating the Azure Sql Database

In the Azure Portal (https://portal.azure.com) click the New Plus button, and enter SQL Database in the filter field. Selecting the SQL Database option will display the creation panel. Click the Create button.

Fill in all the fields, and click Create again.

If you are using Azure Sql Server (as this example is), you might need to add your IP address to the firewall. It can be found on the SQL Database Overview blade.

Database Connection String

Once the Azure Sql Database has been provisioned, retrieve the connection string by navigating to the Properties page and clicking the Show database connection string link.

Copy the connection string and add it to both the <configuration> section in the App.config of the .Data project and the web.config of the .Bot project. This example uses Azure Sql connection string in ADO.NET, but any sql server instance compatible with the Entity Framework will suffice. Azure also provides support for JDBC, ODBC, and PHP.

<connectionStrings> 
<add name="ConversationDataContextConnectionString"
    providerName="System.Data.SqlClient"
    connectionString="Server=tcp:YourAzureSqlServerName.database.windows.net,1433;
        Initial Catalog=YourSqlServerDatabase;
        Persist Security Info=False;
        User ID=YourSqlServerLogin;
        Password=YourSqlServerPassword;
        MultipleActiveResultSets=False;
        Encrypt=True;
        TrustServerCertificate=False;
        Connection Timeout=30;" />
</connectionStrings>

Entity and Data Context

This example uses the Entity Framework code first method to create the Activity Logger table. To prepare the project for updating the database, create a new class in the data project named Activityand add the fields that will be logged.

Note: Only a small subset of the fields present on the Bot Framework Activity class are included in the sample.

public class Activity
{
    public string Id { get; set; }
    public string FromId { get; set; }
    public string FromName { get; set; }
    public string RecipientId { get; set; }
    public string RecipientName { get; set; }
    public string TextFormat { get; set; }
    public string TopicName { get; set; }
    public bool HistoryDisclosed { get; set; }
    public string Local { get; set; }
    public string Text { get; set; }
    public string Summary { get; set; }
    public string ChannelId { get; set; }
    public string ServiceUrl { get; set; }
    public string ReplyToId { get; set; }
    public string Action { get; set; }
    public string Type { get; set; }
    public DateTimeOffset Timestamp { get; set; }
    public string ConversationId { get; set; }
}

Next, add a class called ConversationDataContext per the sample below.

public class ConversationDataContext : DbContext
{
    public ConversationDataContext()
        : base("ConversationDataContextConnectionString")
    {
    }

    public DbSet<Activity> Activities { get; set; }
}

Notice the connection string passed to the base DbContext constructor matches the name of the conneciton string added earlier to App.config and web.config of the two projects.

Next, open the NuGet Package Manager Console. This is found under the Tools menu.

In the Default project: dropdown, choose the data project.

Type enable-migrations at the PM> prompt and hit Enter. If everything has been setup correctly, the package manager will state “Code First Migrations enabled for project [YourProjectName]”. There should also be a new folder created in the project named “Migrations”. The folder will have a Configuration class.

Next, type add-migration InitialSetup at the PM>, and hit Enter. This will create the migration file that constructs the Activities table.

Finally, type update-database at the PM> and hit enter. This command will run the InitialSetup migration against the database defined in the conneciton string, and create the Activities table.

Mapping Activities

Open the Bot project’s Global.asax.cs file, and add the following code to the Application_Start() method:

AutoMapper.Mapper.Initialize(cfg =>
{
    cfg.CreateMap<Microsoft.Bot.Connector.IMessageActivity, Data.Activity>()
        .ForMember(dest => dest.FromId, opt => opt.MapFrom(src => src.From.Id))
        .ForMember(dest => dest.RecipientId, opt => opt.MapFrom(src => src.Recipient.Id))
        .ForMember(dest => dest.FromName, opt => opt.MapFrom(src => src.From.Name))
        .ForMember(dest => dest.RecipientName, opt => opt.MapFrom(src => src.Recipient.Name));
});

This enables easily creating the Data.Activity objects from IMessageActivity objects within the EntityFrameworkActivityLogger

Entity Framework Activity Logger

Create a class in the Bot project called EntityFrameworkActivityLogger. It should inherit from the Microsoft.Bot.Builder.History.IActivityLogger interface.

public class EntityFrameworkActivityLogger : IActivityLogger
{        
    Task IActivityLogger.LogAsync(IActivity activity)
    {
        IMessageActivity msg = activity.AsMessageActivity();

        using (Data.ConversationDataContext dataContext = new Data.ConversationDataContext())
        {
            var newActivity = Mapper.Map<IMessageActivity, Data.Activity>(msg);
            if (string.IsNullOrEmpty(newActivity.Id))
                newActivity.Id = Guid.NewGuid().ToString();

            dataContext.Activities.Add(newActivity);
            dataContext.SaveChanges();
        }
        return null;
    }
}

Finally, go back to the Global.asax.cs file and add the following:

var builder = new ContainerBuilder();
builder.RegisterType<EntityFrameworkActivityLogger>().AsImplementedInterfaces().InstancePerDependency();
builder.Update(Conversation.Container);

Logs

You should now be able to run your project, and view all messages logged and stored in the database table created earlier. 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.

Happy Making!

Eric Dahlvang from the Bot Framework Team

Posted in Bot Framework