The Connection Object - Connection State
(Page 2 of 15 )
One important point to note about connection pooling is that the connection state is reset when the connection is returned to the pool. This means that if you have set any properties for the connection, the values of these properties will return to their default values. So, even though pooling returns the same connection to you, you cannot rely upon the connection’s previous state.
Methods of the Connection Object Now let’s take a look at how to work with the Connection object. The remainder of this chapter looks at the methods, properties, collections, and events that this object makes available to us. Let’s begin with the methods.
The BeginTrans Method The BeginTrans method begins a new transaction.
Level = Connection.BeginTrans()
A transaction provides atomicity to a series of data changes to a recordset (or recordsets) within a connection, allowing all of the changes to take place at once, or not at all. Once a transaction has been started, any changes to a recordset attached to the Connection are cached until the transaction is either completed or abandoned. At that stage, all of the changes will be either written to the underlying data store (if the transaction is committed) or discarded (if the transaction is aborted).
The return value indicates the level of nested transactions. This will be 1 for a top-level transaction and will be incremented by 1 for each subsequent contained transaction. You can ignore this value if you don’t need to keep track of transaction levels.
Not all providers support transactions, and calling this method against a provider that does not support transactions will generate an error. To check that transactions are supported, you can check the Transaction DDL dynamic property of the connection’s Properties collection. For example:
intSupported = objConn.Properties("Transaction DDL")
If intSupported = DBPROPVAL_TC_ALL Then
' transactions are fully supported
objConn.BeginTrans
End If
DBPROPVAL_TC_ALL has a value of 8. The constants are explained in more detail in Appendix B, and the Properties collection in Appendix C.
For a good description of transactions, check out the MSDN article titled Microsoft SQL Server: An Overview of Transaction Processing Concepts and the MS DTC, available at http://msdn.microsoft.com/library/backgrnd/html/msdn_dtcwp.htm.
Nested Transactions Nested transactions allow you to have transactions within transactions, and allow you to segment your work in a more controlled manner. For example, consider the situation shown in this diagram of nested transactions:
Transaction A starts; this is the first transaction, and no changes made within this transaction will be visible outside of the transaction (unless dirty reads are being used, which allow you to read values before a transaction is committed). Then Transaction B starts, and the nesting level is now 2. While B is running, the changes in B are not visible to Transaction A. When Transaction B finishes, it either commits or rolls back; at this stage, A can see the changes made in B, but processes outside of Transaction A cannot. The same happens with C; its changes will not be visible to A until C commits or rolls back. Once A commits, all the changes in A, B, and C are visible to other processes. Note that if A rolls back its changes, it rolls back transactions B and C, irrespective of whether they have committed or not.
It’s possible to use connection attributes (set the Attributes property to include adXactCommitRetaining) to force transactions to start automatically on commit and rollback, and this can have serious consequences when nesting transactions. This is because every time you commit or rollback a transaction, a new one is automatically started. Imagine some code like this:
objConn.BeginTrans ' start first transaction
objConn.BeginTrans ' start nested transaction
' do some processing
objConn.CommitTrans ' commit nested transaction
If auto-transaction mode is in place, then as soon as this nested transaction is committed, another nested transaction is started. You don’t have a way to commit a transaction without it starting another, so you can never get back to the level 1 transaction.
You might never use nested transactions (or be able to for that matter-some providers, including the Provider for ODBC, don’t support them), but it’s good to be aware that this problem can arise.
The best way to build transactional systems is to utilize the facilities of MTS, where you don’t need to write any code to start or end transactions. MTS also allows for distributed transactions, allowing applications to be distributed across machines. |
See also the CommitTrans method and the RollbackTrans method.
 This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now. |
Next: The Cancel Method >>
More ASP.NET Articles
More By Apress Publishing
/ 19 
