CODEWITHSUNDEEP
mysql.nettransactionsentity-framework-6
betelgewse
betelgewse

Reputation: 406

TransactionScope with EntityFramework 6 and MySql

I have the following requirement for a project: preform transactions over multiple databases in MySql with EntityFramework (all databases are on the same mysql server).

When trying to solve the issue with TransactionScope, there seems to be an issue with different MySql connection strings:

"Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported."

The issue is already described here (without any concrete solution): How do I use TransactionScope with MySql and Entity Framework? (getting Multiple simultaneous connections...are not currently supported error)

As a workaround I tried ommiting the database parameter from the connection string, which works if I open the connection and set the database before the .SaveChanges() method is called (by overloading the method in a class that inherits from DbContext). But calling the same statement for each select statement is just not feasable.

My custom class looks like this:

public class ContextBase : DbContext
{
    public ContextBase(string connectionStringWithoutDatabase)
        : base(connectionStringWithoutDatabase)
    {}

    public override int SaveChanges()
    {
        Database.Connection.Open();
        Database.Connection.ChangeDatabase("MyDatabaseName");
        base.SaveChanges();
        Database.Connection.Close();
    }

    // How to handle Selects?
}

My Unit of work class:

public class UnitOfWork
{
    private IEnumerable<DbContext> ContextList
    {
        get { return _contextList; }
    }

    private readonly IEnumerable<DbContext> _contextList;
    public UnitOfWork(IEnumerable<DbContext> contextList)
    {
        _contextList = contextList;
    }

    public void Save()
    {
        var transactionScope = new TransactionScope();
        foreach (DbContext context in ContextList)
        {
            context.SaveChanges();
        }
        transactionScope.Complete();
        transactionScope.Dispose();
    }
}

Another possible workaround would be to create a wrapper in which there would be two DbContext instances - one with the database set for select statements, the other without for the non-query operations. But this just feels wrong.

Now my question(s):

Upvotes: 0

Views: 1009

Answers (2)

betelgewse
betelgewse

Reputation: 406

We ended up finding a solution to the problem of setting the database name before each select. To achieve this one must create a class that implements IDbCommandInterceptor and register it with your context (we did it with this.AddInterceptor(new DatabaseSetterCommandInterceptor()); in the constructor of the DbContext). In the different functions of that interface you can change the Database before the SQL is sent to the server. A rough test also showed no noticable performance degradation.

Upvotes: 1

Hieu Le
Hieu Le

Reputation: 1132

I tried with Devart library. It works for me. The belows are my code that I run.

NOT WORKING - MySql.Data.MySqlClient.MySqlConnection

using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(TransactionScopeOption.Required))
{
    MySql.Data.MySqlClient.MySqlConnection connect1 = new MySql.Data.MySqlClient.MySqlConnection("Server=192.168.0.1;Database=db1;Uid=root;Pwd=root;");
    MySql.Data.MySqlClient.MySqlConnection connect2 = new MySql.Data.MySqlClient.MySqlConnection("Server=192.168.0.2;Database=db2;Uid=root;Pwd=root;");
    connect1.Open();
    connect2.Open();


    var command1 = connect1.CreateCommand();
    var command2 = connect2.CreateCommand();

    command1.CommandText = "INSERT INTO test01(`Value`) VALUES(SYSDATE());";
    command2.CommandText = "INSERT INTO test02(`Value`) VALUES(SYSDATE())";

    command2.ExecuteNonQuery();
    throw new Exception("bbbbbb");
    command1.ExecuteNonQuery();

    scope.Complete();
}

WORKING - Devart.Data.MySql.MySqlConnection

using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(TransactionScopeOption.Required))
{
    Devart.Data.MySql.MySqlConnection connect1 = new Devart.Data.MySql.MySqlConnection("Server=192.168.0.1;Database=db1;Uid=root;Pwd=root;");
    Devart.Data.MySql.MySqlConnection connect2 = new Devart.Data.MySql.MySqlConnection("Server=192.168.0.2;Database=db2;Uid=root;Pwd=root;");
    connect1.Open();
    connect2.Open();


    var command1 = connect1.CreateCommand();
    var command2 = connect2.CreateCommand();

    command1.CommandText = "INSERT INTO test01(`Value`) VALUES(SYSDATE());";
    command2.CommandText = "INSERT INTO test02(`Value`) VALUES(SYSDATE())";

    command2.ExecuteNonQuery();
    throw new Exception("bbbbbb");
    command1.ExecuteNonQuery();

    scope.Complete();
}

Upvotes: 1

Related Questions