使用Twilio API发送短信后无法保存数据至数据库的问题排查

问题现象 #

• 调试器在SendSMS方法内的if (!string.IsNullOrEmpty(tableName))条件处停止，无法继续执行
• Windows服务运行程序时，短信相关数据可正常保存至ClientVisitReminder表；但网站运行时，短信发送成功，数据却无法存入数据库

相关代码 #

public void SendMessage(RoboCall roboCall, string tableName)
{
    _ = SendSMS(roboCall, tableName);
}

private static async Task SendSMS(RoboCall roboCall, string tableName = null)
{
    TwilioClient.Init(_accountSid, _authToken);
    var message = await MessageResource.CreateAsync(
        body: roboCall.Message,
        from: new Twilio.Types.PhoneNumber(_fromPhoneNumber),
        to: new Twilio.Types.PhoneNumber(roboCall.RecipientNumber));
    
    if (!string.IsNullOrEmpty(tableName))
    {
        System.Diagnostics.Debugger.Launch();
        switch (tableName)
        {
            case "ClientVisitReminder":
                using (TransactionWrapper tw = new TransactionWrapper())
                {
                    ClientVisitReminderDAO clientVisitReminderDAO = new ClientVisitReminderDAO();
                    ClientVisitReminder clientVisitReminder = new ClientVisitReminder();
                    clientVisitReminder.VisitId = roboCall.RefId;
                    clientVisitReminder.ClientId = roboCall.RecipientRefId;
                    clientVisitReminder.PhoneNumber = roboCall.RecipientNumber;
                    clientVisitReminder.DateSubmitted = DateTime.Now;
                    clientVisitReminder.CommunicationMethod = "SMS";
                    clientVisitReminder.OutboundNotificationId = message.Sid;
                    clientVisitReminder.SMSDeliveryStatus = message.Status.ToString();
                    clientVisitReminderDAO.Save(clientVisitReminder, tw);
                    tw.Commit();
                }
                break;

            case "ShiftRequestSMSNotification":
                using (TransactionWrapper tw = new TransactionWrapper())
                {
                    ShiftRequestSMSNotificationDAO shiftRequestSMSNotificationDAO = new ShiftRequestSMSNotificationDAO();
                    ShiftRequestSMSNotification shiftRequestSMSNotification = new ShiftRequestSMSNotification();
                    shiftRequestSMSNotification.ContactId = roboCall.RefId;
                    shiftRequestSMSNotification.NurseId = roboCall.RecipientRefId;
                    shiftRequestSMSNotification.PhoneNumber = roboCall.RecipientNumber;
                    shiftRequestSMSNotification.DateQueued = DateTime.Now;
                    shiftRequestSMSNotification.OutboundNotificationId = message.Sid;
                    shiftRequestSMSNotification.SMSDeliveryStatus = message.Status.ToString();
                    shiftRequestSMSNotificationDAO.Save(shiftRequestSMSNotification, tw);
                    tw.Commit();
                }
                break;

            default:
                break;
        }
    }
}

排查与解决方向 #

1. 修复异步调用未等待的问题
   当前SendMessage方法直接抛弃SendSMS的任务，网站环境下请求结束后线程可能被回收，导致后续数据库操作未完成。修改代码确保异步等待：

   public async Task SendMessage(RoboCall roboCall, string tableName)
   {
       await SendSMS(roboCall, tableName);
   }
   

2. 检查数据库权限差异
   Windows服务和网站的运行账号不同，确认网站应用池账号是否拥有目标数据库表的写入权限。

3. 排查事务包装器的环境适配问题
   TransactionWrapper可能在网站环境下无法正确处理事务，尝试暂时移除事务包装器直接调用DAO的Save方法，或者检查TransactionWrapper内部是否适配了ASP.NET的事务上下文。

4. 移除调试器启动代码
   System.Diagnostics.Debugger.Launch()在网站环境下可能无法正常触发调试，导致代码卡住。注释掉该行，改用日志记录跟踪执行流程，比如记录tableName值、数据库操作的异常信息。

5. 验证tableName参数的正确性
   检查网站调用SendMessage时传入的tableName是否为空、拼写错误（比如大小写不匹配），确保与switch分支的字符串完全一致。

内容的提问来源于stack exchange，提问作者Alexander Samuel