文章：https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/transaction-and-bulk-copy-operations
标题：Transaction and Bulk Copy Operations
示例代码：
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;"; } }
另一个示例，使用已有的事务
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;"; } }
您可以将现有的SqlTransaction对象指定为SqlBulkCopy构造函数中的参数。在这种情况下，大容量复制操作将在现有事务中执行，并且不会对事务状态进行任何更改（即，它既不提交也不中止）。这允许应用程序将大容量复制操作与其他数据库操作一起包含在事务中。但是，如果不指定SqlTransaction对象并传递null引用，并且连接有活动事务，则会引发异常。
如果因为发生错误而需要回滚整个大容量复制操作，或者如果大容量复制应作为可回滚的较大进程的一部分执行，则可以向SqlBulkCopy构造函数提供SqlTransaction对象。
以下控制台应用程序与第一个（非事务性）示例相似，但有一个例外：在本示例中，大容量复制操作包含在一个较大的外部事务中。当发生主键冲突错误时，将回滚整个事务，并且不向目标表添加任何行。