Nested TransactionScope

[dazzknowles]

Hi,

We're having an issue with transactions and TransactionScope.

Basically we're using the repository pattern and there are certain processes which mean that different repository calls and chained one after the other to and we want those to happen atomically. The repository calls also need to be available separately and each of our add/update methods will always run at least 2 DB sproc calls as we have an auditing framework implemented within them.

To keep the layers separated we want to avoid bubbling the MySQL Connection/Transaction all the way into the Business Logic Layer, we would prefer to use the inbuilt TransactionScope but this doesn't appear to work with this connector as all the db calls need to happen within the same connection.

We did create our own simple Transaction helper which was OK but now we have these nested transactions to deal with it is not working as it should.

Is there a way of using TransactionScope with dotConnect? Can these be nested? Has anyone had an similar experience in this or can shed any light on alternative solutions?

[Pinturiccio]

dotConnect for MySQL supports distributed transactions. Here is a small sample of using nested TransactionScopes with dotConnect for MySQL:

Code: Select all

using (var scopeOuter = new TransactionScope())
{
    using (var conn = new MySqlConnection("Connection string 1"))
    {
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO Dept (deptno, dname, loc)VALUES(701,'test', 'test')";
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
    using (var scopeInner = new TransactionScope())
    {
        using (var conn = new MySqlConnection(""))
        {
            using (MySqlCommand cmd = conn.CreateCommand("Connection string 2"))
            {
                cmd.CommandText = "INSERT INTO Dept (deptno, dname, loc)VALUES(702,'test', 'test')";
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
        }
        scopeInner.Complete();
    }
    scopeOuter.Complete();
}

If TransactionScope does not work for you with dotConnect for MySQL, please create and send us a small test project with the corresponding DDL/DML script for reproducing the issue. Please also describe the steps we should perform for reproducing the issue.

[dazzknowles]

Hi,

Thanks for the reply. We've tried it again and it does indeed work.

From subsequent development work we've found that if there's an error in a stored procedure called within a TransactionScope, the error refers to a global transaction being active rather than the details of the error which is what sent us on the wrong path.