In this article I will explain with an example, how to solve the following error (exception) that occurs in the WriteToServer function when SqlBulkCopy is used along with SqlTransaction class.
System.InvalidOperationException: Unexpected existing transaction.
Error
The following error (exception) that occurs in the WriteToServer function when SqlBulkCopy is used along with SqlTransaction class.
System.InvalidOperationException: Unexpected existing transaction.
![SqlBulkCopy WriteToServer Error: Unexpected existing transaction in C# and VB.Net]()
Cause
The above error (exception) occurs in the WriteToServer function when the SqlBulkCopy class object is not associated with the SqlTransaction and hence it is unable to identify the Transaction.
Solution
The solution to this problem is to associate the SqlBulkCopy class object with the SqlTransaction when it is declared as shown below.
C#
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
con.Open();
using (SqlTransaction sqlTransaction = con.BeginTransaction())
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.Customers";
//[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
try
{
sqlBulkCopy.WriteToServer(dt);
sqlTransaction.Commit();
}
catch
{
sqlTransaction.Rollback();
}
}
}
con.Close();
}
VB.Net
Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(consString)
con.Open()
Using sqlTransaction As SqlTransaction = con.BeginTransaction()
Using sqlBulkCopy As New SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction)
'Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.Customers"
'[OPTIONAL]: Map the DataTable columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId")
sqlBulkCopy.ColumnMappings.Add("Name", "Name")
sqlBulkCopy.ColumnMappings.Add("Country", "Country")
Try
sqlBulkCopy.WriteToServer(dt)
sqlTransaction.Commit()
Catch
sqlTransaction.Rollback()
End Try
End Using
End Using
con.Close()
End Using
