Using Transaction In ADO.NET

Hi,

In most of the cases we need to execute a number of statements together.

Best example would be a bank transaction in which we are debiting certain amount from one of the user’s account and crediting the same in someone else’s account. So in this case the entire thing should run or nothing should run.

We have in our .NET framework various transaction class one for each .NET-managed provider i.e.

OracleTransaction, OleDbTransaction, SqlTransaction etc.

The most basic way we can use the transaction class is the following way

SqlTransaction myTransaction;
SqlCommand myCommand1 = new SqlCommand();
myCommand1.CommandText = “some command”;
SqlCommand myCommand2 = new SqlCommand();
myCommand2.CommandText = “some command”;
using(SqlConnection myConn = new SqlConnection(connectionString))
{
myConn.Open();

myTransaction = myConn.BeginTransaction();

myCommand1.Connection = myConn;
myCommand1.Transaction = myTransaction;

myCommand2.Connection = myConn;
myCommand2.Transaction = myTransaction;

try
{
myCommand1.ExecuteNonQuery();
myCommand2.ExecuteNonQuery();
myTransaction.Commit();

}
catch
{
myTransaction.Rollback();
}
finally
{
myConn.Close();
}
}

Bye