ADO TransactionScope Usage Instructions (reprint)

TransactionScope is the. Net Framework 2.0 lag and adds a new namespace. Its purpose is to provide a "lightweight" [difference from: sqltransaction] thing for database access. You must add a reference to System.Transactions.dll before you use it.

The following code is a transaction that is being created, and the transaction itself encapsulates multiple database queries. As soon as any one of the SqlCommand objects throws an exception, the program flow control jumps out of the TransactionScope using statement block, and then TransactionScope frees itself and rolls back the transaction. Because this code uses a using statement, both the SqlConnection object and the TransactionScope object are automatically called Dispose (). So, with just a few lines of code, you can build a transactional model that handles exceptions, cleans itself after execution, and manages the commit or rollback of a command.

//Create TransactionScopeusing(TransactionScope Tscope =NewTransactionScope ()) {    using(SqlConnection cn2005 =NewSqlConnection (someSql2005)) {SqlCommand cmd=NewSqlCommand (Sqlupdate, cn2005); cn2005.        Open (); Cmd.    ExecuteNonQuery (); }    using(SqlConnection cn2005 =NewSqlConnection (anotherSql2005)) {SqlCommand cmd=NewSqlCommand (Sqldelete, cn2005); cn2005.        Open (); Cmd.    ExecuteNonQuery (); } tscope.complete ();} 

Connection string keyword (Enlist)

The SqlConnection.ConnectionString property supports the keyword Enlist, which indicates whether System.Data.SqlClient will detect the transaction context and automatically enlist the connection in the distributed transaction. If enlist=true, the connection is automatically enlisted in the current transaction context of the open thread. If the enlist=false,sqlclient connection does not interact with the distributed transaction. The default value of Enlist is true. If Enlist is not specified in the connection string, if one is detected when the connection is opened, the connection is automatically enlisted in the distributed transaction.

server= (local) sql2005;database=northwind;integrated Security=sspi;auto-enlist=false

In the example above, we used the default settings for TransactionScope. TransactionScope has three modes, transactionscopeoptions description

• Required: If a transaction already exists, the transaction scope will join the existing transaction. Otherwise, it will create its own transaction.
• RequiresNew: This transaction scope will create its own transaction.
• Suppress: If it is within the scope of the currently active transaction, the transaction scope will neither join the ambience transaction (ambient transaction) nor create its own transaction. This option is available when part of the code needs to remain outside the transaction.

You can see whether a transaction scope exists at any point in the code, by looking at the System.Transactions.Transaction.Current property.        If this property is "null", the current transaction does not exist. To change the default settings for the TransactionScope class, you can create a Transactionoptions object and then set the isolation level and the transaction time-out on the TransactionScope object through it. The Transactionoptions class has a IsolationLevel property that allows you to change the isolation level, for example, from the default serializable (Serializable) to readcommitted, or even to SQL Server The new Snapshot (Snapshot) level that was introduced in 2005. (Keep in mind that isolation levels are just a recommendation.) Most database engines try to use the recommended isolation level, but may also choose a different level.        In addition, the Transactionoptions class has a timeout property that can be used to change the time-out (the default setting is 1 minutes). The default TransactionScope object and its default constructor are used in the following code. That is, its isolation level is set to Serializable (Serializable), the transaction time-out is 1 minutes, and the transactionscopeoptions is set to Required.

Transactionoptions topt=Newtransactionoptions ();//Set Transactionoptions modeTopt.isolationlevel=isolationlevel.readcommitted;//set the time-out interval to 2 minutes and default to 60 secondstopt.timeout=NewTimeSpan (0,2,0);stringcnstring= configurationmanager.connectionstrings["Sql2005dbserver"]. ConnectionString;using(TransactionScope tscope=NewTransactionScope (transactionscopeoption.requiresnew, topt)) {    using(SqlConnection cn2005=NewSqlConnection (cnstring)) {SqlCommand cmd=NewSqlCommand (UPDATESQL1, cn2005); cn2005.        Open (); Cmd.    ExecuteNonQuery (); } tscope.complete ();} 

Nested apps
As the following code, suppose Method1 creates a TransactionScope, executes a command against a database, and then calls METHOD2. METHOD2 creates a TransactionScope of its own and executes another command against one database.

Private voidMethod1 () {using(TransactionScope ts =NewTransactionScope (transactionscopeoption.required)) {        using(SqlConnection cn2005 =NewSqlConnection ()) {SqlCommand cmd=NewSqlCommand (UPDATESQL1, cn2005); cn2005.            Open (); Cmd.        ExecuteNonQuery ();        } Method2 ();    Ts.complete (); }}Private voidMethod2 () {using(TransactionScope ts =NewTransactionScope (transactionscopeoption.requiresnew)) {        using(SqlConnection cn2005 =NewSqlConnection ()) {SqlCommand cmd=NewSqlCommand (UPDATESQL2, cn2005); cn2005.            Open (); Cmd.        ExecuteNonQuery ();    } ts.complete (); }} 

In addition, when using TransactionScope must pay attention to SqlConnection open must be in the TransactionScope using statement block, otherwise TransactionScope will be invalid, see the following link to the article.

The correct usage of TransactionScope

The original link in this article

ADO TransactionScope Usage Instructions (reprint)