EF Transactions c# MySQL connector

Question

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

On another topic: Oh noes! a throw ex; !!! You should do throw unless you really want to lose your stacktracke. — BennyM, Jul 20 '11 at 12:32
That throw is being catched by the caller and thrown again so that the Global.asax in the Application_Error event would do the generic handling of the error — Ryan, Jul 20 '11 at 12:36
Just making sure you know the difference between throw; and throw ex; — BennyM, Jul 20 '11 at 12:45

BennyM · Accepted Answer · 2011-07-20T15:23:45.163

1

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

edited Jul 20 '11 at 15:23

answered Jul 20 '11 at 12:40

BennyM

2,796
16
24

I am not sure where you got the ChangeRole method under userFromDb EntityObject, could you please explain? – Ryan Jul 20 '11 at 12:54
Or those user and role classes you created? Then I would add functionality to them to suit the problem at hand. – BennyM Jul 20 '11 at 12:57
But the problem is that at each method I am saving the changes (calling entities.SaveChanges()) since sometimes I will need to call the ChangeRole or AddUser method manually (without being invoked by the EditUser or RegisterUser) that's why I will need to have a transaction so that I could commit or rollback depending of what have happened – Ryan Jul 20 '11 at 13:02
Thanks mate, I appreciate, cause I've been stuck on this for like 2 days already – Ryan Jul 20 '11 at 13:08
I tried to go once again with the TransactionScope and the error happens on the EnlistTransaction method since I am running in Medium trust, any ideas why? – Ryan Jul 20 '11 at 14:13
You're probably experiencing this: http://stackoverflow.com/questions/794364/how-do-i-use-transactionscope-in-c if so you can only get around it with the 2nd option I mentioned. Or you need to switch hoster :) – BennyM Jul 20 '11 at 15:34

score 0 · Answer 2 · answered Jul 20 '11 at 12:33

0

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.

answered Jul 20 '11 at 12:33

jlew

10,491
1
35
58

Yes this is an option but I was thinking in the beginning that this option is some kind of "patch work" – Ryan Jul 20 '11 at 12:37
This is essentially what TransactionScope does -- it enlists in the ambient transaction if there is one and creates a new one otherwise. You are just doing this yourself since you can't use TransactionScope. If you wrap it up nicely in a method, it seems legit to me. – jlew Jul 20 '11 at 13:43

EF Transactions c# MySQL connector

2 Answers2