Transaction Management in ADO.NET

A Database is a software system that defines a collection of predefined operations. Mainly it includes following operations

· Efficient management of large amount of persistent data in a persistent storage (database)

· Transaction Management which includes Concurrency Control, Atomicity and backup recovery procedure

· A DataModel which gives a separate level of abstraction

In this article I am concentrating of transaction management that is Concurrency Control in .NET environment.

A transaction is an abstract unit of concurrent computation that execute automatically. The effect of transaction does not interfere with other transactions that access the same data. Also a transaction happens with all of its effects (In this case you will commit the changes) or it doesn't happen none of its effects (In this case you will rollback the changes).

In the transaction control we generally define code in between a block where we perform mission critical operation. If all operations get completed successfully then that part is committed in the database otherwise what ever modification you might have done during the process is roll backed from the database so that it never affect other user's operations.

In .NET environment we can define transaction boundary by Transaction object.

If you are using SqlClient (namespace System.Data.SqlClient) Managed Provider you can SqlTransaction object.

If you are using Oledb (namespace System.Data.Oledb) Managed Provider you can OledbTransaction object.

If you are using Odbc (namespace Microsoft.Data.Odbc) Managed Provider you can OdbcTransaction object

Let us discuss a simple block of transaction control. In this block I am taking SqlClient Managed Provider

string connectionString = ".........";

SqlConnection myConnection = new SqlConnection(connectionString);

myConnection.Open();

// Start transaction.

SqlTransaction myTransaction = myConnection.BeginTransaction();

// Assign command in the current transaction.

SqlCommand myCommand = new SqlCommand();

myCommand.Transaction = myTransaction;

try

{

.........................

Database operations

........................

myTransaction.Commit();

Console.WriteLine("Records are modified in the database.");

}

catch(Exception e)

{

myTransaction.Rollback();

Console.WriteLine(e.ToString());

Console.WriteLine("Neither record was written to database.");

}

finally

{

myConnection.Close();

}

In Above Block

BeginTransaction method of the Connection object to mark the start of the transaction, which returns a Transaction object.

The newly created transaction object is assigned to CommandObject so that what ever the database operation is performed by that commandObject can be managed by Transaction Object.

If anything gets wrong the Transaction object will raise an Exception otherwise it will run through a normal process.

Call the Commit method of the Transaction object to complete the transaction if everything works fine otherwise call the Rollback method to cancel the transaction.

Concurrency Control

While doing certain modification in the database some time you need to lock the data so that no one can else perform modification in that data. There are two commonly known approaches for locking database they are optimistic locking and pessimistic locking.

Both these approaches are used to maintain concurrency in the database. Pessimistic concurrency locking is done at rows of the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, no one else can perform action until unless owner releases that lock. But this is not case with optimistic currency model. In optimistic concurrency model user does not lock row while reading it, while user only locks the row while updating changes to the database.

In .NET we use DataSet object for modifying changes in the database. The DataSet object uses optimistic concurrency model with the help of DataAdaptor. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are working in distributed environment.

In real time execution DataSet maintains the versions of data that means if anyone modify any data in the DataSet then it get maintain in the dataset as old version and new version. While updating modified data in the database if any of the concurrency conflict occur it raises Exception, which sets DataRow's HasError Boolean value. This we can easily handle with DataAdaptor event and with our own programming logic.

Here I am giving a simple code sample, which explains you how can you manage, concurrency control in .NET environment

string connectionString = ".......................";

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter myAdaptor = new SqlDataAdapter("SELECT Name, City FROM Employee ORDER BY EmpID", myConnection);

// Add the RowUpdated event handler.

myAdaptor.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

DataSet supplierData = new DataSet();

myAdaptor.Fill(supplierData, "Supplier");

// Modify the DataSet contents.

..........................................

.........................................

myAdaptor.Update(supplierData, "Supplier");

foreach (DataRow myRow in supplierData.Tables["Supplier"].Rows)

{

if (myRow.HasErrors)

Console.WriteLine(myRow[0] + "\n" + myRow.RowError);

}

protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)

{

if (args.RecordsAffected == 0)

{

args.Row.RowError = "Optimistic Concurrency Violation Encountered";

args.Status = UpdateStatus.SkipCurrentRow;

}

}

Explanation of Code:

In this code you have SqlDataAdaptor, which is retrieving supplier record from a database and filling it in a DataSet supplierData. After that you have performed certain modification in that data via DataSet. After modifying data we have used dataAdaptor to update that changes in the database.

So what is new in this code? You might have noticed that in this code we have defined a event handler on dataAdaptor's RowUpdated event. This event will be fired when row is updated in the database and in that event handler mechanism we can define different status to argument so that further action can be take place.

In the main code I have specified code to write all those rows in which error has occurred during modification.

There are different type of status is available for SqlRowUpdatedEventArgs by which you can direct the updating process. Those status are as follows

Status Description

Continue - Continue the update operation.

ErrorsOccurred - Abort the update operation and throw an exception.

SkipCurrentRow - Ignore the current row and continue the update operation.

SkipAllRemainingRows - Abort the update operation but do not throw an exception.

Transactions
So far we haven't used explicit transaction handling and therefore auto-commit has been enabled. This means that all commands have been executed in their own transaction, and that might not be what we want. Fortunately, transaction handling is quite easy and straightforward in ADO.NET using the OdbcTransaction class.

A transaction is started by calling the OdbcConnection.BeginTransaction(isolationlevel) that returns an instance of OdbcTransaction. The different isolation levels are available in System.Data.IsolationLevel. For example, System.Data.IsolationLevel.Serializable can be used.

All SQL statements that we want to be part of the transaction have to be associated with the transaction object. This can be done by passing the transaction through the constructor of the OdbcCommand or by setting the Transaction property. When we are done with the work we simply call OdbcTransaction.Commit() or OdbcTransaction.Rollback() to commit or roll back the transaction.

try
{
OdbcConnection con = new OdbcConnection("...");
con.Open();

//Start the transaction
OdbcTransaction trans =
con.BeginTransaction(System.Data.IsolationLevel.Serializable);
OdbcCommand command = new OdbcCommand(sql, con, trans);

//An alternative but equivalent approach
OdbcCommand command2= new OdbcCommand(sql2, con);
command2.Transaction=trans;

//Execute and commit
command.ExecuteNonQuery();
command2.ExecuteNonQuery();
trans.Commit();
con.Close();
}
catch(OdbcException oe)
{
//Show errors
try
{
trans.Rollback();
}
catch(OdbcException oe2)
{
//Show errors
}
}

As you can see, we simply commit when we are done and if an OdbcException is thrown we do a roll back. Note that the trans.RollBack() method can throw an OdbcEvent itself and is therefore surrounded by its own try-catch.