트랜잭션 및 대량 복사 작업(ADO.NET)
대량 복사 작업을 격리된 작업이나 여러 단계로 이루어진 트랜잭션의 일부로 수행할 수 있습니다. 후자를 사용하면 다른 데이터베이스 작업(삽입, 업데이트, 삭제 등)을 수행하면서 같은 트랜잭션 내에서 둘 이상의 대량 복사 작업을 수행할 수 있을 뿐만 아니라 전체 트랜잭션을 커밋하거나 롤백할 수 있습니다.
기본적으로 대량 복사 작업은 격리된 작업으로 수행됩니다. 대량 복사 작업은 롤백할 수 없는 비트랜잭트 방식으로 이루어집니다. 오류가 발생할 때 대량 복사의 전체 또는 일부를 롤백해야 하는 경우에는 SqlBulkCopy에서 관리되는 트랜잭션을 사용하거나 기존 트랜잭션 내에서 대량 복사 작업을 수행하거나, 또는 System.Transactions Transaction에 인리스트먼트될 수 있습니다.
비트랜잭트 대량 복사 작업 수행
다음 콘솔 응용 프로그램은 비트랜잭트 대량 복사 작업에서 작업 중에 오류가 발생하는 경우를 보여 줍니다.
이 예제에서 소스 테이블과 대상 테이블에는 ProductID라는 Identity 열이 각각 포함되어 있습니다. 이 코드에서는 우선 모든 행을 삭제한 후 소스 테이블에 ProductID가 있다고 알려진 단일 행을 삽입하여 대상 테이블을 준비합니다. 기본적으로 Identity 열의 새 값이 대상 테이블의 추가된 행마다 생성됩니다. 이 예제에서는 연결이 열려 있기 때문에 대량 로드 프로세스에서 소스 테이블의 Identity 값을 대신 강제로 사용해야 하는 경우에 옵션이 설정됩니다.
대량 복사 작업은 BatchSize 속성을 10으로 설정하여 실행합니다. 작업 중 올바르지 않은 행이 발견되면 예외가 throw됩니다. 이 첫 번째 예제에서 대량 복사 작업은 비트랜잭트 방식입니다. 오류 지점까지 복사한 모든 배치가 커밋되어, 중복 키를 포함하는 배치가 롤백되고 다른 모든 배치가 처리될 때까지 대량 복사 작업이 중지됩니다.
.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 클래스 생성자에서 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 참조를 전달하며 연결에 활성 트랜잭션이 있는 경우에는 예외가 throw됩니다.
오류가 발생하여 전체 대량 복사 작업을 롤백해야 하는 경우나 롤백할 수 있는 보다 큰 프로세스의 일부로 대량 복사를 실행해야 하는 경우에는 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;";
}
}