Transaction Scope ignored on SubmitUpdates

[colinbull]

Detected as part of #114

[pezipink]

I thought I fixed this here e2d7009

The connection was sometimes already open, and if you create a transaction scope when the connection is already open, the transaction does not get enlisted. It was certainly working on my machine after the fix, but I think Paul Blasucci was still having the problem ...

[colinbull]

I have done a really quick test on my work machine and it was still failing. Didn't do any investigation though. Just opened this a s a reminder really. Hoping to have sometime when work to look at these issues when work gets quieter after next week.

[pezipink]

Further to your comment in the other post about this code https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/SqlRuntime.DataContext.fs#L53 - this is wrong because we do not want to be creating connection until after the scope (which is handled in the implementation of SubmitUpdates) so we do need to move this around.

My other change should have still fixed it though. It might be reasonable to just move the scope into the area you highlighted, but I wasn't sure whether to generalise it as providers may not support the scope, might have a problem generating the commands before it needs the scope, etc

[colinbull]

Ahh, I just thought that the connection created in SubmitPendingChanges was the connection that was used to actually apply the updates, I didn't figure that the provider would manage the transaction / connection at all. I think we will be pretty safe moving the scope to there as Transactions are a pretty primitive thing w.r.t to DB's. As always though I'm ready to be proved wrong!!

[pezipink]

Yeah, ok, I am happy for it to be moved - but we need to find out why it isn't working first. At the moment it needlessly opens and closes the connection, but it does re-open it within the scope so it should be enlisting just fine....

[colinbull]

I cannot replicate this at all now. I'm begining to wonder weather this was a trigger thing in the DB I was testing against. Sadly I don't have access to that DB any more so can't verify but I'm gonna close this for now.

[mFingers]

FWIW, if you create a TransactionScope around your unit of work, this code will have the effect desired in the first requirement for #114 (create multiple related entities).

use scope = new TransactionScope()

let newInvoice = ctx.Dbo.Invoice.Create(customerId, invoiceDate)    
ctx.SubmitUpdates()

ctx.Dbo.InvoiceLine.Create(amount, description, newInvoice.InvoiceId)
ctx.SubmitUpdates()

scope.Complete()  //Everything is OK
scope.Dispose()    //Commit the transaction

This works because in ProcessUpdates, where the TransactionScope is declared, it will use the transaction already created, here.

Granted, this usability isn't as nice as what was proposed in #114 (here, you need to call SubmitUpdates twice instead of passing the related entities into the create function), but it works....

[pezipink]

Cool! :)