using SqlBulkCopy with SqlTransaction

參考引用:Example for using SqlBulkCopy with SqlTransaction



DataTable dt = new DataTable();
dt.Columns.Add("TestId");
dt.Columns.Add("TestDesc");

DataRow drNew = dt.NewRow();
drNew[0] = "1";
drNew[1] = "Test Row 1";
dt.Rows.Add(drNew);

DataRow drNew1 = dt.NewRow();
drNew1[0] = "2";
drNew1[1] = "Test Row 2";
dt.Rows.Add(drNew1);

DataRow drNew2 = dt.NewRow();
drNew2[0] = "3";
drNew2[1] = "Test Row 3";
dt.Rows.Add(drNew2);

DataRow drNew3 = dt.NewRow();
drNew3[0] = "4";
drNew3[1] = "Test Row 4";
dt.Rows.Add(drNew3);

DataRow drNew4 = dt.NewRow();
drNew4[0] = "5";
drNew4[1] = "Test Row 5";
dt.Rows.Add(drNew4);

DataRow drNew5 = dt.NewRow();
drNew5[0] = "6";
drNew5[1] = "Test Row 6";
dt.Rows.Add(drNew5);

SqlConnection conn = new SqlConnection("YourConnectionString");
conn.Open();

SqlTransaction transaction = conn.BeginTransaction();
try
{
  SqlCommand cmd = new SqlCommand("INSERT INTO BulkCopyTableDesc (BulkCopyId,BulkCopyDesc,BulkCopyFlag) Select Isnull(Max(BulkCopyId)+1,1), 'Test Bulk Copy '+Convert(VarChar(10), ceiling(rand()*10000)), 'Y' from BulkCopyTableDesc", conn);
  cmd.Transaction = transaction;
  cmd.ExecuteNonQuery();

  SqlBulkCopy sqlbulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
  sqlbulk.DestinationTableName = "BulkCopyTable";
  sqlbulk.BatchSize = 2;
  sqlbulk.WriteToServer(dt);
  transaction.Commit();
  lbStatus.Text = "Success";
}
catch (Exception exp)
{
  lbStatus.Text = exp.Message;
  transaction.Rollback();
}
finally
{
  transaction.Dispose();
  conn.Close();
}

參考引用:Using Transactions with SqlBulkCopy

' STEP 1 - Create a SqlConnection to the destination database server
Using destinationConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("InventoryDBConnectionString").ConnectionString)
   ' STEP 2 - Open the connection.
   destinationConnection.Open()

   ' STEP 3 - Create a SqlTransaction object.
   Dim myTransaction As SqlTransaction = destinationConnection.BeginTransaction()

   ' STEP 4 - Create the SqlBulkCopy object passing in the SqlTransaction object into the constructor.
   Using bulkCopy As New SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, myTransaction)
      bulkCopy.DestinationTableName = "InventoryItems"

      'Define column mappings
      For Each col As DataColumn In excelData.Columns
         bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)
      Next

      'With external transactions, the entire operation occurs under the umbrella of one transaction,
      'regardless of the BatchSize value
      bulkCopy.BatchSize = 100


      ' STEP 5 - Perform the import - the call to WriteToServer - within a Try...Catch block.
      Try
         bulkCopy.WriteToServer(excelData)

         ' STEP 6a - Success! Commit transaction
         myTransaction.Commit()

         Display some sort of success message...

      Catch ex As Exception
         ' STEP 6b - Failure - rollback the transaction
         myTransaction.Rollback()

         Display some sort of error message...
      End Try
   End Using

   destinationConnection.Close()
End Using