A transaction consists of a single command or a group of commands that execute together. When we do some database operations in such a way that either all the database operations are successful or all of them fail. This would result in the amount of information being same once the transaction is complete or it fails.
Transactions allow you to combine multiple operations into a single unit of work. If a failure occurs at one point in the transaction, all of the updates can be rolled back to their pre-transaction state. In ADO.NET, you can control transactions using the Connection and Transaction objects. You can initiate a local transaction using BeginTransaction statement.
ADO.NET supports single-database transactions as well as distributed transactions. Single-database transaction model is implemented using the underlying .NET managed providers for Transaction and Connection classes from the System.Data namespace. Distributed transaction model is implemented using classes in the namespace System.Transactions.
Transaction and its Properties
The properties of transaction are simply called as “ACID” properties.
Atomic: Atomic means that all the statements (SQL statement or operations) that are a part of the transaction should work as atomic operation i.e. either all are successful or all should fail.
Consistent: This means that in case the atomic transaction success, the database should be in a state that reflect changes. If the transaction fails then database should be exactly like it was when the transaction started.
Isolated: If more than one transactions are in process then each of these transactions should work independently and should not effect the other transactions.
Durable: Durability means that once the transaction is committed, the changes should be permanent i.e. these changes will get saved in database and should persist no matter what(like power failure or something).
A simple transaction to insert rows in a table is given below.