Tuesday, March 25, 2008

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)



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.


It permits transaction to read data within the database, even data that have not yet been committed by another transaction.


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.


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
SqlTransaction nTran = myCon.BeginTransaction();
//some code for work

No comments:

Post a Comment