ADO.Net 2.0 Transaction Processing

Transactions processing is such an area and any improvement in that area is definitely going to make your application robust and stable. The transactions can be distributed or local. Implementing a series of task and committing the task if all the tasks were successful or else if any of the tasks fail the whole series of tasks is rolled back is called a transaction. This is how most of the related tasks are done with respect to a database application. It is also possible to write your transaction code in a stored procedure and then invoke the stored procedure. A developer can make use of the TransactionScope object while working with transactions.

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.

_______________________________________________________________________

_______________________________________________________________________

FREE Subscription

Subscribe to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.

Name:
Email:

Note : We never rent, trade, or sell my email lists to
anyone. We assure that your privacy is respected
and protected.

Visit .NET Programming Tutorial Homepage

______________________________________________________

Recommended Resource