Types of Transactions
Microsoft SQL Server Compact supports two types of transactions: explicit and autocommit.
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.
By using Visual Studio, the following SQL statements can be used to define explicit transactions:
Marks the starting point of an explicit transaction for a connection.
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.
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.
When you commit transactions by using the ADO.NET API, all open data readers and result sets within that transaction should be closed.
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.
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);
If these queries were sent as a batch to SQL Server, all queries would fail because they are parsed together. The syntax error prevents SQL Server from constructing an execution plan for the batch.
Higher-level transactions in which an operation is occurring neither roll back nor commit the transaction.