如果在几个语句中有任何其他语句失败，我如何逆转查询影响?

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;

问题是，如果第二个或任何其他语句失败，那么前面的语句保持不变，即已经删除。

我想让它反转，如果其中任何一个失败并返回false。

注意:必须以相同的方式完成，而不是在存储过程中。

Answer 1

EF让你很容易做到这一点:

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

Answer 2

在c#中可以这样使用TransactionScope:

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

Answer 3

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

Answer 4

由于许多原因，使用BEGIN TRANCOMMIT比使用客户端事务更好。在出现异常的情况下，确保使用XACT_ABORT ON来防止悬空事务

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;