How do I reverse the query affect if any of the other statement fails among several statements?

Question

var sqlCommand= "@ DELETE FROM table1 WHERE id = @Id
                   DELETE FROM table2 WHERE id = @Id ";

var isDeleted = db.Database
                  .ExecuteSqlCommand(sqlCommand, new SqlParameter("@Id", Id)) > 0;

The problem is that if the 2nd or any other statement fails, then the preceding one remains intact i.e. already deleted.

I want it to reverse if any of it fails and return false.

Note: it has to be done the same way, not in the stored procedure.

Use `BEGIN TRANSACTION`, `COMMIT TRANSACTION` and `ROLLBACK TRANSACTION`. — Dai, Oct 20 '21 at 08:45
@FannyHunayn yes, transactions have support at multiple levels - you can work with transactions inside an individual command statement, or there is a higher-level transaction API that you can use *around* the ADO.NET commands - or in the case of EF, there's an overload of `ExecuteSqlCommand` that takes a `TransactionalBehavior` enum, to deal with it for you - specify `EnsureTransaction`; however, you'd need to define what you mean by "fails" - errors that *don't surface as errors* (for example, omitting a `where` clause) won't cause rollback — Marc Gravell, Oct 20 '21 at 08:49
"not in the stored procedure" - there is no stored procedure here... — Marc Gravell, Oct 20 '21 at 09:46

score 2 · Answer 1 · answered Oct 20 '21 at 08:50

2

In C# you can use TransactionScope like this:

using (TransactionScope t = new TransactionScope(TransactionScopeOption.Required))
{
   //do your work
   if(everything is ok)
     t.Complete();
}

answered Oct 20 '21 at 08:50

Alireza Ahmadi

8,579
5
15
42

Client-side transactions are not widely supported (EF Core doesn't support them at all, AFAIK), nor necessarily a good idea (e.g. what happens if your client process crashes or quits before it can do _either_ a rollback or a commit?) – Dai Oct 20 '21 at 08:53
1

@Dai EF core supports them: https://learn.microsoft.com/en-us/ef/core/saving/transactions#using-systemtransactions (why wouldn't it?). And it's not "client side" transaction in general sense, sql client will use normal database transaction for that. – Evk Oct 20 '21 at 09:20

score 2 · Answer 2 · edited Oct 20 '21 at 08:57

    public void YourMethod(Sqlconnection conn,int id)
    {
        conn.Open();
        using (SqlTransaction oTransaction = conn.BeginTransaction())
        {
            using (SqlCommand command = conn.CreateCommand())
            {
                string query =
                       "DELETE FROM table1 WHERE id=@Id;" +
                       "DELETE FROM table2 WHERE id=@Id;";
                command.CommandText = query;

                command.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int));

                command.Transaction = oTransaction;
                command.CommandType = CommandType.Text;

                try
                {
                    command.Parameters[0].Value = id;
                    command.ExecuteNonQuery();


                    //start transaction
                    oTransaction.Commit();
                }
                catch (Exception)
                {
                    //if the transaction fails then rollback the data
                    oTransaction.Rollback();
                    //notice the call method that there was an exception
                    throw;
                }
                finally
                {
                    // Close connection
                    conn.Close();
                }
            }
        }
    }

I note that `SqlTransaction` in ADO.NET does not support asynchronous commits/rollbacks (see https://stackoverflow.com/questions/31152954/is-it-possible-to-commit-rollback-sqltransaction-in-asynchronous ) - it's best to use `TRANSACTION` directly within T-SQL instead. — Dai, Oct 20 '21 at 08:54

score 2 · Accepted Answer · answered Oct 20 '21 at 08:53

2

EF makes this pretty easy for you:

var isDeleted = db.Database.ExecuteSqlCommand(
    TransactionalBehavior.EnsureTransaction, // <=== new
    sqlCommand, new SqlParameter("@Id", Id)) > 0;

answered Oct 20 '21 at 08:53

Marc Gravell

1,026,079
266
2,566
2,900

score 1 · Answer 4 · answered Oct 20 '21 at 09:20

It's better for many reasons to use BEGIN TRAN COMMIT rather than using a client-side transaction. Make sure to have XACT_ABORT ON to prevent dangling transactions in case of an exception

var sqlCommand= @"
SET XACT_ABORT ON;
BEGIN TRAN;

DELETE FROM table1 WHERE id = @Id;
DELETE FROM table2 WHERE id = @Id;

COMMIT TRAN;
";

var isDeleted = db.Database
                  .ExecuteSqlCommand(sqlCommand, new SqlParameter("@Id", Id)) > 0;

How do I reverse the query affect if any of the other statement fails among several statements?

4 Answers4