A transaction is a group of operations combined into a logical unit of work that is either guaranteed to be executed as a whole or rolled back. Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing is an indispensible part of ADO.NET. It guarantees that a block of statements will either be executed in its entirety or rolled back,( i.e., none of the statements will be executed). Transaction processing has improved a lot in ADO.NET 2.0. This article discusses how we can work with transactions in both ADO.NET 1.1 and 2.0.

Implementing Transactions in ADO.NET

Note that in ADO.NET, the transactions are started by calling the BeginTransaction method of the connection class. This method returns an object of type SqlTransaction.
Other ADO.NET connection classes like OleDbConnection, OracleConnection also have similar methods. Once you are done executing the necessary statements within the transaction unit/block, make a call to the Commit method of the given SqlTransaction object, or you can roll back the transaction using the Rollback method, depending on your requirements (if any error occurs when the transaction unit/block was executed).
To work with transactions in ADO.NET, you require an open connection instance and a transaction instance. Then you need to invoke the necessary methods as stated later in this article.  Transactions are supported in ADO.NET by the SqlTransaction class that belongs to the System.Data.SqlClient namespace.

The two main properties of this class are as follows:

• Connection: This indicates the SqlConnection instance that the transaction instance is associated with
• IsolationLevel: This specifies the IsolationLevel of the transaction

The following are the methods of this class that are noteworthy:
Commit()   This method is called to commit the transaction
Rollback()  This method can be invoked to roll back a transaction. Note that a transaction can only be rolled back after it has been committed.
Save()       This method creates a save point in the transaction. This save point can be used to rollback a portion of the transaction at a later point in time. The following are the steps to implement transaction processing in ADO.NET.

• Connect to the database
• Create a SqlCommand instance with the necessary parameters
• Open the database connection using the connection instance
• Call the BeginTransaction method of the Connection object to mark the beginning of the transaction
• Execute the sql statements using the command instance
• Call the Commit method of the Transaction object to complete the
  transaction, or the Rollback method to cancel or abort the transaction
• Close the connection to the database

The following code snippet shows how we can implement transaction processing using ADO.NET in our applications.

string connectionString = ...; //Some connection string
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

try
{
sqlCommand.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)";
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
//Usual code
}

catch(Exception e)
{
sqlTransaction.Rollback();
//Usual code
}

finally
{
sqlConnection.Close();
}

The next piece of code illustrates how we can use the “using” statement for the above code. According to MSDN, the “using” statement, “defines a scope, outside of which an object or objects will be disposed.
Continues…