SqlBulkCopy с ObjectReader - не удалось преобразовать значение параметра из строки в Int32

Question

Я использую SqlBulkCopy (.NET) с ObjectReader (FastMember) для выполнения импорта из файла на основе XML. Я добавил правильные сопоставления столбцов.

В некоторых случаях я получаю сообщение об ошибке: Не удалось преобразовать значение параметра из строки в Int32.

Я хотел бы понять, как 1. Отследить фактический столбец таблицы, который не удалось 2. Получить "текущий" на ObjectReader

образец кода:

     using (ObjectReader reader = genericReader.GetReader())
                {
                    try
                    {
                        sbc.WriteToServer(reader); //sbc is SqlBulkCopy instance
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();                           
                    }
                }

Несет ли "ex" больше информации, чем просто ошибка:
System.InvalidOperationException : The given value of type String from the data source cannot be converted to type int of the specified target column.

Answer 1

Простой ответ

Простой ответ - нет. Одна из причин .NET's SqlBulkCopy так быстро, что он не регистрирует ничего, что делает. Вы не можете напрямую получить дополнительную информацию от .NET's SqlBulkCopy исключение. Тем не менее, Дэвид Катриэль написал статью об этом и предоставил возможное решение, о котором вы можете прочитать здесь полностью.

Несмотря на то, что этот метод может дать ответ, который вы ищете, я предлагаю использовать только вспомогательный метод при отладке, поскольку вполне возможно, что он может оказать некоторое влияние на производительность, если будет выполняться последовательно в вашем коде.

Зачем использовать работу вокруг

Отсутствие регистрации определенно ускоряет работу, но когда вы качаете сотни тысяч строк и внезапно возникает сбой в одном из них из-за ограничений, вы застреваете. Все SqlException скажет вам, что что-то пошло не так с данным ограничением (вы получите имя ограничения по крайней мере), но это все. Затем вы застряли на необходимости вернуться к исходному тексту, выполнить для него отдельные операторы SELECT (или выполнить ручной поиск) и найти строки виновника самостоятельно.

Кроме того, это может быть очень длительный и итеративный процесс, если у вас есть данные с несколькими потенциальными сбоями в нем, потому что SqlBulkCopy остановится, как только будет обнаружен первый сбой. Как только вы исправите это, вам нужно перезапустить загрузку, чтобы найти вторую ошибку и т. Д.

преимущества:

1. Сообщает обо всех возможных ошибках, с которыми может столкнуться SqlBulkCopy

2. Сообщает обо всех строках данных виновника, за исключением того, что эта строка будет вызывать

3. Все это выполняется в транзакции, откат которой выполняется в конце, поэтому изменения не фиксируются.

недостатки:

1. Для очень больших объемов данных это может занять пару минут.

2. Это решение является реактивным; т.е. ошибки не возвращаются как часть исключения, созданного вашим процессом SqlBulkCopy.WriteToServer(). Вместо этого этот вспомогательный метод выполняется после возникновения исключения, чтобы попытаться перехватить все возможные ошибки вместе с соответствующими данными. Это означает, что в случае исключения ваш процесс займет больше времени, чем просто массовое копирование.

3. Вы не можете повторно использовать один и тот же объект DataReader из отказавшей SqlBulkCopy, поскольку считыватели представляют собой только пожарные рукава, которые не могут быть сброшены. Вам нужно будет создать нового считывателя того же типа (например, переиздать исходный SqlCommand, воссоздать считыватель на основе того же DataTable и т. Д.).

Использование метода GetBulkCopyFailedData

private void TestMethod()
{
   // new code
   SqlConnection connection = null;
   SqlBulkCopy bulkCopy = null;

   DataTable dataTable = new DataTable();
   // load some sample data into the DataTable
   IDataReader reader = dataTable.CreateDataReader();

   try 
   {
      connection = new SqlConnection("connection string goes here ...");
      connection.Open();
      bulkCopy = new SqlBulkCopy(connection); 
      bulkCopy.DestinationTableName = "Destination table name";
      bulkCopy.WriteToServer(reader);
   }
   catch (Exception exception)
   {
      // loop through all inner exceptions to see if any relate to a constraint failure
      bool dataExceptionFound = false;
      Exception tmpException = exception;
      while (tmpException != null)
      {
         if (tmpException is SqlException
            && tmpException.Message.Contains("constraint"))
         {
            dataExceptionFound = true;
            break;
         }
         tmpException = tmpException.InnerException;
      }

      if (dataExceptionFound)
      {
         // call the helper method to document the errors and invalid data
         string errorMessage = GetBulkCopyFailedData(
            connection.ConnectionString,
            bulkCopy.DestinationTableName,
            dataTable.CreateDataReader());
         throw new Exception(errorMessage, exception);
      }
   }
   finally
   {
      if (connection != null && connection.State == ConnectionState.Open)
      {
         connection.Close();
      }
   }
}

