.net mysql transactionscope,使用.NET的MySqlConnector是否完全支持TransactionScope对象？

I'm writing unit tests (technically integration tests since I am connecting to a database) and I want to create records in a transaction within the test, and rollback all database modifications once the test completes. The idea being that I'll create records through one API call that another API call expects to find in the database.

I have the following code that works:

string connectionstring = "Server=MyDbServer;Database=MySchema;Uid=MyUser;Pwd=XXX;";

string sql = "Insert Into MyTable (date, Description) VALUES('2009-12-11', 'foo test description');";

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))

using (MySqlConnection conn = new MySqlConnection(connectionstring))

{

conn.Open();

using (MySqlCommand cmd = new MySqlCommand(sql, conn))

{

cmd.ExecuteNonQuery();

}

}

However, if I move the TransactionScope instantiation to occur just before the MySqlCommand, the transaction is not rolled back, and my test data is persisted to the database:

string connectionstring = "Server=MyDbServer;Database=MySchema;Uid=MyUser;Pwd=XXX;";

string sql = "Insert Into MyTable (date, Description) VALUES('2009-12-11', 'foo test description');";

using (MySqlConnection conn = new MySqlConnection(connectionstring))

{

conn.Open();

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))

using (MySqlCommand cmd = new MySqlCommand(sql, conn))

{

cmd.ExecuteNonQuery();

}

}

What am I missing?

Note: The MySQL server (v. 5.0.67-0ubuntu6) is hosted on a linux machine, MySqlConnecter version is 6.1.3

解决方案

From the docs (for SQL Server compact, but still relevant):

Implicitly enlisting connections is

not supported. To enlist in a

transaction scope, you can do the

following:

Open a connection in a transaction

scope.

Or, if the connection is already

opened, call EnlistTransaction method

on the connection object.

In the first example, you are opening a connection in a transaction scope. In the 2nd you are not enlisting.

Try adding:

cmd.Connection.EnlistTransaction(Transaction.Current);

To see if that helps.