I have a set of objects:
Person, Address, Phone, Subscription, Inventory, InventoryItem, Item, Parameters, ParameterValues, Delivery, etc.
Subscription is defined as having a two different collections of Persons as well as one person. It also contains an Inventory and a Delivery.
Inventory has inventoryItems which in turn have parametervalues.
Person has Phones and Addresses
Delivery has an Inventory.
All of the objects that have child objects (subscription, person, delivery, inventory) implement using(transactionscope scope = new transactionscope()) {} in the update method. The most nested of these is the subscription's update method which ultimately triggers the update method for most of the objects I've mentioned above.
With the exception of delivery, this has been working as hoped. On a failure anywhere in the chain, the whole transaction is rolled back and I report the error to the UI. I recently implemented Delivery in the same way as all the rest. It is the last thing that is updated in the subscription update process.
The problem is that when I run it with Delivery executing it's update method, my db.ExecuteNonQuery(cmd) method call triggers the following error:
"The operation is not valid for the state of the transaction."
I'll paste in some code to give you an idea.
1 public static void Update(Subscription subscription)
2 {
3 Database db = DatabaseFactory.CreateDatabase();
4 DbCommand cmd = db.GetStoredProcCommand("subscriptionUpdate");
5
6 using (TransactionScope scope = new TransactionScope())
7 {
8 // create an inventory if one hasn't already been created because we need the inventory
9 // id for the subscription
10 if (subscription.Inventory.Id == 0)
11 {
12 try
13 {
14 subscription.Inventory.Create();
15 }
16 catch (Exception ex)
17 {
18 throw;
19 }
20 }
21 // create or update the customer as we need the customer's id for the subscription record
22 try
23 {
24 subscription.Customer.Update();
25 }
26 catch (Exception ex)
27 {
28 throw;
29 }
30
31
32 db.AddParameter(cmd, "@id", DbType.Int32, ParameterDirection.InputOutput, string.Empty
33 , DataRowVersion.Default, subscription.Id);
34 db.AddInParameter(cmd, "@customer", DbType.Int32, subscription.Customer.Id);
35 db.AddInParameter(cmd, "@inventory", DbType.Int32, subscription.Inventory.Id);
36 db.AddInParameter(cmd, "@plan", DbType.Int32, subscription.Plan.Id);
37
38 if(subscription.Schedule.Start==DateTime.MinValue) db.AddInParameter(cmd, "@start", DbType.DateTime, null);
39 else db.AddInParameter(cmd, "@start", DbType.DateTime, subscription.Schedule.Start);
40
41 if(subscription.Schedule.End==DateTime.MinValue) db.AddInParameter(cmd, "@end", DbType.DateTime, null);
42 else db.AddInParameter(cmd, "@end", DbType.DateTime, subscription.Schedule.End);
43
44 db.AddInParameter(cmd, "@dayOfWeek", DbType.String, subscription.Schedule.DayOfWeek.ToString());
45 db.AddInParameter(cmd, "@status", DbType.Int32, subscription.Status);
46 db.AddOutParameter(cmd, "@created", DbType.DateTime, 16);
47 db.AddOutParameter(cmd, "@modified", DbType.DateTime, 16);
48 db.AddOutParameter(cmd, "@result", DbType.Int32, 4);
49
50 try
51 {
52 db.ExecuteNonQuery(cmd);
53
54 if (int.Parse(db.GetParameterValue(cmd, "@result").ToString()) == 0)
55 {
56 subscription.Id = int.Parse(db.GetParameterValue(cmd, "@id").ToString());
57 subscription.Created = Convert.ToDateTime(db.GetParameterValue(cmd, "@created").ToString());
58 subscription.Modified = Convert.ToDateTime(db.GetParameterValue(cmd, "@modified").ToString());
59 }
60 else throw new Exception("Encountered an unexpected error updating the record.");
61
62 }
63 catch (Exception ex) { throw; }
64
65 try
66 {
67 foreach (Entity entity in subscription.Entities)
68 {
69 entity.Details.Update();
70 }
71 AttachEntities(subscription);
72 DeleteEntities(subscription);
73 }
74 catch (Exception ex) { throw; }
75
76 try
77 {
78 foreach (Payer payer in subscription.Payers)
79 {
80 payer.Details.Update();
81 }
82 AttachPayers(subscription);
83 DeletePayers(subscription);
84 }
85 catch (Exception ex) { throw; }
86
87 //update the delivery information
88 try
89 {
90 subscription.NextDelivery.Update();
91 AttachDelivery(subscription);
92 }
93 catch (Exception ex)
94 {
95 throw;
96 }
97
98 scope.Complete();
99 }
100 }
101