How to use SubSonic Transaction with Parent Child Tables

Question

I have 2 tables (For simplicity many field where removed)

tblOrder - OrderId - OrderDate - UserId

tblOrderDetail - OrderDetailId - OrderId - ProductId - Qantity

I go ahead and create my object and I want to save everything in memory in one transaction because if something fail in the order details, I don't want the order to be saved. I use the following code, but I don't understand how to properly do it in one transaction.

Order newOrder = new Order();
OrderDetailCollection newOrderDetails = new OrderDetailCollection();
OrderDetail newOrderDetail;

newOrder.OrderDate = DateTime.Now();
newOrder.UserId = 1;

newOrderDetail = new OrderDetail();
// newOrderDetail.OrderId = newOrder.OrderId; // Can't do that yet, newOrder is not saved and OrderId is Null.
newOrderDetail.ProductId = 1;
newOrderDetail.Quantity = 25;

newOrderDetails.Add(newOrderDetail);

newOrderDetail = new OrderDetail();
// newOrderDetail.OrderId = newOrder.OrderId; // Can't do that yet, newOrder is not saved and OrderId is Null.
newOrderDetail.ProductId = 1;
newOrderDetail.Quantity = 25;

newOrderDetails.Add(newOrderDetail);

// Only two for test purpose.

Now I could easily do this

using (System.Transactions.TransactionScope ts = new TransactionScope()) {    
   using (SharedDbConnectionScope scs = new SharedDbConnectionScope()) { 
      try {
         newOrder.Save();

         foreach(OrderDetail anOrderDetail in newOrderDetails) {
            anOrderDetail.OrderId = newOrder.OrderId;
         }

         newOrderDetails.BatchSave();

         ts.Complete();
      }        
      catch (Exception ex) {
         //Do stuff with exception or throw it to caller
      }
   }
}

But this does not seem the most elegant solution:

1. Because I have to iterate through each items in the tables to set the ID and then save the batch.

2. Because on error, I loose an ID in my database. (i.e. on first save if an error occured, OrderId = 1 is lost and next OrderId will be 2)

3. Not scalable for very large transaction with another sub table. In this solution I built the exemple with an Order table and an Order Detail table but in my real application, there are two other levels unders OrderDetail OrderDetailCreditMember and OrderDetailCreditCompany where on any purchase, a tiny amount is credited to the member and to the company (multiple rows) so I can end up having a tree with 1 order that has 2 order details and each order details has 2 member credits and 3 company credits.

So does anyone have a better way of doing this.

Answer 1

To be honest I think you may be prematurely optimizing here.

1. Because I have to iterate through each items in the tables to set the ID and then save the batch.

You're iterating in memory you only have two SQL calls in your transaction, doesn't seem massively inefficient to me.

1. Because on error, I loose an ID in my database. (i.e. on first save if an error occured, OrderId = 1 is lost and next OrderId will be 2)

Is this really a problem, how often would you expect an error to occur, I'm assuming you're validating your objects before you try to save them so it really should be very rare that you get an error (e.g. can't connect to db) so I'm not convinced this is a problem. If you're getting a lot of errors that is a problem that needs to be fixed separately.

1. Not scalable for very large transaction with another sub table. In this solution I built the exemple with an Order table and an Order Detail table but in my real application, there are two other levels unders OrderDetail OrderDetailCreditMember and OrderDetailCreditCompany where on any purchase, a tiny amount is credited to the member and to the company (multiple rows) so I can end up having a tree with 1 order that has 2 order details and each order details has 2 member credits and 3 company credits.

Yes there could be scaling issues here but you're talking about 1 x 2 x 2 x 3 = 12 iterations of a loop in memory here and 3 SQL calls. That really shouldn't be too much of a problem unless you've got this happening very regularly. How often are you expecting this method to be called?