时间戳

首页 > 解决方案 > 将机器人对话存储到 Azure sql 数据库。机器人响应未记录

c# - 将机器人对话存储到 Azure sql 数据库。机器人响应未记录

问题描述

我使用 Botframework v3 创建了一个机器人，它将对话记录到一个 azure sql 数据库中。它使用机器人模拟器工作。bot 记录的问题和用户响应记录在 azure sql db 中。

但是，当我使用机器人注册通道将机器人从 Visual Studio 部署到 azure 时。（我已经多次部署了其他不同类型的机器人，它通常可以工作）当我在（测试网络聊天）天蓝色门户中使用机器人时，它失败了——收到消息——“对不起，我的机器人有问题”和用户的消息类型发送失败。

发送的消息/用户键入的内容会记录在 sql 数据库中。但是，机器人提出的问题不会记录在 sql 数据库中。

SqlActivityogger 类：

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;

        //when creating the sql database make sure you create a table userChatLog table in the db in portal.azure.com
        string insertQuery = "INSERT INTO userChatLog(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);



        // Insert to Azure sql database
        command.ExecuteNonQuery();

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

全球.asax

 public class WebApiApplication : System.Web.HttpApplication
{
    SqlConnection connection = null;
    protected void Application_Start()
    {
        //setting up sql string connection string

        SqlConnectionStringBuilder sqlbuilder = new SqlConnectionStringBuilder();
        sqlbuilder.DataSource = "##########";
        sqlbuilder.UserID = "#####";
        sqlbuilder.Password = "#####";
        sqlbuilder.InitialCatalog = "######";

        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");
    }

}

三明治班

public enum SandwichOptions
{
    BLT, BlackForestHam, BuffaloChicken, ChickenAndBaconRanchMelt, ColdCutCombo, MeatballMarinara,
    OvenRoastedChicken, RoastBeef, RotisserieStyleChicken, SpicyItalian, SteakAndCheese, SweetOnionTeriyaki, Tuna,
    TurkeyBreast, Veggie
};
public enum LengthOptions { SixInch, FootLong };
public enum BreadOptions { NineGrainWheat, NineGrainHoneyOat, Italian, ItalianHerbsAndCheese, Flatbread };
public enum CheeseOptions { American, MontereyCheddar, Pepperjack };
public enum ToppingOptions
{
    Avocado, BananaPeppers, Cucumbers, GreenBellPeppers, Jalapenos,
    Lettuce, Olives, Pickles, RedOnion, Spinach, Tomatoes
};
public enum SauceOptions
{
    ChipotleSouthwest, HoneyMustard, LightMayonnaise, RegularMayonnaise,
    Mustard, Oil, Pepper, Ranch, SweetOnion, Vinegar
};

[Serializable]
public class SandwichOrder
{
    public SandwichOptions? Sandwich;
    public LengthOptions? Length;
    public BreadOptions? Bread;
    public CheeseOptions? Cheese;
    public List<ToppingOptions> Toppings;
    public List<SauceOptions> Sauce;

    public static IForm<SandwichOrder> BuildForm()
    {
        OnCompletionAsyncDelegate<SandwichOrder> processOrder = async (context, state) =>
        {
            await context.PostAsync("This is the end of the form, you would give a final confirmation, and then start the ordering process as needed.");
        };

        return new FormBuilder<SandwichOrder>()
                .Message("Welcome to the simple sandwich order bot!")
                .OnCompletion(processOrder)
                .Build();
    }
};

感谢帮助。我一直试图解决这个问题很长时间了，但不明白为什么这不起作用。

更新：使用 ngrok 调试后，出现以下错误：

{
  "message": "An error has occurred.",
  "exceptionMessage": "String or binary data would be truncated.
The statement has been terminated.",
  "exceptionType": "System.Data.SqlClient.SqlException",
  "stackTrace": "   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at FormBot.Models.SqlActivityLogger.<LogAsync>d__2.MoveNext() in C:\\FormSqlTest1-src\\Models\\SqlActivityLogger.cs:line 42
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Bot.Builder.Dialogs.Internals.LogPostToBot.<Microsoft-Bot-Builder-Dialogs-Internals-IPostToBot-PostAsync>d__3.MoveNext() in D:\\a\\1\\s\\CSharp\\Library\\Microsoft.Bot.Builder\\ConnectorEx\\IActivityLogger.cs:line 108
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Bot.Builder.Dialogs.Conversation.<SendAsync>d__11.MoveNext() in D:\\a\\1\\s\\CSharp\\Library\\Microsoft.Bot.Builder.Autofac\\Dialogs\\Conversation.cs:line 182
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Bot.Builder.Dialogs.Conversation.<SendAsync>d__6.MoveNext() in D:\\a\\1\\s\\CSharp\\Library\\Microsoft.Bot.Builder.Autofac\\Dialogs\\Conversation.cs:line 108
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
   at Microsoft.Bot.Sample.FormBot.MessagesController.<Post>d__1.MoveNext() in C:\\FormSqlTest1-src\\Controllers\\MessagesController.cs:line 35
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"
}

错误：“字符串或二进制数据将被截断。”

但我只是输入例如单个你好

任何建议......非常感谢这里的任何帮助。谢谢

标签： c#sqlazurebotframeworkbots

解决方案

我之前实际上遇到过 SQL 截断错误，尽管在使用 Bot Framework 时没有。CHAR记下和列的最大长度非常重要，VARCHAR这样您就可以确保写入这些列的所有内容都在限制范围内。如有必要，您应该在将字符串发送到数据库之前自行截断这些字符串。

确定列的最大长度后，请在 LogAsync 方法中设置断点，以便在记录每个活动时查看其详细信息。您说您只是在输入诸如“hello”之类的短消息，但请记住，不是您的消息，而是机器人无法记录的消息。由于尚不清楚您的机器人在模拟器中运行时发送的消息与在不同通道中发送的消息之间的操作差异是什么，因此请注意fromId以及toId，特别是如果您的表中的这些列具有不同的长度。不同的频道会生成不同的 ID，而在 Emulator 中，机器人的 ID 通常只有一位数。

TL;DR：找出表中fromIdandtoId和message列的最大长度userChatLog，然后调试代码并查看值在中断时有多长。

推荐阅读