Devart Forums

Posted: **Tue 06 Mar 2012 09:33**

Hi,

I've got a question about the Dispose() method of the PgSqlTransaction class.

When I use the code as stated below I sometimes run into a "Connection must be opened" exception. From the stack trace it appears that PgSqlTransaction.Dispose() invokes PgSqlTransaction.Rollback(). This seems rather strange to me.

Can you elaborate on why this happens, or is my use of Dispose() illegal?

Code: Select all

DbConnection connection = null;
DbTransaction transaction = null;
DbCommand command1 = null;
DbCommand command2 = null;

try
{
    connection = OpenConnection();
    transaction = connection.BeginTransaction();

    command1 = connection.CreateCommand();
    command1.CommandText = "INSERT INTO test_data1 VALUES(1)";
    command1.ExecuteNonQuery();

    command2 = connection.CreateCommand();
    command2.CommandText = "INSERT INTO test_data2 VALUES(2)";
    command2.ExecuteNonQuery();

    transaction.Commit();
}
catch (DbException e)
{
    if (transaction != null)
    {
        transaction.Rollback();
    }

    mLog.Error(e);
}
catch (Exception e)
{
    mLog.Error(e);
}
finally
{
    if (command1 != null)
        command1.Dispose();

    if (command2 != null)
        command2.Dispose();

    if (transaction != null)
        transaction.Dispose();

    if (connection != null)
    {
        connection.Close();
        connection.Dispose();
    }
}

Posted: **Wed 14 Mar 2012 09:35**

We cannot reproduce the mentioned error in our environment with the 5.70.311 version of dotConnect for PostgreSQL using your code.

bozr wrote:From the stack trace it appears that PgSqlTransaction.Dispose() invokes PgSqlTransaction.Rollback(). This seems rather strange to me.

We think that you have encountered the following scenario:
1. Some exception, which is not caught by "catch (DbException e)" but makes connection be closed, is raised in this code:

Code: Select all

    command1 = connection.CreateCommand();
    command1.CommandText = "INSERT INTO test_data1 VALUES(1)";
    command1.ExecuteNonQuery();

    command2 = connection.CreateCommand();
    command2.CommandText = "INSERT INTO test_data2 VALUES(2)";
    command2.ExecuteNonQuery();

2. As a result, "catch (Exception e)" logs the exception and

Code: Select all

finally
{
    ...
    if (transaction != null)
        transaction.Dispose();
    ...

sees that transaction is opened (after "transaction = connection.BeginTransaction();") but not committed/rolled back. So Dispose() calls Rollback() which leads to "Connection must be opened" because connection is closed.

Devart Forums

Behavior of PgSqlTransaction.Dispose()

Behavior of PgSqlTransaction.Dispose()