c# - 数据库更新异常 : Which field is causing "String or binary data would be truncated"

Question

我收到一个带有消息的 DbUpdateException

String or binary data would be truncated

我知道实体中的某个字段不适合数据库中列的长度。而且我可以下去手动检查它们。

然而，我想要做的是获得一条合理的错误消息，它可能会告诉我它实际上是哪个字段!例如。

String or binary would be truncated at field ProspectName.

我能够打印出很多随机信息。并尝试了各种东西。但没有任何内容指向字段名称。

请注意，这不是 DbEntityValidationException 类型的NOT，它是 DbUpdateException

// DbUpdateException exception
foreach (var entry in exception.Entries)
{ 
    builder.AppendLine(String.Format("Error at: Type {0}", entry.Entity.GetType().Name));

    if ((exception.InnerException is System.Data.Entity.Core.UpdateException) &&
        (exception.InnerException.InnerException is System.Data.SqlClient.SqlException))
    {
        var updateException = (System.Data.Entity.Core.UpdateException)exception.InnerException;

        var sqlException = (System.Data.SqlClient.SqlException)exception.InnerException.InnerException;
        var result = new List<ValidationResult>();

        for (int i = 0; i < sqlException.Errors.Count; i++)
        {
            builder.AppendLine(String.Format("Error code: {0} ", sqlException.Errors[i].Number));
            builder.AppendLine(String.Format("Source: {0} ", sqlException.Errors[i].Source));
            builder.AppendLine(String.Format("Message: {0} ", sqlException.Errors[i].Message));
            builder.AppendLine(String.Format("State: {0} ", sqlException.Errors[i].State));
            builder.AppendLine(String.Format("Procedure: {0} ", sqlException.Errors[i].Procedure));
        } 
    }
}

完整的错误:

String or binary data would be truncated. The statement has been terminated.

Error at: Type tree_1ECACDBB4458C7A9DEC7CD183FD8B8C3473502FEFFACF160E17AD47718DCE5EA
Error code: 8152
Source: .Net SqlClient Data Provider
Message: String or binary data would be truncated.
State: 14
Procedure:
Error code: 3621
Source: .Net SqlClient Data Provider
Message: The statement has been terminated.
State: 0
Procedure:

Answer 1

一个“丑陋”的解决方案(但功能正常且仅使用 C# 代码)可以准确找到导致错误的属性:

如果您正在进行更新，请执行以下操作:

 var myDBObj = db.Mytables.Where(x=>x.Id == myId).FirstOrDefaul();
 if(myDBObj == null) return false; // or something else with the error msg

 myDBObj.Property1 = myObjToSave.Property1;
 db.SaveChanges();

 myDBObj.Property2 = myObjToSave.Property2;
 db.SaveChanges();

 myDBObj.Property3 = myObjToSave.Property3;
 db.SaveChanges();
 .... // EACH PROPERTY....
 myDBObj.PropertyX = myObjToSave.PropertyX;
 db.SaveChanges();

现在，使用制动点或增量变量来跟踪“位置”等...您将确切知道在这种特定情况下出现异常的情况...

注意:这是一个丑陋的解决方案，只是为了找出它失败的地方……永远不要在生产中使用它……当然，IMO 还有其他更友好的东西，比如有一个 sql profiler 并查看生成的 SQL 然后尝试在 sql management studio 上运行它然后在那里看到错误......

更新 #1

类似这样的东西(注意:我没有编译它):P

Type type = obj.GetType();
PropertyInfo[] properties = type.GetProperties();  
string lastPropertyWithError = ""; // You can replace this with a list or so  
foreach (PropertyInfo property in properties)
{
   try{
    property.SetValue(myDBObj, property.GetValue(myObj, null));
    db.SaveChanges();
   }catch()
   {
     lastPropertyWithError  = property.Name;
   }
}