解决System.Data.SqlClient.SqlException截断错误及Alert外键保存问题

我的应用突然抛出如下异常：

System.Data.SqlClient.SqlException: 'String or binary data would be truncated in table 'SmartOne.dbo.Problem', column 'nameOfAlert'. Truncated value: 'Index was out of range. Must be non-negative and l'

我清楚该异常是由于存储的数据超出数据库列定义长度导致，但对此感到疑惑：数据及提取数据的邮件大小均未改变，且截至昨晚应用运行都正常。今天我给同事演示时突然出现此问题，且无人修改过应用代码。

抛出异常的Problem数据保存方法

public void Save(Problem element)
{
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();

        using (SqlCommand command = new SqlCommand("INSERT INTO Problem VALUES " +
            "(@nameOfAlert , @value , @result , @message_ID) ", conn))
        {

            command.Parameters.Add(new SqlParameter("@nameOfAlert", element.NameOfAlert));
            command.Parameters.Add(new SqlParameter("@value", (int)element.Value));
            command.Parameters.Add(new SqlParameter("@result", (int)element.Result));
            command.Parameters.Add(new SqlParameter("@message_ID", element.Message_Id));
            command.ExecuteNonQuery(); //Exception is throwed there
            command.CommandText = "Select @@Identity";
            element.Id = Convert.ToInt32(command.ExecuteScalar());
        }
        
        
        conn.Close();
    }
}

Save方法的调用位置

for (int i = 0; i < inbox.Count; i++)
{
    var message = inbox.GetMessage(i, cancel.Token);
    GetBodyText = message.TextBody;
    Problem problem = new Problem(message.MessageId);
    if (!dAOProblem.GetAll().Any(x => x.Message_Id.Equals(problem.Message_Id)))
    {
        dAOProblem.Save(problem);
        Alert alert = new Alert(message.MessageId, message.Date.DateTime, message.From.ToString(), 1, problem.Id);
        if (!dAOAlert.GetAll().Any(x => x.Id_MimeMessage.Equals(alert.Id_MimeMessage)))
        {
            dAOAlert.Save(alert);
            LoadAlertGrid();
        }
        else
        {
            
            MessageBox.Show("Duplicate");
        }

    }
}

Alert数据保存方法

public void Save(Alert element)
{
    using (SqlConnection conn = new SqlConnection(DatabaseSingleton.connString))
    {
        conn.Open();
        using (SqlCommand command = new SqlCommand("INSERT INTO [Alert] VALUES (@message_ID, @date, @email, @AMUser_ID, @Problem_ID) ", conn))
        {

            command.Parameters.Add(new SqlParameter("@message_ID", element.Id_MimeMessage));
            command.Parameters.Add(new SqlParameter("@date", element.Date));
            command.Parameters.Add(new SqlParameter("@email", element.Email));
            command.Parameters.Add(new SqlParameter("@AMUser_ID", element.User_ID));
            command.Parameters.Add(new SqlParameter("@Problem_ID", element.Problem_ID));

            command.ExecuteNonQuery();
            command.CommandText = "Select @@Identity";
            element.Id = Convert.ToInt32(command.ExecuteScalar());
        
        }
       
        conn.Close();
    }
}

更新：现在保存Alert时也出现问题，尤其是关联Problem的外键部分...

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