Problem with the Database Transaction

Dec 29, 2010 at 4:37 AM

Hi Guys

I am facing a problem with Database transactions.

i have the following piece of code.

PowerUtility objPowerUtility = new PowerUtility();
PowerServiceClient objPowerCenter = new PowerServiceClient(objPowerUtility.ActiveLocation); // This is my WCF Service
TransactionOptions txOptions = new TransactionOptions();
txOptions.IsolationLevel = System.Transactions.IsolationLevel.Snapshot;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, txOptions))
          objPowerUtility.DeleteTempTable(); // Call to remove Temp records from DB
          dsRecordSet = objPowerUtility.DownloadData();  //Download the data from table
          recordCount = (dsRecordSet == null) ? 0 : (dsRecordSet.Tables[0] == null) ? 0 : dsRecordSet.Tables[0].Rows.Count;
          bool appendFile = false;
          bool terminateFlag = objPowerUtility.GenerateFile(dsRecordSet, recordCount, ref appendFile); File operation - This is working well. If an exception occurs, this items are rolled back
          if (!terminateFlag)
                 if (recordCount > 0)
                     updateRecordCount = objPowerUtility.UpdateData(); - Updating the processed records
       if (recordCount == updateRecordCount && !config.Debug)
                            if (config.SuccessMail.Count > 0)
                                objPowerCenter.SendNotificationMail("Data download successful", "Download complete; There were " + recordCount + " number of records downloaded", config.SuccessMail.ToArray());
                                objPowerCenter.SendNotificationMail("Data upload successful", "Upload complete; There were " + updateRecordCount + " number of records uploaded", config.SuccessMail.ToArray());
                                ServiceFault fault = new ServiceFault { Id = Guid.NewGuid(), Message = "Error in Sending Notification Message From Power App" };
                                throw new FaultException<ServiceFault>(fault, new FaultReason("Notification Failure")); // I am throwing an exception here to see if the previous operations can be rolled back - But i cannot see any one of the database operations are rolling back

This program tries to fetch the records from the database and use an xslt, convert it into a text file and store it in client place. I am using a WCF Service to perform the operations. I am facing a probelm with the NotificatiionMail method occassionally, which will send a mail to the client about the sussessful completion of the programm. But in general, after all the output is generated, if the Notification mail is failing, the gererated files contets can be rolled back with your DLL. But the changes made on the database are persisting. I have turned on the SNAPSHOT level transaction on in the DB. Even the stored procs are all usign the SNAPSHOT Level transaction. Do you have any idea of the problem?