SqlBulkCopy  做为SQL Server 官方 批量入库类，性能不会太差。其事务部份官方说明较模糊，因此 针对事务配置做了一些测试。

 

  A. 先准备测试场景 ，关于SqlBulkCopyOptions.KeepIdentity 应用。  新建两张表 , 每张表列a 做为自增列，同时做为主键 ， 其中 test_sqlbulk 源表 ，先添加一组数据。

  1. CREATE TABLE [dbo].[test_sqlbulk](
  2.     [a] [int] IDENTITY(1,1) NOT NULL,
  3.     [b] [int] NULL,
  4.  CONSTRAINT [PK_test_sqlbulk] PRIMARY KEY CLUSTERED 
  5. (
  6.     [a] ASC
  7. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  9.  
  10.  
  11.  
  12.  
  13. CREATE TABLE [dbo].[test_sqlbulk_des](
  14.     [a] [int] IDENTITY(1,1) NOT NULL,
  15.     [b] [int] NULL,
  16.  CONSTRAINT [PK_test_sqlbulk_des] PRIMARY KEY CLUSTERED 
  17. (
  18.     [a] ASC
  19. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  20. ) ON [PRIMARY]

 

添加数据:

 

测试代码:

  1.  SqlConnection sqlConn = new SqlConnection( sourceConnection) ;
  2.             sqlConn.Open() ;
  3.             SqlCommand commandSourceData = new SqlCommand("select * from test_sqlbulk ", sqlConn);
  4.             SqlDataReader reader = commandSourceData.ExecuteReader();
  6.             // Set up the bulk copy object using the KeepIdentity option. 
  7.             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
  8.                        sourceConnection))
  9.             {
  10.                 bulkCopy.BatchSize = 2;
  11.                 bulkCopy.DestinationTableName =
  12.                     "dbo.test_sqlbulk_des";
  14.                 // Write from the source to the destination.
  15.                 // This should fail with a duplicate key error
  16.                 // after some of the batches have been copied.
  17.                 try
  18.                 {
  19.                     bulkCopy.WriteToServer(reader);
  20.                 }
  21.                 catch (Exception ex)
  22.                 {
  23.                     Console.WriteLine(ex.Message);
  24.                 }
  25.                 finally
  26.                 {
  27.                     reader.Close();
  28.                 }
  29.             }

View Code

 

再看看目标表 test_sqlbulk_des 结果 , 由于SqlBulkCopy  构造参数没有 KeepIdentity 标识，目标表自增列与源表不一致 :

test_sqlbulk_des 查询结果:

 

 =>调整代码，增加 SqlBulkCopyOptions.KeepIdentity 选项，再次导入：

  1.  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
  2.                        sourceConnection, SqlBulkCopyOptions.KeepIdentity))
  3.             {
  4.                 bulkCopy.BatchSize = 2;
  5.                 bulkCopy.DestinationTableName =
  6.                     "dbo.test_sqlbulk_des";
  7.  
  8.                 // Write from the source to the destination.
  9.                 // This should fail with a duplicate key error
  10.                 // after some of the batches have been copied.
  11.                 try
  12.                 {
  13.                     bulkCopy.WriteToServer(reader);
  14.                 }
  15.                 catch (Exception ex)
  16.                 {
  17.                     Console.WriteLine(ex.Message);
  18.                 }
  19.                 finally
  20.                 {
  21.                     reader.Close();
  22.                 }
  23.             }

 

 =>目标表，源表自增列一致了:

 

B. 测试无事务，内部事务（SqlBulkCopyOptions.UseInternalTransaction） ， 外部传入事务 之间区别 ， 以下所有测试均事先在目标表保留一行数据 使copy 过程中造成主键冲突，查看回退情况。

1. 无事务测试。初使化源表，目标表数据:

 

测试代码 ， 不意外，如下代码最终会产生主键冲突错误， 执行结束后，再查询源表，目标表数据 。 BatchSize 设置为2 , 结果表明在第二批次主键冲突, 第一批次数据成功提交了（没有显示声明事务）, 再将 BatchSize 设置为3 验证结果。

  1.  SqlConnection sqlConn = new SqlConnection( sourceConnection) ;
  2.             sqlConn.Open() ;
  3.             SqlCommand commandSourceData = new SqlCommand("select * from test_sqlbulk ", sqlConn);
  4.             SqlDataReader reader = commandSourceData.ExecuteReader();
  5.  
  6.             // Set up the bulk copy object using the KeepIdentity option. 
  7.             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
  8.                        sourceConnection, SqlBulkCopyOptions.KeepIdentity))
  9.             {
  10.                 bulkCopy.BatchSize = 2;
  11.                 bulkCopy.DestinationTableName =
  12.                     "dbo.test_sqlbulk_des";
  13.  
  14.                 // Write from the source to the destination.
  15.                 // This should fail with a duplicate key error
  16.                 // after some of the batches have been copied.
  17.                 try
  18.                 {
  19.                     bulkCopy.WriteToServer(reader);
  20.                 }
  21.                 catch (Exception ex)
  22.                 {
  23.                     Console.WriteLine(ex.Message);
  24.                 }
  25.                 finally
  26.                 {
  27.                     reader.Close();
  28.                 }
  29.             }

 

BatchSize = 2 结果:

 

