Working with ADO.NET Transactions

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.
A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement”.

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
sqlConnection.Open();
transaction = sqlConnection.BeginTransaction();

command.Transaction = transaction;

command.CommandText = "Insert into employee (empID, empName) values (1, 'Joydip');
command.ExecuteNonQuery();

command.CommandText = "Insert into dept (deptID,deptName,empID) values (9,'Software',1)";
command.ExecuteNonQuery();

transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
sqlConnection.Close();
}
}

The Microsoft’s ADO.NET version 2.0 added a lot of new features to its earlier counterpart to add moer flexibility and ease of use. As far as transactions are concerned, a new namespace called System.Transactions has been introduced that promises a significantly improved support for distributed transactions. It contains a class called TransactionScope that can run a set of statements. It can also determine whether the objects in the scope have support for transactions. If the transaction has completed successfully, the changes are committed to the database else it is rolled back. We need to specify whether the transaction block is complete by making a call to the TransactionScope.Complete method explicitly, else, the transaction would be rolled back when the transaction instance would be discarded by the implicit Dispose method.
The following piece of code illustrates what we have learnt so far in this section.

bool IsConsistent = false;
using (System.Transactions.TransactionScope transactionScope = new System.Transactions.TransactionScope())
{
SqlConnection sqlConnection = newSqlConnection(connectionString);
string sqlString = "Update emp set empName = 'Joydip Kanjilal' where empID = 9";
SqlCommand cmd1 = newSqlCommand(sql, cn);
sqlConnection.Open();
cmd1.ExecuteNonQuery();
sqlConnection.Close();
transactionScope.Consistent = IsConsistent;
}

TransactionScope also has support for distributed transactions. 

We can implement transactions for multiple database connections using it. 

The following piece of code shows how we can implement transactional support for 

multiple databases using the TransactionScope class.

using (TransactionScope transactionScope = new TransactionScope())
{
using (SqlConnection codesDatabaseConnection = new SqlConnection(codesDatabaseConnectionString))
{
SqlCommand sqlCommandCodes = codesDatabaseConnection.CreateCommand();
sqlCommandCodes.CommandText = "Insert Into codes (codeID,codeText) values (1,'Test')";
codesDatabaseConnection.Open();
sqlCommandCodes.ExecuteNonQuery();
codesDatabaseConnection.Close();
}

using (SqlConnection statesDatabaseConnection = new SqlConnection(statesDatabaseConnectionString))
{
SqlCommand sqlCommandStates = statesDatabaseConnection.CreateCommand();
sqlCommandStates.CommandText = "Insert into States(stateID,stateName) values (1, 'Test')";
codesDatabaseConnection.Open();
sqlCommandStates.ExecuteNonQuery();
statesDatabaseConnection.Close();
}

transactionScope.Complete();
}

Points to be noted

It should be noted that the SqlTransaction object returned by the BeginTransaction () method has to be assigned to the Transaction property of the Command object; else an InvalidOperationException will be thrown by the application when the first query is executed. Likewise, the Connection instance should be open by invoking the Open method on it prior to starting a new transaction; else an InvalidOperationException would be thrown. In order to improve the performance of applications, we should try to keep the transactions (the transaction units/blocks that contain the statements to be executed in a batch as a whole) as short as possible. This will help minimize the lock contention and hence increase throughput. Further, we should analyze whether or not we actually require a transaction for a batch of statements. Try not to unnecessarily have transactional statements in you code as it might have a performance drawback due to the reasons stated above.

Conclusion

The usage of transactions guarantee the execution of a batch of statements sequentially in its entirety or roll them back hence preserving the database integrity and data consistency. In a nutshell, in order to work with transactions, invoke the BeginTransaction() method of the appropriate database connection instance and then call either the Commit() or Rollback() method on the returned transaction object reference depending on the circumstances. However, it should be noted that transactions hold locks and may cause contention issues; they should be as short as it is possible. Hence, this is a major performance drawback in using transactions in our code. But, if used properly, it can facilitate the design and implementation of robust applications with data security and consistency. This article has discusses transactions in details in a lucid language with code examples to illustrate the concepts.