Transactions in ADO.NET with Examples
Posted by Superadmin on November 15 2023 11:10:18

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.

  

Why we need Transactions?

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.

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.

Single Database Transaction using BeginTransaction

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.

 

CREATE DATABASE BankDB;
 GO 
 
 USE BankDB;
 GO 
 
 CREATE TABLE Accounts
(
AccountNumber VARCHAR(60) PRIMARY KEY,
CustomerName VARCHAR(60),
Balance int
);
 GO 
 
 INSERT INTO Accounts VALUES('Account1', 'James', 1000);
 INSERT INTO Accounts VALUES('Account2', 'Smith', 1000);
 GO
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.

 

How to use Transaction in ADO.NET?

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.

 

using System; 
 using System.Data.SqlClient; 
 
 namespace ADOTransactionsDemo
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
Console.WriteLine("Before Transaction");
GetAccountsData();
 
//Doing the Transaction 
MoneyTransfer();
 
//Verifying the Data After Transaction 
Console.WriteLine("After Transaction");
GetAccountsData();
} 
catch (Exception ex) 
{ 
Console.WriteLine($"Exception Occurred: {ex.Message}");
} 
Console.ReadKey();
} 
 
private static void MoneyTransfer() 
{ 
//Store the connection string in a variable 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; initial catalog=BankDB; integrated security=True";
//Creating the connection object 
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Open the connection 
//The connection needs to be open before we begin a transaction
connection.Open();
// Create the transaction object by calling the BeginTransaction method on connection object
SqlTransaction transaction = connection.BeginTransaction();
try
{
// Associate the first update command with the transaction
SqlCommand cmd = new SqlCommand("UPDATE Accounts SET Balance = Balance - 500 WHERE AccountNumber = 'Account1'", connection, transaction);
//Execute the First Update Command
cmd.ExecuteNonQuery();
// Associate the second update command with the transaction
cmd = new SqlCommand("UPDATE Accounts SET Balance = Balance + 500 WHERE AccountNumber = 'Account2'", connection, transaction);
//Execute the Second Update Command
cmd.ExecuteNonQuery();
// If everythinhg goes well then commit the transaction
transaction.Commit();
Console.WriteLine("Transaction Committed");
}
catch(Exception EX)
{
// If anything goes wrong, then Rollback the transaction
transaction.Rollback();
Console.WriteLine("Transaction Rollback");
}
}
}
private static void GetAccountsData()
{
//Store the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; initial catalog=BankDB; integrated security=True";
//Create the connection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("Select * from Accounts", connection);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
Console.WriteLine(sdr["AccountNumber"] + ", " + sdr["CustomerName"] + ", " + sdr["Balance"]);
}
}
}
}
}

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

Transactions in ADO.NET with Examples

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.

using System;
using System.Data.SqlClient;
namespace ADOTransactionsDemo
{
class Program
{
static void Main(string[] args)
{
try
{
Console.WriteLine("Before Transaction");
GetAccountsData();
//Doing the Transaction
MoneyTransfer();
//Verifying the Data After Transaction
Console.WriteLine("After Transaction");
GetAccountsData();
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
private static void MoneyTransfer()
{
//Store the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; initial catalog=BankDB; integrated security=True";
//Creating the connection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Open the connection
//The connection needs to be open before we begin a transaction
connection.Open();
// Create the transaction object by calling the BeginTransaction method on connection object
SqlTransaction transaction = connection.BeginTransaction();
try
{
// Associate the first update command with the transaction
SqlCommand cmd = new SqlCommand("UPDATE Accounts SET Balance = Balance - 500 WHERE AccountNumber = 'Account1'", connection, transaction);
//Execute the First Update Command
cmd.ExecuteNonQuery();
// Associate the second update command with the transaction
//MyAccounts table does not exists, so it will throw an exception
cmd = new SqlCommand("UPDATE MyAccounts SET Balance = Balance + 500 WHERE AccountNumber = 'Account2'", connection, transaction);
//Execute the Second Update Command
cmd.ExecuteNonQuery();
// If everythinhg goes well then commit the transaction
transaction.Commit();
Console.WriteLine("Transaction Committed");
}
catch(Exception ex)
{
// If anything goes wrong, then Rollback the transaction
transaction.Rollback();
Console.WriteLine("Transaction Rollback");
}
}
}
private static void GetAccountsData()
{
//Store the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; initial catalog=BankDB; integrated security=True";
//Create the connection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("Select * from Accounts", connection);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
Console.WriteLine(sdr["AccountNumber"] + ", " + sdr["CustomerName"] + ", " + sdr["Balance"]);
}
}
}
}
}

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

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.