Transactions (SQL Server Compact Edition)

Transactions provide a mechanism for grouping a series of database changes into one logical operation. After changes are made to the database, these changes can be committed or canceled as a single unit. Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) supports transactions. You can use transactions in multiple ways:

  • Programmatically by using ADO .NET or OLE DB API functions
  • By using SQL Server Compact Edition Query Analyzer on a device
  • By using SQL Server Management Studio on a desktop

Transactions ensure that the properties of atomicity, consistency, isolation, and durability (ACID) are followed so that data is correctly committed to the database. For more information about the ACID properties of transactions, see "Transactions" in SQL Server Books Online.

When you develop applications to run on SQL Server Compact Edition, note the following differences between SQL Server Compact Edition and Microsoft SQL Server 2005:

  • SQL Server Compact Edition does not support nesting of transactions but it does support parallel transactions in ADO .NET.
  • In SQL Server Compact Edition, if a cursor is opened within a transaction, the cursor exists within the scope of that transaction. If the transaction is aborted, the cursor ceases to exist. To continue using a cursor after a canceled transaction, create the cursor outside the scope of the transaction. Within the context of OLE DB for SQL Server Compact Edition, this means that the recordset will not be valid, and must be closed. If the transaction commits, the cursor still exists and is fully functional.
  • SQL Server Compact Edition does not support distributed transactions.
  • SQL Server Compact Edition does not support save points. Save points permit an application to roll back part of a transaction if a minor error is encountered. The application must still commit or roll back the full transaction when it is complete.

Topic Description

Controlling Transactions

Describes how applications control when a transaction begins and ends.

Types of Transactions

Describes the two types of transactions supported by: SQL Server 2005 Compact Edition: explicit and autocommit.

Transactions and Connectivity

Desscribes the two methods of connecting to and exchanging data with a Microsoft SQL Server database: remote data access (RDA) and merge replication.

Community Additions