Transaction processing

With ASE ADO.NET Data Provider, you can use the AseTransaction 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, respectively. When the transaction is complete, you must create a new AseTransaction 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, ASE ADO.NET Data Provider operates in autocommit mode by default. There is an implicit Commit after each insert, update, or delete, and when an operation is completed, the change is made to the database. In this case, the changes cannot be rolled back.

You can choose to specify an isolation level when you begin the transaction. The isolation level applies to all commands executed within the transaction.

For more information about isolation levels, see the Adaptive Server Enterprise Performance and Tuning Guide.

The locks that ASE uses when you enter a Select statement depend on the transaction's isolation level.

The following example uses an AseTransaction 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.

1. Declare and initialize an AseConnection object:

   For C#:

   AseConnection conn = new AseConnection(
      c_connStr );
   

   For Visual Basic .NET:

   Dim conn As New AseConnection( _
      c_connStr )
   

2. Open the connection:

   For C#:

   conn.Open();
   

   For Visual Basic .NET:

   conn.Open()
   

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

   For C#:

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

   For Visual Basic .NET:

   Dim stmt As String = "update product " + _
      " set unit_price = 2000.00 " + _
      " where name = 'Tee shirt'"
   

4. Create an AseTransaction 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:

   For C#:

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

   For Visual Basic .NET:

   Dim trans As AseTransaction = _
      conn.BeginTransaction( _
      IsolationLevel.RepeatableRead )
   Dim cmd As New AseCommand( _
      stmt, conn, trans )
   Dim rows As Integer = cmd.ExecuteNonQuery()
   

5. Roll back the changes:

   For C#:

   trans.Rollback();
   

   For Visual Basic .NET:

   trans.Rollback()
   

   The AseTransaction object allows you to commit or roll back your changes to the database. If you do not use a transaction, ASE ADO.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:

   For C#:

   trans.Commit();
   

   For Visual Basic .NET:

   trans.Commit()
   

6. Close the AseConnection object:

   For C#:

   conn.Close();
   

   For Visual Basic .NET:

   conn.Close()