BatchSize = 3 结果:

 

 

2.指定内部事务 ， 分别将 BatchSize 指定为 2 , 3, 4  。如下执行结果与上文示例 结果一致， 因此无论是否指定 SqlBulkCopyOptions.UseInternalTransaction 参数（不包括外部传入事务） ，SqlBulkCopy 内部按照一个批次一个事务。在出现异常时只回滚当前批次 ， 在此之前成功执行批次不回滚。 因此若需全部回退，需将  BatchSize 设为 总记录条数 ，此时所有数据做为一个批次提交 ，  异常回退批次，回退数据范围即全部数据。

 

测试代码：

  1.  // Set up the bulk copy object using the KeepIdentity option. 
  2.             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
  3.                        sourceConnection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
  4.             {
  5.                 bulkCopy.BatchSize = 2;
  6.                 bulkCopy.DestinationTableName =
  7.                     "dbo.test_sqlbulk_des";
  8.  
  9.                 // Write from the source to the destination.
  10.                 // This should fail with a duplicate key error
  11.                 // after some of the batches have been copied.
  12.                 try
  13.                 {
  14.                     bulkCopy.WriteToServer(reader);
  15.                 }
  16.                 catch (Exception ex)
  17.                 {
  18.                     Console.WriteLine(ex.Message);
  19.                 }
  20.                 finally
  21.                 {
  22.                     reader.Close();
  23.                 }
  24.             }

 

 =>BatchSize = 2  , 目标表结果:

 =>BatchSize = 3  , 目标表结果:

 

3. 外部传入事务对象场景 , 如果 使用SqlBulkCopy入库操作只做为本次业务处理一个子集，在整个业务处理环节中异常情况，需要回滚整个业务操作。 本次再新建一张表:test_sqlbulk_update ， 测试过程中，先对该表增加一条记录，再重复之前异常场景。 结束时，之前新增记录也回退了。

因此， 当外部传入事务对象时，SqlBulkCopy  内部不提交事务， 在异常情况，有可能会自动回滚。

  1. CREATE TABLE [dbo].[test_sqlbulk_update](
  2.     [a] [int] IDENTITY(1,1) NOT NULL,
  3.     [b] [int] NULL,
  4. )

View Code

 

  1. SqlConnection sqlConn = new SqlConnection( sourceConnection) ;
  2.             sqlConn.Open() ;
  3.  
  4.             SqlTransaction tran = sqlConn.BeginTransaction();
  5.  
  6.             SqlCommand commandInsert = new SqlCommand("insert into [test_sqlbulk_update]( b ) values (1) ", sqlConn, tran);
  7.  
  8.             int result = commandInsert.ExecuteNonQuery();
  9.  
  10.             SqlCommand commandSourceData = new SqlCommand("select * from test_sqlbulk ", sqlConn, tran);
  11.             
  12.             SqlDataReader reader = commandSourceData.ExecuteReader();
  13.             
  14.             // Set up the bulk copy object using the KeepIdentity option. 
  15.             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity, tran))
  16.             {
  17.                 bulkCopy.BatchSize = 2;
  18.                 bulkCopy.DestinationTableName =
  19.                     "dbo.test_sqlbulk_des";
  20.  
  21.                 // Write from the source to the destination.
  22.                 // This should fail with a duplicate key error
  23.                 // after some of the batches have been copied.
  24.                 try
  25.                 {
  26.                     bulkCopy.WriteToServer(reader);
  27.                     reader.Close();
  28.                     tran.Commit();
  29.                 }
  30.                 catch (Exception ex)
  31.                 {
  32.                     reader.Close();
  33.                     tran.Rollback();
    　　　　　　　　　　　　// tran.Commit(); 异常仍提交执行，同时注释上一行。
  34.                 }
  35.                
  36.             }

 

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

 

 

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

  1. SqlTransaction tran = sqlConn.BeginTransaction();
  4.             SqlCommand commandSourceData = new SqlCommand("select * from test_sqlbulk ", new SqlConnection(sourceConnection));
  5.             
  6.             SqlDataAdapter ada = new SqlDataAdapter(commandSourceData ) ;
  7.             System.Data.DataSet ds = new System.Data.DataSet();
  8.             ada.Fill(ds);
  9.             
  10.             // Set up the bulk copy object using the KeepIdentity option. 
  11.             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepIdentity, tran))
  12.             {
  13.                 bulkCopy.BatchSize = 2;
  14.                 bulkCopy.DestinationTableName =
  15.                     "dbo.test_sqlbulk_des";
  17.                 // Write from the source to the destination.
  18.                 // This should fail with a duplicate key error
  19.                 // after some of the batches have been copied.
  20.                 try
  21.                 {
  22.                     
  23.                     bulkCopy.WriteToServer(ds.Tables[0]);
  25.                     SqlCommand commandInsert = new SqlCommand("insert into [test_sqlbulk_update]( b ) values ('a') ", sqlConn, tran);
  27.                     int result = commandInsert.ExecuteNonQuery();
  30.                     tran.Commit();
  31.                 }
  32.                 catch (Exception ex)
  33.                 {
  34.                     tran.Commit();
  35.                 }
  36.                
  37.             }

View Code

 

 

版权声明：本文为a_bu原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/a_bu/p/8480111.html