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 !

Answer 1

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);

Answer 2

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.