ADO.Net

What’s difference between “Optimistic” and “Pessimistic” locking ?
In pessimistic locking when user wants to update data it locks the record and till then no
one can update data. Other user’s can only view the data when there is pessimistic locking.
In optimistic locking multiple users can open the same record for updating, thus increase
maximum concurrency. Record is only locked when updating the record. This is the most
preferred way of locking practically. Now a days browser based application is very common
and having pessimistic locking is not a practical solution.


How can we perform transactions in .NET?

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

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.

most common example for the transactions is Debit and Credit from the Accounts

PROGRAM

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();
}