ADO.NET Transactions

A transaction is a group SQL statements to be executed as one unit to modify the state of database, a transaction requires a database connection and a transaction object. The crux of utilizing transactions with SQL Server and ADO.NET is the SqlTransaction class. This class name varies depending upon the database platform utilized. For instance, the transaction class for OLEDB databases is OleDbTransaction.

The System.Data.SqlClient namespace contains the SqlTransaction class. There are two properties in the class:
· Connection: Indicates the SqlConnection object associated with the transaction.
· IsolationLevel: Specifies the transaction's IsolationLevel.

The IsolationLevel property is an Enumeration object with the following members:
· Chaos: The pending changes from more highly isolated transactions cannot be overwritten.
· ReadCommitted: Shared locks are held while the data is read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.
· ReadUncommitted: A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
· RepeatableRead: Locks are placed on all data that is used in a query, preventing other users from updating the data. This prevents nonrepeatable reads, though phantom rows are still possible.
· Serializable: A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

The IsolationLevel allows a level of record locking, The SqlTransaction object provides methods as well. You may use the following methods to work with the transaction:
· Commit: The database transaction is committed.
· Rollback: A database transaction is rolled back from a pending state. A transaction may not be rolled back once it's committed.
· Save: Creates a savepoint in the transaction that can be used to roll back a portion of the transaction, and specifies the savepoint name.

Example on SqlTransaction:
SqlConnection sqlConn = new SqlConnection(ConnectionString);
cmd1 = new SqlCommand();
cmd2=new SqlCommand();
cmd1.Connection = sqlConn;
cmd2.Connection = sqlConn;
cmd1.CommandText = "insert into Employee(EmpName,EmpSalary,deptid) values('A',1000,9)";
cmd2.CommandText = "Update Employee set EmpSalary=EmpSalary + 1000 where EmpName='A'"; // for commit
//cmd2.CommandText = "Update Employee set EmpSalary=EmpSalary = 1000 where EmpName='A'"; // for Rollback
SqlTransaction SqlTran;
sqlConn.Open();
SqlTran = sqlConn.BeginTransaction();
cmd1.Transaction = SqlTran;
cmd2.Transaction = SqlTran;
try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery(); // if both command excuted sucessfully then will commit if any of command fails that is attched to sqltransaction then that will be roll backed.
SqlTran.Commit();
MessageBox.Show("Transcation Committed Sucessfully");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
SqlTran.Rollback();
MessageBox.Show("Transaction Rollbacked");
}
finally
{
sqlConn.Close();
}
Note: Once a transaction has begin over a connection, a command without its transaction property set cannot be executed in that connection unless either the transaction is committed or roll backed.
Transactions help the database in satisfying all the ACID (Atomic, Consistent, Isolated, and Durable). Transaction processing has improved a lot in ADO.NET 2.0.