newbie14 0 Posting Pro

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();
                        }


                      }
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.