Mailkit读取邮件生成Alert时SQL参数异常但数据仍保存问题排查

项目背景 #

我正在做一个学校项目，通过Mailkit NuGet包读取邮箱邮件，并从中创建Alert对象。

Alert类定义 #

public Alert(string id_MimeMessage = " ", string nameOfAlert = null,DateTime date = default, string element = " ", int user_ID = 0, AlertValue value = default,AlertResult result = default, int clearTime = 0, string email = null)
{
    this.id = 0;
    this.Id_MimeMessage = id_MimeMessage;
    this.Date = date;
    this.Element = element;
    this.User_ID = user_ID;
    this.NameOfAlert = nameOfAlert;
    this.Value = value;
    this.Result = result;
    this.ClearTime = clearTime;
    this.Email = email;
}

问题现象 #

应用读取邮件时抛出异常：

System.Data.SqlClient.SqlException: 'The parameterized query '(@message_ID varchar(100),@date datetime,@element varchar(100),@' expects the parameter '@element', which was not supplied.'

但打印后发现Alert对象仍已保存到数据库，希望找到原因并实现无异常保存。

核心代码与配置 #

邮件读取与Alert保存逻辑 #

for (int i = 0; i < inbox.Count; i++)
{                            
    // 获取邮件内容
    var message = inbox.GetMessage(i, cancel.Token);
    GetBodyText = message.TextBody ?? message.HtmlBody ?? string.Empty;
    // 避免保存重复数据
    if (!dAOAlert.GetAll().Any(x => x.Id_MimeMessage.Equals(message.MessageId)))
    {
        foreach (var item in FindResult(GetBodyText, message))
        {
            // 保存新Alert
            dAOAlert.Save(item);
        }
    }
}

解析邮件生成Alert的FindResult方法 #

public List<Alert> FindResult(string text, MimeMessage message)
{
    return Regex
    .Split(text, @"\r?\n\s*\r?\n")
    .Select(s => new Alert(
        message.MessageId, 
        s.Contains("PING") ? "PING" : "Backup", 
        message.Date.DateTime, 
        s.Contains("PING") ? s.Split(' ')[0] : s.Split().Where(x => x.StartsWith("(") && x.EndsWith(")") && x.Any(char.IsUpper)).FirstOrDefault(), 
        3, 
        s.Contains("PING") ? AlertValue.Low : AlertValue.High, 
        s.Contains("Failed") ? AlertResult.FAIL : AlertResult.SUCCESS, 
        5, 
        "emailTest"))
    .ToList();
}

数据库TestAlert表结构 #

CREATE TABLE TestAlert(
id INT PRIMARY KEY IDENTITY(1,1),
id_MimeMessage VARCHAR(100) NOT NULL,
date_Receive DATETIME NOT NULL,
element VARCHAR(100) NOT NULL,
AMUser_ID INT FOREIGN KEY REFERENCES AMUser(id),
name_Of_Alert VARCHAR(100) NOT NULL,
alert_Value VARCHAR(50) NOT NULL,
alert_Result VARCHAR(50) NOT NULL,
clear_Time INT NOT NULL,
email VARCHAR(100)
);

Alert保存到数据库的方法 #

public void Save(Alert alert)
{
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();
        string query = "INSERT INTO [TestAlert] (id_MimeMessage, date_Receive, element, AMUser_ID,name_Of_Alert,alert_Value, alert_Result, clear_Time, email)" +
            "VALUES (@message_ID, @date, @element, @AMUser_ID, @name_Of_Alert,@alert_Value,@alert_Result, @clearTime,@email);";
       
        using (SqlCommand command = new SqlCommand(query, conn))
        {
            command.Parameters.Add("@message_ID", SqlDbType.VarChar, 100).Value = alert.Id_MimeMessage ;
            command.Parameters.Add("@date", SqlDbType.DateTime, 50).Value = alert.Date;
            command.Parameters.Add("@element", SqlDbType.VarChar, 100).Value = alert.Element;
            command.Parameters.Add("@AMUser_ID", SqlDbType.Int).Value = alert.User_ID;
            command.Parameters.Add("@name_Of_Alert", SqlDbType.VarChar,100).Value = alert.NameOfAlert;
            command.Parameters.Add("@alert_Value", SqlDbType.VarChar,100).Value = alert.Value;
            command.Parameters.Add("@alert_Result", SqlDbType.VarChar, 100).Value = alert.Result;
            command.Parameters.Add("@clearTime", SqlDbType.Int).Value = alert.ClearTime;
            command.Parameters.Add("@email", SqlDbType.VarChar,100).Value = alert.Email;
            var returnedValue = command.ExecuteScalar();
            alert.Id = Convert.ToInt32(returnedValue);
        }
        conn.Close();
    }
}

邮件示例 #

Today at 01:05: (NAME) Failed Backup - The operation encountered an error. (NAME) Failed when backing up: A file was not found (NAME) DISK_OPEN_ERROR

Today at 01:04: (NAME) Successful Backup - Backed 42,73 MB (compressed to 7,32 MB).(Duration: 1 minute)

Today at 00:59: (NAME) Successful Backup - Backed 3,41 GB (compressed to 379,17 MB).(Duration: 4 minutes)

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