Using MySqlTransaction with MySqlDataAdaptor

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
cbamford
Posts: 7
Joined: Wed 17 Nov 2004 15:30
Location: Sunny Manchester, England

Using MySqlTransaction with MySqlDataAdaptor

Post by 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);
}

Serious

Post by 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.

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

Post by 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

Post by 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.

Post Reply