交易和大量複製作業 (ADO.NET)
大量複製作業可做為隔離作業或多步驟交易的其中一個執行步驟。 第二種選項可讓您在同一交易內執行多個大量複製作業，並執行其他資料庫作業 (例如插入、更新及刪除)，同時仍然能夠認可或回復整個交易。
根據預設，大量複製作業會做為隔離作業執行。 大量複製作業會以非交易性方式進行，而且無法回復。 如果需要在發生錯誤時，復原全部或部分大量複製，您可以使用 SqlBulkCopy 管理的交易，在現有交易內執行大量複製作業，或在 System.Transactions Transaction 中登記。
執行非交易大量複製作業
下列主控台應用程式 (Console Application) 會顯示非交易大量複製作業在作業過程中發生錯誤時的狀況。
在範例中，來源資料表及目標資料表都包含名為 ProductID 的 Identity 資料行。 首先，程式碼會透過刪除所有資料列然後插入其 ProductID 已存在於來源資料表中的單一資料列，來準備目標資料表。 依預設會在目標資料表中，為每個加入之資料列的 Identity 資料行產生新的值。 在此範例中，開啟連接時會設定選項，以強制大量載入處理序使用來源資料表中的 Identity 值。
執行大量複製作業時，其 BatchSize 屬性會設定為 10。 當作業遇到無效的資料列時，系統會擲回例外狀況 (Exception)。 在第一個範例中，大量複製作業是非交易的。 發生錯誤前複製的所有批次作業都會得到認可；處理任何其他批次作業之前，會復原包含重複索引鍵的批次作業，並中止大量複製作業。
.gif "注意事項") 注意事項 |
|---|
除非您已依照大量複製範例設定 (ADO.NET)所述來建立工作資料表，否則此範例將無法執行。這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。如果來源及目的地資料表位於相同的 SQL Server 執行個體中，則使用 Transact-SQL INSERT … SELECT 陳述式來複製資料會更方便且快速。 |
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
using System.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)
{
Console.WriteLine(ex.Message);
}
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;";
}
}
在交易中執行專用大量複製作業
根據預設，大量複製作業是自己的交易。 當您要執行專用大量複製作業時，請建立具有連接字串的新 SqlBulkCopy 執行個體，或使用不含作用中交易的現有 SqlConnection 物件。 在每個案例中，會先建立大量複製作業，然後認可或復原交易。
您可以在 SqlBulkCopy 類別建構函式 (Constructor) 中明確指定 UseInternalTransaction 選項，以便明確地讓大量複製作業在自己的交易中執行，進而讓每個大量複製作業的批次在個別的交易內執行。
| 注意事項 |
|---|
由於是在不同的交易中執行不同的批次作業，因此如果在大量複製作業期間發生錯誤，則將復原目前批次作業中的所有資料列，但是先前批次作業的資料列將保留在資料庫中。 |
下列主控台應用程式類似於先前的範例，但有一個例外狀況：在此範例中，大量複製作業會管理其本身的交易。 發生錯誤前複製的所有批次作業都會得到認可；處理任何其他批次作業之前，會復原包含重複索引鍵的批次作業，並中止大量複製作業。
.gif "重要事項") 重要事項 |
|---|
除非您已依照大量複製範例設定 (ADO.NET)所述來建立工作資料表，否則此範例將無法執行。這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。如果來源及目的地資料表位於相同的 SQL Server 執行個體中，則使用 Transact-SQL INSERT … SELECT 陳述式來複製資料會更方便且快速。 |
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
using System.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)
{
Console.WriteLine(ex.Message);
}
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;";
}
}
使用現有交易
您可指定現有 SqlTransaction 物件做為 SqlBulkCopy 建構函式中的參數。 在此情況下，系統會在現有交易中執行大量複製作業，而且不會變更交易狀態 (亦即，它尚未認可或中止)。 這可讓應用程式使用其他資料庫作業來將大量複製作業併入交易中。 不過，如果您不指定 SqlTransaction 物件及傳遞 Null 參考，而且連接具有作用中的交易，則會擲回例外狀況。
如果因為發生錯誤而需要回復整個大量複製作業，或者大量複製作業應做為可回復之較大處理序的一部分執行，則可提供 SqlTransaction 物件給 SqlBulkCopy 建構函式。
下列主控台應用程式類似於第一個 (非交易) 範例，但有一個例外狀況：在此範例中，大量複製作業包含在較大的外部交易中。 當發生主索引鍵違規錯誤時，會復原整個交易，並且不會將任何資料列加入目標資料表中。
| 重要事項 |
|---|
除非您已依照大量複製範例設定 (ADO.NET)所述來建立工作資料表，否則此範例將無法執行。這個程式碼僅是為了示範使用 SqlBulkCopy 的語法而提供。如果來源及目的地資料表位於相同的 SQL Server 執行個體中，則使用 Transact-SQL INSERT … SELECT 陳述式來複製資料會更方便且快速。 |
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
using System.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)
{
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;";
}
}