Example for Saving Botframework Messages to Sql Server

In my previous article I showed how to override various methods within botframework such that you could run some logic to save your bot’s dialogues.

In this article I’ll give an example implementation of the IMessageRepository Interface.

IMessageRepository and IMessageActivity

In the last article I introduced an IMessageRepository concept in order to show how it would be possible to hook into botframework’s various MessageReceived methods, and create our own version of PostAsync to save outgoing messages also.

Saving both incoming and outgoing messages passed an IMessageActivity object (“context”) to a MessageRepositorys AddMessageAsync method.

Given a connection string, this implementation could persist the useful parts of a message:

public class SqlMessageRepository : IMessageRepository
    private readonly string _connectionString;

    public SqlMessageRepository(string connectionString)
        _connectionString = connectionString;

    public async Task AddMessageAsync(IMessageActivity message)
        using (var conn = new SqlConnection(_connectionString))
        using (var cmd = new SqlCommand())
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "CreateMessageEntry";
            cmd.Parameters.AddWithValue("conversationId", message.Conversation.Id);
            cmd.Parameters.AddWithValue("messageId", message.Id);
            cmd.Parameters.AddWithValue("timestamp", message.Timestamp);
            cmd.Parameters.AddWithValue("content", message.Text);
            cmd.Parameters.AddWithValue("sender", message.From.Name ?? message.From.Id);
            cmd.Parameters.AddWithValue("recipient", message.Recipient.Name ?? message.Recipient.Id);
            cmd.Parameters.AddWithValue("activityType", message.GetActivityType());
            cmd.Parameters.AddWithValue("channel", message.ChannelId);

            await cmd.ExecuteNonQueryAsync();

An alternative approach using entity framework can be found over on Michael Washington’s blog.

Hopefully you found that useful!

Leave a Reply

Your email address will not be published. Required fields are marked *