Loading


ADO.NET

  • Home
  • ADO.NET
  • What is Transactions in ADO.NET C#?. The Complete ADO.NET Developer Course 2023 [Videos].
ADO.NET

What is Transactions in ADO.NET C#?. The Complete ADO.NET Developer Course 2023 [Videos].

By admin / 11-Feb-2022 / in Blog, ADO.NET / 428 Comments / 18 Likes / Share This Post

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

Why we need Transactions?

The most important thing in todays 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 do 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.

Case1:

The First update statement 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.

Case2:

The First update statement Failed but the second update statement executed successfully. In that case, 500 is not deducted from Account1 but 500 is added to Account2 which results in data inconsistency.

Case3:

When both the update statements are Failed, then the data is in a consistent state.

Case4:

When both the update statements are Successful, then the data is also in a consistent state. That is 500 is deducted from Account1 and 500 is added to Account2.

From the above discussed four cases, we dont have any issues in Case3 and Case4. At the same time, we also cant give the 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 of the database operations succeed 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. The single database transaction is implemented using the .NET managed providers for Transaction and Connection classes which are basically belong to System.Data namespace.

How to use Transaction in ADO.NET?

There are many different ways that we can use to implement Transaction using ADO.NET and C#. They are as follows:

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

Let us understand how to implement a Single Database Transaction 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 Accounts Table using SQL Script

Please use the below SQL Statements to create and populate the Accounts table with the required data.

CREATE TABLE Accounts
(
AccountNumber VARCHAR(60) PRIMARY KEY,
CustomerName VARCHAR(60),
Balance int
);
GO

GO
How to Implement a Single Database Transaction using ADO.NET?

In order to understand how to implement transactions, please have a look at the below image.

How to use Transaction in ADO.NET?

Step1: First you need to create and open the connection object. The following two statements do the same.
SqlConnection connection = new SqlConnection(ConnectionString)
connection.Open();

Step2: 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();

Step3: If everything goes well then commit the transaction. To do so call the Commit method on the transaction object as shown below.
transaction.Commit();

Step4: If anything goes wrong then rollback the transaction. To do so call the Rollback method on the transaction object as shown below.
transaction.Rollback();

Create a new console application

First, open visual studio and then create a new console application with the name ADOTransactionsDemo. Open Program.cs class file and then copy and paste the following code in it. As you can see here, we are using ADO.NET Transaction and executing two update statements.

using System;
using System.Data.SqlClient;
namespace ADOTransactionsDemo
{
class Program
{
public static string ConnectionString = "data source=LAPTOP-ICA2LCQL\SQLEXPRESS; initial catalog=ADODB; integrated security=True";
static void Main(string[] args)
{
try
{
Console.WriteLine("Before Transaction");
GetAccountsData();
MoneyTransfer();
Console.WriteLine("After Transaction");
GetAccountsData();
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong" + e.Message);
}
Console.ReadKey();
}
private static void MoneyTransfer()
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
// 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 =
cmd.ExecuteNonQuery();
// Associate the second update command with the transaction
cmd = new SqlCommand("UPDATE Accounts SET Balance = Balance + 500 WHERE AccountNumber =
cmd.ExecuteNonQuery();
// If everythinhg goes well then commit the transaction
transaction.Commit();
Console.WriteLine("Transaction Committed");
}
catch
{
// If anything goes wrong, rollback the transaction
transaction.Rollback();
Console.WriteLine("Transaction Rollback");
}
}
}
private static void GetAccountsData()
{
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 Program as shown below. In the following code, we deliberately introduce a change that would crash the application at run time after executing the first update statement. Here, in the second update statement in rename the table name as MyAccounts which does not exist in the database.

using System;
using System.Data.SqlClient;
namespace ADOTransactionsDemo
{
class Program
{
public static string ConnectionString = "data source=LAPTOP-ICA2LCQL\SQLEXPRESS; initial catalog=ADODB; integrated security=True";
static void Main(string[] args)
{
try
{
Console.WriteLine("Before Transaction");
GetAccountsData();
MoneyTransfer();
Console.WriteLine("After Transaction");
GetAccountsData();
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong" + e.Message);
}
Console.ReadKey();
}
private static void MoneyTransfer()
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
// 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 =
connection, transaction);
cmd.ExecuteNonQuery();
// Associate the second update command with the transaction
cmd = new SqlCommand("UPDATE MyAccounts SET Balance = Balance + 500 WHERE AccountNumber =
connection, transaction);
cmd.ExecuteNonQuery();
// If everythinhg goes well then commit the transaction
transaction.Commit();
Console.WriteLine("Transaction Committed");
}
catch
{
// If anything goes wrong, rollback the transaction
transaction.Rollback();
Console.WriteLine("Transaction Rollback");
}
}
}
private static void GetAccountsData()
{
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.

Read more
See All

Comments (428 Comments)

Submit Your Comment

See All Posts

Related Posts

ADO.NET / Blog

What is ADO.NET?

In this article, I am going to give you a brief introduction to ADO.NET. As a .NET developer, you should be aware of ADO.NET. At the end of this article, you will understand the following pointers in detail.
11-Feb-2022 /18 /428

ADO.NET / Blog

How to using ADO.NET with SQL Server?

In this article, I am going to discuss ADO.NET using SQL Server Database with Examples. Please read our previous article where we discussed the Architecture of ADO.NET. At the end of this article, you will understand how to connect with SQL Server database using ADO.NET. I hope you have SQL Server installed on your machine. We are using SQL Server Management Studio (SSMS) Tool to interact with SQL Server.
11-Feb-2022 /18 /428

ADO.NET / Blog

What is ADO.NET SqlConnection Class in C#?

In this article, I am going to discuss the ADO.NET SqlConnection Class in C# with Examples. Please read our previous article where we discussed ADO.NET using SQL Server. As part of this article, we are going to discuss the following pointers in detail.
11-Feb-2022 /18 /428