Transactions on multiple DAL methods from one method to BLL

Question

How could you call multiple methods at the data access level from one method at the business logic level so that all SQL commands live in the same SQL transaction?

Each of the DAL methods can be called individually from other places in the BLL, so there is no guarantee that data-level methods are always part of the transaction. We need this functionality, so if the database goes offline in the middle of a long process, there is no commit. The business layer organizes various calls to data-level methods based on the results of each of the previous calls. We just want to commit (from the business level) at the very end of the whole process.

Answer 1

Well, firstly, you have to stick with the atomic unit of work, which you will specify as one method in your BLL. This could (for example) create a customer, order and order elements. you would neatly wrap all of this inside a TransactionScope statement. TransactionScope is the secret weapon here. below is the code, which, fortunately, I am working now :):

public static int InsertArtist(Artist artist) { if (artist == null) throw new ArgumentNullException("artist"); int artistid = 0; using (TransactionScope scope = new TransactionScope()) { // insert the master Artist /* we plug the artistid variable into any child instance where ArtistID is required */ artistid = SiteProvider.Artist.InsertArtist(new ArtistDetails( 0, artist.BandName, artist.DateAdded)); // insert the child ArtistArtistGenre artist.ArtistArtistGenres.ForEach(item => { var artistartistgenre = new ArtistArtistGenreDetails( 0, artistid, item.ArtistGenreID); SiteProvider.Artist.InsertArtistArtistGenre(artistartistgenre); }); // insert the child ArtistLink artist.ArtistLinks.ForEach(item => { var artistlink = new ArtistLinkDetails( 0, artistid, item.LinkURL); SiteProvider.Artist.InsertArtistLink(artistlink); }); // insert the child ArtistProfile artist.ArtistProfiles.ForEach(item => { var artistprofile = new ArtistProfileDetails( 0, artistid, item.Profile); SiteProvider.Artist.InsertArtistProfile(artistprofile); }); // insert the child FestivalArtist artist.FestivalArtists.ForEach(item => { var festivalartist = new FestivalArtistDetails( 0, item.FestivalID, artistid, item.AvailableFromDate, item.AvailableToDate, item.DateAdded); SiteProvider.Festival.InsertFestivalArtist(festivalartist); }); BizObject.PurgeCacheItems(String.Format(ARTISTARTISTGENRE_ALL_KEY, String.Empty, String.Empty)); BizObject.PurgeCacheItems(String.Format(ARTISTLINK_ALL_KEY, String.Empty, String.Empty)); BizObject.PurgeCacheItems(String.Format(ARTISTPROFILE_ALL_KEY, String.Empty, String.Empty)); BizObject.PurgeCacheItems(String.Format(FESTIVALARTIST_ALL_KEY, String.Empty, String.Empty)); BizObject.PurgeCacheItems(String.Format(ARTIST_ALL_KEY, String.Empty, String.Empty)); // commit the entire transaction - all or nothing scope.Complete(); } return artistid; } 

hope you get the gist. basically, this is all a successful or unsuccessful task, regardless of any disparate databases (i.e., in the above example, the artist and the artist-artist can be located in two separate db repositories, but TransactionScope will take care of it less, it works at the COM + level and controls the atomicity of the region that he can "see")

hope this helps

EDIT:, you will find that the initial TransactionScope call (when the application starts) may be slightly noticeable (i.e., in the above example, if it is called for the first time, it can take 2-3 seconds), but subsequent calls are almost instantaneous (t i.e. usually 250-750 ms). the trade-off between a simple contact transaction with (bulky) alternatives softens (for me and my clients) the initial download delay.

just wanted to demonstrate that lightness does not come without compromise (albeit in the initial stages)

Answer 2

What you are describing is the "definition" of a long transaction.

Each DAL method can simply perform operations (without any specific commits). Your BLL (which actually works when you coordinate any DAL calls anyway) is a place where you can choose a commit or execute a “savepoint”. A savepoint is an optional element that you can use to allow rollbacks in a long-term transaction.

So, for example, if my DAL has methods DAL1, DAL2, DAL3, all of them are mutative, they will simply perform data modification operations (i.e. some types of Create, Update, Delete). From my BLL, let's assume that I have methods BL1 and BL2 (BL1 is long). BL1 calls all of the above DAL methods (i.e. DAL1 ... DAL3), while BL2 calls only DAL3.

Therefore, when executing each business logic method, you may have the following:

BL1 (long-transaction) → {savepoint} DAL1 → {savepoint} DAL2 → DAL3 {commit / end}

BL2 → DAL3 {commit / end}

The idea of ​​a “savepoint” is that BL1 can roll back at any point if there are problems with data operations. A long transaction is only executed after the successful completion of all three operations. BL2 can still call any method in DAL, and it is responsible for controlling commits. NOTE. You can also use “savepoints” in short / regular transactions.

Answer 3

Good question. This leads to impedance mismatch.

This is one of the strongest arguments for using stored procedures. Reason: They are designed to encapsulate multiple SQL statements in a transaction.

The same can be done procedurally in DAL, but this leads to code with less clarity, while it usually leads to shifting the cohesion / cohesion balance in the wrong direction.

For this reason, I implement DAL at a higher level of abstraction than just encapsulating tables.

Answer 4

just in case, if my comment in the original article does not “stick”, here is what I added as additional information:

<----- by coincidence, just noticed another similar link to this, published a few hours after your request. uses a similar strategy and might be worth your while: http://stackoverflow.com/questions/494550/how-does-transactionscope-roll-back-transactions ----->