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

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.

CodePudding user response：

In C# you can use TransactionScope like this:

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

CodePudding user response：

EF makes this pretty easy for you:

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

CodePudding user response：

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

CodePudding user response：

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;