How do i set Savepoints for Linq to SQL and use "NO" ExecuteCommand?

Question

    TransactionScope TransactionABC = new TransactionScope();
    try
    {
        context.Connection.Open();
        {
            context.ExecuteCommand("insert into test (test) values (1)")
            context.SubmitChanges();
                    context.ExecuteCommand("savepoint test");

            context.ExecuteCommand("insert into test (test) values (2)")
            context.SubmitChanges();

                    context.ExecuteCommand("rollback to test");
            }
    TransactionABC.Complete();
    TransactionABC.Dispose();

            }
   catch (Exception ec)
    {
    MessageBox.Show(" ", ec.Message);
    }
   finally
    {
        context.Connection.Close();
    }

It works, but only with ExecuteCommand. I want to use a function, because i can't see what happens in the savepoint !

You **really** want to be `using` the transaction-scope. Otherwise when you get an exception you've left the transaction open - which is bad. It should be `using(var tran = new TransactionScope()) { /* code */ tran.Complete(); }` - then it is correctly disposed upon exit for both success and failure, with only success calling `Complete()`. — Marc Gravell, Jun 16 '10 at 12:11

Marc Gravell · Answer 1 · 2010-06-17T09:24:40.397

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 07:49
@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

Amy B · Answer 2 · 2010-06-17T08:01:39.913

0

How about ExecuteQuery?

With DataContext.ExecuteQuery, you send text into the database, just like ExecuteCommand - but you can get query results back from that text.

IEnumerable<int> results = ExecuteQuery<int>(@"
DECLARE @Table TABLE(Id int)
INSERT INTO @Table SELECT {0}
INSERT INTO @Table SELECT {1}
SELECT Id FROM Table", 101, -101);


IEnumerable<Customer> results = ExecuteQuery<Customer>( @"
Rollback transaction
SELECT *
FROM Customer
WHERE ID = {0}", myId);

edited Jun 17 '10 at 08:01

answered Jun 16 '10 at 12:05

Amy B

108,202
21
135
185

Hallo David, can you give me an idea, what you meen with ExecuteQuery ? Tanks, Nik – nik Jun 17 '10 at 07:50
Hello David, 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 08:19
you put whatever sql you want in there, just like ExecuteCommand. – Amy B Jun 17 '10 at 12:03

How do i set Savepoints for Linq to SQL and use "NO" ExecuteCommand?

2 Answers2