Working with Transactions

Whenever you have to update more than one table or destinations then it must have to sure that the operation done successfully because some time it has been seen that one table updated but due to some error another(s) not. To overcome this problem you have Transactions.

In ADO.Net transactions are initiated by calling BeginTransaction() methods on the database connection object.

Transaction isolation levels

Isolation Level(s)	Description
ReadCommitted	Its default for SQL Server. It ensures that data written by one transaction will only be accessible in a second transaction after the first transaction commits.
ReadUnCommitted	It permits transaction to read data within the database, even data that have not yet been committed by another transaction.
RepeatableRead	It extends the ReadCommitted level, ensures that if the same statement issued within the transaction, regardless of other potential updates made to the database, the same data will always be returned.
Serializable	It is the most exclusive transaction level, which in effect serializes access to data within the database. With this level, phantom rows can never show up, so a SQ statement issued within a serializable transaction will always retrieve the same data.

Bellow is the code snippet to show transaction in action:

string myConStr = "server=(local); integrated security=SSPI;database=HRnPAYROLL";

using (SqlConnection myCon = new SqlConnection(myConStr))
{
//Open connection object
myCon.Open();
SqlTransaction nTran = myCon.BeginTransaction();
//some code for work
nTran.Commit();
}