Transactions in .Net

Transactions in .net

There are 2 main kinds of transactions; connection transactions and ambient transactions. A connection transaction (such as SqlTransaction) is tied directly to the db connection (such as SqlConnection), which means that you have to keep passing the connection around - OK in some cases, but doesn't allow "create/use/release" usage, and doesn't allow cross-db work. An example (formatted for space):

using (IDbTransaction tran = conn.BeginTransaction()) {
    try {
        // your code
        tran.Commit();
    }  catch {
        tran.Rollback();
        throw;
    }
}

Not too messy, but limited to our connection "conn". If we want to call out to different methods, we now need to pass "conn" around.

The alternative is an ambient transaction; new in .NET 2.0, the TransactionScope object (System.Transactions.dll) allows use over a range of operations (suitable providers will automatically enlist in the ambient transaction). This makes it easy to retro-fit into existing (non-transactional) code, and to talk to multiple providers (although DTC will get involved if you talk to more than one).

For example:

using(TransactionScope tran = new TransactionScope()) {
    CallAMethodThatDoesSomeWork();
    CallAMethodThatDoesSomeMoreWork();
    tran.Complete();
}

Note here that the two methods can handle their own connections (open/use/close/dispose), yet they will silently become part of the ambient transaction without us having to pass anything in.

If your code errors, Dispose() will be called without Complete(), so it will be rolled back. The expected nesting etc is supported, although you can't roll-back an inner transaction yet complete the outer transaction: if anybody is unhappy, the transaction is aborted.

The other advantage of TransactionScope is that it isn't tied just to databases; any transaction-aware provider can use it. WCF, for example. Or there are even some TransactionScope-compatible object models around (i.e. .NET classes with rollback capability - perhaps easier than a memento, although I've never used this approach myself).

All in all, a very, very useful object.

Some caveats:

On SQL Server 2000, a TransactionScope will go to DTC immediately; this is fixed in SQL Server 2005 and above, it can use the LTM (much less overhead) until you talk to 2 sources etc, when it is elevated to DTC.
There is a glitch that means you might need to tweak your connection string

Transactions in .net using c#

I am hitting the SQL connection to get some values. Should I use
transaction there?

No, there are not need to use transactions, when you are not alter data in database.

What are the pros and cons of using Transactions.

As you said, you have learned various articles, So may be you have figure out the reason of using the transactions. Look all of these in concern of database.

The advantages of three-tier applications in creating scalable and robust applications are made feasible by transaction processing systems. The ability to distribute the components that make up applications amongst separate servers without explicitly having to develop for that architecture is another advantage of transaction server processing. Transaction processing systems also ensure that transactions are atomic, consistent, isolated, and durable. This alleviates the developer from having to support these characteristics explicitly.

Why Do We Need Transaction Processing?

The Advantages Of Transaction Processing

Can transactions be used in simple code?

Yes, you can simply write code in C# using ADO.Net. (e.g. SQLTransaction class)

e.g.

SqlConnection db = new SqlConnection("connstringhere");
      SqlTransaction transaction;

      db.Open();
      transaction = db.BeginTransaction();
      try 
      {
         new SqlCommand("INSERT INTO TransactionDemo " +
            "(Text) VALUES ('Row1');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO TransactionDemo " +
            "(Text) VALUES ('Row2');", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO CrashMeNow VALUES " +
            "('Die', 'Die', 'Die');", db, transaction)
            .ExecuteNonQuery();
         transaction.Commit();

Reference:

Performing a Transaction Using ADO.NET

.NET 2.0 transaction model

C# NetCore Transaction null

Remove this line

IDbContextTransaction transaction = localcontext.Database.CurrentTransaction;

If there's an active TrasnactionScope your SqlConnection will be automatically enlisted in it. The whole point of TransactionScope is that your data access methods can be completely free of transaction handling. Then in some outer business layer or controller method, the transaction is orchestrated.

The reason CurrentTransaction is null is that there are two different ways to handle transactions. If you want the current System.Transactions.Transaction, you get it with System.Transactions.Transaction.Current.

Stepping back, there are 3 separate ways to manage transactions with SqlConnection.

TSQL Transactions: You can use TSQL API directly issuing BEGIN TRAN, COMMIT TRAN, etc.
ADO.NET Transactions: SqlConnection.BeginTrasaction, IDbTransaction , SqlTransaction, etc. This is a wrapper over the TSQL API, and is a PITA because it introduces a useless requirement to pass the SqlTransaction to each SqlCommand that you want to enlist in the Transaction. But enlisting TSQL commands in the current transaction is not optional, and never has been. And that's a pain because methods that user SqlCommand may not know whether there is a transaction. Dapper and EF both wrap this API in their transaction handling methods.
System.Transactions Transactions: Partly because of this System.Transactions was introduced in .NET 2.0 as a new and unified way to handle transactions in .NET, and SqlClient added support for it. The main innovation of System.Transactions was adding "ambient" transactions. So code could be agnositc about whether there's a transaction and the right thing will just happen. When opening a SqlConnection if there is a current Transaction, the SqlConnection will be enlisted in it, and the changes made using the SqlConnection will not be committed until the Transaction is committed. And there is no need for your ADO.NET code to know about the Transaction. Dapper and EF are both built on top of ADO.NET and SqlClient, so this all just works.

How to make a transaction in asp.net-core 2.0?

Yes man, using System.Transactions.TransactionScope, available since .Net Core 2.0 and above. check this:

Using System.Transactions in ASP.NET:
https://msdn.microsoft.com/en-us/ms229977

Implementing an Implicit Transaction using Transaction Scope:
https://msdn.microsoft.com/en-us/ms172152

Nested Transactions in .NET

Isn't it so that this must be supported by the DBMS you're using ?

SQL Server doesn't really support nested transactions for instance, however, with SQL Server you can use savepoints.

An article I've written some years ago on my blog.