ado.net transactions

I am getting this erroor on transaction.commit "stack overflow" on transaction.rollback i am getting this {"This SqlTransaction has completed; it is no longer usable." } I am passing same transaction object to lot of methods like say 12 which call stored procedures how to overcome this problem. Thanks Ajit

using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { try { using (INASBAL.Service service = new INASBAL.Service()) { service.SequenceID = service.InputAccountData(transaction, ref dt); ParseAndInsertAccountLeadType(transaction, ref dt, nav, service); ParseAndInsertAccountDisabilityCoverage(transaction, ref dt, nav, service); ParseAndInsertAccountAuthorizedRider(transaction, ref dt, nav, service); ParseAndInsertMailingAddress(transaction, ref dt, nav, service); ParseAndInsertAccountAgent(transaction, ref dt, nav, service); ParseAndInsertAccountAuthorizingOfficer(transaction, ref dt, nav, service); ParseAndInsertAccountBillingContact(transaction, ref dt, nav, service); ParseAndInsertAccountBroker(transaction, ref dt, nav, service); ParseAndInsertAccountFlexOneBenefit(transaction, ref dt, nav, service); ParseAndInsertAccountLegalRep(transaction, ref dt, nav, service); ParseAndInsertAccountContactFsa(transaction, ref dt, nav, service); ParseAndInsertAccountFlexIneligibleBen(transaction, ref dt, nav, service); ParseAndInsertAccountOtherCarrierBen(transaction, ref dt, nav, service); // //if all successful commit transaction // transaction.Commit(); }//using service }//try catch (Exception ex) { transaction.Rollback(); throw ex; } }//using transaction }//using connection INSIDE THESE METHODS I AM USING MS DATA APPLICATION BLOCK LIKE Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, strProcedure, arParms); Thanks Ajit

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

srinivas.ajit21y ago
Its working now I was disposing the the service object at wrong place. I am continuing to use transactions object. Thanks
srinivas.ajit21y ago
using (SqlConnection connection = new SqlConnection(ConnectionString)) { connection.Open(); using (SqlTransaction transaction = connection.BeginTransaction()) { try { using (INASBAL.Service service = new INASBAL.Service()) { service.SequenceID = service.InputAccountData(transaction, ref dt); ParseAndInsertAccountLeadType(transaction, ref dt, nav, service); ParseAndInsertAccountDisabilityCoverage(transaction, ref dt, nav, service); ParseAndInsertAccountAuthorizedRider(transaction, ref dt, nav, service); ParseAndInsertMailingAddress(transaction, ref dt, nav, service); ParseAndInsertAccountAgent(transaction, ref dt, nav, service); ParseAndInsertAccountAuthorizingOfficer(transaction, ref dt, nav, service); ParseAndInsertAccountBillingContact(transaction, ref dt, nav, service); ParseAndInsertAccountBroker(transaction, ref dt, nav, service); ParseAndInsertAccountFlexOneBenefit(transaction, ref dt, nav, service); ParseAndInsertAccountLegalRep(transaction, ref dt, nav, service); ParseAndInsertAccountContactFsa(transaction, ref dt, nav, service); ParseAndInsertAccountFlexIneligibleBen(transaction, ref dt, nav, service); ParseAndInsertAccountOtherCarrierBen(transaction, ref dt, nav, service); // //if all successful commit transaction // transaction.Commit(); }//using service }//try catch (Exception ex) { transaction.Rollback(); throw ex; } }//using transaction }//using connection INSIDE THESE METHODS I AM USING MS DATA APPLICATION BLOCK LIKE Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, strProcedure, arParms); Thanks Ajit
shimtanny21y ago
Hi Please post sample code. Simon