Transaction Scope
The TransactionScope class provides a simple way to mark a block of code as participating in a transaction. A transaction scope can select and manage the ambient transaction automatically. When you instantiate a TransactionScope by using the new statement, the transaction manager determines which transaction to participate in. If no exception occurs in the transaction scope (that is, between the initialization of the TransactionScope object and the calling of its Dispose method), the transaction in which the scope participates can continue. If an exception does occur in the transaction scope, the transaction in which it participates will be rolled back. When the application finishes all work it wants to perform in a transaction, the Complete method is called one time. This informs the transaction manager that it is acceptable to commit the transaction. If the Complete method is not called, the transaction ends. For more information about transaction scope, see the MSDN documentation.
Transaction Scope Implementation in .NET Framework
The System.Transactions namespace is part of the Microsoft .NET Framework version 2.0, versions 3.0 and 3.5. It provides a transaction framework that is fully integrated with ADO.NET and SQL Server common language runtime (CLR) integration. The System.Transactions.transactionscope class makes a code block transactional by implicitly enlisting connections in a distributed transaction. The Complete method can be called at the end of the code block marked by the TransactionScope. The transaction is discontinued if the Complete method is not called before a call is made to the Dispose method. If an exception is thrown, the transaction is considered to be discontinued.
For more information, see https://msdn2.microsoft.com/en-us/library/ms172070(VS.80). aspx.
Limitations of System.Transactions
The System.Transactions namespace is not supported by .NET Compact Framework 2.0. Therefore, implementation will be for the Windows desktop operating system only and will correspond with the .NET Framework 2.0, .NET Framework 3.0, or .NET Framework 3.5.
Note that if there is a timeout, the System.Transactions infrastructure will call Rollback from a separate thread. The main thread will not know about a rollback occurring in the separate thread. The long transactions might see a non-deterministic behavior and partial commit scenarios. To resolve this, increase the timespan of the transaction scope object when you create the object.
In a transaction scope, only one SqlCeConnection object can be enlisted that too if no other transaction manager is already enlisted into the transaction scope.
If a connection is opened outside a transaction scope and it has to be enlisted in an existing transaction scope, it can be done by using the method EnlistTransaction.
TransactionScope Implementation
SQL Server Compact 3.5 enlists as a resource to the System.Transactions infrastructure.
By default, when multiple commands are opened on the enlisted connection in a transaction scope, they are enlisted to the current transaction context. It is also possible to open a connection which is not enlisted to a transaction scope. This creates commands that are not enlisted. The default transaction type is serializable for SQL Server Compact 3.5 transactions with transaction scope.
Because distributed transactions are not supported by SQL Server Compact 3.5, two or more connections cannot be enlisted into the same transaction scope, or into a nested transaction scope that shares the same ambient transaction scope.
An explicit transaction for an enlisted connection inside a transaction scope is not allowed.
Implicitly enlisting connections is not supported. To enlist in a transaction scope, you can do the following:
- Open a connection in a transaction scope.
- Or, if the connection is already opened, call EnlistTransaction method on the connection object.
Limitations of SQL Server Compact
The differences between SQL Server Compact 3.5 and SQL Server with regard to transaction scope are as follows:
- Distributed transactions are not supported in SQL Server Compact 3.5. Therefore, a local transaction will not be automatically promoted to a fully distributable transaction.
- Parallel transactions are supported by SQL Server Compact 3.5, even for multiple active result sets but parallel transactions are not supported by SQL Server.
TransactionScope Example 1
The following example shows how to use TransactionScope to enlist and then commit the transaction.
using (TransactionScope transScope = new TransactionScope())
{
using (SqlCeConnection connection1 = new
SqlCeConnection(connectString1))
{
/* Opening connection1 automatically enlists it in the
TransactionScope as a lightweight transaction. */
connection1.Open();
// Do work in the connection.
}
// The Complete method commits the transaction.
transScope.Complete();
}
TransactionScope Example 2
The following example shows how to use TransactionScope to create two tables in the database.
static void Setup(String strDbPath)
{
/* Delete the database file if it already exists. We will create a new one. */
if (File.Exists(strDbPath))
{
File.Delete(strDbPath);
}
// Create a new database.
SqlCeEngine engine = new SqlCeEngine();
engine.LocalConnectionString = @"Data source = " + strDbPath;
engine.CreateDatabase();
engine.Dispose();
}
/* This function creates two tables in the specified database. Before creating the tables, it re-creates the database.
These tables are created in a TransactionScope, which means that either both of them will be created or not created at all. */
static void CreateTablesInTransaction(String strDbPath)
{
/* Create the connection string. In order to have the connection enlisted into the TransactionScope, the Enlist property in the connection string must be explicitly set to true. */
String strConn = @"Data source = " + strDbPath + ";Enlist=true";
SqlCeConnection conn = new SqlCeConnection(strConn);
try
{
Setup(strDbPath); // Create a new database for our tables.
using (TransactionScope scope = new TransactionScope())
{
/* To enlist a connection into a TransactinScope, specify 'Enlist=true' in its connection string and open the connection in the scope of that TransactionScope object. */
conn.Open();
// Create the tables.
SqlCeCommand command = conn.CreateCommand();
command.CommandText = @"create table t1(col1 int)";
command.ExecuteNonQuery();
command.CommandText = @"create table t2(col1 int)";
command.ExecuteNonQuery();
/* If this statement is executed and the TransactionScope has not timed out, t1 and t2 will be created in the specified database. */
scope.Complete();
}
}
catch (SqlCeException e)
{
Console.WriteLine(e.Message);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
TransactionScope Example 3
The following example shows how values are inserted in two tables in a TransactionScope.
/* This function assumes that tables t1(col1 int) and t2(col1 int) are already created in the specified database. The condition for the following function is this:
If INSERTs into the first table succeed, then INSERT into the second table. However, if the INSERTs into the second table fail, roll back the inserts in the second table but do not roll back the inserts in the first table. Although this can also be done by way of regular transactions, this function demonstrates how to do it using TransactionScope objects. */
static void CreateTableAndInsertValues(String strDbPath)
{
/* Create the connection string. To have the connection enlisted into the TransactionScope, the Enlist property in the connection string must be explicitly set to true. */
String strConn = @"Data source = " + strDbPath + ";Enlist=true";
SqlCeConnection conn1 = new SqlCeConnection(strConn);
SqlCeConnection conn2 = new SqlCeConnection(strConn);
try
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
conn1.Open();
SqlCeCommand command1 = conn1.CreateCommand();
command1.CommandText = @"insert into t1(col1) values(1)";
command1.ExecuteNonQuery();
command1.CommandText = @"insert into t1(col1) values(2)";
command1.ExecuteNonQuery();
/* If this statement is executed and the TransactionScope has not timed out, two records will be inserted into table 1. */
scope.Complete();
try
{
using (TransactionScope scopeInner = new TransactionScope(TransactionScopeOption.RequiresNew))
{
conn2.Open();
SqlCeCommand command2 = conn2.CreateCommand();
command2.CommandText = @"insert into t2(col1) values(1)";
command2.ExecuteNonQuery();
command2.CommandText = @"insert into t2(col1) values(2)";
command2.ExecuteNonQuery();
/* If this statement is run and the TransactionScope has not timed out, two records will be inserted into table 2. */
scopeInner.Complete();
}
}
catch (SqlCeException e)
{
Console.WriteLine(@"Exception in Inner block: " + e.Message);
}
}
}
catch (SqlCeException e)
{
Console.WriteLine(@"Exception in Outer block: " + e.Message);
}
finally
{
// Close both the connections.
if (conn1.State != System.Data.ConnectionState.Closed)
{
conn1.Close();
}
if (conn2.State != System.Data.ConnectionState.Closed)
{
conn2.Close();
}
conn1.Dispose();
conn2.Dispose();
}
}