Share via


Controlling Transactions

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

  • SQL statements from within SQL Server Management Studio.
  • Database API functions ADO .NET and OLE DB.

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.

Starting Transactions

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.

Ending Transactions

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.

Using APIs

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).

See Also

Concepts

Transactions (SQL Server Compact Edition)

Other Resources

SQL Reference (SQL Server Compact Edition)

Help and Information

Getting SQL Server Compact Edition Assistance