Controlling Transactions

Applications control transactions by specifying when a transaction starts and ends. You control transactions by using:

Important You can manage each transaction by using only one of the APIs. Using both APIs on the same transaction can lead to undefined results.

The application must be able to correctly handle errors that result when a transaction terminates before it completes.

Using SQL Statements

You can start and end transactions by using SQL statements.

You can start transactions in the Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) Database Engine as explicit or autocommit.

Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server Compact Edition. An autocommit transaction is started when the operation statement starts, and it is committed when the statement ends.

You can end transactions with either a COMMIT or a ROLLBACK statement.

COMMIT
A COMMIT statement guarantees that all the modifications of the transaction are made a permanent part of the database. A COMMIT statement also frees resources, such as locks, that are used by the transaction.
ROLLBACK
If an error occurs in a transaction, or if the user decides to cancel the transaction, then a ROLLBACK statement rolls back the transaction. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state that it was in at the start of the transaction. A ROLLBACK statement also frees resources held by the transaction.

Database APIs such as OLE DB and ADO .NET contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server Compact Edition application. For more information, see SQL Server Compact Edition .NET Programming, SQL Server Compact Edition Native Programming, and How to: Use Transactions with OLE DB (Programmatically).