Implementing DAL Transactions RRS feed

  • Question


    Hello to all. I'm currrently seeking out design for implementing DB Transactions across DAL method calls.


    The following are sample code of my DAL Design:


    My DALs expose methods for CRUD operations for particular Business Entities:


    Here's an example of my DAL design for the scenario of storing



    public class SalesOrderDAL


        public void AddSalesOrder(SalesOrder s)


               /* Some ADO.NET Code here to connect to the database and persist the

               sales order data.*/


    public class SalesOrderItemDAL

        public void AddSalesOrderItems(SalesOrderItemCollection items)

               /* Some ADO.NET Code here to connect to the DB and persist  sales order items data. */




    At some point in my application i need to call these two methods as one unit.


    // PERSIST SalesOrder information


    public void SaveSalesOrderInformation(SalesOrder so)


         // Start Transaction Here.


         SalesOrderDAL s1 = new SalesOrderDAL();

         SalesOrderItemDAL s2 = new SalesOrderItemDAL();




         foreach(SalesOrderItem si in so.Items)



         // Commit Transaction Here.



    How implement the transaction given this code block? Any suggestions will do.. Thanks!

    Saturday, October 6, 2007 7:02 AM

All replies


    Please look at this location for a sample on ADO.Net transaction

    Saturday, October 6, 2007 3:58 PM

  • Are you saving sales order items outside of saving an order?

    why not (conceptually):

    SalesOrder sale = new SalesOrder();
    sale.Cashier = "Bob";
    sale.Add(New SaleItem(45, "Nut"));
    sale.Add(New SaleItem(23, "Screw"));
    New SalesOrderDAL().Save(sale);    //I'd call this SalesOrderRepository in this case instead

    Saving a sale with sales items attached saves them too as part of a transaction.

    A quick google on Aggregates and Repostiories will give you some background on this concept


    Ed Hill

    Saturday, October 6, 2007 8:19 PM
  • I would suggest using the Unit of Work pattern to tie together multiple calls to your DAL in the same transaction.


    Technologically speaking, using System.Transactions.TransactionScope within your DAL, as well as in your "SaveSalesOrderInformation" method like so:


    using (TransactionScope scope = new TransactionScope())


      // your logic here




    Sunday, October 7, 2007 12:30 PM
  • I agree with Udi on this one and just to add that transactions should be started and completed\failed in the BLL and NOT the DAL - this then allows you to pass the transaction between mutliple DAL methods, this is where System.Transaction namespace is very useful.


    Ollie Riches



    Sunday, October 7, 2007 12:53 PM
  • plenty resources available

    Implementing Database Transactions with Microsoft .NET

    Introducing System.Transactions in the .NET Framework 2.0
    Handling Transactions Between .NET Components


    and two webcasts from India [still to come];  Data Architecture, Designing Service and Business Layer

    and I'm these webcasts, a year old [MSDN webcasts by Joe Hummel]


    happy reading

    Monday, October 8, 2007 3:54 AM
  • I agree with the above that transactions must be managed within the BLL. I'd prefer implicit transactions managment rather than explicit transactions management. Most of the above all use explicit transactions where your write the code to start and end transactions.

    Implicit transaction hide all the details about transactions from you and it gives you the prower of controlling the transactions behavior. Implicit transactions almost go like this...

    1. From the UI layer, you call a method in a controller class within your BLL
    2. The method in the controller class implement all the necessary work
    3. All transactional resources involved in this method call can be handled implicitly in single transaction

    All transactional resources handling are done by the platform you are using (WCF or COM+). You do not have to worry about managing consistency of data.


    There are 2 approaches to do the implicit transactions...

    1. Using COM+ components. You need to implement the business logic layer within COM+ components. I call it "the old way"
    2. Better, implement your business logic within WCF services. This would be better since you have more control and more options in WCF than you have in COM+

    As a start, you can refer to this msdn article here. Also, here's a nice article on MSDN magazine here.

    Tuesday, October 9, 2007 8:37 AM