ASA Programming Guide
  Developing Applications with the .NET Data Provider

Transaction processing

With the Adaptive Server Anywhere .NET provider, you can use the AsaTransaction object to group statements together. Each transaction ends with a COMMIT or ROLLBACK, which either makes your changes to the database permanent or cancels all the operations in the transaction. Once the transaction is complete, you must create a new AsaTransaction object to make further changes. This behavior is different from ODBC and embedded SQL, where a transaction persists after you execute a COMMIT or ROLLBACK until the transaction is closed.

If you do not create a transaction, the Adaptive Server Anywhere .NET provider operates in autocommit mode by default. There is an implicit COMMIT after each insert, update, or delete, and once an operation is completed, the change is made to the database. In this case, the changes cannot be rolled back.

For more information about the AsaTransaction object, see AsaTransaction class.

Setting the isolation level for transactions 

The database isolation level is used by default for transactions. However, you may choose to specify the isolation level for a transaction using the IsolationLevel property when you begin the transaction. The isolation level applies to all commands executed within the transaction.

For more information about isolation levels, see Isolation levels and consistency.

The locks that Adaptive Server Anywhere uses when you enter a SELECT statement depend on the transaction's isolation level.

For more information about locking and isolation levels, see Locking during queries.

The following example uses an AsaTransaction object to issue and then roll back a SQL statement. The transaction uses isolation level 2 (RepeatableRead), which places a write lock on the row being modified so that no other database user can update the row.

To use an AsaTransaction object to issue a command

1. Declare and initialize an AsaConnection object.

   AsaConnection conn = new AsaConnection(
       "Data Source=ASA 9.0 Sample" );

2. Open the connection.

   conn.Open();

3. Issue a SQL statement to change the price of Tee shirts.

   string stmt = "update product set unit_price =
       2000.00 where name = 'Tee shirt'";

4. Create an AsaTransaction object to issue the SQL statement using a Command object.

   Using a transaction allows you to specify the isolation level. Isolation level 2 (RepeatableRead ) is used in this example so that another database user cannot update the row.

   AsaTransaction trans = conn.BeginTransaction(
       IsolationLevel.RepeatableRead );
   AsaCommand cmd = new AsaCommand( stmt, conn,
       trans );
   int rows = cmd.ExecuteNonQuery();

5. Roll back the changes.

   trans.Rollback();

   The AsaTransaction object allows you to commit or roll back your changes to the database. If you do not use a transaction, the .NET data provider operates in autocommit mode and you cannot roll back any changes that you make to the database. If you want to make the changes permanent, you would use the following:

   trans.Commit();

6. Close the AsaConnection object.

   conn.Close();