cancel
Showing results for 
Search instead for 
Did you mean: 

SATransaction with using statement

2,775

Hello,

We would like to use the SATransaction in a using statement. But in case of an exception, the transaction is not rolled back and the transaction is still open. This prevent us from opening a second transaction.

In Microsoft SQL Server the Dispose function of a SqlTransaction makes an automatic rollback.

Example: Update the surname of contact 1 and add a "x" after the surname. The Exception should rollback the update because it is in a transaction.

  using (IDbConnection connection = new SAConnection("DSN=SQL Anywhere 12 Demo")) {
    connection.Open();

    String oldValue = null;
    using (IDbCommand command = connection.CreateCommand()) {
      command.CommandText = "SELECT Surname FROM Contacts WHERE ID = 1";
      oldValue = command.ExecuteScalar().ToString();
    }

    try {
      using (IDbTransaction transaction = connection.BeginTransaction()) {
        using (IDbCommand command = connection.CreateCommand()) {
          command.Transaction = transaction;
          command.CommandText = String.Format("UPDATE Contacts SET Surname = '{0}' " +
            "WHERE Id = 1", oldValue + "x");
          command.ExecuteNonQuery();

          throw new Exception();
        }
        transaction.Commit(); // never reached in this example
      }
    }
    catch (Exception) {
      // go ahead
      // but transaction isn't rollbacked
    }

    // -> the surname of contact 1 is now "Hildebrandx" and the transaction is still open

    using (IDbTransaction transaction = connection.BeginTransaction()) {
      // It's not possible to open a new transaction because the first
      // transaction isn't commited or rolled back
    }
  }

I think this is a bug. Of course I can put this in a try/catch but SATransaction is IDisposable so a using statment is allowed.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

We've modified the ADO.NET provider to fix this problem in CR #749295. The next EBF should have the fix (11.0.1.3057, 12.0.1.3996, 16.0.0.1709 and higher).

0 Kudos

Thats great! Thank you

MCMartin
Participant
0 Kudos

You can accept this as the answer, so everyone sees the state of your question

Answers (0)