Затем GetBulkCopyFailedData() открывает новое соединение с базой данных, создает транзакцию и начинает массовое копирование данных по одной строке за раз. Это делается путем чтения предоставленного DataReader и копирования каждой строки в пустой DataTable. Затем DataTable массово копируется в базу данных назначения, и любые возникающие в результате исключения перехватываются, документируются (вместе с вызвавшим его DataRow), а затем цикл повторяется со следующей строкой. В конце DataReader мы откатываем транзакцию и возвращаем полное сообщение об ошибке. Исправление проблем в источнике данных теперь должно быть проще простого.

Метод GetBulkCopyFailedData

/// <summary>
/// Build an error message with the failed records and their related exceptions.
/// </summary>
/// <param name="connectionString">Connection string to the destination database</param>
/// <param name="tableName">Table name into which the data will be bulk copied.</param>
/// <param name="dataReader">DataReader to bulk copy</param>
/// <returns>Error message with failed constraints and invalid data rows.</returns>
public static string GetBulkCopyFailedData(
   string connectionString,
   string tableName,
   IDataReader dataReader)
{
   StringBuilder errorMessage = new StringBuilder("Bulk copy failures:" + Environment.NewLine);
   SqlConnection connection = null;
   SqlTransaction transaction = null;
   SqlBulkCopy bulkCopy = null;
   DataTable tmpDataTable = new DataTable();

   try
   { 
      connection = new SqlConnection(connectionString); 
      connection.Open();
      transaction = connection.BeginTransaction();
      bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.CheckConstraints, transaction);
      bulkCopy.DestinationTableName = tableName;

      // create a datatable with the layout of the data.
      DataTable dataSchema = dataReader.GetSchemaTable();
      foreach (DataRow row in dataSchema.Rows)
      {
         tmpDataTable.Columns.Add(new DataColumn(
            row["ColumnName"].ToString(), 
            (Type)row["DataType"]));
      }

      // create an object array to hold the data being transferred into tmpDataTable 
      //in the loop below.
      object[] values = new object[dataReader.FieldCount];

      // loop through the source data
      while (dataReader.Read())
      {
         // clear the temp DataTable from which the single-record bulk copy will be done
         tmpDataTable.Rows.Clear();

         // get the data for the current source row
         dataReader.GetValues(values);

         // load the values into the temp DataTable
         tmpDataTable.LoadDataRow(values, true);

         // perform the bulk copy of the one row
         try
         {
            bulkCopy.WriteToServer(tmpDataTable);
         }
         catch (Exception ex)
         {
            // an exception was raised with the bulk copy of the current row. 
            // The row that caused the current exception is the only one in the temp 
            // DataTable, so document it and add it to the error message.
            DataRow faultyDataRow = tmpDataTable.Rows[0];
            errorMessage.AppendFormat("Error: {0}{1}", ex.Message, Environment.NewLine);
            errorMessage.AppendFormat("Row data: {0}", Environment.NewLine);
            foreach (DataColumn column in tmpDataTable.Columns)
            {
               errorMessage.AppendFormat(
                  "\tColumn {0} - [{1}]{2}", 
                  column.ColumnName, 
                  faultyDataRow[column.ColumnName].ToString(), 
                  Environment.NewLine);
            }
         }
      }
   }
   catch (Exception ex) 
   { 
      throw new Exception(
         "Unable to document SqlBulkCopy errors. See inner exceptions for details.", 
         ex); 
   }
   finally
   {
      if (transaction != null)
      {
         transaction.Rollback();
      }
      if (connection.State != ConnectionState.Closed)
      {
         connection.Close();
      }
   }
   return errorMessage.ToString();