Types of Transactions

Microsoft SQL Server Compact supports two types of transactions: explicit and autocommit.

Explicit Transactions

An explicit transaction is one in which you explicitly define both the start and end of the transaction. This can be specified by using either SQL statements or database API functions.

SQL Statements

By using Visual Studio, the following SQL statements can be used to define explicit transactions:

• BEGIN TRANSACTION

  Marks the starting point of an explicit transaction for a connection.

• COMMIT TRANSACTION

  Ends a transaction successfully if no errors are encountered. All data modified by the transaction becomes a permanent part of the database. Resources held by the transaction are freed.

• ROLLBACK TRANSACTION

  Erases a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.

ADO.NET and OLE DB

You can also use explicit transactions in ADO.NET and OLE DB.

In ADO.NET, use the BeginTransaction method on a SqlCeConnection object to start an explicit transaction. To end the transaction, call the Commit or Rollback methods of the SqlCeTransaction object.

In OLE DB, call the ITransactionLocal::StartTransaction method to start a transaction. Call either the method ITransaction::Commit or ITransaction::Abort, with fRetaining set to FALSE to end the transaction without automatically starting another transaction. Note that you can create multiple transactions on a single ADO.NET connection and assign them to individual commands.

Autocommit Transactions

Autocommit mode is the default transaction management mode of SQL Server Compact. Every SQL statement is committed or rolled back when it finishes. A SQL Server Compact connection operates in autocommit mode whenever this default mode has not been overridden by explicit transactions. Autocommit mode is also the default mode for ADO.NET and OLE DB.

A SQL Server Compact connection operates in autocommit mode until a BEGIN TRANSACTION statement starts an explicit transaction. When the explicit transaction is committed or rolled back, SQL Server Compact returns to autocommit mode.

Compile and Run-Time Errors

Unlike SQL Server, SQL Server Compact does not process statements in batches. SQL Server Compact processes the statements one at a time and executes each statement individually. If a particular statement returns an error, that does not affect any other statements that are included in the batch. For example, if Visual Studio is used to execute the following set of queries in SQL Server Compact, the first two queries would succeed but the third would fail because of the syntax error.

CREATE TABLE TestData (col int);

INSERT INTO TestData VALUES (1);

INSERTT INTO TestData VALUES (1);

Higher-level transactions in which an operation is occurring neither roll back nor commit the transaction.

See Also

Concepts

Transactions (SQL Server Compact)

Controlling Transactions

Other Resources

Transactions and Connectivity