Transazioni e operazioni di copia di massa
Le operazioni di copia bulk possono essere eseguite come operazioni isolate oppure come parte di una transazione in più passaggi. Quest'ultima opzione consente di eseguire più di un'operazione di copia bulk all'interno della stessa transazione, nonché di eseguire altre operazioni sul database (come inserimenti, aggiornamenti ed eliminazioni), mantenendo comunque la possibilità di eseguire il commit o il rollback dell'intera transazione.
Per impostazione predefinita, una copia bulk viene eseguita come un'operazione isolata. L'operazione di copia bulk avviene in modalità non transazionale, senza la possibilità di eseguirne il rollback. Se è necessario eseguire il rollback della copia bulk, interamente o in parte, quando si verifica un errore, è possibile usare una transazione gestita da SqlBulkCopy, eseguire l'operazione di copia bulk all'interno di una transazione esistente oppure inserirla in System.TransactionsTransaction.
Esecuzione di un'operazione di copia di massa non transazionale
L'applicazione console seguente illustra cosa accade quando si verifica un errore durante un'operazione di copia bulk non transazionale.
Nell'esempio, le tabelle di origine e di destinazione contengono entrambe una colonna Identity denominata ProductID. Il codice innanzitutto prepara la tabella di destinazione eliminando tutte le righe e quindi inserendo una singola riga il cui ProductID è presente nella tabella di origine. Per impostazione predefinita, viene generato un nuovo valore per la colonna Identity nella tabella di destinazione per ogni riga aggiunta. In questo esempio, all'apertura della connessione viene impostata un'opzione che impone al processo di caricamento bulk di usare i valori Identity dalla tabella di origine.
L'operazione di copia bulk viene eseguita con la proprietà BatchSizeimpostata su 10. Quando è rilevata la riga non valida, viene generata un'eccezione. In questo primo esempio, l'operazione di copia bulk è non transazionale. Viene eseguito il commit di tutti i batch copiati fino al punto dell'errore. Viene eseguito il rollback del batch che contiene la chiave duplicata e l'operazione di copia bulk viene interrotta prima dell'elaborazione di altri batch.
Nota
Questo esempio non funzionerà, a meno che non siano state create le tabelle di lavoro come descritto in Creazione di esempi di copia di massa. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e rapido usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
using System.Data.SqlClient;
static class Program
{
static void Main()
{
var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new(connectionString))
{
sourceConnection.Open();
// Delete all from the destination table.
SqlCommand commandDelete = new()
{
Connection = sourceConnection,
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()
{
Connection = sourceConnection,
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(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new(
"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(
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)
{
Console.WriteLine(ex.Message);
}
finally
{
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = 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();
}
}
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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a sourceConnection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Delete all from the destination table.
Dim commandDelete As 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.
Dim commandInsert As 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.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = _
commandSourceData.ExecuteReader()
' Set up the bulk copy object using the KeepIdentity option.
Using bulkCopy As SqlBulkCopy = 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 already been copied.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Esecuzione di un'operazione di copia bulk dedicata in una transazione
Per impostazione predefinita, un'operazione di copia bulk costituisce la propria transazione. Quando si vuole eseguire un'operazione di copia bulk dedicata, creare una nuova istanza di SqlBulkCopy con una stringa di connessione oppure usare un oggetto SqlConnection esistente senza una transazione attiva. In questo scenario, l'operazione di copia bulk crea e quindi esegue il commit o il rollback della transazione.
È possibile specificare esplicitamente l'opzione UseInternalTransaction nel costruttore della classe SqlBulkCopy per fare in modo che un'operazione di copia bulk venga eseguita nella propria transazione, determinando l'esecuzione di ogni batch dell'operazione di copia bulk all'interno di una transazione distinta.
Nota
Poiché i diversi batch vengono eseguiti in diverse transazioni, se si verifica un errore durante l'operazione di copia bulk, verrà eseguito il rollback di tutte le righe nel batch corrente, ma le righe dei batch precedenti rimarranno nel database.
L'applicazione console seguente è simile all'esempio precedente, con una sola eccezione: in questo esempio l'operazione di copia bulk gestisce le proprie transazioni. Viene eseguito il commit di tutti i batch copiati fino al punto dell'errore. Viene eseguito il rollback del batch che contiene la chiave duplicata e l'operazione di copia bulk viene interrotta prima dell'elaborazione di altri batch.
Importante
Questo esempio non funzionerà, a meno che non siano state create le tabelle di lavoro come descritto in Creazione di esempi di copia di massa. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e rapido usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
using System.Data.SqlClient;
static class Program
{
static void Main()
{
var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new(connectionString))
{
sourceConnection.Open();
// Delete all from the destination table.
SqlCommand commandDelete = new()
{
Connection = sourceConnection,
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()
{
Connection = sourceConnection,
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(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new(
"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(
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)
{
Console.WriteLine(ex.Message);
}
finally
{
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = 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();
}
}
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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a sourceConnection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Delete all from the destination table.
Dim commandDelete As 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.
Dim commandInsert As 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.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = _
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 bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString, _
SqlBulkCopyOptions.UseInternalTransaction Or _
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 already been copied.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module
Uso di transazioni esistenti
È possibile specificare un oggetto SqlTransaction esistente come parametro in un costruttore SqlBulkCopy. In questo caso, l'operazione di copia bulk viene eseguita in una transazione esistente e non viene apportata alcuna modifica allo stato della transazione (ovvero, non ne viene eseguito il commit, né viene interrotta). Questo consente a un'applicazione di includere l'operazione di copia bulk in una transazione con altre operazioni sul database. Se tuttavia non si specifica un oggetto SqlTransaction, si passa un riferimento null e la connessione ha una transazione attiva, viene generata un'eccezione.
Se è necessario eseguire il rollback dell'intera copia bulk perché si verifica un errore o se la copia bulk deve essere eseguita come parte di un processo più ampio di cui è possibile eseguire il rollback, è possibile aggiungere un oggetto SqlTransaction al costruttore SqlBulkCopy.
L'applicazione console seguente è simile al primo esempio (non transazionale), con una sola eccezione: in questo esempio, l'operazione di copia bulk è inclusa in una transazione esterna di maggiori dimensioni. Quando si verifica l'errore di violazione della chiave primaria, viene eseguito il rollback dell'intera transazione e non vengono aggiunte righe alla tabella di destinazione.
Importante
Questo esempio non funzionerà, a meno che non siano state create le tabelle di lavoro come descritto in Creazione di esempi di copia di massa. Il codice viene fornito solo per illustrare la sintassi relativa all'uso di SqlBulkCopy. Se le tabelle di origine e di destinazione si trovano nella stessa istanza di SQL Server, è più semplice e rapido usare un'istruzione Transact-SQL INSERT … SELECT per copiare i dati.
using System.Data.SqlClient;
static class Program
{
static void Main()
{
var connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new(connectionString))
{
sourceConnection.Open();
// Delete all from the destination table.
SqlCommand commandDelete = new()
{
Connection = sourceConnection,
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()
{
Connection = sourceConnection,
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(
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new(
"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(connectionString))
{
destinationConnection.Open();
using (SqlTransaction transaction =
destinationConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new(
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)
{
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 = 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();
}
}
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;";
}
}
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim connectionString As String = GetConnectionString()
' Open a sourceConnection to the AdventureWorks database.
Using sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Delete all from the destination table.
Dim commandDelete As 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.
Dim commandInsert As 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.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = _
commandSourceData.ExecuteReader()
' Set up the bulk copy object inside the transaction.
Using destinationConnection As SqlConnection = _
New SqlConnection(connectionString)
destinationConnection.Open()
Using transaction As SqlTransaction = _
destinationConnection.BeginTransaction()
Using bulkCopy As SqlBulkCopy = 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 ex As Exception
Console.WriteLine(ex.Message)
transaction.Rollback()
Finally
reader.Close()
End Try
End Using
End Using
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
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()
End Using
End Sub
Private Function GetConnectionString() As String
' 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;"
End Function
End Module