SqlBulkCopy 参数配置示例

SqlConnection sqlConn = new SqlConnection( sourceConnection) ;
            sqlConn.Open() ;
 
            SqlTransaction tran = sqlConn.BeginTransaction();
 
            SqlCommand commandInsert = new SqlCommand("insert into [test_sqlbulk_update]( b ) values (1) ", sqlConn, tran);
 
            int result = commandInsert.ExecuteNonQuery();
 
            SqlCommand commandSourceData = new SqlCommand("select * from test_sqlbulk ", sqlConn, tran);
             
            SqlDataReader reader = commandSourceData.ExecuteReader();
             
            // Set up the bulk copy object using the KeepIdentity option.
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity, tran))
            {
                bulkCopy.BatchSize = 2;
                bulkCopy.DestinationTableName =
                    "dbo.test_sqlbulk_des";
 
                // 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);
                    reader.Close();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    reader.Close();
                    tran.Rollback();<br>　　　　　　　　　　　　// tran.Commit(); 异常仍提交执行，同时注释上一行。
                }
                
            }

=> 异常时执行 rollback，最终结果:没做任何修改.

=> 外部事务，Catch 中执行 Commint ,发生异常，因为自动回滚了，但在Catch 执行 Rollback 无异常。

View Code