Transactions in ADO.NET with Examples

In this article, I am going to discuss Transactions in ADO.NET with Examples. Before proceeding to this article, I strongly recommended you read our ADO.NET Basics section. At the end of this article, I am sure, you will understand why we need transactions, what is exactly a transaction and how to implement transactions using ADO.NET.

Why do we need Transactions?

The most important thing in today’s world is data and the most challenging job is to keep the data consistent. The Database systems stores the data and ADO.NET is one of the data access technology to access the data stored in the database.

Let us first understand what you mean by data consistency and then we will understand why we need transactions. For this please have a look at the following diagram. Here, you can see, we have an Accounts Table with two Account Numbers.

Now, the business requirement is to transfer 500 from Account1 to Account2. For this, we need to write two update statements as shown below. The first update statement deducts 500 from Account1 and the 2nd update statement Adds 500 to Account2.

UPDATE Accounts SET Balance = Balance – 500 WHERE AccountNumber = ‘Account1’;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountNumber = ‘Account2’;

Our intention is data consistency. Once the update statements are executed the data should be in a consistent state. Now let us understand the following cases.

• Case 1: The First update statement was executed successfully but the second update statement Failed. In that case, 500 is deducted from Account1 but that amount is not added to Account2 which results in data inconsistency.
• Case 2: The First update statement Failed but the second update statement was executed successfully. In that case, 500 is not deducted from Account1 but 500 is added to Account2 which results in data inconsistency.
• Case 3: When both update statements are Failed, then the data is in a consistent state.
• Case 4: When both update statements are Successful, then the data is also in a consistent state. That means 500 is deducted from Account1 and 500 is added to Account2.

From the above four cases, we don’t have any issues in Case3 and Case4. At the same time, we also can’t give guarantee that every time both the update statements are Failed and succeed. That means we need to do something special to handle Case1 and Case2 so that the data is to be in a consistent state and for this, we need to use transactions. So, in order to keep the data in a consistent state in the database while accessing the data using ADO.NET, we need to use transactions.

What is a Transaction?

A Transaction is a set of operations (multiple DML Operations) that ensures either all database operations succeeded or all of them failed to ensure data consistency. This means the job is never half done, either all of it is done or nothing is done.

ADO.NET Transactions Supports:

The ADO.NET supports both Single Database Transactions as well as Distributed Transactions (i.e. Multiple Database Transactions). The single database transaction is implemented using the .NET Managed Providers for Transaction and Connection classes which basically belong to System.Data namespace. On the other hand, the Distributed Transactions are implemented using the TransactionScope object which belongs to System.Transactions namespace

How to use Transaction in ADO.NET?

In two ways, we can implement transactions in C# using ADO.NET. They are as follows:

1. Single Database Transaction using BeginTransaction which belongs to System.Data namespace
2. Distributed Transactions using TransactionScope which belongs to System.Transactions namespace

Single Database Transaction in C# using BeginTransaction

Let us understand how to implement the Single Database Transaction in C# using BeginTransaction. We are going to implement the same money transfer example. Here, we are executing two update statements using ADO.NET Transaction. For this, we are going to use the following Accounts table in this demo.

Create a Database and Database Table with Sample Data

Please execute the below SQL Statements in the SQL Server database to create the BankDB and Accounts table as well as populate the Accounts table with the required data.

How to Implement a Single Database Transaction in C# using ADO.NET?

In order to understand how to implement transactions in C# using ADO.NET, please have a look at the below image.

Step 1: First you need to create and open the connection object. And the following two statements exactly do the same thing.
SqlConnection connection = new SqlConnection(ConnectionString)
connection.Open();

Step 2: Then you need to create the SqlTransaction object and to do so, you need to call the BeginTransaction method on the connection object. The following piece of code does the same.
SqlTransaction transaction = connection.BeginTransaction();

Step 3: Then you need to create the command object and while creating the command object, we need to text (in this case of the UPDATE statement) that we want to execute in the database, the connection object where we want to execute the command, and the transaction object which will execute the command as part of the transaction. And then we need to call the ExecuteNonQuery method to execute the DML Statement. The following code exactly does the same thing.
// Associate the first update command with the transaction
SqlCommand cmd = new SqlCommand(“UPDATE Accounts SET Balance = Balance – 500 WHERE AccountNumber = ‘Account1′”, connection, transaction);
cmd.ExecuteNonQuery();

// Associate the second update command with the transaction
cmd = new SqlCommand(“UPDATE Accounts SET Balance = Balance + 500 WHERE AccountNumber = ‘Account2′”, connection, transaction);
cmd.ExecuteNonQuery();

Step 4: If everything goes well then commit the transaction i.e. if both the UPDATE statements are executed successfully, then commit the transaction. To do so call the Commit method on the transaction object as follows.
transaction.Commit();

Step 4: If anything goes wrong then roll back the transaction. To do so call the Rollback method on the transaction object as follows.
transaction.Rollback();

Example to Understand ADO.NET Transactions using C#:

In the below example, we are executing two UPDATE statements by implementing ADO.NET Transactions. The following example code is self-explained, so please go through the comment lines. If both the UPDATE statements are executed successfully, it will commit the transaction and changes are going to be reflected in the database, and if anything goes wrong, then it will Rollback the transaction and the changes will not reflect in the database, and in this way it will maintain data consistency.

Output: As you can see in the below output the data is in a consistent state i.e. updated in both the Account Number.

Verifying Data Consistency:

Let us modify the example code as follows. In the following code, we deliberately introduce a change that would crash the application at runtime after executing the first update statement. Here, in the second update statement rename the table name as MyAccounts which does not exist in the database. In this case, the first UPDATE statement is executed, then it will try to execute the second UPDATE statement which will throw an exception and in that case, that runtime exception is going to handle by the Catch block and inside the catch block, we are calling the Rollback method which will rollback everything which is executed as part of the transaction.

Output: As you can see the transaction is rollback and the data which is updated by the first update statement is rollback to its previous state, and hence transaction maintains data consistency.

Setting Isolation Level in ADO.NET Transaction:

It is also possible in ADO.NET to set the Transaction IsolationLevel while creating the transaction object from the connection object as SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

Please read our different transaction isolation levels and their needs in real-time applications.

In the next article, I am going to discuss ADO.NET Distributed Transactions with Examples. Here, in this article, I try to explain Transactions in ADO.NET and I hope you enjoy this ADO.NET Transaction article.