Transactions provide a mechanism to group a series of changes to a database in one atomic operation. After changes have been made to the database, these changes can be committed or canceled. Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) supports transactions and exposes the ability to use them directly through both Microsoft ActiveX® Data Objects for Windows CE 3.1 (ADOCE) and the OLE DB layer. Transactions can also be nested. Within a larger transaction, it is possible to have atomic units of work that are individually committed or canceled.
The SQL Server CE transaction model differs from that of Microsoft SQL Server. When you develop applications to run on both, it is important to keep the following information in mind:
- SQL Server CE supports only single-phase commit transactions.
- In SQL Server CE, transactions can be nested up to five levels deep.
- The transaction isolation model exposed in SQL Server CE is "Read Committed," which means SQL Server CE can isolate users from uncommitted changes in a database.
- SQL Server CE holds an exclusive lock on a table that has been altered in a transaction until that transaction completes. Additional attempts to open the table or perform modifications on it will fail until the lock in the transaction is released. Consequently, it is not generally recommended that DDL operations be performed inside transactions because of the potential for locking conflicts to occur.
- Transactions also influence the way cursors function. In SQL Server CE, if a cursor is opened within a transaction, the cursor exists within the scope of that transaction. If the transaction is canceled, the cursor ceases to exist. To continue using a cursor after a canceled transaction, the cursor should be created outside the scope of the transaction. Within the context of ADOCE and OLE DB for SQL Server CE, this means that the recordset would become a zombie and must be closed. If the transaction commits, the cursor still exists and is fully functional.
For more information about using transactions with the OLE DB Provider for SQL Server CE, see Transactions.
The following Microsoft Visual Basic® example shows the use of transactions using ADOCE.
Sub TransactionExample() Dim cn As ADOCE.Connection Dim rs As ADOCE.Recordset Set cn = CreateObject("ADOCE.Connection.3.1") Set rs = CreateObject("ADOCE.Recordset.3.1") 'Open a connection to a SQL Server CE database. cn.Open "provider=Microsoft.SQLServer.OLEDB.CE.2.0;data source=\ssce.sdf;" ' Begin a transaction. cn.BeginTrans ' Commit that transaction. cn.CommitTrans ' Demonstrate that opening a table outside of the transaction ' avoids potential problems with DBPROP_ABORTPRESERVE. rs.Open "helloworld", cn, adOpenDynamic, adLockOptimistic cn.BeginTrans cn.RollbackTrans rs.MoveFirst ' recordset is still functional rs.Close ' Opening a rowset within a transaction that is canceled ' will zombie the rowset. This can be accessed in ADOCE ' through the "Preserve on Abort" property. cn.BeginTrans rs.Open "helloworld", cn, adOpenDynamic, adLockOptimistic Debug.Print rs.Properties("Preserve on Abort") Debug.Print rs.State cn.RollbackTrans rs.MoveFirst End Sub