| Author: R Sudha 05 Jul 2008 | Member Level: Gold | Rating:  Points: 6 |
SqlTransaction objects is the easiest way to group the execution of multiple SQL statements into one transaction.
To use this
You need a SqlConnection object that has been opened with its Open() method.
Call the SqlConnection.BeginTransaction() method, which returns a SqlTransaction object.
Execute any number of SQL statements, with calls such as SqlCommand.ExecuteReader() or SqlCommand.ExecuteNonQuery().
When you decide whether everything was successful, call either the SqlTransaction.Commit() or the SqlTransaction.Abort() method.
Example is here
public void Test(string strConnString) {
SqlConnection conn = new SqlConnection(strConnString);
conn.Open();
SqlCommand myCommand = new SqlCommand();
SqlTransaction myTrans;
// Start the transaction here
myTrans = myConnection.BeginTransaction();
// Assign the connection object to command
// Also assign our transaction object to the command
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
// 1. SQL command to tp update a table
myCommand.CommandText = "Update .........";
myCommand.ExecuteNonQuery();
// 2. Do some processing here
myCommand.CommandText = "Update .... ";
myCommand.ExecuteNonQuery();
myTrans.Commit();
} catch(Exception e) {
myTrans.Rollback();
Console.WriteLine("Error: {1}", e.Message);
}
finally
{
// Close the connection.
myConnection.Close();
}
}
The kind of problems u might face while using this depends on the transactions performed. Sometime timeout error may occur which depends on various factors. Sometimes if the sql statement cannot be executed due to some problems the error may occur. Each case is different and should be analysed when it happens. It is important to find out what is causing the error. So the solution depends on that.
|