CODEWITHSUNDEEP
.net-coreazure-sql-database
Masayuki Ozawa
Masayuki Ozawa

Reputation: 13

.NET Core + Elastic Transaction + SqlBulkCopy, "Unexpected existing transaction" exception occurs

I'm Using .NET Core 2.1.2.

I use SQL Database for DB and run the following code, the second sqlbulk.WriteToServer raises "Unexpected existing transaction" exception.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace sqldb
{
    class Program
    {
        static void Main(string[] args)
        {
            var constr = new SqlConnectionStringBuilder(){
                DataSource = "xxxxxxxx.database.windows.net",
                UserID = "xxxxxxx",
                Password = "xxxxxx"
            };
            using (var scope = new TransactionScope()){
                constr.InitialCatalog = "DB1";
                var dtable = new System.Data.DataTable();
                dtable.TableName = "T1";
                dtable.Columns.Add("C1");
                var drow = dtable.NewRow();
                drow["C1"] = 1;
                dtable.Rows.Add(drow);

                using (var con = new SqlConnection(constr.ToString())){
                    con.Open();
                    var sqlbulk = new System.Data.SqlClient.SqlBulkCopy(con);
                    sqlbulk.DestinationTableName = "T1";
                    sqlbulk.WriteToServer(dtable);
                }

                constr.InitialCatalog = "DB2";
                using (var con = new SqlConnection(constr.ToString())){
                    con.Open();
                    var sqlbulk = new System.Data.SqlClient.SqlBulkCopy(con);
                    sqlbulk.DestinationTableName = "T1";
                    sqlbulk.WriteToServer(dtable);
                }
                scope.Complete();
            }
        }
    }
}

I executed the following query on each DB and confirmed the status of Elastic Transaction, it was registered as DTC.

SELECT * FROM sys.dm_tran_active_transactions

In Transaction Scope, inserting data with sqlbulkcopy to multiple DBs of SQL Database seems to cause an error, but is there some workaround? (In the Transaction Scope, inserting data with multiple SqlBulkCopy for the same db / simple insert for multiple db is working fine)

Upvotes: 1

Views: 339

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

Looks like SqlBulkCopy gets confused with System.Transactions. With your SqlConnection already enlisted in the System.Transactions.Transaction, you can start a "nested" transaction with the SqlConnection.BeginTransaction, and pass that transaction to SqlBulkCopy. EG

            using (var con = new SqlConnection(constr.ToString()))
            {
                con.Open();

                using (var tran = con.BeginTransaction())
                {
                    var options = new SqlBulkCopyOptions();                        
                    var sqlbulk = new SqlBulkCopy(con,options,tran);

                    sqlbulk.DestinationTableName = "T1";
                    sqlbulk.WriteToServer(dtable);
                    tran.Commit();
                }
            }

Upvotes: 1

Related Questions