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](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:


question asked: 21 Oct '13, 12:50

question was seen: 2,423 times

last updated: 22 Oct '13, 10:40