LINQ and Transactions? How to rollback appropriately?
-
I have some new stuff that I'm writing for work and thought "Oh let's see if we can't show the value of LINQ with this project!" Well I'm having some problems and those problems majorly are as far as transactions (at least for now). Essentially the specs are something like this Start transaction if (Do some stuff for Table 1 != OK) { Rollback and leave } if (Do some stuff for Table 2 != OK) { RollBack and leave } if (Do some stuff for Table 3 != OK) { Rollback and leave } if (Do some stuff for Table n != OK) { Rollback and leave } End transaction (Commit) Now I dont' see anything as far as the documentation that insertonsubmit (formerly known as add) actually does anything that would cause SQL Server to return an error of some sort that you would if you called it with an Insert clause. And I for sure don't see anything that denotes itself as a rollback. My understanding if things is that the SubmitChanges is akin to calling Commit. And if that is so then you can't roll back after a commit. So given the above scenario I don't see how you can get around having to do a lot of work you need to do to set up for an insert (or delete or such) in Table N, when Table N-1 will have an error that will mean a rollback will need to be done. I hope that makes sense (at least a bit) and I'll be glad to clarify as much as possible. Christopher
-
I have some new stuff that I'm writing for work and thought "Oh let's see if we can't show the value of LINQ with this project!" Well I'm having some problems and those problems majorly are as far as transactions (at least for now). Essentially the specs are something like this Start transaction if (Do some stuff for Table 1 != OK) { Rollback and leave } if (Do some stuff for Table 2 != OK) { RollBack and leave } if (Do some stuff for Table 3 != OK) { Rollback and leave } if (Do some stuff for Table n != OK) { Rollback and leave } End transaction (Commit) Now I dont' see anything as far as the documentation that insertonsubmit (formerly known as add) actually does anything that would cause SQL Server to return an error of some sort that you would if you called it with an Insert clause. And I for sure don't see anything that denotes itself as a rollback. My understanding if things is that the SubmitChanges is akin to calling Commit. And if that is so then you can't roll back after a commit. So given the above scenario I don't see how you can get around having to do a lot of work you need to do to set up for an insert (or delete or such) in Table N, when Table N-1 will have an error that will mean a rollback will need to be done. I hope that makes sense (at least a bit) and I'll be glad to clarify as much as possible. Christopher
The SubmitChanges call in LINQ to SQL does create an implicit transaction to ensure that all changes are made, or if any fails, the whole thing fails. However, there are times when you might want to specifically have several SubmitChanges calls which need to be wrapped in an explicit transaction. This is simple enough, as the DataContext has a Transaction property and you can also use wider transactions - see this video: Mike Taulty's screencast on transactions[^]
'Howard
-
The SubmitChanges call in LINQ to SQL does create an implicit transaction to ensure that all changes are made, or if any fails, the whole thing fails. However, there are times when you might want to specifically have several SubmitChanges calls which need to be wrapped in an explicit transaction. This is simple enough, as the DataContext has a Transaction property and you can also use wider transactions - see this video: Mike Taulty's screencast on transactions[^]
'Howard
nice find , thanx