1

We are migrating a project from .NET Framework to .NET Core, the project is working with multiple data bases that are on the same SQL server. In the past we used a transaction scope for any transaction that we wanted to roll back in case of an error.

When the transaction is involving multiple DBs the transaction is being promoted to a distributed transactions which is not supported in .NET Core.

Question is, if all DBs are actually on the same server, if I will use a 'cross-database queries' like is suggested at the very last part of this Answer will I be insured against such a scenario?

Does 'cross-database queries' simply means running raw-SQL commands like:

using(TransactionScope scope = new TransactionScope())
{
    var connection = new SqlConnection(connectionString);
    connection.Open();

    var SqlComm1 = new SqlCommand("Insert into TableA...", connection);
    SqlComm1 .ExecuteNonQuery();

    var SqlComm2 = new SqlCommand("Insert into [DB2].[dbo].[TableB]...";
    SqlComm2 .ExecuteNonQuery();
    .
    .
}

if not, can I get a code example of what it actually is?

lastly, while using 'cross-database queries' can I take advantage of anything from my actual DBContexts? like connections, dbSets or anything and if so, how?

Ravid Goldenberg
  • 2,119
  • 4
  • 39
  • 59
  • The linked answer simulates two phase commit (without quite the guarantees that DTC or XA would normally give) by manually completing all distributed work on all connections without committing any of them and then committing all right at the end. Theres no requirement to be on the same server. DbContext might be tricky as SaveChanges does all the work under its own transaction. You will want to control that txn too. – StuartLC Nov 20 '19 at 19:41
  • I was referring to the last sentence in the linked answer, not what was proposed in the code sample he gave. – Ravid Goldenberg Nov 20 '19 at 19:43
  • 1
    I believe David Bowne was referring to [this](https://stackoverflow.com/a/23976801), i.e. a single connection to a Server can be established and e.g. by using 3 part dot notation `database.schema.table` you'll be able to perform updates to multiple databases on the same transaction. Technically this is still a distributed transaction, but as per link, it does not require DTC, so you might get away with this in Core. You might want to explore [Elastic Transactions](https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-transactions-overview) if you are using SQL Azure. – StuartLC Nov 21 '19 at 09:36

0 Answers0