views:

207

answers:

0

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