I am using SqlBulkCopy
to insert large amount of data:
try
{
using (var bulkCopy = new SqlBulkCopy(connection))
{
connection.Open();
using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
bulkCopy.DestinationTableName = "table";
bulkCopy.ColumnMappings.Add("...", "...");
using (var dataReader = new ObjectDataReader<MyObject>(data))
{
bulkCopy.WriteToServer(dataReader);
}
tran.Commit();
return true;
}
}
}
catch (Exception ex)
{
return false;
}
But I always get exception:
Unexpected existing transaction.
Why this exception happens?
"Unexpected existing transaction" ... Why this exception happens?
This happens because using the SqlBulkCopy
constructor without specifying a transaction will create its own transaction internally.
Avoid this by creating your transaction and then use it to create the SqlBulkCopy
. SqlBulkCopy
can be created with the transaction that you want to use, like this:
connection.Open();
using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))
{