Transaction

TRANSACTIONS
A transaction symbolizes code or a set of components or procedures which must be executed as a unit. All the methods must execute successfully or the complete unit fails. A transaction can be described to cover the ACID properties for any applications
What are the ACID Properties?
Atomicity
Consistency
Isolation
Durability
e..g. which will transfer a specified amount from one account to another

transaction should be rolled back (Cancelled) if :
There are insufficient funds in the From Account to cover the transfer.

Either of the SQL statements
for marking credit or debit in the To and From accounts results in an error.
execute the two SQL queries one to subtract the amount from the From Account and one to add the same amount to the balance in the To Account.
We start the transaction after we have created the data objects
.The transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits.
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction = null;

try
{
// BeginTransaction() Requires Open Connection
connection.Open();

transaction = connection.BeginTransaction();

// Assign Transaction to Command
command.Transaction = transaction;

// Execute 1st Command
command.CommandText = "Insert ...";
command.ExecuteNonQuery();

// Execute 2nd Command
command.CommandText = "Update...";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}


How can we perform transactions in .NET?
The most common sequence of steps that would be performed while developing a transactional application is as follows:
√ Open a database connection using the Open method of the connection object.
√ Begin a transaction using the Begin Transaction method of the connection object. This method provides us with a transaction object that we will use later to commit or rollback the transaction. Note that changes caused by any queries executed before calling the Begin Transaction method will be committed to the database immediately after they execute. Set the Transaction property of the command object to the above mentioned transaction object.
√ Execute the SQL commands using the command object. We may use one or more command objects for this purpose, as long as the Transaction property of all the objects is set to a valid transaction object. √ Commit or roll back the transaction using the Commit or Rollback method of the transaction object.
√ Close the database connection.