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