Scope of transaction includes activities such as retrieving data from SQLServer, reading message from the message queue & writing to the database. Microsoft DTC, which implements two phases commits protocols that ensure that the transaction outcome is consistent across all data resources involved in a transaction.
Database transaction: Invoking a stored procedure that wraps required operation with BEGIN TRANSACTION & COMMIT/ROLLBACK TRANSACTION gives you to run transaction in a single round trip to the database server.
There are two types of transaction that you will deal with Local Implicit and Distributed Implicit A local implicit transaction is the transaction that simply talks to only one database. A distributed transaction talks to multiple databases at the same time.
The transaction namespace is smart and will automatically use the appropriate transaction broker based on what type of database you are using, if you are talking to SQL Server 2000 databases, the transactions will be handled through the DTC on the client machine. When SQL Server 2005 is used, the transactions are handled directly by the SQL server.
Creating a transaction is easy, simply create a TransactionScope with a using statement and include your code to execute your sql statements. When the transaction is complete, tell the TransactionScope, that the commit is ready by setting the Consistent property to true
The managed functionality of COM+ is exposed in the .net via assembly name called as 'System.EnterpriseServices' which is part of framework class library, but still you have to add a reference from the project properties.
The TransactionScope class in the System.Transactions namespace enables developer to programmatically wrap a series of statements within the scope of a transaction and includes support for complex transactions that involve multiple sources, such as two different databases or even heterogeneous type of data stores such as Microsoft SQL server, and Oracle database & a web services. TransactionScope class uses Microsoft Distributed Transaction Coordinator (MSDTC) the configuration and implementation make it rather advance topic and beyond the scope of this tutorials.
Steps for TransactionScope:
-
Reference to the system.Transactions assembly.
-
Import the system.Transactions namespace in your application.
-
If you want to specify nesting behavior for transaction declare TransactionScopeOption variable and assign it a suitable value.
-
If you want to specify the isolation level or timeout for the transaction, create a TransactionOptions instance and set it's isolation level and TimeOut properties.
-
Create new TransactionScope Object.
-
Pass a TransactionScope object variable and TransactionOptions object into the constructor, if appropriate.
-
Open connections to each database that you need to update, and perform update operations as required by application, if all updates succeed, call the Complete method on the TransactionScope object close the using block.
Using (TransactionScope transScope = new TransactionScope())
{
string conString1 = WebConfigurationManager.ConnectionString DB1].ConnectionString;
string conString2 = WebConfigurationManager.ConnectionString[DB2].ConnectionString;
using(SqlConnection Sqlcon = new SqlConnection(conString1))
{
SqlCommand SqlCmd = Sqlcon.CreateCommand();
cmd.CommandText = "INSERT INTO Tbl_Categories(catId, catName) Values1,'Electronics')";
SqlCon.Open();
SqlComd.ExecuteNonQuery();
SqlCon.Close();
}
using SqlConnection SqlCon1 = new SqlConnection(conString2);
{
SqlCommand SqlCmd2 = SqlCon1.CreateCommand();
SqlCmd2.CommandText = "INSERT INTO Tbl_Products(ProdId, catId, ProdName, Qty) VALUES(1,1,'Samsung Monitor')";
SqlCon2.Open();
SqlCmd2.ExecuteNonQuery();
SqlCon2.Close();
}
transScope.Complete();
}
If all the operations in the transaction succeed then we should call the complete method on the TransactionScope object to indicate that the transaction completed successfully and thus the transaction manager commits the transaction.