Transações e operações de cópia em massa

Artigo
05/23/2023

Operações de cópia em massa podem ser executadas como operações isoladas ou como parte de uma transação de várias etapas. Essa última opção permite executar mais de uma operação de cópia em massa dentro da mesma transação, bem como executar outras operações de banco de dados, como inserções, atualizações e exclusões, podendo ainda confirmar ou reverter toda a transação.

Por padrão, uma operação de cópia em massa é realizada como uma operação isolada. A operação de cópia em massa ocorre de forma não transacionada, sem a oportunidade de revertê-la novamente. Se você precisar reverter toda a cópia em massa ou parte dela quando um erro ocorrer, poderá:

Usar uma transação gerenciada por SqlBulkCopy
Realizar a operação de cópia em massa dentro de uma transação existente
Inscrever-se em um System.TransactionsTransaction.

Executando uma operação de cópia em massa não transacionada

O aplicativo de Console a seguir mostra o que acontece quando uma operação de cópia em massa não transacionada encontra um erro na operação.

No exemplo, cada tabela de origem e cada tabela de destino incluem uma coluna Identity denominada ProductID. O código prepara primeiro a tabela de destino excluindo todas as linhas e, em seguida, inserindo uma única linha cuja ProductID é conhecida por existir na tabela de origem. Por padrão, um novo valor para a coluna Identity é gerado na tabela de destino para cada linha adicionada. Neste exemplo, uma opção é definida quando a conexão é aberta, o que força o processo de carregamento em massa a usar os valores de Identity da tabela de origem.

A operação de cópia em massa é executada com a propriedade BatchSize definida como 10. Quando a operação encontra a linha inválida, uma exceção é gerada. Neste primeiro exemplo, a operação de cópia em massa é não transacionada. Todos os lotes copiados até o ponto do erro são confirmados. O lote que contém a chave duplicada é revertido e a operação de cópia em massa é interrompida antes de processar os lotes restantes.

Observação

Essa amostra não será executada, a menos que você tenha criado as tabelas de trabalho conforme descrito em Configuração de exemplo de cópia em massa. Esse código é fornecido para demonstrar a sintaxe para usar somente SqlBulkCopy. Se as tabelas de origem e destino estiverem localizadas na mesma instância do SQL Server, será mais fácil e mais rápido usar uma instrução INSERT … SELECT do Transact-SQL para copiar os dados.

using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            //  Delete all from the destination table.         
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key         
            //  when all rows from source are bulk copied.         
            //  Note that this technique will only be successful in          
            //  illustrating the point if a row with ProductID = 446           
            //  exists in the AdventureWorks Production.Products table.          
            //  If you have made changes to the data in this table, change         
            //  the SQL statement in the code to add a ProductID that         
            //  does exist in your version of the Production.Products         
            //  table. Choose any ProductID in the middle of the table         
            //  (not first or last row) to best illustrate the result.         
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            //  Get data from the source table as a SqlDataReader.         
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader = commandSourceData.ExecuteReader();

            // Set up the bulk copy object using the KeepIdentity option. 
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.BatchSize = 10;
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                // Write from the source to the destination.
                // This should fail with a duplicate key error
                // after some of the batches have been copied.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    // Print the number of rows processed using the 
                    // RowsCopied property.
                    Console.WriteLine("{0} rows were processed.",
                        bulkCopy.RowsCopied);
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            // Note that for this scenario, the value will 
            // not be equal to the RowsCopied property.
            long countEnd = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Ending row count = {0}", countEnd);
            Console.WriteLine("{0} rows were added.", countEnd - countStart);
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }
    }

    private static string GetConnectionString()
    // To avoid storing the sourceConnection string in your code, 
    // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

Como executar uma operação de cópia em massa dedicada em uma transação

