Visual DataSet designer generated MySqlTableAdapter and Transactions

Visual DataSet designer generated MySqlTableAdapter and Transactions

Postby bunton » Fri 16 Feb 2007 21:11

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
bunton
 
Posts: 7
Joined: Wed 07 Feb 2007 19:40

Postby Alexey » Mon 19 Feb 2007 07:58

Could you please clarify which class you rely on: DataAdapter or TableAdapter.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby bunton » Mon 19 Feb 2007 16:17

Alexey wrote: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
bunton
 
Posts: 7
Joined: Wed 07 Feb 2007 19:40

Postby Alexey » Tue 20 Feb 2007 08:17

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?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby bunton » Tue 20 Feb 2007 15:05

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
bunton
 
Posts: 7
Joined: Wed 07 Feb 2007 19:40

Postby Alexey » Wed 21 Feb 2007 07:00

Well done.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby JoeDev » Thu 22 Feb 2007 17:32

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...
JoeDev
 
Posts: 2
Joined: Thu 22 Feb 2007 17:26
Location: Curitiba, Brasil

Getting a MySqlConnection from a DataSet

Postby bunton » Thu 22 Feb 2007 20:26

JoeDev wrote: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
bunton
 
Posts: 7
Joined: Wed 07 Feb 2007 19:40

Postby JoeDev » Tue 13 Mar 2007 13:18

thanks bunton!!! :D
JoeDev
 
Posts: 2
Joined: Thu 22 Feb 2007 17:26
Location: Curitiba, Brasil


Return to dotConnect for MySQL