Types of Transactions
Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) 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 SQL Server Management 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 ITransaction::Commit or ITransaction::Abort method with fRetaining set to FALSE to end the transaction without automatically starting another transaction.
Note
You can create multiple transactions on a single ADO .NET connection and assign them to individual commands.
Important
When you commit transactions by using the ADO .NET API, all open data readers and result sets within that transaction should be closed.
Autocommit Transactions
Autocommit mode is the default transaction management mode of SQL Server Compact Edition. Every SQL statement is committed or rolled back when it finishes. A SQL Server Compact Edition 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 Edition 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 Edition returns to autocommit mode.
Compile and Run-time Errors
Unlike Microsoft SQL Server, SQL Server Compact Edition does not process statements in batches. SQL Server Compact Edition processes the statements one at a time and executes each statement individually. If a particular statement returns an error, that does not affect the state of any other statements that are included in that batch. For example, if SQL Server Management Studio is used to execute the following set of queries in SQL Server Compact Edition, 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);
Note
If these queries were sent as a batch to SQL Server, all would fail because the queries would be parsed together. The syntax error would result in SQL Server not being able to construct an execution plan for the whole batch.
Higher level transactions in which an operation is occurring neither roll back nor commit the transaction.
See Also
Concepts
Transactions (SQL Server Compact Edition)
Controlling Transactions
Transactions and Connectivity