Description:
I am Senior developer using C# with asp.net facing distributed transaction not supported currently in MySql connector.
I am using MySQL ENGINE = InnoDB and In Connection string set AutoEnlist = True and providerName="MySql.Data.MySqlClient"
Server -- Web server as IIS
Platform ---> VS 2008
Database --> Oracle11g and MySql Server 5.6
MySql Connector --> connector Net 6.7.4 with version 2.0
I am trying to insert record in first MySql DB and than update in oracle DB but always get exception distributed transaction not supported currently in MySql connector.
Can anybody atleast tell me it is possible or not, if yes than How can i acheive it. Please see the code below...
protected void Button3_Click_SqlInsert_Another(object sender, EventArgs e)
{
CitiesAccess citiesAccess = new CitiesAccess();
List<Cities> lstCities;
lstCities = citiesAccess.GetCities();
// Initialize the return value to zero and create a StringWriter to display results.
int returnValue = 0;
string mySQL = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString.ToString();
string myOracle = System.Configuration.ConfigurationManager.ConnectionStrings["DMSConnectionString"].ToString();
try
{
foreach (Cities cities in lstCities)
{
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope1 = new TransactionScope())
{
using (MySqlConnection mysqlCon = new MySqlConnection(mySQL))
{
mysqlCon.Open();
MySqlCommand cmd = new MySqlCommand(Constants.Cities.SP_INSERTUPDATECITIES);
//cmd.CommandTimeout = 0;
cmd.Connection = mysqlCon;
cmd.CommandType = CommandType.StoredProcedure;
if (cities.CityID != 0)
{
cmd.Parameters.AddWithValue("in_city_id", cities.CityID);
}
else
{
cmd.Parameters.AddWithValue("in_city_id", DBNull.Value);
}
//if (cities.CityID == 1)
// cmd.Parameters["in_city_id"].Value = "sdsds";
cmd.Parameters.AddWithValue("in_city_code", cities.CityCode);
cmd.Parameters.AddWithValue("in_city_name", cities.CityName);
if (cities.DistrictId != 0)
{
cmd.Parameters.AddWithValue("in_district_id", cities.DistrictId);
}
else
{
cmd.Parameters.AddWithValue("in_district_id", DBNull.Value);
}
cmd.Parameters.AddWithValue("in_servicable", cities.Servicable);
if (cities.CreatedBy != 0)
{
cmd.Parameters.AddWithValue("in_created_by", cities.CreatedBy);
}
else
{
cmd.Parameters.AddWithValue("in_created_by", DBNull.Value);
}
cmd.Parameters.AddWithValue("in_creation_date", cities.CreationDate);
if (cities.LastUpdatedBy != 0)
{
cmd.Parameters.AddWithValue("in_last_updated_by", cities.LastUpdatedBy);
}
else
{
cmd.Parameters.AddWithValue("in_last_updated_by", DBNull.Value);
}
cmd.Parameters.AddWithValue("in_last_updated_date", cities.LastUpdatedDate);
if (cities.LastUpdatedLogin != 0)
{
cmd.Parameters.AddWithValue("in_last_updated_login", cities.LastUpdatedLogin);
}
else
{
cmd.Parameters.AddWithValue("in_last_updated_login", DBNull.Value);
}
cmd.ExecuteNonQuery();
//////////////////////
using (OracleConnection MyOracleconn = new OracleConnection(myOracle))
{
MyOracleconn.Open();
OracleCommand cmdo = new OracleCommand("obs_update_cities_tflag_proc1", MyOracleconn);
cmdo.CommandType = CommandType.StoredProcedure;
OracleParameter inval = new OracleParameter("in_city_id", OracleType.VarChar, 50);
inval.Direction = ParameterDirection.Input;
inval.Value = cities.CityID;
if (cities.CityID == 2)
inval.Value = "sdsd";
cmdo.Parameters.Add(inval);
cmdo.ExecuteNonQuery();
}
}
//// Now scope commit
scope1.Complete();
}
}
}
catch (Exception ex)
{
}
finally
{
}
}
How to repeat:
Please try to insert records in 2 table of different databases in single transaction.
1st table should be in MySql and 2nd should be in Oracle
Description: I am Senior developer using C# with asp.net facing distributed transaction not supported currently in MySql connector. I am using MySQL ENGINE = InnoDB and In Connection string set AutoEnlist = True and providerName="MySql.Data.MySqlClient" Server -- Web server as IIS Platform ---> VS 2008 Database --> Oracle11g and MySql Server 5.6 MySql Connector --> connector Net 6.7.4 with version 2.0 I am trying to insert record in first MySql DB and than update in oracle DB but always get exception distributed transaction not supported currently in MySql connector. Can anybody atleast tell me it is possible or not, if yes than How can i acheive it. Please see the code below... protected void Button3_Click_SqlInsert_Another(object sender, EventArgs e) { CitiesAccess citiesAccess = new CitiesAccess(); List<Cities> lstCities; lstCities = citiesAccess.GetCities(); // Initialize the return value to zero and create a StringWriter to display results. int returnValue = 0; string mySQL = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString.ToString(); string myOracle = System.Configuration.ConfigurationManager.ConnectionStrings["DMSConnectionString"].ToString(); try { foreach (Cities cities in lstCities) { // Create the TransactionScope to execute the commands, guaranteeing // that both commands can commit or roll back as a single unit of work. using (TransactionScope scope1 = new TransactionScope()) { using (MySqlConnection mysqlCon = new MySqlConnection(mySQL)) { mysqlCon.Open(); MySqlCommand cmd = new MySqlCommand(Constants.Cities.SP_INSERTUPDATECITIES); //cmd.CommandTimeout = 0; cmd.Connection = mysqlCon; cmd.CommandType = CommandType.StoredProcedure; if (cities.CityID != 0) { cmd.Parameters.AddWithValue("in_city_id", cities.CityID); } else { cmd.Parameters.AddWithValue("in_city_id", DBNull.Value); } //if (cities.CityID == 1) // cmd.Parameters["in_city_id"].Value = "sdsds"; cmd.Parameters.AddWithValue("in_city_code", cities.CityCode); cmd.Parameters.AddWithValue("in_city_name", cities.CityName); if (cities.DistrictId != 0) { cmd.Parameters.AddWithValue("in_district_id", cities.DistrictId); } else { cmd.Parameters.AddWithValue("in_district_id", DBNull.Value); } cmd.Parameters.AddWithValue("in_servicable", cities.Servicable); if (cities.CreatedBy != 0) { cmd.Parameters.AddWithValue("in_created_by", cities.CreatedBy); } else { cmd.Parameters.AddWithValue("in_created_by", DBNull.Value); } cmd.Parameters.AddWithValue("in_creation_date", cities.CreationDate); if (cities.LastUpdatedBy != 0) { cmd.Parameters.AddWithValue("in_last_updated_by", cities.LastUpdatedBy); } else { cmd.Parameters.AddWithValue("in_last_updated_by", DBNull.Value); } cmd.Parameters.AddWithValue("in_last_updated_date", cities.LastUpdatedDate); if (cities.LastUpdatedLogin != 0) { cmd.Parameters.AddWithValue("in_last_updated_login", cities.LastUpdatedLogin); } else { cmd.Parameters.AddWithValue("in_last_updated_login", DBNull.Value); } cmd.ExecuteNonQuery(); ////////////////////// using (OracleConnection MyOracleconn = new OracleConnection(myOracle)) { MyOracleconn.Open(); OracleCommand cmdo = new OracleCommand("obs_update_cities_tflag_proc1", MyOracleconn); cmdo.CommandType = CommandType.StoredProcedure; OracleParameter inval = new OracleParameter("in_city_id", OracleType.VarChar, 50); inval.Direction = ParameterDirection.Input; inval.Value = cities.CityID; if (cities.CityID == 2) inval.Value = "sdsd"; cmdo.Parameters.Add(inval); cmdo.ExecuteNonQuery(); } } //// Now scope commit scope1.Complete(); } } } catch (Exception ex) { } finally { } } How to repeat: Please try to insert records in 2 table of different databases in single transaction. 1st table should be in MySql and 2nd should be in Oracle