Por padrão, uma operação de cópia em massa é sua própria transação. Quando quiser executar uma operação de cópia em massa dedicada, crie uma instância do SqlBulkCopy com uma cadeia de conexão ou use um objeto SqlConnection existente sem uma transação ativa. A operação de cópia em massa cria uma transação em cada cenário e a confirma ou a reverte.

Você pode especificar explicitamente a opção UseInternalTransaction no construtor da classe SqlBulkCopy para executar uma operação de cópia em massa em sua transação. Cada lote da operação será executado em uma transação separada.

Observação

Como lotes diferentes são executados em diferentes transações, se ocorrer um erro durante a operação de cópia em massa, todas as linhas no lote atual serão revertidas, mas as linhas de lotes anteriores permanecerão no banco de dados.

O aplicativo de console a seguir é semelhante ao exemplo anterior, com uma exceção: neste exemplo, a operação de cópia em massa gerencia suas próprias transações. Todos os lotes copiados até o ponto do erro são confirmados. O lote que contém a chave duplicada é revertido e a operação de cópia em massa é interrompida antes de processar os lotes restantes.

Importante

using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
        using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            //  Delete all from the destination table.         
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key         
            //  when all rows from source are bulk copied.         
            //  Note that this technique will only be successful in          
            //  illustrating the point if a row with ProductID = 446           
            //  exists in the AdventureWorks Production.Products table.          
            //  If you have made changes to the data in this table, change         
            //  the SQL statement in the code to add a ProductID that         
            //  does exist in your version of the Production.Products         
            //  table. Choose any ProductID in the middle of the table         
            //  (not first or last row) to best illustrate the result.         
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount = new SqlCommand(
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            //  Get data from the source table as a SqlDataReader.         
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader = commandSourceData.ExecuteReader();

            // Set up the bulk copy object.
            // Note that when specifying the UseInternalTransaction
            // option, you cannot also specify an external transaction.
            // Therefore, you must use the SqlBulkCopy construct that
            // requires a string for the connection, rather than an
            // existing SqlConnection object. 
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity |
                       SqlBulkCopyOptions.UseInternalTransaction))
            {
                bulkCopy.BatchSize = 10;
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                // Write from the source to the destination.
                // This should fail with a duplicate key error
                // after some of the batches have been copied.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    // Print the number of rows processed using the 
                    // RowsCopied property.
                    Console.WriteLine("{0} rows were processed.",
                        bulkCopy.RowsCopied);
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    reader.Close();
                }
            }

            // Perform a final count on the destination 
            // table to see how many rows were added.
            // Note that for this scenario, the value will 
            // not be equal to the RowsCopied property.
            long countEnd = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Ending row count = {0}", countEnd);
            Console.WriteLine("{0} rows were added.", countEnd - countStart);
            Console.WriteLine("Press Enter to finish.");
            Console.ReadLine();
        }
    }

    private static string GetConnectionString()
    // To avoid storing the sourceConnection string in your code, 
    // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

Usando transações existentes

É possível especificar um objeto SqlTransaction existente como um parâmetro em um construtor SqlBulkCopy. Nessa situação, a operação de cópia em massa é realizada na transação existente e nenhuma alteração é feita no estado da transação – ela não é confirmada nem anulada. Esse método permite que um aplicativo inclua a operação de cópia em massa em uma transação com outras operações de banco de dados. No entanto, uma exceção será lançada caso você não especifique um objeto SqlTransaction, passe uma referência nula e caso a conexão tenha uma transação ativa.

Caso precise reverter toda a operação de cópia em massa porque ocorreu um erro. Ou caso a cópia em massa deva ser executada como parte de um processo maior que pode ser revertido, será possível fornecer um objeto SqlTransaction para o construtor SqlBulkCopy.

O aplicativo de console a seguir é semelhante para o primeiro exemplo (não transacionado), com uma exceção: neste exemplo, a operação de cópia em massa está incluída em uma transação maior, externa. Quando ocorre o erro de violação de chave primária, a transação inteira é revertida e nenhuma linha é adicionada à tabela de destino.