The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
81237
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.7k171174

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:

×53
×47
×35

question asked: 21 Oct '13, 12:50

question was seen: 712 times

last updated: 22 Oct '13, 10:40