Using MySqlTransaction with MySqlDataAdaptor

Using MySqlTransaction with MySqlDataAdaptor

Postby cbamford » Thu 24 Mar 2005 09:53

I have recently upgraded to version 2.70.3.0 and am now experiencing some problems with my source code.

Prior to the upgrade I simply used a MySqlDataAdaptor (with a DataSet) to handle all DB operations (insert, updates, etc) and everything worked with no problems. Since the upgrade I have had need to use transactions in my application (user discarding new edits, etc). The code I had to write changes back to the DB has not changed.

The problem. DB inserts, updates and deletes are no longer working since I started using the transaction. These are valid DB table changes which all worked previously and no code has changed to do with saving changes back to the DB.

My question is. Do I need to revise my commit method to take into account the changes to use transactions ? If I do, will the adaptor be aware of the transaction ? In essence, what code needs to be looked at and revised ?

Many thanks in advance for any info/suggestions.

Here's the code I use to save changes back to the DB.

Code: Select all
cMySqlDataAdapter.Update(m_cDataSet);


Here's the code I use to populate the data set and adaptor:

Code: Select all
try
{
   m_cDataSet = new DataSet();
   cMySqlDataAdapter.FillSchema(m_cDataSet, SchemaType.Mapped, "ResultsTable");
   cMySqlDataAdapter.Fill(m_cDataSet);
}
catch (MySqlException e)
{
   // throw a new select exception
   string strError;
   strError = "Select command failed.  SQL is: \n";
   strError += cMySqlDataAdapter.SelectCommand.CommandText;
   strError += "\nError is: \n";
   strError += e.Message;
   throw new DAOSelectException(strError);
}


Here's the code I use to create a transaction.

Code: Select all
try
{
   cMySqlConnection.Open();
   cMySqlConnection.BeginTransaction();
   m_bIsConnectionOpen = true;
}
catch (MySqlException ex)
{
   throw new DAOConnectException("Failed to connect to the database. Reason: " + ex.Message);
}
cbamford
 
Posts: 7
Joined: Wed 17 Nov 2004 15:30
Location: Sunny Manchester, England

Postby Serious » Thu 24 Mar 2005 10:47

Try to use this code:
Code: Select all
...
MySqlTransaction transaction = cMySqlConnection.BeginTransaction();

...
insert, update, delete rows
...

if ()
  transaction.Rollback(); // cancel modifications
else
  transaction.Commit(); // save modifications to the database
...
Note that instead of m_bIsConnectionOpen you can use MySqlConnection.State property.
Serious
 

Postby cbamford » Thu 24 Mar 2005 11:18

I'm still unsure of how the transaction works with the adaptor ?

I have created a library (around CoreLab) which relies heavily on the MySqlDataAdaptor. If I use MySqlTransaction to commit or rollback data, do I need to revise my library code and remove the use of MySqlAdaptor ?

Thanks.
cbamford
 
Posts: 7
Joined: Wed 17 Nov 2004 15:30
Location: Sunny Manchester, England

Postby cbamford » Thu 24 Mar 2005 12:17

It's OK, I've resolved my problem now. I simply call RejectChanges on my data set when the user wants to cancel any edits.

Thx for the help though.
cbamford
 
Posts: 7
Joined: Wed 17 Nov 2004 15:30
Location: Sunny Manchester, England


Return to dotConnect for MySQL