Nota
O acesso a esta página requer autorização. Podes tentar iniciar sessão ou mudar de diretório.
O acesso a esta página requer autorização. Podes tentar mudar de diretório.
As 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 que você execute mais de uma operação de cópia em massa dentro da mesma transação, bem como execute outras operações de banco de dados, como inserções, atualizações e exclusões, enquanto ainda pode confirmar ou reverter toda a transação.
Por padrão, uma operação de cópia em massa é feita como uma operação isolada. A operação de cópia em massa ocorre de forma não transacionada, sem oportunidade de revertê-la. Se precisares de reverter toda ou parte da cópia em massa quando ocorrer um erro, podes:
Usar uma SqlBulkCopytransação gerenciada
Fazer a operação de cópia em massa dentro de uma transação existente
Aliste-se em um System.TransactionsTransaction.
Executando uma operação de cópia em massa não transacionada
O aplicativo Console a seguir mostra o que acontece quando uma operação de cópia em massa não transacionada encontra um erro no meio da operação.
No exemplo, a tabela de origem e a tabela de destino incluem uma Identity coluna chamada ProductID. O código primeiro prepara a tabela de destino excluindo todas as linhas e, em seguida, inserindo uma única linha cujo ProductID é conhecido por existir na tabela de origem. Por padrão, um novo valor para a Identity coluna é gerado na tabela de destino para cada linha adicionada. Neste exemplo, ao abrir a conexão, uma opção é configurada para que o processo de carregamento em massa utilize, em vez disso, os valores provenientes da tabela de origem.
A operação de cópia em massa é executada com a BatchSize propriedade definida como 10. Quando a operação se depara com a linha inválida, uma exceção é lançada. Neste primeiro exemplo, a operação de cópia em massa não é transacionada. Todos os lotes que são 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
Este exemplo não será executado a menos que você tenha criado as tabelas de trabalho conforme descrito em Configuração de exemplo de cópia em massa. Este código é fornecido para demonstrar a sintaxe para usar SqlBulkCopy somente. Se as tabelas de origem e destino estiverem localizadas na mesma instância do SQL Server, será mais fácil e rápido usar uma instrução Transact-SQL INSERT ... SELECT 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;";
}
}
Executando uma operação dedicada de cópia em massa em uma transação
Por padrão, uma operação de cópia em massa é uma transação independente. Quando desejar executar uma operação de cópia em massa dedicada, crie uma instância de SqlBulkCopy com uma cadeia de conexão ou utilize um SqlConnection objeto existente sem uma transação ativa. A operação de cópia em massa cria uma transação em cada cenário e, em seguida, confirma ou reverte a transação.
Você pode especificar explicitamente a opção de UseInternalTransaction no construtor de classe SqlBulkCopy para executar uma operação de cópia em lotes na sua própria transação. Cada lote da operação será executado dentro de uma transação separada.
Observação
Como lotes diferentes são executados em transações diferentes, se ocorrer um erro durante a operação de cópia em massa, todas as linhas do 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 que são 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
Este exemplo não será executado a menos que você tenha criado as tabelas de trabalho conforme descrito em Configuração de exemplo de cópia em massa. Este código é fornecido para demonstrar a sintaxe para usar SqlBulkCopy somente. Se as tabelas de origem e destino estiverem localizadas na mesma instância do SQL Server, será mais fácil e rápido usar uma instrução Transact-SQL INSERT ... SELECT 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.
// 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
Você pode especificar um objeto existente SqlTransaction como um parâmetro em um SqlBulkCopy construtor. Nessa situação, a operação de cópia em massa é feita na transação existente e nenhuma alteração é feita no estado da transação - ela não é confirmada ou abortada. 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, se não especificar o objeto SqlTransaction e fornecer uma referência nula, caso a conexão possua uma transação ativa, uma exceção será lançada.
Se precisar reverter toda a operação de cópia em massa porque ocorre um erro, ou se a cópia em massa deve ser executada como parte de um processo maior que pode ser revertido, pode fornecer um objeto SqlTransaction ao construtor SqlBulkCopy.
O aplicativo de console a seguir é semelhante ao primeiro exemplo (não transacionado), com uma exceção: neste exemplo, a operação de cópia em massa é incluída em uma transação externa maior. Quando ocorre o erro de violação de chave primária, toda a transação é revertida e nenhuma linha é adicionada à tabela de destino.
Importante
Este exemplo não será executado a menos que você tenha criado as tabelas de trabalho conforme descrito em Configuração de exemplo de cópia em massa. Este código é fornecido para demonstrar a sintaxe para usar SqlBulkCopy somente. Se as tabelas de origem e destino estiverem localizadas na mesma instância do SQL Server, será mais fácil e rápido usar uma instrução Transact-SQL INSERT ... SELECT 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 inside the transaction.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction =
destinationConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
destinationConnection, SqlBulkCopyOptions.KeepIdentity,
transaction))
{
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
// Write from the source to the destination.
// This should fail with a duplicate key error.
try
{
bulkCopy.WriteToServer(reader);
transaction.Commit();
}
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);
transaction.Rollback();
}
finally
{
reader.Close();
}
}
}
}
// Perform a final count on the destination
// table to see how many rows were added.
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;";
}
}