A transaction problem while executing a C# code

Question

In M code, I am getting an error, "This SqlTransaction has completed; it is no longer usable." The line of code that is generating this code is this sqlTransaction

MSSqlConnectionHandler.CommitTransaction();

This CommitTransaction function is

public static void CommitTransaction()
    {
        try
        {
            _theTran.Commit();
        }
        catch (Exception Ex)
        {
            try
            {
                _theTran.Rollback();
            }
            catch (Exception InnerEx)
            {
                throw InnerEx;
            }
            throw Ex;
        }
    }

But, I Comment out this MSSqlConnectionHandler.CommitTransaction(); line, then no error occured but no data is saved either. What's going wrong, What should I provide here to make it more clear? Thanx in advance

why are you catching and then (incorrectly) re-throwing the exception? It's pointless, and you lose your call stack — Mitch Wheat, Jul 25 '11 at 14:02
what is `_theTran`? it has a code smell with the static method. — Daniel A. White, Jul 25 '11 at 14:02
@Mitch Wheat:The CommitTransaction() is not written by me, its written by ex programmer of the team, I am not accusing him. I am just telling that I a to follow the prev structure of the code, I have to use hat method, anyways, I regularly post code-segment where I need clarification or I cant handle the error, and this site and guys like you are the only hope for me. Will you tell me what you meant by catching and then (incorrectly) re-throwing the exception, I need a bit more explanation to realize it. — Pankouri, Jul 25 '11 at 14:15
http://stackoverflow.com/questions/178456/what-is-the-proper-way-to-re-throw-an-exception-in-c — Mitch Wheat, Jul 25 '11 at 14:17
Suggest you follow up on @Daniel A. White pointing out the global/static _theTran .... — Mitch Wheat, Jul 25 '11 at 14:21
OK, I don know what have happened, but this line solved my problem, I have used SqlTransaction sqlTransaction = MSSqlConnectionHandler.StartTransaction(); // mode code ... and then sqlTransaction.Commit(); can anyone explain me what have happened? — Pankouri, Jul 25 '11 at 14:36

score 1 · Answer 1 · answered Jul 25 '11 at 14:02

1

The error indicates that the transaction is being committed somewhere else in your code, is _theTran an instance variable that could be used and committed somewhere else?

answered Jul 25 '11 at 14:02

CodingGorilla

19,612
4
45
65

Jon Raynor · Answer 2 · 2011-07-25T15:10:24.460

A possbility is the connection that the transaction was on was closed. If that was the case, when this was called:

_theTran.Commit();

The error would occur.

Whoever is calling

CommitTransaction

Should do some checking prior to commit, like maybe something like this:

if (conn.State == ConnectionState.Open) 
{
     if (_theTran != null)
     {
          CommitTransaction();
     }
     conn.Close();
}

As far as the exception handling is concerned, the exception handler is doing nothing but rethrowing. IF exception occurs on commit, then try the rollback and always throw. I don't think you need the inner try catch.

try
{             
    _theTran.Commit();         
}         
catch (Exception)
{                        
    _theTran.Rollback();             
    throw;
}

A transaction problem while executing a C# code

2 Answers2