SqlTransaction causing application crash on error?

Question

Here's some background: Periodically our site will crash to the point of having to restart IIS; this almost always happens within an hour of patching a DLL (we use a Web Site project, not a Web Application project, so each ASPX page is a separate DLL).

In doing some research I have found that our homebrew DAL can, while debugging, cause the built-in webserver with Visual Studio to actually stop working and be shut down if it encounters a SQL error in a stored procedure (I mean it will not only throw an exception that is displayed in the browser, it will actually say that the web server has experienced an error and needs to close!)

In digging further the error seems to be related to the use of transactions for everything (including Select statements) in the DAL. What seems to happen is this:

1. Tries to execute stored procedure, stored procedure fails due to missing/invalid column or other error.
2. Application code catches the error and rethrows it (bad, yes, but I didn't write this).
3. Transaction tries to commit despite the exception, gets a NullReferenceException on the transaction.Commit() line (seems to be on the Connection property because there is a transaction object). Also this NullRef seems like it cannot be caught (I tried a demo that force crashed with an invalid Sproc and the NullRef was never caught even though outputting the error gave its type as System.NullReferenceException)
4. Transaction throws error that say something like "The transaction has completed and is no longer usable".
5. ??? but the VS web server crashes. Debugging this part seems to hang on the above exception, never leaving the method.

Now, I don't know if this is what causes IIS to crash, but it seems quite suspicious and it's a glaring error in any event.

Having not dealt with transactions before and having only the basic idea of them, my first question is why the transaction is still trying to commit after an exception is being thrown? My second question is how to fix the failing commit and presumably infinite looping of exceptions until the server dies. Wouldn't it make sense to add something like this (the method takes a SqlTransaction parameter named transaction):

catch (SqlException se) 
{
    if (transaction != null)
    {
        transaction.Rollback();
    }
    throw;
}

Would that small change fix the constant exception loop that I think is crashing IIS? The DAL itself is extremely brittle and is used concretely in hundreds of files so I can't rewrite it from scratch correctly.

EDIT The entire code block is this (again, legacy code - uses the old microsoft data access block helper):

    public static DataSet ExecuteDatasetStoredProc(SqlConnection conn, String storedProcName, SqlTransaction transaction, params SqlParameter[] storedProcParms)
    {
        try
        {
            // Execute the stored proc
            if (transaction != null)
            {
                return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, storedProcName, storedProcParms);
            }
            else
            {
                return SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, storedProcName, storedProcParms);
            }
        }
        catch (SqlException se)
        {
            throw new ApplicationException("Error calling " + storedProcName + ". " + se.Message, se);
        }
    }

However, if the catch block executes the transaction still tries to commit and this seems to be causing the hangups.

Answer 1

also Change your if you wrap your Transactional code in a try catch

Try
{
// your code that you assign and execute the SQl

}
catch (SQLException sqlex)
{
  try 
  {
    //try to do the rollback here.. don't always assume the commit or rollback will work
  }
  catch (Your SQL Exception ex)
  {
  }
}