Does SqlBulkCopy automatically start a transaction?

Question

I am inserting data via SqlBulkCopy like so:

public void testBulkInsert(string connection, string table, DataTable dt) {     using (SqlConnection con = new SqlConnection(connection))     {         con.Open();          using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))         {             bulkCopy.DestinationTableName = table;             bulkCopy.WriteToServer(dt);         }     } } 

Will this automatically be wrapped in a SQL transaction so that if something goes wrong half way through the DB will be left in the same state as it was before the bulk insert began? Or will half the data be inserted?

i.e. is it necessary for me to explicitly call con.BeginTransaction

Or if I call SqlBulkCopy's constructor that takes a string, is that a better way of getting it to occur in a transaction?

public void testBulkInsert(string connection, string table, DataTable dt) {     using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))     {         bulkCopy.DestinationTableName = table;         bulkCopy.WriteToServer(dt);     } } 

I find the docs a bit unclear on this matter as they initially state that

By default, a bulk copy operation is performed as an isolated operation. The bulk copy operation occurs in a non-transacted way, with no opportunity for rolling it back

but then later state

By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction. In each scenario, the bulk copy operation creates, and then commits or rolls back the transaction.

So is it necessary to do:

public void testBulkInsert(string connection, string table, DataTable dt) {     using (SqlConnection con = new SqlConnection(connection))     {         con.Open();         using (SqlTransaction tr = con.BeginTransaction(IsolationLevel.Serializable))         {             using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, tr))             {                 bulkCopy.DestinationTableName = table;                 bulkCopy.WriteToServer(dt);             }             tr.Commit();         }     } } 

Answer 1

No here is text from SqlBulkCopy documentation in msdn

By default, a bulk copy operation is performed as an isolated operation. The bulk copy operation occurs in a non-transacted way, with no opportunity for rolling it back. If you need to roll back all or part of the bulk copy when an error occurs, you can use a SqlBulkCopy-managed transaction, perform the bulk copy operation within an existing transaction, or be enlisted in a System.Transactions Transaction.

EDIT: Read properly the documentation, from the link which I gave you:

By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an
existing SqlConnection object without an active transaction. In each scenario, the bulk copy operation creates, and then commits or rolls back the transaction.

This is written for the case internal bulk copy transaction, which is not the default!

   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(                        connectionString, SqlBulkCopyOptions.KeepIdentity |                        SqlBulkCopyOptions.UseInternalTransaction))    {        ....    } 

Look closely in SqlBulkCopyOptions.UseInternalTransaction ! You are explicitly specify the UseInternalTransaction option in the SqlBulkCopy class constructor to explicitly cause a bulk copy operation to execute in its own transaction, causing each batch of the bulk copy operation to execute within a separate transaction.Since different batches are executed in different transactions, if an error occurs during the bulk copy operation, all the rows in the current batch will be rolled back, but rows from previous batches will remain in the database.

---

If you need to roll back the entire bulk copy operation because an error occurs, or if the bulk copy should execute as part of a larger process that can be rolled back, you can provide a SqlTransaction object to the SqlBulkCopy constructor.

The external transaction case.

            using (SqlTransaction transaction =                        destinationConnection.BeginTransaction())             {                 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(                            destinationConnection, SqlBulkCopyOptions.KeepIdentity,                            transaction))                 {                      ....                 }             } 

---

Like I said in the begging the answer is no, you should use existing transaction or internal bulk copy transaction. Read the documentation file which is in the link, for more information.

If you want to have transaction you should use one of the two cases which I wrote.