Visual DataSet designer generated MySqlTableAdapter and Transactions

[bunton]

Hello everyone,

I'm currently developing an application which relies heavily on the use of MySqlDataAdapter classes that are auto-generated by the Visual Studio DataSet designer (using the MySqlDirect provider).

What I'm trying to figure out is if there is an easy way for me to incorporate transaction support with the Insert/Update/Delete commands that are auto-generated for a given MySqlTableAdapter.

Here's what I've tried but it doesn't seem to be using the MySqlTransaction that I'm creating from a MySqlConnection that I manually open.

Please note, I've created a static function in my DataSet class to return a new MySqlConnection object.

Code: Select all

TableAdapter1 ta1 = new TableAdapter1();
TableAdapter2 ta2 = new TableAdapter2();
MySqlConnection conn = MyDataSet.GetConnection();  // returns a new un-opened connection
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();

MySqlConnection origconn = ta1.Connection;
ta1.Connection = conn;  // set it to the connection I've started a transaction in
ta1.Update(Table1);
ta1.Connection = origconn; // reset the connection back to the original one

origconn = ta2.Connection;
ta2.Connection = conn;  // set it to the connection I've started a transaction in
ta2.Update(Table2);
ta2.Connection = origconn;  // reset the connection back to the original one

transaction.Commit();  // attempt to commit the transaction to the db
conn.Close(); // close the connection

Now, it seems as though the transaction is not actually working as I can comment out the transaction.Commit() statment and the database updates are still being made.

From some of the documentation I've read, it seems as though you have to also explicity set the Transaction property of the MySqlCommand class, in addition to the Connection property. My problem is that I do not have public access to the MySqlCommand objects that are auto-generated in the MySqlTableAdapters.

Can anybody shed some light on this problem? Is what I'm asking even possible?

Thanks,
bunton

[Alexey]

Could you please clarify which class you rely on: DataAdapter or TableAdapter.

[bunton]

Could you please clarify which class you rely on: DataAdapter or TableAdapter.

Sorry about that, its the auto-generated MyDataTableAdapter classes which wrap MySqlDataApater.

When performing sql commands on a Connection that has been opened and a transaction has been started using Connection.BeginTransaction(), are the commands only committed to the database when the Transaction.Commit() method is called?

If a MySqlTransaction has been started and then the associated MySqlConnection is closed before the transaction is committed, does the MySqlConnection automatically roll-back the transaction?

Thanks,
bunton

[Alexey]

Commands are committed to the database only when the Transaction.Commit() method is called.
If you encounter another behaviour, check that you have InnoDB database engine. What is the version of your MySQL Server?

[bunton]

Thanks for your help. Not sure what I was doing wrong before, but I managed to get it working using the example I had provided above.

Basically I am able to swap in the Connection for any generated DataAdapter and then restore the Connection to the previous one once I've finished executing my update. Changes are not made to the database until Transaction.Commit() is called. If I Close() the connection before I Commit(), all the changes are automatically rolled-back.

Just to answer your question, I am using InnoDB and MySQL Community edition 5.0.27.

Thanks again,
bunton

[Alexey]

Well done.

[JoeDev]

do you can explain how to create a static function in the DataSet class to return a new MySqlConnection object please? thanks!

sorry my english...

[bunton]

do you can explain how to create a static function in the DataSet class to return a new MySqlConnection object please? thanks!

sorry my english...

Sure no problem.. it's actually pretty straight forward. All you have to do in Visual Studio is open up the xxxDataSet.cs file that's automatically created when you add a DataSet to your project. In the xxxDataSet.cs file, just add the following lines of code:

Code: Select all

// returns back a MySqlConnection object
public static CoreLab.MySql.MySqlConnection GetMySqlConnection()
{
   MySqlConnection connection = new MySqlConnection();

   // pull the ConnectionString from the application settings
   connection.ConnectionString = global::MyProject.Data.Properties.Settings.Default.ConnectionString;
   return connection;
}

// same function as above, just returns a generic DbConnection object
public static DbConnection GetConnection()
{
   return (DbConnection) GetMySqlConnection();
}

// another useful function to return back a generic DbCommand object
// that can be used to perform generic sql commands
public static DbCommand GetCommand()
{
   MySqlConnection connection = GetMySqlConnection();
   MySqlCommand cmd = new MySqlCommand();
   cmd.Connection = connection;
   return cmd;
}

That should do it. If you're going to use the generic DB objects, remember to add using System.Data.Common; to the top of the file.

Cheers,
bunton

[JoeDev]

thanks bunton!!!