How do I set Savepoints for Linq to SQL and use “NO” ExecuteCommand?

I would advise simply not to. It isn't necessarily what you want to hear, but especially when mixing with TransactionScope save-points aren't a great idea TransactionScope s can be nested, but the first rollback dooms everything and the commit only happens at the outermost transaction.

I would advise simply not to. It isn't necessarily what you want to hear, but especially when mixing with TransactionScope, save-points aren't a great idea. TransactionScopes can be nested, but the first rollback dooms everything, and the commit only happens at the outermost transaction.In most scenarios I can think of, it is better to sanitise the data first.

You can (and should) also use contraints for a safety net, but if you hit that safety net, assume big problems and rollback everything. Example of nested transactions: public void DebitCreditAccount(int accountId, decimal amount, string reference) { using(var tran = new TransactionScope()) { // confirm account exists, and update estimated balance var acc = db.Accounts. Single(a => a.

Id == accountId); acc. BalanceEstimate += amount; // add a transaction (this defines the **real** balance) db. AccountTransactions.

InsertOnSubmit( new AccountTransaction { AccountId = accountId, Amount = amount, Code = amount >= 0? "C" : "D", Reference = reference }); db.SubmitChanges(); tran.Complete(); } } public void Transfer(int fromAccountId, int toAccountId, decimal amount, string reference) { using(var tran = new TransactionScope()) { DebitCreditAccount(fromAccountId, -amount, reference); DebitCreditAccount(toAccountId, amount, reference); tran.Complete(); } } In the above, DebitCreditAccount is atomic - we'll either add the account-transaction and update the estimated balance, or neither. If this is the only transaction, then it is committed at the end of this method.

However, in the Transfer method, we create another outer transaction; we'll either perform both DebitCreditAccount, or neither. Here, the inner tran.Complete() (in DebitCreditAccount) doesn't commit the db-transaction, as there is an outer transaction.It simply says "I'm happy". Conversely, though, if either of the inner transactions is aborted (Dispose() called without Complete()), then the outer transaction is rolled back immediately, and that transaction will refuse any additional work.

The outer transaction is committed only if no inner transaction was aborted, and Complete() is called on the outer transaction.

Thanks mark for answering! Could you give me a sample of a nested transaction? Thanks, Nik – nik Jun 17 '10 at 7:49 @nik - I'll edit to show that – Marc Gravell?

Jun 17 '10 at 9:12 @Mark Thanks for the code. If the both inner transactions don't work, I got an exception. Is this nested transaction nearly the same like a savepoint?

I would say no! I'm looking for a code that is the same like a Savepoint but without ExecuteCommand! Thanks, Nik – nik Jun 17 '10 at 11:55 @nik - not quite: you can't complete an outer-transaction after you roll-back an inner-transaction.

With a save-point you can do that. – Marc Gravell? Jun 17 '10 at 12:56 @Mark.... Thanks a lot for your super support!

Nik – nik Jun 17 '10 at 13:12.

With DataContext. ExecuteQuery, you send text into the database, just like ExecuteCommand - but you can get query results back from that text. IEnumerable results = ExecuteQuery(@" DECLARE @Table TABLE(Id int) INSERT INTO @Table SELECT {0} INSERT INTO @Table SELECT {1} SELECT Id FROM Table", 101, -101); IEnumerable results = ExecuteQuery( @" Rollback transaction SELECT * FROM Customer WHERE ID = {0}", myId).

Tanks, Nik – nik Jun 17 '10 at 7:50 thanks for example, but it is "only" a rollback! I need a rollback to savepoint! Or do I understand something miss?

Regards, Nik – nik Jun 17 '10 at 8:19 you put whatever sql you want in there, just like ExecuteCommand. – David B Jun 17 '10 at 12:03.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.