Transactions (SQL Server Compact)

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 Compact supports transactions. You can use transactions in multiple ways:

  • Programmatically by using ADO .NET or OLE DB API functions

  • By using Visual Studio 2010 SP1 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, note the following differences between SQL Server Compact and Microsoft SQL Server 2008 R2:

  • SQL Server Compact does not support nesting of transactions but it does support parallel transactions in ADO.NET.

  • In SQL Server Compact, 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, 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 does not support distributed transactions.

  • SQL Server Compact 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.

In This Section



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 Compact: explicit and autocommit.

Transaction Scope

Describes implementation and limitations of the TransactionScope class.