I am using FirebirdSql for Interbase database in C# .net environment.
My application is divided into 3 parts, 1. Handler 2. Connector 3. DBLayer
In Handler I set all my fields property, Like
Decimal name = payment.name;
Decimal convRate = payment.ConvRate;
string remark = "Some Text";
retValue = Payment.Insert(name,convRate,remark); // Calling Connector Method
Now In Connector I am setting these properties to SQL Data Fields, Like
paramList = AddPaymentParams(name,convRate,remark) ; /// Make a Key-Value Pair
StringBuilder qName = new StringBuilder(InsertPaymentSQLQuery);
DBUtilities.ExecuteVoidCommand(qName, paramList); // Calling DBLayer Method
And in DBLayer I am Executing the actual query, like
qName = DBUtilities.GetCommand(qName, paramList); // Replace the Vaues in SQL queries
using (FbConnection conn = new FbConnection(ConfigurationManager.ConnectionStrings))
{
SqlHelper.ExecuteScalar(conn, CommandType.Text, qName.ToString());
return true;
}
All that thing working fine, but problem comes in consistency .
Suppose I went to insert/Update multiple Table ,Like Insert in Account table and then in Communication table and then in Address table.
In that case if some error/Exception occurs while updating Communication table then Communication table and Address table does not get updated but there is a entry in Account table , which is I want to revert back.
I think this might be done via TransactionScope like ,
using (TransactionScope iScope = new TransactionScope())
{
bool retAccountUpdate = InsertNewAccount(Account a);
bool retCommunicationUpdate = InsertNewCommunication(Account a,Communication c);
bool retAddressUpdate = InsertNewAddress(Account a, Address ad);
if (retAccountUpdate == true && retcommunicationUpdate == true && retAddressUpdate = true)
{
iScope.Complete();
}
}
But this come some/Maximum time throws transaction abort exception , even thou all the Insert/Update happens correctly(without fail).
Any idea How to resolve that