Database.Begintransaction VS Transactions.Transactionscope

Database.BeginTransaction vs Transactions.TransactionScope

I found out the answer in Entity Framework 6's documentation:

With the introduction of EF6, Microsoft recommends to use new API methods: Database.BeginTransaction() and Database.UseTransaction(). Although System.Transactions.TransactionScope is still very well supported, it is no longer necessary for most users of EF6.

While Database.BeginTransaction() is used only for database related operations transaction, System.Transactions.TransactionScope, in addition to that, makes it possible for 'plain C# code' to also be transactional.

Hence, use Database.BeginTransaction() where ever doing only db related operations in a transaction in EF6 otherwise use System.Transactions.TransactionScope for mixing db operations and C# code together in a transaction.

For those who still prefer the TransactionScope approach, it is recommended they checkout its limitations, especially in cloud scenarios (cloud scenarios do not support distributed transactions).

Further information can be found here

TransactionScope transaction = new TransactionScope() VS TransactionScope s = context.Connection.BeginTransaction()

While Database. BeginTransaction() is used only for database related operations transaction, System. Transactions. ... TransactionScope for mixing db operations and C# code together in a transaction.

please see Below Links.Hope they help you:

TransactionScope vs Transaction in LINQ to SQL

Database.BeginTransaction vs Transactions.TransactionScope

C# System.Transactions Vs TransactionScope

Looking around various resources, I stumbled to the following which partially answers my question: Working with transactions in EF 6

Based on this documentation (which is mostly based on EF but limitations seem to apply anyway):

There are still some limitations to the TransactionScope approach:

Requires .NET 4.5.1 or greater to work with asynchronous methods.

• It cannot be used in cloud scenarios unless you are sure you have one and only one connection (cloud scenarios do not support distributed transactions).
• It cannot be combined with the Database.UseTransaction() approach of the previous sections.
• It will throw exceptions if you issue any DDL and have not enabled distributed transactions through the MSDTC Service.

Advantages of the TransactionScope approach:

• It will automatically upgrade a local transaction to a distributed transaction if you make more than one connection to a given database or combine a connection to one database with a connection to a different database within the same transaction (note: you must have the MSDTC service configured to allow distributed transactions for this to work).
• Ease of coding. If you prefer the transaction to be ambient and dealt with implicitly in the background rather than explicitly under you control then the TransactionScope approach may suit you better.

Being that the first article is no longer supported and the EF 6 article is newer + the comment from @MarcGravell, I assume that the decision comes down to the advantages and disadvantages of the EF 6 article above.

Which transaction is better with Dapper: BEGIN TRAN or TransactionScope?

Actually, this has nothing to do with Dapper. Refer this answer.

TransactionScope or connection.BeginTransaction or "Transaction in stored procedure" decision is outside the scope of Dapper. Dapper simply exposes few extension methods on DBConnection object of ADO.NET those map the output of queries to object. Rest is up to you.

1. TransactionScope is generally used for distributed transactions; transaction spanning different databases may be on different system. This needs some configurations on operating system and SQL Server without which this will not work. This is not recommended if all your queries are against single instance of database.
   
   As @ImrePühvel noted in comments: With single database this may be useful when you need to include the code in transaction that is not under your control. With single database, it does not need special configurations either.

2. connection.BeginTransaction is ADO.NET syntax to implement transaction (in C#, VB.NET etc.) against single database. This does not work across multiple databases.

3. "Transaction in stored procedure" is implemented against single database in Stored Procedure instead of doing this in application code.

Nested transactions or combination of above types is another topic for discussion; try searching net for this.

My personal opinion is to use UnitOfWork to handle transactions in better way. I have already mentioned the details in the link above. It uses connection.BeginTransaction internally.

Difference Between Transaction and TransactionScope

From msdn :

The TransactionScope class provides a
simple way to mark a block of code as
participating in a transaction,
without requiring you to interact with the transaction itself. A
transaction scope can select and
manage the ambient transaction
automatically. Due to its ease of use
and efficiency, it is recommended that
you use the TransactionScope class
when developing a transaction
application. When you instantiate
TransactionScope, the transaction
manager determines which transaction
to participate in. Once determined,
the scope always participates in that
transaction. The decision is based on
two factors: whether an ambient
transaction is present and the value
of the TransactionScopeOption
parameter in the constructor. The
ambient transaction is the transaction
within which your code executes. You
can obtain a reference to the ambient
transaction by calling the static
Current property of the Transaction
class.

You can read more about that here :

http://msdn.microsoft.com/en-us/library/ms172152(v=vs.90).aspx

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.90).aspx

Great (a bit old) article about transaction in .NET 2.0

http://msdn.microsoft.com/en-us/library/ms973865.aspx

When to use BeginTransaction in EF 6? vs SaveChanges

SaveChanges always executes its DML in a transaction even if you do not specify a transaction explicitly.

Adding entities to the context does not make database calls. The only time DML is executed is when SaveChanges is called. Therefore the code that you posted is atomic (which you call "correct").

Multiple contexts cannot share a transaction (easily). You should not be using multiple contexts anyway in the majority of the cases. Execute your work in one context and one transaction by default.

TransactionScope vs Transaction in LINQ to SQL

Linq2SQL will use an implicit transaction. If all of your updates are done within a single Submit, you may not need to handle the transaction yourself.

From the documentation (emphasis mine):

When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. When all SQL commands have been successfully completed, LINQ to SQL commits the local transaction and returns.

Difference between different BeginTransaction methods.

1. SqlConnection.BeginTransaction creates a SqlTransaction, which is specific to MS SQL Server
2. DbConnection.BeginTransaction creates a DbTransaction, which is generic, and relies on the underlying connection to create a database-specific transaction. If your DbConnection is of type SqlConnection, this will be a SqlTransaction.
3. DbConnection.BeginDbTransaction is a protected method that you override if you're creating your own class that inherits from DbConnection.

EDIT:

These are all specific to the database connection from which they were created, which is used differently than a TransactionScope, which isn't database-dependent. I believe if you wanted to coordinate transactions between multiple connections, you have to explicitly call DbConnection.EnlistTransaction(transaction). With a TransactionScope, a connection will (depending on the database provider, at least it should) automatically enlist in the TransactionScope if one exists when the connection is opened. In WCF, a TransactionScope can also be passed across service boundaries, and can be used to commit the results of multiple service calls as a single transaction.

---

Related Topics