G
Guest
I have the following code and the rollback is giving me an exception that
there was no BEGIN TRANSACTION. I have stepped through the code and the
BeginTransaction is created.
Now I am running a set of unit test forcing both failure and success
scenarios. All the test to this point are failures. They all reported as
expected. The test prior to the exception is the first to execute the parent
update with an original key that does not exist. The transaction is rolled
back and the exception is correctly reported back to the client.
The next test trys changing the primary key. The expected initial error is
thrown at the ExecuteNonQuery statemetn on the parent. The exception is "The
query processor could not product a query plan from the optimizer because a
query cannot update a text, ntext, or image column and the clustering key at
the same time." The is the exception the unit test is expecting. but the
Rollback statement faile with "The ROLLBACK TRANSACTION request has no
corresponding BEGIN TRANSACTION."
My questions are:
1) Is the implementation of the transaction rollback valid in may code?
2) Why is the rollback failing with an exception when it did execute the
"transaction = dataConnection.BeginTransaction( IsolationLevel.Serializable,
transactionName );" statement creating a corresponding BEGIN TRANSACTION? It
is like the transaction was lost, corrupted, or not properly created. But it
work in the previous unit test. I don't see what the error is.
Help!
Example Code:
[WebMethod( Description = "Update the data in the SQL Database." )]
public void UpdateData( Data original, Data current )
{
sqlTransaction transaction;
const string transactionName = "UpdateData";
try
{
if( IsDisposed )
{
throw new ObjectDisposedException( this.ToString( ), ServiceNotRunning
);
}
Authorization( ); // throws exception on authentication and
authorization failures.
//----------------------------------------------------------------------
// Validation
//----------------------------------------------------------------------
ArgumentCollection args = null; // ValidateData instantiates object if
needed.
if( ! ( this.ValidateData( original, ref args ) &
this.ValidateData( current, ref args ) ) )
{
throw new ArgumentExceptions( ArgumentMessage, args );
}
//----------------------------------------------------------------------
// Any changes made.
//----------------------------------------------------------------------
if( original == current )
{
throw new Exception( NoChangesMessage );
}
else
{
dataConnection.Open( ); // Open the SQLConnection object;
transaction = dataConnection.BeginTransaction(
IsolationLevel.Serializable,
transactionName );
try // Try-Catch for SQL error processing
{
int countParentData = 0;
int countChildData = 0;
if( original.ParentDataChanged( current ) ) // did any parent fields
change.
{ // Yes, update the parent;
updParentCommand.Connection = dataConnection;
updParentCommand.Transaction = transaction;
// The update sql parameters are set from the Data objects.
countParentData = updParentCommand.ExecuteNonQuery( );
}
else
{
countParentData = -1; // no changes in parent data.
}
if( original.ChileDataChanged( current ) )
{
updChildCommand.Connection = dataConnection;
updChildCommand.Transaction = transaction;
// The update sql parameters are set from the Data objects.
countChildData = updChildCommand.ExecuteNonQuery( );
}
else
{
countChildData = -1; // no changes in child data.
}
//---------------------------------------------------------------------
// No error occurred check the counts to see if anything was done.
//---------------------------------------------------------------------
if( countParentData > 0 || countChildData > 0 )
{
transaction.Commit( );
}
else
{
// WHERE clause did not find the matching entry.
throw new Exception( NoMatchingEntry );
}
}
catch( SqlException ex )
{
transaction.Rollback( transactionName );
string message = "SQL Update transaction failure. ";
if( ex.Class > 13 )
{
LogEvent( ex.ToString( ) );
message += EventLogged + DateTime.Now.ToString("F") + ReportTo;
}
else
{
message += ex.Message;
}
throw BuildSqlSoapException( message, ex );
}
catch( Exception ex )
{
transaction.Rollback( transactionName );
LogEvent( ex.ToString( ) );
throw BuildFatalSoapException( SoapException.ServerFaultCode, ex );
}
finally
{
dataConnection.Close( );
}
}
}
catch( SoapException )
{
throw;
}
catch( Exception ex )
{
throw BuildSoapException( ( ( ex is ArgumentException ) ?
SoapException.ClientFaultCode :
SoapException.ServerFaultCode
), ex );
}
}
there was no BEGIN TRANSACTION. I have stepped through the code and the
BeginTransaction is created.
Now I am running a set of unit test forcing both failure and success
scenarios. All the test to this point are failures. They all reported as
expected. The test prior to the exception is the first to execute the parent
update with an original key that does not exist. The transaction is rolled
back and the exception is correctly reported back to the client.
The next test trys changing the primary key. The expected initial error is
thrown at the ExecuteNonQuery statemetn on the parent. The exception is "The
query processor could not product a query plan from the optimizer because a
query cannot update a text, ntext, or image column and the clustering key at
the same time." The is the exception the unit test is expecting. but the
Rollback statement faile with "The ROLLBACK TRANSACTION request has no
corresponding BEGIN TRANSACTION."
My questions are:
1) Is the implementation of the transaction rollback valid in may code?
2) Why is the rollback failing with an exception when it did execute the
"transaction = dataConnection.BeginTransaction( IsolationLevel.Serializable,
transactionName );" statement creating a corresponding BEGIN TRANSACTION? It
is like the transaction was lost, corrupted, or not properly created. But it
work in the previous unit test. I don't see what the error is.
Help!
Example Code:
[WebMethod( Description = "Update the data in the SQL Database." )]
public void UpdateData( Data original, Data current )
{
sqlTransaction transaction;
const string transactionName = "UpdateData";
try
{
if( IsDisposed )
{
throw new ObjectDisposedException( this.ToString( ), ServiceNotRunning
);
}
Authorization( ); // throws exception on authentication and
authorization failures.
//----------------------------------------------------------------------
// Validation
//----------------------------------------------------------------------
ArgumentCollection args = null; // ValidateData instantiates object if
needed.
if( ! ( this.ValidateData( original, ref args ) &
this.ValidateData( current, ref args ) ) )
{
throw new ArgumentExceptions( ArgumentMessage, args );
}
//----------------------------------------------------------------------
// Any changes made.
//----------------------------------------------------------------------
if( original == current )
{
throw new Exception( NoChangesMessage );
}
else
{
dataConnection.Open( ); // Open the SQLConnection object;
transaction = dataConnection.BeginTransaction(
IsolationLevel.Serializable,
transactionName );
try // Try-Catch for SQL error processing
{
int countParentData = 0;
int countChildData = 0;
if( original.ParentDataChanged( current ) ) // did any parent fields
change.
{ // Yes, update the parent;
updParentCommand.Connection = dataConnection;
updParentCommand.Transaction = transaction;
// The update sql parameters are set from the Data objects.
countParentData = updParentCommand.ExecuteNonQuery( );
}
else
{
countParentData = -1; // no changes in parent data.
}
if( original.ChileDataChanged( current ) )
{
updChildCommand.Connection = dataConnection;
updChildCommand.Transaction = transaction;
// The update sql parameters are set from the Data objects.
countChildData = updChildCommand.ExecuteNonQuery( );
}
else
{
countChildData = -1; // no changes in child data.
}
//---------------------------------------------------------------------
// No error occurred check the counts to see if anything was done.
//---------------------------------------------------------------------
if( countParentData > 0 || countChildData > 0 )
{
transaction.Commit( );
}
else
{
// WHERE clause did not find the matching entry.
throw new Exception( NoMatchingEntry );
}
}
catch( SqlException ex )
{
transaction.Rollback( transactionName );
string message = "SQL Update transaction failure. ";
if( ex.Class > 13 )
{
LogEvent( ex.ToString( ) );
message += EventLogged + DateTime.Now.ToString("F") + ReportTo;
}
else
{
message += ex.Message;
}
throw BuildSqlSoapException( message, ex );
}
catch( Exception ex )
{
transaction.Rollback( transactionName );
LogEvent( ex.ToString( ) );
throw BuildFatalSoapException( SoapException.ServerFaultCode, ex );
}
finally
{
dataConnection.Close( );
}
}
}
catch( SoapException )
{
throw;
}
catch( Exception ex )
{
throw BuildSoapException( ( ( ex is ArgumentException ) ?
SoapException.ClientFaultCode :
SoapException.ServerFaultCode
), ex );
}
}