CODEWITHSUNDEEP
c#loggingbotframework
Mounika
Mounika

Reputation: 35

how to log conversation messages into azure sql database in microsoft botframework sdk v4 using c#

I created a bot with the Microsoft Boframework C# V4 SDK, and it is working well. Now, I want to store the conversation messages of the bot and users in an Azure SQL database. How can I connect and log these conversation messages in Azure SQL database.

I already tried this with SDK V3. In SDK V3 I created a SqlActivityLogger class and called it from Global.asax file and opened a Sql Connection there. And it is successfully logging conversation messages in to an Azure SQL database. Now How can I do the same thing in SDK V4 using C#.

SqlActivityLogger.cs

using Microsoft.Bot.Builder.History;
using Microsoft.Bot.Connector;
using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;

namespace Robo
{
public class SqlActivityLogger : IActivityLogger
{
    SqlConnection connection;


    public SqlActivityLogger(SqlConnection conn)
    {
        this.connection = conn;
    }
    public async Task LogAsync(IActivity activity)
    {
        string fromId = activity.From.Id;
        string toId = activity.Recipient.Id;
        string message = activity.AsMessageActivity().Text;
       // DateTime DateTimeNow = DateTime.Now;



        string insertQuery = "INSERT INTO RobosensusLog(fromId, toId, message) VALUES (@fromId,@toId,@message)";

        // Passing the fromId, toId, message to the the user chatlog table 
        SqlCommand command = new SqlCommand(insertQuery, connection);
        command.Parameters.AddWithValue("@fromId", fromId);
        command.Parameters.AddWithValue("@toId", toId);
        command.Parameters.AddWithValue("@message", message);
       // command.Parameters.AddWithValue("@datetime", DateTime.Now);

        // Insert to Azure sql database
        command.ExecuteNonQuery();
        Debug.WriteLine("Insertion successful of message: " + activity.AsMessageActivity().Text);
    }
}
}

Global.asax

using Autofac;
using Microsoft.Bot.Builder.Dialogs;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Routing;

namespace Robo
{
public class WebApiApplication : System.Web.HttpApplication
{
    SqlConnection connection = null;
    protected void Application_Start()
    {
        //Setting up sql string connection
       SqlConnectionStringBuilder sqlbuilder = new SqlConnectionStringBuilder();
        sqlbuilder.DataSource = "Your data source";
        sqlbuilder.UserID = "userid";
        sqlbuilder.Password = "password";
        sqlbuilder.InitialCatalog = "your catalog";

        connection = new SqlConnection(sqlbuilder.ConnectionString);
        connection.Open();
        Debug.WriteLine("Connection Success");

       Conversation.UpdateContainer(builder =>
        {
            builder.RegisterType<SqlActivityLogger>().AsImplementedInterfaces().InstancePerDependency().WithParameter("conn", connection);
        });

        GlobalConfiguration.Configure(WebApiConfig.Register);
    }

   protected void Application_End()
    {
        connection.Close();
        Debug.WriteLine("Connection to database closed");

    }
}
}

Upvotes: 1

Views: 372

Answers (1)

Eric Dahlvang
Eric Dahlvang

Reputation: 8292

In Bot Builder V4, ITranscriptLogger has replaced IActivityLogger.

You can find an Entity Framework implementation here: https://github.com/BotBuilderCommunity/botbuilder-community-dotnet/pull/78 (This was just committed, so it has not been merged yet).

You can create an ITranscriptLogger implementation that just uses System.Data.SqlClient, and persist any fields you want from the Activity in LogActivityAsync

Once you have an ITranscriptLogger implementation, it can be added to the Mideleware stack in Startup.cs with:

var myLogger = new MyTranscriptLogger(Configuration.GetSection("BotDataConnectionString").Value);
var transcriptMiddleware = new TranscriptLoggerMiddleware(myLogger);
options.Middleware.Add(transcriptMiddleware);

Upvotes: 0

Related Questions