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.

asked 21 Oct '13, 12:50

Stefan's gravatar image

Stefan
81238
accept rate: 0%


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

permanent link

answered 21 Oct '13, 16:52

Minghai%20Chang's gravatar image

Minghai Chang
12633
accept rate: 20%

edited 22 Oct '13, 10:40

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175

Thats great! Thank you

(22 Oct '13, 04:11) Stefan
Replies hidden

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

(22 Oct '13, 05:02) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×74
×58
×39

question asked: 21 Oct '13, 12:50

question was seen: 2,301 times

last updated: 22 Oct '13, 10:40