CODEWITHSUNDEEP
c#mysqlentity-framework-4transactionstransactionscope
Ryan
Ryan

Reputation: 263

EF Transactions c# MySQL connector

I was wondering what is the best way or practice to implement the following:

I have a method called EditUser which has the following definition:

public void EditUser(user user, Roles role)
    {
        using (TestEntities entities = new TestEntities())
        {
            entities.Connection.Open();
            using (DbTransaction trans = entities.Connection.BeginTransaction())
            {
                try
                {
                    var tmpUser = entities.users.Where(fields => fields.UserId == user.UserId).FirstOrDefault();

                    RoleManagement rm = new RoleManagement();
                    string oldRole = tmpUser.roles.FirstOrDefault().Name;
                    string newRole = rm.GetRoleName(role);

                    if (oldRole == newRole)
                    {
                        entities.users.ApplyCurrentValues(user);
                    }
                    else
                    {
                        rm.UnbindRoles(tmpUser.UserId, entities.Connection);
                        this.DeleteUser(tmpUser.UserId, entities.Connection);
                        this.Register(user, role, entities.Connection);
                    }

                    entities.SaveChanges();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
        }
    }

As you may notice I am calling several methods: UnbindRoles, DeleteUser and RegisterUser where all of them need to be running under the same transaction (the same transaction the EditUser is running) in case something fails I will need to rollback.

Now my problem is mostly here...the method RegisterUser also starts a new transaction since it adds a user and assign the new user a role.

Can anyone describe the best way how to implement this? I tried using a TransactionScope block but it fails since I am running this application in a Medium Trust environment

EDIT

Confirming that the MySQL .NET connector provider has a bug when using TransactionScope under Medium Trust since I have just tested now the same scenario using a MS SQL Server database and it works without any problems

Upvotes: 0

Views: 1404

Answers (2)

BennyM
BennyM

Reputation: 2856

If I understand it correctly the problem you're having is that your calls to DeleteUser and RegisterUser are starting new transactions and need to enlist in the current one.

Several ways to fix this:

A) When you create a transactionscope you can say that they only need to create one if there isn't one already available.

B) Instead of doing the first approach introduce either private methods let's say InnerEdit and InnerRegister which contain the logic but don't open or close any transactions that code remains in the publicly available Edit and Register methods. Those "inner" methods can be reused while the public ones contain the infrastructure. If you're going the 2nd approach google for aspect oriented programming, that can address these 'cross cutting concerns' 

public void EditUser(user user, Roles role)
{
    using (TestEntities entities = new TestEntities())
    {
        entities.Connection.Open();
        using (DbTransaction trans = entities.Connection.BeginTransaction())
        {
          InnerEditUser(entities, trans);
          InnerThat(entities, trans);
          InnerThis(entities,trans);
          entities.SaveChanges();
          trans.Commit();
        }
    }
 }

TransactionScope options MSDN link

Upvotes: 1

jlew
jlew

Reputation: 10601

Can you have Register check for an existing transaction and only create one if none exists? That is what nested TransactionScopes would do in similar circumstances.

Upvotes: 0

Related Questions