ADO.Net 2.0 Transaction Processing

A lot of new features have been included in ADO.Net 2.0 and we will be discussing transactions in this article. Transactions are an important part in any database applications development.

We will see a simple transaction which uses the TransactionScope object to understand how this object is used.

using (System.Transactions.TransactionScope transScope = new System.Transactions.TransactionScope())
{
SqlConnection conn = new SqlConnection(connection_string_here);
string sQuery = "DELETE Products";
SqlCommand cmdObj = newSqlCommand(sQuery, conn);
conn.Open();
cmdObj.ExecuteNonQuery();
conn.Close();
transScope.Consistent = false;
}

In the above code we are creating a new TransactionScope object and writing all the transaction processing code within a block that comes under the scope of the TransactionScope. In the above code the SQL connection itself comes under the TransactionScope and any error in the connection itself will not commit the transactions done. To commit or rollback the transactions the Consistent property of the TransactionScope object is set to either true or false. You can also enclose the cmdObj’s ExecuteNonQuery() statement in a try/catch block to write an improved version of the above code.

Consider a scenario where you are required to update two databases and both the databases have to be updated or none of the databases should be updated. In such cases you will find the use of TransactionScope object very useful. The only thing you need to do is to enclose all the transaction processing in a block that comes under the TransactionScope object. The code given below gives you an idea on how to go about in that scenario.

bool transConsist = false;
using (TransactionScope transScope = new TransactionScope())
{
using (SqlConnection conn1 = new SqlConnection(conn_string1))
{
string query1 = "DELETE ProductDesc";
SqlCommand cmd1 = newSqlCommand(query1, conn1);
conn1.Open();
try
{
cmd1.ExecuteNonQuery();
using(SqlConnection conn2 = new SqlConnection(conn_string2))
{
string query2 = "DELETE ProductPrice";
SqlCommand cmd2 = newSqlCommand(query2, conn2);
conn2.Open();
cmd2.ExecuteNonQuery();
conn2.Close();
}
transConsist = true;
}
catch (SqlException ex)
{
//Write code to handle exceptions here.
}
conn1.Close();
}
transScope.Consistent = transConsist;
}

In the above code the second line of the code creates the TransactionScope object and all the other codes are written within the scope of the TransactionScope object in a block that comes under it. In this code we are using two connection objects namely conn1 and conn2 for two separate commands to be executed in two different databases. Once all the process in the try block is executed we are setting a Boolean variable transConsist to true. This variable is used to commit or rollback the transactions by setting up the TransactionSope object’s Consistent property in the last line of the code as “transScope.Consistent = transConsist;”.

Thus we find that using the TransactionScope object is a very easy method to deal with transactions in ADO.Net 2.0. The transaction promoted to a distributed transaction mode when we try to access another database for completing the transaction. This conversion to the distribution transaction mode of the local mode is done automatically. Hence is it effective and easy to use the TransactionScope object for transaction processing.