EF Core之SqlException处理

EF Core之SqlException处理

外键，唯一约束等对后端检查起到了很多的帮助，但有的时候需要后端直接将报错信息反馈给前端。这个时候直接返回SqlException就不合适了，需要对其进行翻译，返回可读的信息

• EF Core的SqlException的全局处理还是要在DBContext里进行

  • SaveChanges就是我们要处理的地方，EF Core对应的Exception是DbUpdateException：

    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        try
        {
            return base.SaveChanges(acceptAllChangesOnSuccess);
        }
        catch (DbUpdateException ex)
        {
            HandleDbUpdateException(ex);
            return 0;
        }
    }
    
    /// <summary>
    /// 统一处理数据库报的异常
    /// </summary>
    /// <param name="ex">DbUpdateException</param>
    public void HandleDbUpdateException(DbUpdateException ex)
    {
        if (ex.InnerException is SqlException sqlException)
        {
            switch (sqlException.Number)
            {
                case 547:
                    //外键报错
                    var fkError = ForeignKeyErrorFormatter(sqlException);
                    break;
                case 2601:
                case 2627:
                    //唯一约束报错
                    var uiError = UniqueErrorFormatter(sqlException);
                    break;
                case 50000:
                    //自定义报错
                    var customError = CustomErrorFormatter(sqlException);
                    break;
            }
        }
    }
    

  • 对于SqlException我这里划分为三类：外键、唯一约束和自定义，能统一处理的关键就在于规范

    • 唯一约束：

      //报错信息样例：Cannot insert duplicate key row in object 'dbo.TableName' with unique index 'IX_TableName_FieldName'. The duplicate key value is (XXX).
      private readonly Regex UniqueConstraintRegex_Table = new Regex(@"'dbo.(\w*)'", RegexOptions.Compiled);
      public string UniqueErrorFormatter(SqlException ex)
      {
          var message = ex.Errors[0]?.Message;
          //利用正则表达式匹配到表名
          var tableMatch = UniqueConstraintRegex_Table.Match(message);
          if (!tableMatch.Success)
          {
              return null;
          }
          var tableName = tableMatch?.Groups[1]?.Value;
          //默认情况下，EF Core生成的唯一索引的命名是有规律的(如果是自定义的，也必须有规律)，这样就用样可以通过正则表达式匹配到外键对应的字段
          Regex UniqueConstraintRegex = new Regex("'IX_" + tableName + @"_(\w*)'", RegexOptions.Compiled);
          var columnMatch = UniqueConstraintRegex.Match(message);
          if (!columnMatch.Success)
          {
              UniqueConstraintRegex = new Regex("'AK_" + tableName + @"_(\w*)'", RegexOptions.Compiled);
              columnMatch = UniqueConstraintRegex.Match(message);
              if (!columnMatch.Success)
              {
                  return null;
              }
          }
          var columnName = columnMatch?.Groups[1]?.Value;
          //外键报错末尾是“The duplicate key value is (XXX).”，可以再次通过正则表达式匹配到重复数据
          var dupPart = "";
          var openingBadValue = message.IndexOf("(", StringComparison.Ordinal);
          if (openingBadValue > 0)
          {
              dupPart = message.Substring(openingBadValue + 1, message.Length - openingBadValue - 3);
          }
          //这样就解析出了报错信息，具体怎么组合就看需求了
          return uniqueError;
      }
      

    • 外键：

      private readonly Regex ForeignKeyRegex_Table = new Regex(@"table ""dbo.(\w*)""", RegexOptions.Compiled);
      private readonly Regex ForeignKeyRegex_Column = new Regex(@"column '(\w*)'", RegexOptions.Compiled);
      //报错样例：The INSERT statement conflicted with the FOREIGN KEY constraint "FK_XXX". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.
      private readonly string Operation_Insert = "INSERT";
      private readonly string Operation_Merge = "MERGE";
      private readonly string Operation_Update = "UPDATE";
      private readonly string Operation_Delete = "DELETE";
      public string ForeignKeyErrorFormatter(SqlException ex)
      {
          var message = ex.Errors[0]?.Message;
          //利用规则匹配到操作：The {Operation} statement
          var operationName = message?.Substring(4, 6)?.Trim();
          //利用正则表达式匹配到表名
          var tableMatch = ForeignKeyRegex_Table.Match(message);
          if (!tableMatch.Success)
          {
              return null;
          }
          var tableName = tableMatch?.Groups[1]?.Value;
          //增改涉及到的都是表自身，到这一步就结束了
          if (operationName == Operation_Merge || operationName == Operation_Insert)
          {
              return fk_addError;
          }
          if (operationName == Operation_Update)
          {
              return fk_updateError;
          }
          //删除操作，涉及到的还有影响到的表
          var columnMatch = ForeignKeyRegex_Column.Match(message);
          if (!columnMatch.Success)
          {
              return null;
          }
          var columnName = columnMatch?.Groups[1]?.Value;
      
          Regex ForeignKeyRegex = new Regex($@"""FK_{tableName}_(\w*)_{columnName}""", RegexOptions.Compiled);
          var targetTableMatch = ForeignKeyRegex.Match(message);
          if (!targetTableMatch.Success)
          {
              return null;
          }
          var targetTableName = targetTableMatch?.Groups[1]?.Value;
          
          return deleteError;
      }
      

    • 自定义，比如说自定义的触发器错误：

      /// <summary>
      /// 自定义报错
      /// </summary>
      /// <returns>格式化的自定义报错信息</returns>
      /// <param name="ex">SqlException</param>
      public string CustomErrorFormatter(SqlException ex)
      {
          var message = ex.Errors[0]?.Message;
          //这里根据自己定义的错误格式分别处理，比如说：Error:{ErrorMessage}
          var customErrorCode = message?.Split(":")?.First();
          return customError;
      }
      

• 这样的话，就可以全局处理EF Core的报错信息了