Using an AseTransaction Object to Issue a Command

Issue a command using an AseTransaction object.

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, Adaptive Server ADO.NET Data Provider operates in autocommit mode and you cannot roll back any changes that you make to the database. To make the changes permanent, use:

   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()