Distributed transaction with C# and .net connector

[newtomysql]

Dear All,
I have a quite complex system here. So I have tried to cut it short as much as possible and below is a just a snippet of my code and I will explain to you what I am doing exactly. My application will update my local database server and 2 other remote servers and all I want is under one transaction. First I check is my local connection is up and running if not immediately I set rollbackBoolean =1 so all of it will rollback. Then next I check if my remote 1 is up and running and if it is then I set leave my backUpCentralCopy = 0 else I set to 1 and the same goes for my remote 2. So then below I have shown you how I am inserting a query first to my local server followed by remote 1 and followed by remote 2. So incase any mistake or error all the queries will not be committed as I have stated in the if (rollbackBoolean == 1). Thus in short this is how my application is working. Most of the times is working fine only for certain cases is not working well very very rare cases where it doesnt update either of my remote server. I dont know how to implement the distributed transaction manager in Mysql using the .net connector? So do you think there is mistake in my coding ? Thanks for your help.



//control variable definition

int backUpCentralCopy = 0, backUpCentral = 0;

int rollbackBoolean = 0;



//local connection and transaction
MySqlTransaction transactionLocal = null;
MySqlConnection connectionLocal = null;
transactionConnectionLocal1 callTransactionConnectionLocal1 = null;

try
{
callTransactionConnectionLocal1 = new transactionConnectionLocal1();
connectionLocal = callTransactionConnectionLocal1.localConnection1;
connectionLocal.Open();
transactionLocal = connectionLocal.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection (Local Server Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Database Connection (Local Server Is Down) " + ex.Message);
}



//remote connection and transaction 1
globalConnectionLocal1 myConnect1 = null;
MySqlDataReader myReader1 = null;
MySqlTransaction transactionCentralCopy = null;
MySqlConnection connectionCentralCopy = null;
transactionConnectionCentralCopy1 callTransactionConnectionCentralCopy1 = null;
try
{
callTransactionConnectionCentralCopy1 = new transactionConnectionCentralCopy1();
connectionCentralCopy = callTransactionConnectionCentralCopy1.centralCopyConnection1;
connectionCentralCopy.Open();
transactionCentralCopy = connectionCentralCopy.BeginTransaction();

}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
backUpCentralCopy = 1;
MessageBox.Show("Error From Database Checking (Central C Server Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
backUpCentralCopy = 1;
MessageBox.Show("Error Sockets From Checking Connection (Central C Server Is Down) " + ex.Message);
}




//remote connection and transaction 2

MySqlDataReader myReader2 = null;
MySqlTransaction transactionCentral = null;
MySqlConnection connectionCentral = null;
transactionConnectionCentral1 callTransactionConnectionCentral1 = null;
try
{

callTransactionConnectionCentral1 = new transactionConnectionCentral1();
connectionCentral = callTransactionConnectionCentral1.centralConnection1;
connectionCentral.Open();
transactionCentral = connectionCentral.BeginTransaction();


}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
backUpCentral = 1;
MessageBox.Show("Error From Database Checking (Central Server Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
backUpCentral = 1;
MessageBox.Show("Error Sockets From Checking Connection (CentralServer Is Down) " + ex.Message);
}




String myInsertQuery1 = "Insert into tblReceipt " +
"Set receiptID=" + receiptID + ", " +
"totalCostAmount=" + totalCostPrice + ", " +
"totalSellingAmount=" + totalSellingPrice + ", " +
"totalProfitAmount=" + totalProfit ;



// inserting local server

MySqlCommand myCommandLocal3 = new MySqlCommand(myInsertQuery1);
myCommandLocal3.Connection = connectionLocal;
myCommandLocal3.Transaction = transactionLocal;
try
{
myCommandLocal3.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal3 myInsertQuery1" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal3 myInsertQuery1" + ex.Message);
}
finally
{
myCommandLocal3.Dispose();
}



//check if my remote server 1 is up and running then only update it directly

if (backUpCentral == 0)
{
MySqlCommand myCommandCentral3 = new MySqlCommand(myInsertQuery1);

try
{
myCommandCentral3.Connection = connectionCentral;
myCommandCentral3.Transaction = transactionCentral;
myCommandCentral3.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentral3 myInsertQuery1" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentral3 myInsertQuery1" + ex.Message);
}
finally
{
myCommandCentral3.Dispose();
}
}
else


if (backUpCentralCopy == 0)
{
MySqlCommand myCommandCentralCopy3 = new MySqlCommand(myInsertQuery1);

try
{
myCommandCentralCopy3.Connection = connectionCentralCopy;
myCommandCentralCopy3.Transaction = transactionCentralCopy;
myCommandCentralCopy3.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy3 myInsertQuery1" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy3 myInsertQuery1" + ex.Message);
}
finally
{
myCommandCentralCopy3.Dispose();
}
}


//check if incase any of my query fail then do dont do any of the commit.

if (rollbackBoolean == 1)
{
transactionLocal.Rollback();
if (backUpCentral == 0) // to check if in the first place my remote 1 is up and running
{
transactionCentral.Rollback();
}
if (backUpCentralCopy == 0) // to check if in the first place my remote2 is up and running

{
transactionCentralCopy.Rollback();
}
}
else
{
try
{
transactionLocal.Commit();
if (backUpCentral == 0) // to check if in the first place my remote 1 is up and running
{
transactionCentral.Commit();
}
if (backUpCentralCopy == 0) // to check if in the first place my remote 2is up and running
{
transactionCentralCopy.Commit();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try
{
transactionLocal.Rollback();
if (backUpCentral == 0) // to check if in the first place my remote 1 is up and running
{
transactionCentral.Rollback();
}
if (backUpCentralCopy == 0) // to check if in the first place my remote 2 is up and running
{
transactionCentralCopy.Rollback();
}
}
catch (MySqlException ex1)
{
if (transactionLocal.Connection != null)
{
MessageBox.Show("An exception of type " + ex1.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

MessageBox.Show("An exception of type " + ex.GetType() +
" was encountered while inserting the data.");
MessageBox.Show("Neither record was written to database.");
}
finally
{
connectionLocal.Close();
if (backUpCentral == 0)
{
connectionCentral.Close();
}
if (backUpCentralCopy == 0)
{
connectionCentralCopy.Close();
}
}
}

 

[whosrdaddy]

see my response in your second post.

this is more a C# issue and not mysql, repost this in the C# forum (forum732).

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!