Dear All,
I have this application which is using Innodb engine and .netconnector 5.0.8.1.So I am using C# for my coding. So I am not sure either is the engine or the connector problem? So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So whyis this happening because both are under the same transactions right. I have separately attached below my function for thetransactionConnectionLocal1 where I keep the connection details.
int rollbackBoolean = 0;
MySqlTransaction transactionLocal1 = null;
MySqlConnection connectionLocal1 = null;
transactionConnectionLocal1 callTransactionConnectionLocal1= null;
try
{
callTransactionConnectionLocal1 = newtransactionConnectionLocal();
connectionLocal1=callTransactionConnectionLocal1.localConnec tion1;
connectionLocal1.Open();
transactionLocal1 =connectionLocal1.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show(“Error Sockets From Database Connection” + ex.Message);
}
String myUpdateQuery1 = "Update tblStock " +“Set tblStock.stockStatus = ‘b’” +“Where tblStock.stockSerial ='” +serial + “'” ;
MySqlCommand myCommand1 = newMySqlCommand(myUpdateQuery1);
try
{
myCommand1.Connection = connectionLocal1;
myCommand1.Transaction = transactionLocal1;myCommand1.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myUpdateQuery1 " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myUpdateQuery1 " +ex.Message);
}
finally
{
myCommand1.Dispose();
}
String myInsertQuery1 = "Insert into tblTemp " +“Set stockSerial ='” + serial+ “',” +“tempTimeStamp='” +DateTime.Now.ToString((“yyyy:MM:dd HH:mm:ss”)) + “';”;MySqlCommand myCommand2 = newMySqlCommand(myInsertQuery1);
try
{
myCommand2.Connection = connectionLocal1;
myCommand2.Transaction = transactionLocal1;
myCommand2.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;MessageBox.Show("Error From myInsertQuery1 " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myInsertQuery1 " +ex.Message);
}
finally{myCommand2.Dispose();
}
if (rollbackBoolean == 1)
{
transactionLocal1.Rollback();
}
else
{
try
{
transactionLocal1.Commit();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try
{
transactionLocal1.Rollback();
}
catch (MySqlException ex1){MessageBox.Show(“An exception of type " +ex.GetType() +” was encountered while insertingthe data.“);
if (transactionLocal1.Connection != null)
{
MessageBox.Show(“An exception of type " +ex1.GetType() +” was encountered whileattempting to rollback the transaction.”);
}
}
}
catch (System.Net.Sockets.SocketException ex){rollbackBoolean = 1;MessageBox.Show(“Error Sockets From Commit Process” + ex.Message);
}
finally{connectionLocal1.Close();
}
}
// connection details function
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
namespace mSytemNonFranchise
{
public class transactionConnectionLocal1
{
public MySqlConnection localConnection1;
public MySqlCommand command;
public transactionConnectionLocal1()
{
this.localConnection1 = newMySqlConnection(“Address=‘localhost’;Database=‘localDB’;UserName=‘root’;Pas sword=‘local12’;Pooling=‘false’”);
// this.command = this.localConnection1.CreateCommand();
// this.localConnection1.Open();}
// destructor - explicitly
~transactionConnectionLocal1()
{//this.command.Dispose();this.localConnection1.Close();this .localConnection1.Dis pose();
}
}
}