opening transaction validation in vb.net

Question

Can anyone help me on how can I validate transaction

example:

transaction = mySqlConn.BeginTransaction(IsolationLevel.ReadCommitted)

If there's still an opened transaction, then the code above will ignore.. How do I know if there was a transaction not yet committed before opening new transaction to avoid Nested Transaction?

Answer 1

Have you looked at using System.Transactions.TransactionScope? It is designed to handle this type of scenario implicitly without having to write custom code.

If you are going to use explicit transaction management without using System.Transactions you are going to have to pass your transaction object around (or somehow make it available) and you will need to decide when to start a transaction. e.g. check if the SqlTransaction is null and if so start a transaction otherwise just use the existing transaction.

You could do something like this (Error handling for transaction.Rollback() is omitted):

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = null;

    DoSomething(connection, ref transaction);
    DoSomethingElse(connection, ref transaction);

    transaction.Commit();
}

public void DoSomething(SqlConnection connection, ref SqlTransaction transaction)
{
    SqlCommand command = connection.CreateCommand();
    transaction = GetTransaction(connection, transaction);

    command.Connection = connection;
    command.Transaction = transaction;

    ...
}

public void DoSomethingElse(SqlConnection connection, ref SqlTransaction transaction)
{
    ...
}

public SqlTransaction GetTransaction(SqlConnection connection, SqlTransaction transaction)
{
    if (transaction == null)
    {
        transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    }

    return transaction;  
}

But usually you would just create your transaction and pass it in to other methods with the understanding that it had already been initialized and is safe to use.