Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.


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")) {

    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");

          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

accept rate: 0%

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

permanent link

answered 21 Oct '13, 16:52

Minghai%20Chang's gravatar image

Minghai Chang
accept rate: 20%

edited 22 Oct '13, 10:40

Jeff%20Albion's gravatar image

Jeff Albion

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 21 Oct '13, 12:50

question was seen: 2,517 times

last updated: 22 Oct '13, 10:40