Commit a transaction if part of it fails

Question

I have a method running in COM+ which looks a little like the following:

[AutoComplete(true)]
public bool DoSomething(string args)
{
    DoSomeDBWork(args);
    try {
        DBAccess.RunQuery("INSERT fail");
        return 0;
    }
    catch (Exception ex)
    {
        //Hide the error because it doesnt matter - log it out though for completeness
        DBAccess.RunQuery("INSERT INTO Log VALUES ('{0}')", ex.ToString());
        return -1
    }
}

OK So the method runs, the DoSomeDBWork() method runs, does some updates on the DB.

The 'insert fail' runs, which fails. I just want to ignore it, but log out that it has failed.

But I'm getting an error generated: System.Transactions.TransactionException: The operation is not valid for the state of the transaction.

I'm assuming that even though I catch the Exception, because it is a DB error it is rolling back the transaction automatically.
The error comes from the line 'insert into log ...'
and everything that was updated in DoSomeDBWork() is rolled back as well.

How do I ignore the failing line?

Answer 1

You will have to rollback the transaction being used for "Insert Fail..." before trying to Insert into the log. Not familiar with DBAccess and how it works, but once you rollback you should be able to start a new transaction either implicitly or explicitly.

[AutoComplete(true)]
public bool DoSomething(string args)
{
    DoSomeDBWork(args);
    try {
        DBAccess.RunQuery("INSERT fail");
        return 0;
    }
    catch (Exception ex)
    {
        DBAccess.Rollback();
        DBAccess.RunQuery("INSERT INTO Log VALUES ('{0}')", ex.ToString());
        return -1
    }
}