Here's a very simple example using SQL Server CE 3.5 SP1 where I attempt to use a new transaction inside an existing one.
using (var db = new MyDataContext(<connection string>))
using (var ts = new TransactionScope())
{
db.Thing.InsertOnSubmit(new Thing());
db.SubmitChanges();
using (var ts2 = new TransactionScope(TransactionScopeOption.RequiresNew))
{
db.Thing.InsertOnSubmit(new Thing());
db.SubmitChanges(); // exception here
ts2.Complete();
}
[... do more stuff ...]
ts.Complete();
}
This causes the error "The connection object can not be enlisted in transaction scope." on the second call to "db.SubmitChanges()"
This article states that SQL CE does not support two connections inside one transaction, but I only have one here (unless SubmitChanges() creates another one).
The idea above is that if the sub-transaction commits then it stays committed even if the outer transaction fails. To the best of my understanding, this is the intended use of RequiresNew.
This MSDN article claims that SQL CE supports nested transactions as such, hence my thinking that the above should be possible. Is it? If so, how do I modify the code to make this work?
Edit: While this MSDN article contradicts the other MSDN article and says nested transactions are not supported. Perhaps that is the problem?