I have been using mysql and due to it could not support distributed transaction then I made individual transaction and finally commit each separately. So now I found a library from devart.com which can do distributed transaction but I need to use the Transaction Scope method which is in this link http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.80).aspx .
My problem now is I have a datagrid and when I finally read to insert and update a local db central db and copy of local db which is located at the central server. So currently I run through a loop and then insert and update first the local db and then immediately central db and also copy of local db. So problem is in transaction scope method first I need to resolve the local db stuff then open a new connection for the central db how can I do like my current method is that possible?
Below is my codes I will break it down into section for easier viewing but is a lot of codes ya.
This first actually I am creating 3 transaction each for my local db, central db and also copy of local db in the central server. You will notice first I run the query for transactionLocal, then followed by transactionCentralCopy. So this part is where I am having problem to convert.
int backUpCentralCopy = 0, backUpCentral = 0;
int rollbackBoolean = 0;
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);
}
globalConnectionLocal1 myConnect1 = null;
MySqlDataReader myReader1 = null;
MySqlTransaction transactionCentralCopy = null;
MySqlConnection connectionCentralCopy = null;
transactionConnectionCentralCopy1 callTransactionConnectionCentralCopy1 = null;
try
{
myConnect1 = new globalConnectionLocal1();
myConnect1.command.CommandText = "Select " +
"tblUpdateCentralCopy.updateCentralCopyID " +
"From tblUpdateCentralCopy ";
myReader1 = myConnect1.command.ExecuteReader();
if (myReader1.HasRows == true)
{
backUpCentralCopy = 1;
}
else
{
try
{
callTransactionConnectionCentralCopy1 = new transactionConnectionCentralCopy1();
connectionCentralCopy = callTransactionConnectionCentralCopy1.centralCopyConnection1;
connectionCentralCopy.Open();
transactionCentralCopy = connectionCentralCopy.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
backUpCentralCopy = 1;
MessageBox.Show("Error From Database Connection (Central C Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
backUpCentralCopy = 1;
MessageBox.Show("Error Sockets From Database Connection (Central C Is Down) " + ex.Message);
}
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
//backUpCentralCopy = 1;
MessageBox.Show("Error From UCC Check " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
//backUpCentralCopy = 1;
MessageBox.Show("Error Sockets From UCC Check " + ex.Message);
}
finally
{
myReader1.Close();
myConnect1.command.Dispose();
myConnect1.connection1.Close();
}
MySqlDataReader myReader2 = null;
MySqlTransaction transactionCentral = null;
MySqlConnection connectionCentral = null;
transactionConnectionCentral1 callTransactionConnectionCentral1 = null;
try
{
myConnect1 = new globalConnectionLocal1();
myConnect1.command.CommandText = "Select " +
"tblUpdateCentral.updateCentralID " +
"From tblUpdateCentral ";
myReader2 = myConnect1.command.ExecuteReader();
if (myReader2.HasRows == true)
{
backUpCentral = 1;
}
else
{
try
{
callTransactionConnectionCentral1 = new transactionConnectionCentral1();
connectionCentral = callTransactionConnectionCentral1.centralConnection1;
connectionCentral.Open();
transactionCentral = connectionCentral.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
backUpCentral = 1;
MessageBox.Show("Error From Database Connection (Central Server Is Down) " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
backUpCentral = 1;
MessageBox.Show("Error Sockets From Database Connection (Central Server Is Down) " + ex.Message);
}
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
//backUpCentralCopy = 1;
MessageBox.Show("Error From UC Check " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
//backUpCentralCopy = 1;
MessageBox.Show("Error Sockets From UC Check " + ex.Message);
}
finally
{
myReader2.Close();
myConnect1.command.Dispose();
myConnect1.connection1.Close();
}
This section I am reading from data grid each value and doing the insert and update operation accordingly
for (int j = 0; j < gridTransfer.RowCount; j++)
{
String mySelectQuery6 = "Select tblProduct.productTotalStock, " +
"tblProduct.productTotalAmount, " +
"tblProduct.productPrice " +
"From tblProduct " +
"Where tblProduct.productID=" + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString());
MySqlDataReader myReader8 = null;
MySqlCommand myCommandLocal11 = new MySqlCommand(mySelectQuery6);
myCommandLocal11.Connection = connectionLocal;
int chTSICBefore = 0;
double chTAICBefore = 0.00, chACICBefore = 0.00;
try
{
myReader8 = myCommandLocal11.ExecuteReader();
while (myReader8.Read())
{
chTSICBefore = Convert.ToInt16(myReader8.GetValue(0).ToString());
chTAICBefore = Convert.ToDouble(myReader8.GetValue(1).ToString());
chACICBefore = Convert.ToDouble(myReader8.GetValue(2).ToString());
}
if (chTSICBefore <= 0 || chTAICBefore <= 0.00 || chACICBefore <= 0.00)
{
MessageBox.Show("Error From Before chTSICBefore = " + chTSICBefore + " And chACICBefore = " + chACICBefore + " And chACICBefore = " + chACICBefore + " For pID = " + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString()));
rollbackBoolean = 1;
break;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal11 mySelectQuery6 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal11 mySelectQuery6 " + ex.Message);
}
finally
{
myReader8.Close();
myCommandLocal11.Dispose();
}
if (chTSICBefore > 0 && chTAICBefore > 0.00 && chACICBefore > 0.00)
{
String myInsertQuery3 = "Insert into tblTransferDetails " +
"Set transferDetailsID = " + transferDetailsID + ", " +
"transferID=" + transferID + ", " +
"outletID = " + globalSettings.settingOutletID + ", " +
"stockID = " + Convert.ToInt32(this.gridTransfer[3, j].Value.ToString()) + ", " +
"productID= " + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString()) + ", " +
"productType = '" + this.gridTransfer[2, j].Value.ToString() + "', " +
"stockQuantity = 1, " +
"stockSIQ = '" + this.gridTransfer[10, j].Value.ToString() + "', " +
"costPrice = " + Convert.ToDouble(this.gridTransfer[12, j].Value.ToString()) + ", " +
"transferPrice = " + Convert.ToDouble(this.gridTransfer[13, j].Value.ToString());
MySqlCommand myCommandLocal12 = new MySqlCommand(myInsertQuery3);
try
{
myCommandLocal12.Connection = connectionLocal;
myCommandLocal12.Transaction = transactionLocal;
myCommandLocal12.ExecuteNonQuery();
totalCost = totalCost + Convert.ToDouble(this.gridTransfer[12, j].Value.ToString());
totalTransferAmount = totalTransferAmount + Convert.ToDouble(this.gridTransfer[14, j].Value.ToString());
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal12 myInsertQuery3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal12 myInsertQuery3" + ex.Message);
}
finally
{
myCommandLocal12.Dispose();
}
if (backUpCentralCopy == 0)
{
MySqlCommand myCommandCentralCopy7 = new MySqlCommand(myInsertQuery3);
try
{
myCommandCentralCopy7.Connection = connectionCentralCopy;
myCommandCentralCopy7.Transaction = transactionCentralCopy;
myCommandCentralCopy7.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy7 myInsertQuery3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy7 myInsertQuery3" + ex.Message);
}
finally
{
myCommandCentralCopy7.Dispose();
}
}
else
{
String myInsertQueryReplace3 = myInsertQuery3.Replace("'", "''");
MySqlCommand myCommandCentralCopy7 = new MySqlCommand("Insert into tblUpdateCentralCopy SET updateCentralCopyQuery='" + myInsertQueryReplace3 + "'");
try
{
myCommandCentralCopy7.Connection = connectionLocal;
myCommandCentralCopy7.Transaction = transactionLocal;
myCommandCentralCopy7.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy7 myInsertQueryReplace3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy7 myInsertQueryReplace3" + ex.Message);
}
finally
{
myCommandCentralCopy7.Dispose();
}
}
if (backUpCentral == 0)
{
MySqlCommand myCommandCentral4 = new MySqlCommand(myInsertQuery3);
try
{
myCommandCentral4.Connection = connectionCentral;
myCommandCentral4.Transaction = transactionCentral;
myCommandCentral4.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentral4 myInsertQuery3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentral4 myInsertQuery3" + ex.Message);
}
finally
{
myCommandCentral4.Dispose();
}
}
else
{
String myInsertQueryReplace3 = myInsertQuery3.Replace("'", "''");
MySqlCommand myCommandCentral4 = new MySqlCommand("Insert into tblUpdateCentral SET updateCentralQuery='" + myInsertQueryReplace3 + "'");
try
{
//myCommandCentralDB3.CommandText = "Insert into tblUpdateCentral SET updateCentralQuery='" + myInsertQuery1 + "'";
myCommandCentral4.Connection = connectionLocal;
myCommandCentral4.Transaction = transactionLocal;
myCommandCentral4.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentral4 myInsertQueryReplace3" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentral4 myInsertQueryReplace3" + ex.Message);
}
finally
{
myCommandCentral4.Dispose();
}
}
String myUpdateQuery5 = "Update tblProduct " +
"Set tblProduct.productTotalStock = tblProduct.productTotalStock -" + Convert.ToInt32(this.gridTransfer[11, j].Value.ToString()) + ", " +
"tblProduct.productTotalAmount = tblProduct.productTotalAmount -" + Convert.ToDouble(this.gridTransfer[12, j].Value.ToString()) +
" Where tblProduct.productID=" + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString());
MySqlCommand myCommandLocal13 = new MySqlCommand(myUpdateQuery5);
try
{
myCommandLocal13.Connection = connectionLocal;
myCommandLocal13.Transaction = transactionLocal;
myCommandLocal13.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal13 myUpdateQuery5" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal13 myUpdateQuery5" + ex.Message);
}
finally
{
myCommandLocal13.Dispose();
}
if (backUpCentralCopy == 0)
{
MySqlCommand myCommandCentralCopy8 = new MySqlCommand(myUpdateQuery5);
try
{
myCommandCentralCopy8.Connection = connectionCentralCopy;
myCommandCentralCopy8.Transaction = transactionCentralCopy;
myCommandCentralCopy8.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy8 myUpdateQuery5" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy8 myUpdateQuery5" + ex.Message);
}
finally
{
myCommandCentralCopy8.Dispose();
}
}
else
{
String myUpdateQueryReplace5 = myUpdateQuery5.Replace("'", "''");
MySqlCommand myCommandCentralCopy8 = new MySqlCommand("Insert into tblUpdateCentralCopy SET updateCentralCopyQuery='" + myUpdateQueryReplace5 + "'");
try
{
//myCommandCentralCopy2.CommandText = "Insert into tblUpdateCentralCopy SET updateCentralCopyQuery='" + myUpdateQuery1 + "'";
myCommandCentralCopy8.Connection = connectionLocal;
myCommandCentralCopy8.Transaction = transactionLocal;
myCommandCentralCopy8.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandCentralCopy8 myUpdateQueryReplace5" + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandCentralCopy8 myUpdateQueryReplace5" + ex.Message);
}
finally
{
myCommandCentralCopy8.Dispose();
}
}
String mySelectQuery7 = "Select tblProduct.productTotalStock, " +
"tblProduct.productTotalAmount, " +
"tblProduct.productPrice " +
"From tblProduct " +
"Where tblProduct.productID=" + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString());
int chTSICAfter = 0, transaction = 1;
double chTAICAfter = 0.00, chACICAfter = 0.00;
MySqlCommand myCommandLocal14 = new MySqlCommand(mySelectQuery7);
myCommandLocal14.Connection = connectionLocal;
MySqlDataReader myReader9 = null;
try
{
myReader9 = myCommandLocal14.ExecuteReader();
while (myReader9.Read())
{
chTSICAfter = Convert.ToInt16(myReader9.GetValue(0).ToString());
chTAICAfter = Convert.ToDouble(myReader9.GetValue(1).ToString());
chACICAfter = Convert.ToDouble(myReader9.GetValue(2).ToString());
}
if (chTSICAfter < 0 || chTAICAfter < 0.00 || chACICAfter < 0.00)
{
MessageBox.Show("Error From chTSICAfter = " + chTSICAfter + " And chTAICAfter = " + chTAICAfter + " And chACICAfter = " + chACICAfter + " For pID = " + Convert.ToInt32(this.gridTransfer[0, j].Value.ToString()));
rollbackBoolean = 1;
transaction = 0;
break;
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myCommandLocal14 mySelectQuery7 " + ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myCommandLocal14 mySelectQuery7 " + ex.Message);
}
finally
{
myReader9.Close();
myCommandLocal14.Dispose();
}
}