I am designing a complete set of modules for an organization. The modules would totally abstract the end-users from DNN Administration tasks like Role or User Management, as these tasks are to be performed implicitly as the result of user actions on a module.
e.g. When a user creates an organizational department on one of the modules, a Role needs to be automatically created for that department, and all members of that department be assigned to that module.

The task itself is not too complex. I create a department through my Data Layer, and can create a Role for it through the DNN Provider.

The problem arises when considering the fact that these actions need to be performed inside a transaction. If department is created, but for some reason, the Role API cannot create the role, the Department creation needs to be rolled back.

So, what is the best practise solution in this regard??? Should I implement the transaction in the Business Logic class, which calls 2 methods in DAL (one for creating department, and the other for creating Role) or in the Data Access class, which creates the Department & role in one go???

Also, can I in some way make the DNN Role API's method for creating the Role execute as part of a transaction initiated in my BL or DAL layer, or would I need to rewrite that method in my DAL???

When you say 'Role API commands', what exactly does that mean???

You mean the methods in the Role API provider, or the Stored Procedures in the Sql Database implementation??? I believe you mean SPs as calling methods is hardly useful, because they dont support transactions, but open thier own connections for executing operations!!!

And supposing you mean SPs, then from a best practices perspective, what is advisable?

1) I create methods in the DAL that call the SP to create the role. Then, have my BL class call the methods for creating department & then the Role one after the another. In that case, the BL would need to be aware of the underlying transaction in some way, and would need to request the DAL to commit, if everything is fine.

2) Have my DAL method to create the department perform 2 operations: i) Create department, and then ii) Create Role. In this case, the BL would be totally abstracted from the underlying trasanction, as the DAL can itself decide when to commit or abort.

To me, although the second approach seems more attractive from an implementation point, but logically, the method for creating department should only do that. It is the BL that knows, that creation of department should be followed by creation of a role!!!

Yes, your solution is more maintainable. But for those situations that hit you when you least expect them, it can leave the data in inconsistent state.

Suppose, role insert fails, and then for some reason, the Department deletion also fails (probably Network connection lost, or whatever...)

I thought of delaying department insertion commit, till Role is created, but then, it might happen that the Role is created, but Commit fails

Now, this would be a hard decision to make, ensure 100% consistency, or Upgradation & maintainence issues (Not to mention the complexities of implementing transactions...)

I need more time to make a decision. But by the way, you did not answer my query. If I do decide to take on the complexity, and implement transaction, then what would be the Best practise approach from the above mentioned alternatives in my previous post???

Thanx!!!